|
|
马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有帐号？立即注册
x
对于IT经理来说，令他们喜欢的MySQL的简单性还有另一方面。MySQL可以运行的更快速。某些人或许会说MySQL缺少了一些人们想要的功能。oracle|参考
OracleInternalEvents:
Introduction:(简介)
有四品种型的Events:
Immediatedumps
Conditionaldumps
Tracedumps
Eventsthatchangedatabasebehaviour
每个事务都有一个号跟Oracle的毛病信息是一样的.如10046和ORA-10046
每个事务都有一个Level,能够是以下:
局限1到10
位标0x010x020x040x080x10
标识0=off,1=on
ID号工具ID(objectid),内存地点(memoryaddress)
要注重的是,Events在每个版本之间都有所改动.有一些存在的事务大概存在争议性大概已不成用了,常常这些事务号会由新的事务所替换失落.也要注重在以后的版本中messagefile纷歧定能够反应出Events.
良多Events城市影响数据库的举动,一些测试Events极有大概招致数据库DOWN失落.以是,在没有OracleSupport的条件下,最好不要在PRO体系上做Events操纵.DEV体系上假如要做Events最好先做个数据库的全备份.
EnablingEvents(Enable事务)
Events能够在Instance一级Enabled,次要是在INIT.ORA文件中做操纵:
event=eventtracenamecontextforever,levellevel;
(白色部分:event指事务号.level指定事务的级别)
一次能够Enable多个事务,能够用以下两种体例:
1.用一个冒号离隔
event="10248tracenamecontextforever,level10:10249tracenamecontextforever,level10"
2.两个Events分隔写
event="10248tracenamecontextforever,level10"
event="10249tracenamecontextforever,level10"#一些版本的Oracle，event要一样的巨细写
Events也能够在Instance一级用ALTERSYSTEM命令来Enable:
ALTERSYSTEMSETEVENTSeventtracenamecontextforever,levellevel;
在Instance一级用以下Disable
ALTERSYSTEMSETEVENTSeventtracenamecontextoff;
Events也能够在Session一级用ALTERSESSION命令来Enable:
ALTERSESSIONSETEVENTSeventtracenamecontextforever,levellevel;
在Session一级用以下命令Disable:
ALTERSESSIONSETEVENTSeventtracenamecontextoff;
Events在其他的Session用ORADEBUG来Enable:
在一个Process中完成Enable:
ORADEBUGEVENTeventTRACENAMECONTEXTFOREVER,LEVELlevel
在某个历程中Enable:
ORADEBUGSETORAPID8(PID历程号)
ORADEBUGEVENTeventTRACENAMECONTEXTFOREVER,LEVELlevel
以下命令Disable:
ORADEBUGEVENTeventTRACENAMECONTEXTOFF
在Session中完成Enable:
ORADEBUGSESSION_EVENTeventTRACENAMECONTEXTFOREVER,LEVELlevel
在Session中完成Disable:
ORADEBUGSESSION_EVENTeventTRACENAMECONTEXTOFF
Events也能够用DBMS_SYSTEM.SETEV包来完成Enable和Disable
(在做之前要先从V$session视图中取得SID和Serial#)
用以下体例:EXECUTEDBMS_SYSTEM.SET_EV(SID,Serial#,event,level,)
如EXECUTEdbms_system.set_ev(9,29,10046,8,);
要Disable则将level改成0,如:EXECUTEdbms_system.set_ev(9,29,10046,0,);
ListingAllEvents:(列出一切可用的Events)
年夜部分的Eventsnumber的局限都在10000到10999.能够用以下命令Dump出一切的信息
SETSERVEROUTPUTON
DECLARE
err_msgVARCHAR2(120);
BEGIN
dbms_output.enable(1000000);
FORerr_numIN10000..10999
LOOP
err_msg:=SQLERRM(-err_num);
IFerr_msgNOTLIKE%Message||err_num||notfound%THEN
dbms_output.put_line(err_msg);
ENDIF;
ENDLOOP;
END;
/
在UNIX体系下message文件在底下目次$ORACLE_HOME/rdbms/mesg/oraus.msg
在NT体系下message文件在底下目次$ORACLE_HOME/rdbms/mesg/oraus.msg
ListingEnabledEvents(列出EnabledEvents)
用以下命令列出在以后Session已EnabledEvents:
SETSERVEROUTPUTON
DECLARE
l_levelNUMBER;
BEGIN
FORl_eventIN10000..10999
LOOP
dbms_system.read_ev(l_event,l_level);
IFl_level>0THEN
dbms_output.put_line(Event||TO_CHAR(l_event)||
issetatlevel||TO_CHAR(l_level));
ENDIF;
ENDLOOP;
END;
/
经常使用Events的参考:(白色的为最经常使用的并且对DBA对照有效的Events)
Event10013-MonitorTransactionRecovery------在Startup时跟踪事件恢复
ALTERSESSIONSETEVENTS10013tracenamecontextforever,level1;
Event10015-DumpUndoSegmentHeaders----在事件恢复后做Dump回退段头信息
ALTERSESSIONSETEVENTS10015tracenamecontextforever,level1;
Event10032-DumpSortStatistics----Dump排序的统计信息,level10是最具体的
ALTERSESSIONSETEVENTS10032tracenamecontextforever,level10;
Event10033-DumpSortIntermediateRunStatistics—level10(不分明)
ALTERSESSIONSETEVENTS10033tracenamecontextforever,level10;
Event10045-TraceFreeListManagementOperations—跟踪Freelist
ALTERSESSIONSETEVENTS10045tracenamecontextforever,level1;
Event10046-EnableSQLStatementTrace---跟踪SQL,有实行企图,邦定变量和守候的统计信息,level12最具体
ALTERSESSIONSETEVENTS10046tracenamecontextforever,level12;
级别level参考以下图:
Level
Action
1
PrintSQLstatements,executionplansandexecutionstatistics
4
Aslevel1plusbindvariables
8
Aslevel1pluswaitstatistics
12
Aslevel1plusbindvariablesandwaitstatistics
Event10053-DumpOptimizerDecisions---在剖析SQL语句时,Dump出优化器所做的选择,级别level1最具体
ALTERSESSIONSETEVENTS10053tracenamecontextforever,level1;
参考level:
Level
Action
1
Printstatisticsandcomputations
2
Printcomputationsonly
Event10060-DumpPredicates---(参考http://www.juliandyke.com/)
Event10065-RestrictLibraryCacheDumpOutputforStateObjectDumps-(参考http://www.juliandyke.com/)
Event10079-DumpSQL*NetStatistics---DumpSQL*NeT的统计信息
ALTERSESSIONSETEVENTS10079tracenamecontextforever,level2;
Event10081-TraceHighWaterMarkChanges—跟踪HWM的改动
ALTERSESSIONSETEVENTS10081tracenamecontextforever,level1;
Event10104-DumpHashJoinStatistics—DumpHASHJOIN的统计信息,用level10
ALTERSESSIONSETEVENTS10104tracenamecontextforever,level10;
Event10128-DumpPartitionPruningInformation—Dump分区表信息
ALTERSESSIONSETEVENTS10128tracenamecontextforever,levellevel;
Level参考
Level
Action
0x0001
Dumppruningdescriptorforeachpartitionedobject
0x0002
Dumppartitioniterators
0x0004
Dumpoptimizerdecisionsaboutpartition-wisejoins
0x0008
DumpROWIDrangescanpruninginformation
在9.0.1大概前面的版本,在level2后还必要创建以下的表:
CREATETABLEkkpap_pruning
(
partition_countNUMBER,
iteratorVARCHAR2(32),
partition_levelVARCHAR2(32),
order_ptVARCHAR2(12),
call_timeVARCHAR2(12),
part#NUMBER,
subp#NUMBER,
abs#NUMBER
);
Event10200-DumpConsistentReads---Dump出分歧读的信息
ALTERSESSIONSETEVENTS10200tracenamecontextforever,level1;
Event10201-DumpConsistentReadUndoApplication---(不分明)
ALTERSESSIONSETEVENTS10201tracenamecontextforever,level1;
Event10220-DumpChangestoUndoHeader—Dump出Undo头信息的改动
ALTERSESSIONSETEVENTS10220tracenamecontextforever,level1;
Event10221-DumpUndoChanges—DumpUndo的改动
ALTERSESSIONSETEVENTS10221tracenamecontextforever,level7;
Event10224-DumpIndexBlockSplits/Deletes—Dump索引块的Split和Delete信息
ALTERSESSIONSETEVENTS10224tracenamecontextforever,level1;
Event10225-DumpChangestoDictionaryManagedExtents---Dump出在RowCache中,字典办理的Extents的改动
ALTERSESSIONSETEVENTS10225tracenamecontextforever,level1;
Event10231--设置在做全表扫描时跳过破坏的数据块（在做EXP时，假如有坏块，设置这个可使EXP导出时跳过坏块，使得部分数据能可使用）
ALTERSYSTEMSETEVENTS10231tracenamecontextforever,level10;
Event10241-DumpRemoteSQLExecution—Dump远程SQL语句的实行信息
ALTERSESSIONSETEVENTS10241tracenamecontextforever,level1;
Event10246-TracePMONProcess---只能在init.ora中做,不克不及用ALTERSYSTEM做
event="10246tracenamecontextforever,level1"
Event10248-TraceDispatcherProcesses---在init.ora中做(9iDump到udump目次中)
event="10248tracenamecontextforever,level10"
Event10249-TraceSharedServer(MTS)Processes---在init.ora中做(9iDump到udump目次中)
event="10249tracenamecontextforever,level10"
Event10270-DebugSharedCursors—(不分明)
event="10270tracenamecontextforever,level10"
Event10299-DebugPrefetching---(参考http://www.juliandyke.com/)
event="10299tracenamecontextforever,level1"
Event10357-DebugDirectPath---(参考http://www.juliandyke.com/)
ALTERSESSIONSETEVENTS10357tracenamecontextforever,level1;
Event10390-DumpParallelExecutionSlaveStatistics--(参考http://www.juliandyke.com/)
ALTERSESSIONSETEVENTS10390tracenamecontextforever,levellevel;
Event10391-DumpParallelExecutionGranuleAllocation---
(参考http://www.juliandyke.com/)
ALTERSESSIONSETEVENTS10391tracenamecontextforever,levellevel;
Event10393-DumpParallelExecutionStatistics--(参考http://www.juliandyke.com/)
ALTERSESSIONSETEVENTS10393tracenamecontextforever,level1;
Event10500-TraceSMONProcess--在init.ora中做
event="10500tracenamecontextforever,level1"
Event10608-TraceBitmapIndexCreation—跟踪二位图索引
ALTERSESSIONSETEVENTS10608tracenamecontextforever,level10;
Event10704-TraceEnqueues—跟踪行列
ALTERSESSIONSETEVENTS10704tracenamecontextforever,level1;
Event10706-TraceGlobalEnqueueManipulation-(参考http://www.juliandyke.com/)
ALTERSESSIONSETEVENTS10706tracenamecontextforever,level1;
Event10708-TraceRACBufferCache—跟踪RAC的BufferCache
ALTERSESSIONSETEVENTS10708tracenamecontextforever,level10;
Event10710-TraceBitmapIndexAccess--(参考http://www.juliandyke.com/)
ALTERSESSIONSETEVENTS10710tracenamecontextforever,level1;
Event10711-TraceBitmapIndexMergeOperation—
ALTERSESSIONSETEVENTS10711tracenamecontextforever,level1;
Event10712-TraceBitmapIndexOROperation—
ALTERSESSIONSETEVENTS10712tracenamecontextforever,level1;
Event10713-TraceBitmapIndexANDOperation—
ALTERSESSIONSETEVENTS10713tracenamecontextforever,level1;
Event10714-TraceBitmapIndexMINUSOperation—
ALTERSESSIONSETEVENTS10714tracenamecontextforever,level1;
Event10715-TraceBitmapIndexConversiontoROWIDsOperation—
ALTERSESSIONSETEVENTS10715tracenamecontextforever,level1;
Event10716-TraceBitmapIndexCompress/Decompress—
ALTERSESSIONSETEVENTS10716tracenamecontextforever,level1;
Event10717-TraceBitmapIndexCompaction—
ALTERSESSIONSETEVENTS10717tracenamecontextforever,level1;
Event10719-TraceBitmapIndexDML—
ALTERSESSIONSETEVENTS10719tracenamecontextforever,level1;
Event10730-TraceFineGrainedAccessPredicates—
ALTERSESSIONSETEVENTS10730tracenamecontextforever,level1;
Event10731-TraceCURSORStatements—
ALTERSESSIONSETEVENTS10731tracenamecontextforever,levellevel;
Levelsare:
Level
Action
1
Printparentqueryandsubquery
2
Printsubqueryonly
Event10928-TracePL/SQLExecution—
ALTERSESSIONSETEVENTS10928tracenamecontextforever,level1;
Event10938-DumpPL/SQLExecutionStatistics—
ALTERSESSIONSETEVENTS10938tracenamecontextforever,level1;
一些其他的Events：
ALTERSESSIONSETEVENTSimmediatetracenameflush_cache;--FlushtheBuffercache
材料来历http://www.juliandyke.com/
以上为团体在做DBA时的一些参考材料.
怀疑这些功能在MySQL5.0中的成熟性。充其量它们在MySQL中被支持的时间也就一年左右，而在MySQL学习教程其他关系型数据库中则已经存在了近10年的时间。 |
|
|Archiver|手机版|仓酷云
鄂ICP备14007578号-2
发表于 2015-1-16 22:33:50
收藏
转播
分享
淘帖
顶
踩
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜