Oracle provides a variety of diagnostic tools for RDBMS, diagnostic events (event) is one of which is common, easy to use, which makes DBA to dump database various structures and track specific events. Using these events is not a very highly learned, for example, 10046 this event you must have used, as follows, I will discuss some discuss around Event, don't tell me, Qiuyb@21cn.com This article mainly talks about the method of opening diagnostic events.
First, EVENT usual format and classification
1, usually the format is as follows:
Event = "
<
Tracking Projects>
2, EVENT classification
Diagnostic events can be substantially divided into four categories:
a. Dump events: They are primarily used to dump some of Oracle's structure, such as dump control files, data file headers, and so on.
b. Capture the class: They are used to capture some Error events, such as capturing some RDBMS information when ORA-04031 occurs, to determine the problem of BUG or other causes.
C. Changing the Performing Way Event: They are used to change the execution path of some Oracle internal code, such as setting 10269 will make the SMON process does not merge those spaces.
d. Tracking class events: This is used to obtain some tracking information for SQL tuning, etc., the most typical is 10046, will track SQL.
3, Michael R. AULT summarizes the following table:
Track category
Event name
Action
Name
Tracking project
Range limited
Dump event
Immediate
Trace
"Name"
Blockdump
Redohdr
FILE_HDRS
Controlf
SYSTEMSTATE
Level block #
Level 10
Level 10
Level 10
Level 10
Capture event
Error Number
Trace
"Name"
Error Stack
ProcessState
HEAPDUMP
FOREVE
Off
Level NR
Change the execution path
Even code Corresponding to Path
Trace
"Name"
Context
Forever or
Level 10
Track class event
10046
Trace
"Name"
Context
FOREVER
Level n
Off
4, other descriptions:
a. If immediate is placed in the first instructions, there is an unconditional event, that is, the command is sent to the trace file.
b. TRACE NAME is located in the second, three, except for other qualitations outside it is used for Oracle internal development groups.
C. Level is typically located between 1-10 (10046 Sometimes 12), 10 means that all information is dumped. For example, when a dump control file, Level1 represents a dump control file header, while Level 10 indicates that the dump control file is all content.
d. Dumps The generated trace file is initialized at the location specified by the user_dump_dest initialization parameter.
Second, talk about the problem of setting up
You can set the required events in init.ora, which will be tracked for all session periods, or you can set up event trace with methods such as ALTER SEXSSION SET Event, which will open event traces that are performing sessions.
1. Set the method of tracking events in Init.ora
a. grammar
Event = "EVENT Syntax |, Level N |: EVENT Syntax |, Level N | ..."
b. Example Event = "10231 TRACE NAME CONTEXT ForeVER, Level 10 '
C. Multiple events can be set this:
Event = "/
10231 Trace Name Context ForeVer, Level 10: /
10232 TRACE Name Context Forever, Level 10 "
2, ways passing through Alter Session / System Set Events
For example, everyone will understand
EXAMPLE:
Alter Session Set Events 'Immediate Trace Name Controlf Level 10'
Alter Session Set Events' Immediate Trace Name Blockdump Level 112511416; (*)
There is also such a statement in the version of Oracle8X and above:
ALTER SYSTEM DUMP DATAFILE 13 Block 15; implemented features and (*) are similar.
3, use dbms_system.set_ev method
a. Overhead
DBMS_SYSTEM.SET_EV (
Si binary_integer,
Se binary_integer
,
EV binary_integer,
Le binary_integer,
Nm binary_integer;
Si: SID in V $ Session
SE: SERIAL # in V $ Session
EV: Event to set
Le: Set the level of events
NM: Name
b. For example, take 10046 as an example
SQL> Execute Sys.dbms_system.set_ev (SID, Serial #, 10046, 12, ');
4. Use ORADEBUG to set the method of diagnostic events
Also give an example, you will understand:
a. Find spid
SQL> Select Username, SID, Serial #, Paddr from v $ session where username = 'qiuyb'
UserName Sid Serial # Paddr
-------------------------------------------------- --------
HRB3 265 910 C000000000084435AD8
SQL> SELECT Addr, PID, SPID from V $ Process Where Addr = C000000084435AD8 '
AddR Pid SPID
------------------------------------------
C000000084435AD8 91 4835
b. Set the event, take 10046 as an example
SQLPlus / NOLOG
SQL> Connect / as sysdba;
SQL> Oradebug SetOspid 4835
SQL> ORADEBUG UNLIMIT - Do not limit the size of the dump file
SQL> ORADebug Event 10046 Trace Name Context Forever, Level 12 - Settings Event SQL Tracking
SQL> ORADEBUG EVENT 10046 Trace Name Context Off - Close Tracking
Be careful not to use ORADUG to track the Oracle's SMON, PMON and other processes, and improper operations may kill these rear atcens and cause downtime.
Third, you may have problems
1, how do I know which EVENT is set in the system? Answer:
a. If your event is set in Init.ora, you can use it.
SQL> Show parameter Event;
Come to view
b. Michael R.Ault gives SQL
ServerOutput on size 1000000
Declare
Event_level Number;
Begin
For i Iin 10000..10999 loop
sys.dbms_system.read_ev (i, event_level);
IF (Event_level> 0) THEN
DBMS_OUTPUT.PUT_LINE ('Event' || to_Char (i) || 'set at level' ||
TO_CHAR (Event_level));
END IF;
End loop;
END;
/
2, how to set the diagnostic event in Oracle9i's use of SPFILE?
Reply:
Simple, the alter system command can complete
Alter system set es Event = '10046 Trace Name Context Forever, Level 12' Scope = SPFILE;
After restarting, it takes effect.
3, broken, my 9i set the diagnostic event, can't get up, how to do ora-02194?
Reply:
Then you must write a certain item when you use the alter system, such as writing the context to conteTxt, you can do the following solution:
a. Generate Pfile from SPFILE
SQL> CREATE PFILE from SPFILE;
File created.
b. Edit Pfile to correct the error
Change ... * .event = '10046 Trace Name Contxt Forever, Level 12'
-to- * .event = '10046 Trace Name Context Forever, Level 12'
C. Start with Pfile
SQL> Startup Pfile = / .....
d. Regenerate SPFile.
SQL> CREATE SPFILE from Pfile;
File created.
4. What are the commonly used diagnostic events, how to set up and function?
These will mention it in the following article.
Article reference documentation:
1, "Using Oracle Events" written by Michael R.Ault
2, Metalink Note: 168042.1
(to be continued)
