Oracle wait event-1

Oracle wait event-1. Knowledge about waiting events 1. The concept of waiting events from the source is introduced from ORACLE 7.0.12, with approximately 100 waiting events. In ORACLE 8.0, this number has increased to approximately 150, with approximately 220 events in ORACLE 8I and about 400 waiting events in ORACLE 9IR2. in ORACLE 10GR2, there are about 874 waiting events. Although there may be different numbers of waiting events for different versions and component installation, these waiting events can be obtained by querying the V $ EVENT_NAME view. 2. Waiting event category: wait events can be divided into two types: IDLE wait events (IDLE) and non-IDLE wait events (NON-IDLE) 1), IDLE wait events: Refers to oracle is waiting for a job, when diagnosing and optimizing databases, you do not need to pay too much attention to this part of wait events. 2) Non-idle wait events: events that specifically target oracle database activities, wait events that occur when database tasks or applications are running, these wait events require attention and research when adjusting the database. 3. view the field structure SQL> desc v $ event_name; Name Null? Type reserved -------- invalid EVENT # NUMBER EVENT_ID number name VARCHAR2 (64) PARAMETER1 VARCHAR2 (64) PARAMETER2 VARCHAR2 (64) PARAMETER3 VARCHAR2 (64) WAIT_CLASS_ID NUMBER WAIT_CLASS # NUMBER WAIT_CLASS VARCHAR2 (64) 4. view the total number of wait events SQL> select count (*) from v $ event_name; COUNT (*) ---------- 874 5. view the wait event category SQL> select WAIT_CLASS #, WAIT_CLASS, wait_cia SS_ID, count (*) as "count" from v $ event_name group by WAIT_CLASS #, WAIT_CLASS, WAIT_CLASS_IDorder by WAIT_CLASS #; 2 3 4 5 WAIT_CLASS # WAIT_CLASS WAIT_CLASS_ID count limit ----------------------------- ---------- 0 Other 1893977003 590 1 Application 4217450380 12 2 Configuration3290255840 23 3 limit 46 4 Concurrency 3875070507 24 5 Commit 3386400367 1 6 Idle 2723168908 62 7 Network 2000153315 26 8 User I/O 1740759767 17 9 System I/O 4108307767 2410 Scheduler 2396326234 2 WAIT_CLASS # WAIT_CLASS WAIT_CLASS_IDcount ----------- begin------------ ---------- 11 Cluster 3871361733 47 12 rows selected. 6. Related query view: V $ SESSION: indicates the opening of database activities Starting from V $ SESSION_WAIT: The view records active sessions in real time, which is the current information. V $ SESSION_WAIT_HISTORY: indicates the Enhanced memory of session_wait and records the activity sessions for nearly 10 times. V $ SQLTEXT: When a database bottleneck occurs, you can find the sessions waiting for resources from session_wait, the session SID can be used together with the V $ SESSION and V $ SQLSTEXT views to capture the SQL statements being executed by those sessions. V $ ACTION_SESSION_HISTORY: it is the core of ASE, used to record the historical wait information of the Active session. This part is recorded in the memory, and the expected value is to record the session of an hour. V $ SYSTEM_EVENT: Because the session records dynamic information and is related to the session lifecycle, it does not record historical information, therefore, oracle provides the view V $ SYSTEM_EVENT to record the summary information of all wait events of the database since it was started. Through this view, you can quickly obtain the overall running status of the database.