subject         part

stay Oracle in ,SQL_TRACE What is it? ? What is the diagnostic event ?

          Answer section          


SQL_TRACE yes Oracle The information provided is used for SQL The means of tracking , It's a powerful diagnostic aid . In the daily diagnosis and solution of database problems ,SQL_TRACE It's a very common method . You can set SQL_TRACE by TRUE, However, it is generally not recommended to specify... Globally TRUE, It is only recommended to specify... At the session level . If it is set globally, the performance will be greatly affected .

( Two ) Diagnostic events

Oracle Provides a variety of diagnostic tools for the database , Diagnostic events (Event) It's one of the most practical ways , It enables DBA It can easily dump various database structures and track the occurrence of specific events .

You can get a list of all diagnostic events through the following script :

 2  ERR_MSG VARCHAR2(32767);
 5  FOR ERR_NUM IN 10000 .. 10999 LOOP
 7    IF ERR_MSG NOT LIKE '%Message ' || ERR_NUM || ' not found%' THEN
 9    END IF;

stay Linux The message files of diagnostic events in the system are placed in the directory :$ORACLE_HOME/rdbms/mesg/oraus.msg.oraus.msg A file belongs to a text file , You can directly open it to see .

Diagnostic events can be like normal ORA Use the same as mistakes oerr Order to inquire , As shown below :

 1[[email protected] ~]$ oerr ora 10046
 210046, 00000, "enable SQL statement timing"
 3// *Cause:
 4// *Action:
 5[[email protected] ~]$ oerr ora 10053
 610053, 00000, "CBO Enable optimizer trace"
 7// *Cause:
 8// *Action:
 9[[email protected] ~]$ oerr ora 10704
1010704, 00000, "Print out information about what enqueues are being obtained"
11// *Cause:  When enabled, prints out arguments to calls to ksqcmi and
12//          ksqlrl and the return values.
13// *Action: Level indicates details:
14//   Level: 1-4: print out basic info for ksqlrl, ksqcmi
15//          5-9: also print out stuff in callbacks:  ksqlac, ksqlop
16//          10+: also print out time for each line

There are two ways to set diagnostic events , One is in pfile Set the event in the parameter file , So the database is OPEN after , Will affect all conversations . The format is as follows :

1event="eventnumber trace name eventname [forever,] [level levelnumber] : ......."

By colon (:) Symbol , Multiple events can be set continuously , It can also be used continuously event To set up multiple events . Such as :

1event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"

Or write separately , Such as :

1event="10248 trace name context forever, level 10"
2event="10249 trace name context forever, level 10"

Another way is to use... During a conversation ALTER SESSION SET EVENTS command , It only affects the current session . The format is as follows :

1ALTER SESSION|SYSTEM SET EVENTS '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'

Through the English colon (:) Symbol , Multiple events can be set continuously , It can also be used continuously ALTER SESSION SET EVENTS To set up multiple events , Such as :

2           EVENTS='10325 trace name context forever, level 10','10015 trace name context forever, level 1' 
3           COMMENT='Debug tracing of control and rollback';

or :

1ALTER SYSTEM SET EVENTS='10325 trace name context forever, level 10:10015 trace name context forever, level 1';

Format specification :

l eventnumber To trigger dump The event number of , The event number can be Oracle Error number ( Trace the specified event when the corresponding error occurs ) or Oralce Internal event number , The internal event number is in 10000 To 10999 Between , Cannot be associated with immediate Keywords are used together .

l immediate Keyword means after the command is issued , Immediately put the specified structure dump Into the trace file , This keyword is only used in ALTER SESSION In the sentence , And not with eventnumber、forever Keywords are used together .

l trace name Key words ,trace name Second place 、 Three items , Besides them, the other determiners are for Oracle It's for the internal development team .

l eventname Refers to the name of the event , That is to do dump The actual structure name of . if eventname by context, It means tracking according to the internal event number .

l forever Keyword indicates that the event remains valid for the life of the instance or session , Cannot be associated with immediate Same use .

l level For event level keywords . But in dump Error stack (errorstack) There is no level .level Usually located in 1-10 Between (10046 Sometimes we use 12),10 It means dumping all the information about the event . For example, when dumping control files ,level 1 Represents the header of the dump control file , and level 10 Indicates the entire contents of the dump control file .

l levelnumber Represents the event level number , Generally from 1 To 10,1 Means only dump Structure header information ,10 Express dump All the information about the structure .

l  Dump the generated trace The file in user_dump_dest Initialize the location specified by the parameter .

l  Remove all diagnostic events (Event):


If diagnostic events are set (Event), So how to know which diagnostic events are set in the system (Event) Well ? If the event is pfile Set in file , Then you can use it “SHOW PARAMETER EVENT” Check it out. . If it's through “ALTER SYSTEM|SESSION” To set up , Then you can use the following SQL Check it out. :

 5  FOR I IN 10000 .. 99999 LOOP
 8      DBMS_OUTPUT.PUT_LINE('Event ' || TO_CHAR(I) || ' set at level ' ||TO_CHAR(EVENT_LEVEL));
 9    END IF;

however ,10046 and 10053 Events cannot be queried in this way , Only through oradebug To query , as follows :

[email protected] > oradebug setmypid
[email protected] > oradebug eventdump system
[email protected] > oradebug eventdump session

What you need to pay attention to when setting diagnostic events is , You can use “ALTER SYSTEM EVENTS”, You can also use “ALTER SYSTEM EVENT”, The differences are as follows 2 spot :

① EVENTS You can modify it dynamically , have access to “ALTER SESSION” or “ALTER SYSTEM” Set up , Only memory is affected, parameter files are not

② EVENT Can't dynamically modify , Only use “ALTER SYSTEM” Or set in the parameter file , You have to restart the library to take effect

in addition ,“ALTER SYSTEM” It will be recorded in the alarm log ,“ALTER SESSION” It will not be recorded in the alarm log .