oracle event相关操作.docx
- 文档编号:5343039
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:19
- 大小:22.91KB
oracle event相关操作.docx
《oracle event相关操作.docx》由会员分享,可在线阅读,更多相关《oracle event相关操作.docx(19页珍藏版)》请在冰豆网上搜索。
oracleevent相关操作
oracle事件及相关操作介绍
Introduction
Therearefourtypesofnumericevents
∙Immediatedumps
∙Conditionaldumps
∙Tracedumps
∙Eventsthatchangedatabasebehaviour
EveryeventhasanumberwhichisintheOracleerrormessagerangee.g.event10046isORA-10046
Eacheventhasoneormorelevelswhichcanbe
∙rangee.g.1to10
∙bitmaske.g.0x010x020x040x080x10
∙flage.g.0=off;1=on
∙identifiere.g.objectid,memoryaddressetc
Notethateventschangefromonereleasetoanother.Asexistingeventsbecomedeprecatedandthenobsolete,theeventnumberisfrequentlyreusedforanewevent.Notealsothatthemessagefilesometimesdoesnotreflecttheeventsinthecurrentrelease.
Manyeventschangethebehaviourofthedatabase.Sometestingeventsmaycausethedatabasetocrash.NeversetaneventonaproductiondatabasewithoutobtainingpermissionfromOraclesupport.Inaddition,neversetaneventonadevelopmentdatabasewithoutfirstmakingabackup.
EnablingEvents
Eventscanbeenabledatinstancelevelintheinit.orafileusing
event='eventtracenamecontextforever,levellevel';
Multipleeventscanbeenabledinoneoftwoways
1-Useacolontoseparatetheeventtexte.g.
event="10248tracenamecontextforever,level10:
10249tracenamecontextforever,level10"
2-Listeventsonconsecutivelinese.g.
event="10248tracenamecontextforever,level10"
event="10249tracenamecontextforever,level10"
NotethatinsomeversionsofOracle,thekeyword"event"mustbeinthesamecase(i.e.alwaysuppercaseoralwayslowercase).
EventscanalsobeenabledatinstancelevelusingtheALTERSYSTEMcommand
ALTERSYSTEMSETEVENTS
'eventtracenamecontextforever,levellevel';
Eventsaredisabledatinstancelevelusing
ALTERSYSTEMSETEVENTS
'eventtracenamecontextoff';
EventscanalsobeenabledatsessionlevelusingtheALTERSESSIONcommand
ALTERSESSIONSETEVENTS
'eventtracenamecontextforever,levellevel';
Eventsaredisabledatsessionlevelusing
ALTERSESSIONSETEVENTS
'eventtracenamecontextoff';
EventscanbeenabledinothersessionsusingORADEBUG
Toenableaneventinaprocessuse
ORADEBUGEVENTeventTRACENAMECONTEXTFOREVER,LEVELlevel
Forexampletosetevent10046,level12inOracleprocess8use
ORADEBUGSETORAPID8
ORADEBUGEVENT10046TRACENAMECONTEXTFOREVER,LEVEL12
Todisableaneventinaprocessuse
ORADEBUGEVENTeventTRACENAMECONTEXTOFF
Toenableaneventinasessionuse
ORADEBUGSESSION_EVENTeventTRACENAMECONTEXTFOREVER,LEVELlevel
Forexample
ORADEBUGSESSION_EVENT10046TRACENAMECONTEXTFOREVER,LEVEL12
Todisableaneventinasessionuse
ORADEBUGSESSION_EVENTeventTRACENAMECONTEXTOFF
EventscanbealsoenabledinothersessionsusingDBMS_SYSTEM.SETEV
TheSIDandtheserialnumberofthetargetsessionmustbeobtainedfromV$SESSION.
Forexampletoenableevent10046level8inasessionwithSID9andserialnumber29use
EXECUTEdbms_system.set_ev(9,29,10046,8,'');
Todisableevent10046inthesamesessionuse
EXECUTEdbms_system.set_ev(9,29,10046,0,'');
ListingAllEvents
Mosteventsarenumberedintherange10000to10999.Todumpalleventmessagesinthisrangeuse
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;
/
OnUnixsystemseventmessagesareintheformattedtextfile
$ORACLE_HOME/rdbms/mesg/oraus.msg
Toprintdetailedeventmessages(Unixonly)usethefollowingscript
event=10000
while[$event-ne10999]
do
event=`expr$event+1`
oerrora$event
done
ListingEnabledEvents
Tocheckwhicheventsareenabledinthecurrentsession
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;
/
EventReference
10013-MonitorTransactionRecovery
10015-DumpUndoSegmentHeaders
10032-DumpSortStatistics
10033-DumpSortIntermediateRunStatistics
10045-TraceFreeListManagementOperations
10046-EnableSQLStatementTrace
10053-DumpOptimizerDecisions
10060-DumpPredicates
10065-RestrictLibraryCacheOutputforStateObjectDumps
10079-DumpSQL*NetStatistics
10081-DumpHighWaterMarkChanges
10104-DumpHashJoinStatistics
10128-DumpPartitionPruningInformation
10200-DumpConsistentReads
10201-DumpConsistentReadUndoApplication
10220-DumpChangestoUndoHeader
10221-DumpUndoChanges
10224-DumpIndexBlockSplits/Deletes
10225-DumpChangestoDictionaryManagedExtents
10241-DumpRemoteSQLExecution
10246-TracePMONProcess
10248-TraceDispatcherProcesses
10249-TraceSharedServer(MTS)Processes
10270-DebugSharedCursors
10357-DebugDirectPath
10390-DumpParallelExecutionSlaveStatistics
10391-DumpParallelExecutionGranuleAllocation
10393-DumpParallelExecutionStatistics
10500-TraceSMONProcess
10608-TraceBitmapIndexCreation
10704-TraceEnqueues
10706-TraceGlobalEnqueueManipulation
10708-TraceRACBufferCache
10710-TraceBitmapIndexAccess
10711-TraceBitmapIndexMergeOperation
10712-TraceBitmapIndexOROperation
10713-TraceBitmapIndexANDOperation
10714-TraceBitmapIndexMINUSOperation
10715-TraceBitmapIndexConversiontoROWIDs
10716-TraceBitmapIndexCompress/Decompress
10717-TraceBitmapIndexCompaction
10719-TraceBitmapIndexDML
10730-TraceFineGrainedAccessPredicates
10731-TraceCURSORStatements
10928-TracePL/SQLExecution
10938-TracePL/SQLExecutionStatistics
Event10013-MonitorTransactionRecovery
Thiseventcanbeusedtotracetransactionrecoveryduringstartup
Forexample
ALTERSESSIONSETEVENTS
'10013tracenamecontextforever,level1';
Event10015-DumpUndoSegmentHeaders
Thiseventcanbeusedtodumpundosegmentheadersbeforeandaftertransactionrecovery
Forexample
ALTERSESSIONSETEVENTS
'10015tracenamecontextforever,level1';
Event10032-DumpSortStatistics
Thiseventcanbeusedtodumpsortstatistics.Level10isthemostdetailed
Forexample
ALTERSESSIONSETEVENTS
'10032tracenamecontextforever,level10';
Event10033-DumpSortIntermediateRunStatistics
Thiseventcanbeusedtodumpsortintermediaterunstatistics.Level10isthemostdetailed
Forexample
ALTERSESSIONSETEVENTS
'10033tracenamecontextforever,level10';
Event10045-TraceFreeListManagementOperations
Thiseventcanbeusedtotracefreelistmanagementoperations
Forexample
ALTERSESSIONSETEVENTS
'10045tracenamecontextforever,level1';
Event10046-EnableSQLStatementTrace
ThiseventcanbeusedtodumpSQLstatementsexecutedbyasessionwithexecutionplansandstatistics.Bindvariableandwaitstatisticscanoptionallybeincluded.Level12isthemostdetailed.
Forexample
ALTERSESSIONSETEVENTS
'10046tracenamecontextforever,level12';
Levelsare
Level
Action
1
PrintSQLstatements,executionplansandexecutionstatistics
4
Aslevel1plusbindvariables
8
Aslevel1pluswaitstatistics
12
Aslevel1plusbindvariablesandwaitstatistics
Event10053-DumpOptimizerDecisions
Thiseventcanbeusedtodumpthedecisionsmadebytheoptimizerwhenparsingastatement.Level1isthemostdetailed
Forexample
ALTERSESSIONSETEVENTS
'10053tracenamecontextforever,level1';
Levelsare
Level
Action
1
Printstatisticsandcomputations
2
Printcomputationsonly
Event10060-DumpPredicates
ThiseventcanbeusedtoforcetheoptimizertodumppredicatestoatableItisavailableinOracle7.1.3andabove,andwasstillworkinginOracle9.2.
Thiseventrequiresthefollowingtabletobecreatedintheschemaoftheuserparsingthestatement
CREATETABLEkkoipt_table
(
c1INTEGER,
c2VARCHAR2(80)
);
Toenablethiseventuse
ALTERSESSIONSETEVENTS
'10060tracenamecontextforever,level1';
Thisexampleusesthefollowingobject
CREATETABLEt1(c01NUMBER,c02NUMBER);
Withevent10060settolevel1,whenthefollowingstatementisexecutedforthefirsttime
SELECTc01FROMt1WHEREc02=0;
Itisparsedandtheresultswrittentokkoipt_table
Theresultscanbeselectedusingthestatement
SELECTc1,c2FROMkkoipt_tableORDERBYc1;
C1
C2
1
Table:
2
T1
3
frofand
4
"T1"."C02"=0
Thefollowingtablesummarisesthevariousoperationsthatcanbereportedbythisevent
Operation
Description
fptconst
Foldingconstants
fptrnum
RemoveROWNUMpredicates
fptwhr
RemoveallWHEREpredicatesexceptremainingROWNUMpredicates
frofkks(rowidlookup)
ROWIDlookup
frofkks[i](and-equallookup)
startkey
frofkke[i](and-equallookup)
endkey
froiand
index-onlypredicate
frofkksm[i](sort-merge)
sort-mergekey
frosand(sort-merge)
sort-mergepredicates
frojand(sort-merge)
joinpredicates
frofkks[i](indexstartkey)
indexstartkey
frofkke[i](indexstopkey)
indexendkey
frofand(hashpart)
tablepredicate(hash)
froiand(indexonlyfilter)
indexonlypredicate
frofand
tablepredicate
froutand
outerjoinpredicates
Event10065-RestrictLibraryCacheDumpOutputforStateObjectDumps
Theamountoflibrarycachedumpoutputforstateobjectdumpscanbelimitedusingevent10065
ALTERSESSIONSETEVENTS'10065tracenameconte
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle event相关操作 event 相关 操作
![提示](https://static.bdocx.com/images/bang_tan.gif)