Extract of Beginning Performance Tuning By Arup Nanda - Artical from ORACLE MAGAZINE
http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html
Query for displaying sessions, session state, and events
select sid, state, event from v$session where username = 'ARUP';
SID STATE EVENT
————— ————————————————— ————————————————————————————
2832 WAITED KNOWN TIME SQL*Net message from client
3346 WAITING enq: TX - row lock contention
Query for displaying sessions, session state, and wait details
col "Description" format a50
select sid,decode(state, 'WAITING','Waiting','Working') state,
decode(state,'WAITING','So far '||seconds_in_wait,'Last waited '||wait_time/100)||' secs for '||event "Description"
from v$session where username = 'ARUP';
Output:
SID STATE Description
————— —————————— ———————————————————————————————————————————————————————
2832 Working Last waited 2029 secs for SQL*Net message from client
3346 Waiting So far 743 secs for enq: TX - row lock contention
4208 Waiting So far 5498 secs for SQL*Net message from client
You can find out the blocking session and instance by issuing the following SQL statement:
select blocking_session B_SID,blocking_instance B_Inst from v$session
where sid = 3346;
B_SID B_INST
—————— ———————
2832 1
Getting row lock information
select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
from v$session where sid = 3346;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————————————— —————————————— ———————————————— ——————————————
241876 1024 2307623 0
To get the object information:
select owner, object_type, object_name, data_object_id from dba_objects
where object_id = 241876;
OWNER OBJECT_TYPE OBJECT_NAME DATA_OBJECT_ID
————— ———————————— ———————————— ——————————————
ARUP TABLE T1 241877
Finding the row information
REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM 1. owner
REM 2. table name
REM 3. data_object_id
REM 4. relative file ID
REM 5. block ID
REM 6. row Number
REM
select *
from &1..&2
where rowid =
dbms_rowid.rowid_create (
rowid_type => 1,
object_number => &3,
relative_fno => &4,
block_number => &5,
row_number => &6
)
/
SQL> @rowinfo ARUP T1 241877 1024 2307623 0
COL1 C
————— —
1 x
Sessions from a specific user
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = 'ARUP';
SID OSUSER MACHINE TERMINAL SERVICE_NAME LOGON_TIME LAST_CALL_ET
————— —————— ——————— ———————— ———————————— —————————— ————————————
3346 oradb prodb1 pts/5 SYS$USERS 05-FEB-12 6848
2832 oradb prodb1 pts/6 SERV1 05-FEB-12 7616
4408 ANANDA ANLAP ANLAP ADHOC 05-FEB-12 0
Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/
Getting the SQL
Another key piece of performance tuning information is the SQL statement a session is executing, which will provide more insights into the workings of the session. The same V$SESSION view also shows the SQL statement information. The SQL_ID column in the V$SESSION view shows the ID of the last SQL statement executed. You can get the text of that SQL statement from the V$SQL view, using the SQL_ID value. Here is an example of how I have identified the SQL statement executed by the session that appears slow to the user.
select sql_id from v$session
where sid = 3089;
SQL_ID
—————————————————
g0uubmuvk4uax
set long 99999
select sql_fulltext
from v$sql
where sql_id = 'g0uubmuvk4uax';
SQL_FULLTEXT
————————————————————————————————————————
update t1 set col2 = 'y' where col1 = 1
Data Access Issues
I have used row-level locking as the cause of a slowdown in this article. Although locking-related contention is a very common cause, it is not the only cause of performance problems. Another major cause of contention is disk I/O. When a session retrieves data from the database data files on disk to the buffer cache, it has to wait until the disk sends the data. This wait shows up for that session as “db file sequential read” (for index scans) or “db file scattered read” (for full-table scans) in the EVENT column, as shown below:
select event from v$session where sid = 3011;
EVENT
—————————————————————————
db file sequential read
