Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 6 June 2012

Dynamic Performance Tables\Views for Tuning

Dynamic Performance Tables: These table records current database activities. A DBA can query and create view on that table. It cannot be altered by the DBA. By default, they are available only to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM.
V$LOCK: Locks currently held/requested on the instance
V$LATCHHOLDER: Sessions/processes holding a latch
V$OPEN_CURSOR: Cursors opened by sessions on the instance
V$SESSION: Sessions currently connected to the instance
V$SESSION_WAIT: Different resources sessions are currently waiting for

V$LOCK
You should examine this view if you find session waiting for the wait event enqueue. In that case sequence of event could be:
1. Use V$LOCK to find the sessions holding the lock.
2. Use V$SESSION to find the SQL statements being executed by the sessions holding the lock and waiting for the lock.
3. Use V$SESSION_WAIT to find what the session holding the lock is blocked on.
4. Use V$SESSION to get more details about the program and user holding the lock.
Common Lock Type
TX: Row Transaction Lock
TM: DML Lock
ST: Space Transaction Lock
UL: User Defined Locks
Finding the session Holding the lock
SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
 WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
 ORDER BY id1,request
Finding the Statements being Executed by these Sessions just by Putting the SID number getting from above query:
SELECT sid, sql_hash_value
FROM V$SESSION
WHERE SID IN (1237, 1256, 1176, 938, 1634);
V$LATCHHOLDER
This view is useful to see if the session holding the latch is changing.
Finding the SQL Statement Executed by the Latch Holder
SELECT s.sql_hash_value, l.name
  FROM V$SESSION s, V$LATCHHOLDER l
WHERE s.sid = l.sid;
V$OPEN_CURSOR
This view lists all the cursors opened by the sessions. You can monitor the number of cursors opened by different sessions.
Finding the Session(s) that Executed a Statement
SELECT hash_value, buffer_gets, disk_reads
FROM V$SQLAREA
WHERE disk_reads > 1000000
ORDER BY buffer_gets DESC;
Finding Sessions That Have More Than 400 Cursors Open
SELECT sid, count(*)
FROM v$open_cursor
 GROUP BY sid HAVING COUNT(*) > 400
 ORDER BY count(*) desc;
V$SESSION
This is very important view. It has one row for every session connected to the database instance. The sessions include user sessions, as well as background processes like DBWR, LGWR, archiver. V$SESSION is basically an information view used for finding the SID or SADDR of a user. It has some column that changes dynamically such as SQL_HASH_VALUE, SQL_ADDRESS:
These identify the SQL statement currently being executed by the session. If NULL or 0, then the session is not executing any SQL statement. PREV_HASH_VALUE and PREV_ADDRESS identify the previous statement being executed by the session.
Finding your session:
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
  FROM V$SESSION
 WHERE audsid = userenv('SESSIONID');
Finding a session when machine is known:
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
  FROM V$SESSION
 WHERE terminal = 'IT-MSHAHID'  AND machine  = 'AL-SADHAN\IT-MSHAHID';
Finding the SQL Statement Currently Being Run by a Session:
SELECT sql_hash_value hash_value
  FROM V$SESSION WHERE sid = 13;
Wait for few second and execute the statement again and again you will see the same hash_value comes up again and again, indicating that the statement is being executed by the session. 
V$SESSION_WAIT
This view can be used as a starting point to find which direction to proceed in when a system is experiencing performance problems.It tells what every session in the database is currently waiting for (or the last event waited for by the session if it is not waiting for anything).
V$SESSION_WAIT has a row for every session connected to the instance. It indicates if the session is:
Using a resource
Waiting for a resource
Idle (waiting on one of the idle events)
Finding Current Waits on the System:
SELECT event,
       sum(decode(wait_time,0,1,0)) "Curr",
       sum(decode(wait_time,0,0,1)) "Prev",
       count(*)"Total"
  FROM v$session_wait
 GROUP BY event ORDER BY count(*);
Finding Current Waits on the System while the session is active
SELECT event,
       sum(decode(wait_Time,0,0,DECODE(s.status,'ACTIVE',1,0))) "Prev",
       sum(decode(wait_Time,0,1,DECODE(s.status,'ACTIVE',1,0))) "Curr",
       count(*) "Tot"
  FROM v$session s, v$session_wait w
 WHERE s.sid = w.sid
 GROUP BY event ORDER BY count(*);
References:

0 comments:

Post a Comment