Be sure you understand any problem from the users viewpoint - spend time with the users to ensure the real issue is looked into. Eg: If two statements which are used by the most common user transactions have poor execution plans this may be reported as "everything is slow all the time" but closer inspection may reveal that certain operations give perfectly acceptable response times.
a. Idle waiting for something to do b. Running code - ie: using CPU or on a run queue c. Waiting, either: i. for some resource to become available or ii. for an activity to complete that it has requestedTo clarify this here are some examples:
a. Idle User shadow process is waiting for a data packet from the user telling them what to do next or providing information to allow them to continue. b. Running code The process expects to be on a run queue for a CPU. Oracle itself does not know if it is on-CPU or just on a run queue. c. i. Waiting for a resource to become available Like an enqueue (lock) or a latch c. ii. Waiting for an activity to complete Like an IO read request, or waiting for LGWR to write redo to disk.
The times recorded by Oracle only have a resolution of 1/100th of a second (10mS). This is usually sufficient to help determine where time is going. As of Oracle9i some times are available to microsecond accuracy.
State Notes... ~~~~~ ~~~~~~~~ IDLE Waiting for 'SQL*Net message from client'. Receives a SQL*Net packet requesting 'parse/execute' of a statement ON CPU decodes the SQL*Net packet. WAITING Waits for 'latch free' to obtain the a 'library cache' latch Gets the latch. ON CPU Scans for the SQL statement in the shared pool, finds a match, frees latch , sets up links to the shared cursor etc.. & begins to execute. WAITING Waits for 'db file sequential read' as we need a block which is not in the buffer cache. Ie: Waiting for an IO to complete. ON CPU Block read has completed so execution can continue. Constructs a SQL*Net packet to send back to the user containing the first row of data. WAITING Waits on 'SQL*Net message to client' for an acknowledgement that the SQL*Net packet was reliably delivered. IDLE Waits on 'SQL*Net message from client' for the next thing to do.If we can apportion timings to each of the above steps then one can get an indication of what tuning is possible. This applies at individual statement level, session level or system-wide - Oracle can provide useful information at any of these levels:
At an Instant in time: V$SESSION V$SESSION_WAIT Statement level: SQL_TRACE or DBMS_SUPPORT.SET_TRACE output Session level: SQL_TRACE or DBMS_SUPPORT.SET_TRACE output V$SESSION_EVENT V$SESSTAT System-wide: V$SYSTEM_EVENT V$SYSSTAT
SELECT sid, status FROM V$SESSION;shows whether each session is considered ACTIVE or INACTIVE. An INACTIVE session is generally waiting for a request from the client. In most cases this will appear in V$SESSION_WAIT as waiting for "SQL*Net message from client" with a WAIT_TIME of ZERO.
For ACTIVE sessions
SELECT sid, wait_time, event FROM V$SESSION_WAIT;shows the state of each session at an instant in time:
WAIT_TIME Meaning 0 Session is currently WAITING for the specified EVENT (See #1 below) !=0 Session is currently on CPU (but see the notes below). The EVENT listed is the last thing this session waited for. WAIT_TIME then indicates how long the session waited for this last event: -1 The session waited a short time for the listed event -2 We do not know how long we waited (Used when TIMED_STATISTICS=false) >0 Actual time waited in 1/100ths of a second. #1 Note: In Oracle9i 9.0.1 V$SESSION_WAIT may show WAIT_TIME of 0 when the session is not actually waiting but is on CPU due to Bug:2117360. V$SESSION_WAIT.STATE will show a value other than "WAITING" in this case.Typically there will only be a few sessions "on CPU" at any point in time. If you run the above statement the session running the SELECT should show as "on CPU" (WAIT_TIME!=0).
NB:If an application uses multiple sessions or performs session switching then it is possible for WAIT_TIME to be non-zero while the session is actually INACTIVE. This occurs when the session is not the current session for the client and so V$SESSION_WAIT shows the last thing that the session waited for but the session itself is now INACTIVE. You may see this if:
The V$SESSION_EVENT and V$SYSTEM_EVENT views show the total time spent waiting for each wait-event as session and system-wide levels.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-1134357/，如需转载，请注明出处，否则将追究法律责任。