Managing Sessions Waits
-----------------------
Active/Inactive sessions
------------------------
select USERNAME,SCHEMANAME,OSUSER,MACHINE,PROGRAM,MODULE,SID,SERIAL#,STATUS,SQL_ID,
LOGON_TIME,BLOCKING_SESSION,EVENT,WAIT_TIME,SECONDS_IN_WAIT,STATE
from v$session
where SCHEMANAME <> 'SYS'
ORDER BY STATUS,USERNAME
Overall Performance
-------------------
select A.SID,A.SERIAL#,A.USERNAME,A.SCHEMANAME,A.OSUSER,A.MACHINE,A.MODULE,
A.TERMINAL,A.PROGRAM,A.SQL_ID,A.STATUS,
A.LAST_CALL_ET LAST_CALL_STARTED_FINISHED_SEC,ROUND(A.LAST_CALL_ET/60,1) LAST_CALL_STARTED_FINISHED_MIN,ROUND(A.LAST_CALL_ET/(60*60),1) LAST_CALL_STARTED_FINISHED_HR, A.LOGON_TIME,A.EVENT,
B.CPU/100 CPU_TIME_SEC,B.PHYSICAL_READS PHYSICAL_READS_BLKS,B.PHYSICAL_READ_PCT,B.LOGICAL_READS LOGICAL_READS_BLKS,B.LOGICAL_READ_PCT,B.PGA_MEMORY,B.HARD_PARSES,B.SOFT_PARSES
from v$session a,V$SESSMETRIC b
where b.session_ID=a.sid
and b.session_serial_num=a.serial#
ORDER BY B.CPU DESC,B.PHYSICAL_READS,B.HARD_PARSES
SELECT A.SID, B.SCHEMANAME,B.OSUSER,B.PROGRAM,
A.BLOCK_GETS,A.CONSISTENT_GETS,A.PHYSICAL_READS,A.BLOCK_CHANGES,A.CONSISTENT_CHANGES
FROM V$SESS_IO A,V$SESSION B
WHERE A.SID=B.SID
ORDER BY A.PHYSICAL_READS DESC
SELECT
1,USERNAME,MACHINE,PROGRAM,COUNT(*) SESSIONS
FROM V$SESSION
GROUP BY USERNAME,MACHINE,PROGRAM
ORDER BY USERNAME,SESSIONS DESC;
The default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter.
If the value of PROCESSES changes, the default value of SESSIONS changes as well unless you override it with a specified value.
PROCESSES=150 (Both the SESSIONS and TRANSACTIONS parameters derive their default values from this parameter)
SESSIONS=170 =>(1.1 *PROCESSES) + 5
db_writer_processes=2 (1 OR number of CPUs divided by 8, whichever is greater)
The default value for DB_WRITER_PROCESSES is (CPU_COUNT+7)/8.
By default, Oracle starts one database writer process when the
instance starts. For multiuser and busy systems, you can have up to 20 database writer
processes (DBW0-9, DBWa-j) to improve performance. The parameter DB_WRITER_PROCESSES
determines the additional number of database writer processes to be started. Having more
DBWn processes than the number of CPUs is normally not beneficial.
Examining Session Waits
------------------------
Monitor session waits. If your application suddenly slows down, this is the first place you should look.
/* This shows waits */
SELECT S.USERNAME, S.OSUSER, S.MACHINE, S.SERIAL#, W.*
FROM V$SESSION_WAIT W, V$SESSION S
WHERE S.SID=W.SID
AND W.EVENT <> 'SQL*NET MESSAGE FROM CLIENT'
AND W.EVENT<>'RDBMS IPC MESSAGE'
ORDER BY S.SECONDS_IN_WAIT desc;
SELECT WAIT_CLASS, EVENT, TOTAL_WAITS AS WAITS,
ROUND (TIME_WAITED_MICRO / 1000) AS TOTAL_MS,
ROUND (TIME_WAITED_MICRO * 100 / SUM (TIME_WAITED_MICRO) OVER (), 2 ) AS PCT_TIME,
ROUND ((TIME_WAITED_MICRO / TOTAL_WAITS) / 1000, 2) AS AVG_MS
FROM V$SYSTEM_EVENT
WHERE WAIT_CLASS <> 'IDLE'
ORDER BY TIME_WAITED_MICRO DESC;
You can use the V$SESSION_WAIT view to get a quick idea about what a particular session is waiting for:
SELECT COUNT(*),EVENT,WAIT_CLASS FROM V$SESSION_WAIT
WHERE WAIT_CLASS <> 'Idle'
GROUP BY EVENT,WAIT_CLASS
select event, state, count(*)
from v$session_wait group by event, state
order by 3 desc # very important for DB Performance
SELECT COUNT(*),sum(seconds_in_wait),EVENT FROM V$SESSION_WAIT
WHERE WAIT_CLASS <> 'Idle'
GROUP BY EVENT
select SID,EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED/100 TIME_WAITED_SEC,TIME_WAITED_MICRO,AVERAGE_WAIT/100 AVG_WAIT_SEC,
MAX_WAIT, WAIT_CLASS
from v$session_event
where wait_class NOT IN ('Idle','Other')
order by total_waits desc
select event, total_waits, total_timeouts, (time_waited / 100) tot_time, (average_wait / 100) Avg_time from v$session_event
where wait_class<>'Idle'
order by total_waits desc
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session
WHERE type = 'USER' AND status = 'ACTIVE' --# Comment this line if want to see all wait events
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
SELECT sid,
username,
event,
total_waits,
100 * round((total_waits / sum_waits),2) pct_of_total_waits,
time_wait_sec,
total_timeouts,
average_wait_sec,
max_wait_sec
FROM
(SELECT a.event,
b.sid sid,
decode (b.username,null,c.name,b.username) username,
a.total_waits total_waits,
round((a.time_waited / 100),2) time_wait_sec,
a.total_timeouts total_timeouts,
round((average_wait / 100),2)
average_wait_sec,
round((a.max_wait / 100),2) max_wait_sec
FROM sys.v_$session_event a,
sys.v_$session b,
sys.v_$bgprocess c,
sys.v_$process d
WHERE a.event NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data from client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
AND a.event NOT LIKE 'DFS%'
AND a.event NOT LIKE 'KXFX%'
AND a.sid = b.sid
AND d.addr = b.paddr
AND c.paddr (+) = b.paddr
),
(select sum(total_waits) sum_waits
FROM sys.v_$session_event a,
sys.v_$session b
WHERE a.event NOT IN
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net more data from client',
'SQL*Net message to client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
AND a.event NOT LIKE 'DFS%'
AND a.event NOT LIKE 'KXFX%'
AND a.sid = b.sid)
order by 2,6 DESC
Shows active (in progress) transactions (very important)
----------------------------------------
SELECT SID, serial#, username, terminal, osuser,
t.start_time, r.NAME, t.used_ublk "ROLLB BLKS",
DECODE(t.SPACE, 'YES', 'SPACE TX',
DECODE(t.recursive, 'YES', 'RECURSIVE TX',
DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
WHERE t.xidusn = r.usn
AND t.ses_addr = s.saddr;
Identifying Open Uncommitted Transactions(very important)
-----------------------------------------
select s.INST_ID, s.SID, s.SERIAL#,s.USERNAME,s.MACHINE
from gv$transaction t , gv$session s
where t.INST_ID = s.INST_ID
and t.ses_addr = s.SADDR;
Cpu consumption by sessions(Add terminal in MENUWEB)(very important)
----------------------------
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
s.SID SID, s.serial#, p.spid SPID, s.status, s.username,
UPPER(DECODE(command,
1,'Create Table',2,'Insert',3,'Select',
4,'Create Cluster',5,'Alter Cluster',6,'Update',
7,'Delete', 8,'Drop Cluster', 9,'Create Index',
10,'Drop Index', 11,'Alter Index', 12,'Drop Table',
13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table',
16,'Drop Sequence', 17,'Grant', 18,'Revoke',
19,'Create Synonym', 20,'Drop Synonym', 21,'Create View',
22,'Drop View', 23,'Validate Index', 24,'Create Procedure',
25,'Alter Procedure', 26,'Lock Table', 27,'No Operation',
28,'Rename', 29,'Comment', 30,'Audit',
31,'NoAudit', 32,'Create Database Link', 33,'Drop db Link',
34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment',
37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace', 40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions',
43,'Alter User', 44,'Commit', 45,'Rollback',
46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction',
49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User',
52,'Create Role', 53,'Drop User', 54,'Drop Role',
55,'Set Role', 56,'Create Schema', 57,'Create Control File',
58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger',
61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index',
64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile',
67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure',
70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log',
73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot',
76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table',
86,'Truncate Cluster', 88,'Alter View', 91,'Create Function',
92,'Alter Function', 93,'Drop Function', 94,'Create Package',
95,'Alter Package', 96,'Drop Package', 97,'Create Package Body',
98,'Alter Package Body', 99,'Drop Package Body')) command,
ROUND((RATIO_TO_REPORT(t.VALUE) OVER ())*100, 2) AS CPU_PERCENT,
s.machine || '(' || s.osuser || ')' machine_osuser,
s.program, s.module,
t.VALUE CPU_value
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE s.SID = t.SID AND
p.addr = s.paddr
AND t.statistic# = n.statistic#
AND n.NAME = 'CPU used by this session'
AND t.VALUE > 0
--AND s.status = 'ACTIVE'
AND audsid > 0
ORDER BY t.VALUE DESC;
What's going on: ACTIVE SQL
----------------------------
Select sid, substr(program,1,19) prog, b.sql_id, child_number child,
plan_hash_value, executions execs,
(elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime,
sql_text
from v$session a, v$sql b
where status = 'ACTIVE'
and username is not null
and a.sql_id = b.sql_id
and a.sql_child_number = b.child_number
and sql_text not like 'select sid, substr(program,1,19) prog, b.sql_id%' -- don't show this
order by avg_etime desc
select sql_text from v$sqlarea
where hash_value in
(select sql_hash_value from v$session
where event like 'SQL*Net%');
Identifying these SQL statements and then tuning them to reduce the number of logical IOs required I have found to be the best way to tackle cache buffers chains waits.
If tuning is not possible then it is advisable to rebuild the relevant tables / indexes with a higher pctfree setting to reduce the amount of data which will be stored on each block.
SELECT HASH_VALUE, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, EXECUTIONS, SORTS,ADDRESS, SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > 10000000 OR DISK_READS > 1000000
ORDER BY BUFFER_GETS + 100*DISK_READS DESC
OR
Run the select bellow to find the top intensive I/O SQL statement:
SQL>SELECT EXECUTIONS, DISK_READS PHYSICAL_READS,FIRST_LOAD_TIME,SQL_TEXT
FROM V$SQLAREA
ORDER BY 2 DESC,1 DESC;
Current sessions running stored code
-------------------------------------
SELECT DECODE(o.kglobtyp, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13, 'CLASS') "TYPE",
o.kglnaown "OWNER", o.kglnaobj "NAME", s.indx "SID", s.ksuseser "SERIAL",
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE, ACTION
FROM sys.x$kglob o, sys.x$kglpn p, sys.x$ksuse s, v$session
WHERE o.inst_id = USERENV('Instance')
AND p.inst_id = USERENV('Instance')
AND s.inst_id = USERENV('Instance')
AND o.kglhdpmd = 2
--AND o.kglobtyp IN (7, 8, 9, 12, 13)
AND p.kglpnhdl = o.kglhdadr
AND s.addr = p.kglpnses
AND v$session.SID = s.indx
--AND o.kglobtyp = 13 --(JAVA CLASS)
ORDER BY 1, 2, 3;
Are There Any Long-Running Transactions?
------------------------------------------
1. Get the highest SQL hash value:
SELECT HASH_VALUE, EXECUTIONS,
ROUND (ELAPSED_TIME/1000000, 2) TOTAL_TIME,
ROUND (CPU_TIME/1000000, 2) CPU_SECONDS
FROM (SELECT * FROM V$SQL
ORDER BY CPU_TIME DESC);
2. Find the SQL of the above hash value:
The following
query uses the V$SQL_PLAN view to get you the execution plan for your longest-running SQL
statements:
SQL> SELECT * FROM V$SQL_PLAN WHERE hash_value = 238087931;
OR
SELECT * FROM V$SQL
WHERE HASH_VALUE='2588521346'
Which SQL spending most time doing I/O in the past 5 minutes
-------------------------------------------------------------
Troubleshooting I/O Related Waits (Doc ID 223117.1) (very very important)
How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)
SELECT ash.sql_id, COUNT(*)
FROM v$active_session_history ash, v$event_name evt
WHERE ash.sample_time > SYSDATE - 5/24/60
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id ORDER BY COUNT(*) DESC;
select ash.Session_ID,ash.session_serial#,ash.sql_ID,ash.event,ash.time_waited
FROM v$active_session_history ash
WHERE ash.sample_time > SYSDATE - 5/24/60
AND ash.session_state = 'WAITING'
AND ash.wait_class = 'User I/O'
Heavy I/O on datafiles
----------------------
select b.name Tablespace, a.name datafile,C.PHYRDS,C.PHYWRTS,C.PHYBLKRD,C.PHYBLKWRT
from v$datafile a,v$tablespace b,V$FILESTAT c
where a.ts#=b.ts#
and C.FILE#=A.FILE#
union all
select b.name Tablespace, a.name datafile,C.PHYRDS,C.PHYWRTS,C.PHYBLKRD,C.PHYBLKWRT
from v$tempfile a,v$tablespace b,v$tempstat c
where a.ts#=b.ts#
and C.FILE#=A.FILE#
order by 3 desc,4 desc,5 desc,6 desc
Watch Read_ratio and write_ratio
---------------------------------
select
name,
sum(phyrds) reads,
sum(readtim) read_time,
sum(readtim)/sum(phyrds) read_ratio,
sum(phywrts) writes,
sum(writetim) write_time,
sum(writetim)/sum(phywrts) write_ratio
from v$filestat a, v$datafile b
where a.file#=b.file#
group by name
order by name desc
Datafiles Disk I/O
-------------------
SELECT NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs
WHERE df.FILE# = fs.FILE#
ORDER BY phyrds DESC;
How I/O is spread per filesystem
SELECT filesystem, ROUND((RATIO_TO_REPORT(READS) OVER ())*100, 2) || '%' PERC_READS,
ROUND((RATIO_TO_REPORT(WRITES) OVER ())*100, 2) || '%' PERC_WRITES,
ROUND((RATIO_TO_REPORT(TOTAL) OVER ())*100, 2) || '%' PERC_TOTAL
FROM (SELECT filesystem, SUM(Physical_READS) READS, SUM(Physical_WRITES) WRITES, SUM(total) TOTAL
FROM (SELECT SUBSTR(NAME, 0, 25) filesystem, phyrds Physical_READS,
ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs
WHERE df.FILE# = fs.FILE#
ORDER BY phyrds DESC) A
GROUP BY filesystem) B
ORDER BY ROUND((RATIO_TO_REPORT(total) OVER ())*100, 2) DESC;
Tip: To see the filesystems correct experiment with the SUBSTR(NAME, 0, 25)
How I/O is spread for the datafiles of a specific tablespace
SELECT df.NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs, ts$ t
WHERE df.FILE# = fs.FILE#
AND df.ts# = t.ts#
AND t.NAME = 'TABLESPACE_NAME'
ORDER BY phyrds DESC;
Tablespace Disk I/O
---------------------
SELECT T.NAME, SUM(Physical_READS) Physical_READS,
ROUND((RATIO_TO_REPORT(SUM(Physical_READS)) OVER ())*100, 2) || '%' PERC_READS,
SUM(Physical_WRITES) Physical_WRITES,
ROUND((RATIO_TO_REPORT(SUM(Physical_WRITES)) OVER ())*100, 2) || '%' PERC_WRITES,
SUM(total) total, ROUND((RATIO_TO_REPORT(SUM(total)) OVER ())*100, 2) || '%' PERC_TOTAL
FROM (SELECT ts#, NAME, phyrds Physical_READS, phywrts Physical_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs
WHERE df.FILE# = fs.FILE#
ORDER BY phyrds DESC ) A, sys.ts$ T
WHERE A.ts# = T.ts#
GROUP BY T.NAME
ORDER BY Physical_READS DESC;
Which segments have top Logical I/O & Physical I/O
----------
Do you know which segments in your Oracle Database have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and helps to target tuning efforts on these segments:
SELECT ROWNUM AS Rank, Seg_Lio.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'logical reads'
ORDER BY St.VALUE DESC) Seg_Lio
WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_r.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Reads' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads'
ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_w.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Writes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical writes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE ROWNUM <= 10;
Which SQL are doing a lot of disk I/O
-------------------------------------
SELECT * FROM
(SELECT SUBSTR(sql_text,1,500) SQL,
ELAPSED_TIME, CPU_TIME,
disk_reads, executions,
disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE ( hash_value, address ) IN (
SELECT DISTINCT HASH_VALUE, address
FROM v$sql_plan
WHERE DISTRIBUTION IS NOT NULL )
AND disk_reads > 100
AND executions > 0
ORDER BY ELAPSED_TIME DESC)
WHERE ROWNUM <=30;
select * from V$SESS_IO
order by 4 desc
Buffer cache current I/O Status of the above query
--------------------------------------------------
select b.file_name,a.file#,a.cnt
from
(select file#,count(1) cnt from v$bh group by file#) a, dba_data_files b where a.file#=b.file_id
order by 3 desc
Find the objects of heavy I/O in above query
--------------------------------------------
select b.owner,b.object_name,a.cnt from
(select objd,count(1) cnt from v$bh group by objd) a
,dba_objects b
where b.data_object_id = a.objd and b.owner = 'NCSSLIVE'
order by 3 desc;
To find out how many sessions waited for some event
---------------------------------------------------
select session_id||','||session_serial# SID, n.name, wait_time, time_waited
from v$active_session_history a, v$event_name n
where n.event# = a.event#
order by 4 desc
This command tells you the name of the event and how much time was spent in waiting. If you want to drill down to a specific wait event, additional columns of ASH help you with that as well. For instance, if one of the events the sessions waited on is buffer busy wait, proper diagnosis must identify the segments on which the wait event occurred. You get that from the ASH view column CURRENT_OBJ#, which can then be joined with DBA_OBJECTS to get the segments in question.
select * from DBA_HIST_ACTIVE_SESS_HISTORY;
To find session history who changed data:
select * from DBA_HIST_ACTIVE_SESS_HISTORY --this machine changed/selected/inserted/deleted the data
where sql_opname='UPDATE' --this statement was run
and trunc(sample_time)='26-MAY-2014'--at this time
Most active SQL in the past 5 minutes
-------------------------------------
select sql_id, count(*),
100 * round(count(*) / sum(count(*)) over (), 3) pctload
from v$active_session_history
where sample_time > sysdate - 5/24/60
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc;
Monitor sessions performance over last hour
-------------------------------------------
SELECT DECODE (session_state, 'WAITING', event, NULL) event, session_state, COUNT(*),
SUM (time_waited) time_waited
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24 --1/24 shows 1 hour back
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state ORDER BY time_waited DESC;
Investigate one session running that module:
SELECT DECODE (session_state, 'WAITING', event, NULL) event, session_state, COUNT(*) counter ,
SUM (time_waited) time_waited
FROM v$active_session_history
WHERE module = 'ARXENV'
AND sample_time > SYSDATE - 1/24
AND session_id = 276
GROUP BY DECODE (session_state, 'WAITING', event, NULL), session_state ORDER BY time_waited DESC;
Join with v$sqlarea and include sql_stms for that session:
SELECT b.sql_text, DECODE (a.session_state, 'WAITING', a.event, NULL) "EVENT_NAME", a.session_state, COUNT(*) counter ,
SUM (a.time_waited) time_waited
FROM v$active_session_history a , V$SQLAREA b
WHERE a.sample_time > SYSDATE - 1/24
AND a.SQL_ID = b.SQL_ID
AND a.module = 'ARXENV'
AND a.session_id = 276
GROUP BY b.sql_text,
DECODE (a.session_state, 'WAITING', a.event, NULL), a.session_state
ORDER BY time_waited DESC;
What SQL is currently using the most resources?
SELECT active_session_history.user_id,
dba_users.username, sqlarea.sql_text,
SUM(active_session_history.wait_time + active_session_history.time_waited) total_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea, dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4;
What object is currently causing the highest resource waits?
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event, SUM(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history, dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY ttl_wait_time DESC;
This query is useful if you're running a monitoring tool like Spotlight, see a sudden peak in database activity, and want to know what's responsible.
Depending on where the activity peak is, you might want to know which SQL statements are returning the most rows; which have the most buffer gets; or Which have the most disk reads. Change the ORDER BY in the script below to suit your needs.
select s.username curr_user, s.machine, sql_text, executions,
round(decode(executions,0,0,(disk_reads/executions))) reads_per,
round(decode(executions,0,0,(buffer_gets/executions))) buff_per,
round(decode(executions,0,0,(rows_processed/executions))) rows_per,
first_load_time
from v$sqlarea v, dba_users d, v$session s
where d.user_id = v.parsing_user_id
and s.sql_address=v.address and s.sql_hash_value=v.hash_value
--and s.sid=&session_id
order by decode(executions,0,0,(rows_processed/executions)) desc;
select sql_ID,sql_text,
sorts,end_of_fetch_count fetches,first_load_time,last_active_time,parse_calls,disk_reads,user_IO_wait_time,PLSQL_exec_time,
executions,rows_processed,optimizer_cost,cpu_time,elapsed_time,SQL_profile,
to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576,
'9,999,999,990.00') as total_gets_per_exec_mb,
to_char((( disk_reads /executions) * 8192)/1048576,
'9,999,999,990.00') as disk_reads_per_exec_mb,
to_char((( buffer_gets /executions) * 8192)/1048576,
'9,999,999,990.00') as buffer_gets_per_exec_mb,
parsing_schema_name ,module
from v$sqlarea
where executions > 0
order by rows_processed,cpu_time desc
select sql_text,
(100/executions)*parse_calls parse_ratio,
parse_calls,
executions,
parsing_user_id
from v$sqlarea
where parse_calls > 1 and executions > 0
A lower parse ratio is the target for each statement. A parse ratio of 100 means that while the statement is theoretically identical, it is probably not using bind variables and so has to be re-parsed and checked each time.
SELECT sql_text, parse_calls, executions
FROM v$sqlarea
WHERE parse_calls > 100
AND kept_versions = 0
AND executions < 2*parse_calls;
select STATUS, wait_event, QC_SLAVE, PARALLEL_SESS, QC_SID,
SID, USERNAME, OSUSER, MACHINE, TERMINAL,
PROGRAM, LOGON_TIME, SERVER, SPID, PROCESS, SERIAL#,
TYPE, MODULE, ACTION, SCHEMANAME,
CURRENT_USER, TEMP_MB_USED, locks_blocking, locks_not_blocking, waiting_count, holding_count,
IO_block_gets, IO_consistent_gets, IO_physical_reads, IO_block_changes,
IO_consistent_changes,
to_char(PGA_USED_MEM_MB, 'FM99990.09') PGA_USED_MEM_MB,
to_char(PGA_ALLOC_MEM_MB, 'FM99990.09') PGA_ALLOC_MEM_MB,
to_char(PGA_MAX_MEM_MB, 'FM99990.09') PGA_MAX_MEM_MB,
INACTIVITY_DAYS, OPEN_CURSORS, PARAM_OPEN_CURSORS, Param_sess_cached_cursors,
to_char(PERC_OPEN_CURSORS, 'FM99990.09') PERC_OPEN_CURSORS,
to_char(open_cursors_and_cache_closed, 'FM99990.09') open_cursors_and_cache_closed,
PLAN_VARIATIONS,
SQL_HASH_VALUE, trim(to_char(SQL_ADDRESS||' ')) SQL_ADDRESS,
trim(to_char(saddr||' ')) saddr, KILL_SESSION,
START_TRACE, STOP_TRACE,
KILL_UNIX_PROCESS, undo_records, undo_start_time,
trim(STREAM_INFO || ' ' || STREAM_CAPTURE_INFO || ' ' || STREAM_APPLY_READER_INFO || ' ' || STREAM_APPLY_COORDINATOR) stream,
STREAM_CAPTURE_PROBLEM stream_status from (
select a.*
, (select event from v$session_wait where sid = a.sid and not event in ( --select event from perfstat.stats$idle_event
'AQ Proxy Cleanup Wait',
'ASM background timer',
'DBRM Logical Idle Wait',
'DIAG idle wait',
'EMON idle wait',
'EMON slave idle wait',
'IORM Scheduler Slave Idle Wait',
'KSV master wait',
'LNS ASYNC archive log',
'LNS ASYNC dest activation',
'LNS ASYNC end of log',
'LogMiner: client waiting for transaction',
'LogMiner: generic process sleep',
'LogMiner: reader waiting for more redo',
'LogMiner: slave waiting for activate message',
'LogMiner: waiting for processes to soft detach',
'LogMiner: wakeup event for builder',
'LogMiner: wakeup event for preparer',
'LogMiner: wakeup event for reader',
'MRP redo arrival',
'Null event',
'PING',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Par Recov Execute',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'PX Deque wait',
'PX Idle Wait',
'Queue Monitor Shutdown Wait',
'Queue Monitor Slave Wait',
'Queue Monitor Wait',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'STREAMS apply coord waiting for slave message',
'STREAMS apply slave idle wait',
'STREAMS apply slave waiting for coord message',
'STREAMS capture process filter callback wait for ruleset',
'STREAMS fetch slave waiting for txns',
'STREAMS waiting for subscribers to catch up',
'Space Manager: slave idle wait',
'Streams AQ: RAC qmn coordinator idle wait',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: delete acknowledged messages',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams capture: waiting for archive log',
'Streams fetch slave: waiting for txns',
'class slave wait',
'client message',
'cmon timer',
'dispatcher timer',
'fbar timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'knlqdeq',
'lock manager wait for remote message',
'master wait',
'null event',
'parallel query dequeue',
'parallel recovery coordinator waits for slave cleanup',
'parallel recovery slave idle wait',
'parallel recovery slave next change',
'parallel recovery slave wait for change',
'pipe get',
'pmon timer',
'pool server timer',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'virtual circuit status',
'wait for activate message',
'wait for unread message on broadcast channel',
'wakeup event for builder',
'wakeup event for preparer',
'wakeup event for reader',
'wakeup time manager',
'watchdog main loop'
)) wait_event
, (select decode(a.sid, m.sid, '************', '') from v$mystat m where rownum=1) current_user
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', TRUE); end;') start_trace
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', FALSE); end;') stop_trace
, ('ALTER SYSTEM KILL|DISCONNECT SESSION ''' || sid ||','||serial#||''' IMMEDIATE/*spid='||spid||'*/') kill_session
, ('kill -9 '||spid) kill_unix_process
, (select count(*) from v$lock where sid = a.sid and block != 0) locks_blocking
, (select count(*) from v$lock where sid = a.sid and block = 0) locks_not_blocking
, (open_cursors * 100 / param_open_cursors) perc_open_cursors
--Is the session using a query with a variated plan?
, (select count(*) from (
select hash_value, count(*) from (
select hash_value, plan_hash_value from v$sql group by hash_value, plan_hash_value
) group by hash_value having count(*) > 1
) where hash_value = sql_hash_value) plan_variations
-- R O L L B A C K
, (select decode(sort_space_mb, null, 'No temporary used',
sort_space_mb || 'MB, ' || to_char(sort_space_mb * 100 / (sum(bytes_used)/1024/1024), 'FM90.99')||'% of used, '||
to_char(sort_space_mb * 100 / (sum(bytes_used + bytes_free)/1024/1024), 'FM90.99')||'% of ' || (sum(bytes_used + bytes_free)/1024/1024) ||'MB')
from v$temp_space_header) temp_MB_used
from (
select
s.status, s.saddr
, nvl((select decode(px.qcinst_id,NULL, 'Master(QC)', 'Slave') from v$px_session px where px.sid = s.sid), 'Master') QC_Slave
, (select decode(count(*), 0, 0, count(*)-1) from v$px_session px where px.qcsid=(select qcsid from v$px_session where sid =s.sid)) parallel_sess
, (select decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) from v$px_session px where px.sid = s.sid) QC_SID
, (select spid from v$process where addr = paddr) spid
, (select value from v$parameter where name = 'open_cursors') param_open_cursors
, (select value from v$parameter where name = 'session_cached_cursors') Param_sess_cached_cursors
, s.sid, decode(s.username, null, 'Server process', s.username) username
, osuser, machine, terminal, program
, logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server, process, s.serial#, type, s.module, s.action,
resource_consumer_group,
sql_hash_value, sql_address, schemaname
--ORACLE 8 MUST COMMENT ALL begin
, (select pga_used_mem/1024/1024 from v$process where addr = paddr) pga_used_mem_MB
, (select pga_alloc_mem/1024/1024 from v$process where addr = paddr) pga_alloc_mem_MB
, (select pga_max_mem/1024/1024 from v$process where addr = paddr) pga_max_mem_MB
--ORACLE 8 MUST COMMENT All end
, (select sum(V$SORT_USAGE.blocks *
--(select value from v$parameter where upper(name) = 'DB_BLOCK_SIZE')
--ORACLE 8 MUST COMMENT THIS LINE BELOW AND ENABLE THE ABOVE
dba_tablespaces.block_size
)/1024/1024 from V$SORT_USAGE, dba_tablespaces
where dba_tablespaces.tablespace_name = V$SORT_USAGE.tablespace and V$SORT_USAGE.session_addr = s.saddr) sort_space_MB
, (select a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic#
and b.name = 'opened cursors current' and a.sid = s.sid) open_cursors
, (select count(*) from v$open_cursor where sid=s.sid) open_cursors_and_cache_closed
, V$SESS_IO.block_gets IO_block_gets, V$SESS_IO.consistent_gets IO_consistent_gets
, V$SESS_IO.physical_reads IO_physical_reads, V$SESS_IO.block_changes IO_block_changes
, V$SESS_IO.consistent_changes IO_consistent_changes
, (select count(*) from DBA_WAITERS where waiting_session = s.sid) waiting_count
, (select count(*) from DBA_WAITERS where holding_session = s.sid) holding_count
, (select sum(used_urec) from V$TRANSACTION where s.saddr = ses_addr) undo_records
, (select min(start_time) from V$TRANSACTION where s.saddr = ses_addr) undo_start_time
/* stream */
, (select decode(module, 'STREAMS', module||', ' || action, null) from v$session where sid=s.sid) STREAM_INFO
/* stream capture */
, (select state || ', [' || CAPTURE_NAME || ' capt. name], [' || ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) || '/' ||
((SYSDATE - CAPTURE_TIME)*86400 || ' latency sec]') from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_INFO
, (select decode(state, 'CAPTURING CHANGES', 0, 1) from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_PROBLEM
, (select '[Apply status: ' || DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') || ']' ||
'[Program: ' || SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) || ']'
from V$STREAMS_APPLY_READER, DBA_APPLY ap
where SID = s.SID AND SERIAL# = s.SERIAL# and V$STREAMS_APPLY_READER.APPLY_NAME = ap.APPLY_NAME)
STREAM_APPLY_READER_INFO
, (SELECT 'Apply Coordinator: ' || apply_name || ', ' || c.state || ', process: ' || substr(s.program,instr(s.program,'(')+1,4)
FROM v$streams_apply_coordinator c where c.sid = s.sid and c.serial# = s.serial#)
STREAM_APPLY_COORDINATOR
from
v$session s, V$SESS_IO
--where not username is null --avoid Oracle processes
where V$SESS_IO.sid (+)= s.sid
) a
) b
Script to Find CPU/Memory/Ligical IO/Physical IO Load
------------------------------------------------------
declare
cursor c1 is select version
from v$instance;
cursor c2 is
select
host_name
, instance_name
, to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime
, to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime
from v$instance;
cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6 br="">cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;
cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;
cursor c7 is select 'top physical i/o process' category, sid,
username, total_user_io amt_used,
round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
sum(value) total_user_io
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name in ('physical reads', 'physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name)
order by 3 desc),
(select sum(value) total_io
from v$statname c, v$sesstat a
where a.statistic# = c.statistic#
and c.name in ('physical reads', 'physical writes',
'physical reads direct',
'physical reads direct (lob)',
'physical writes direct',
'physical writes direct (lob)'))
where rownum < 2
union all
select 'top logical i/o process', sid, username,
total_user_io amt_used,
round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
sum(value) total_user_io
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name)
order by 3 desc),
(select sum(value) total_io
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid,
username, total_user_mem,
round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username,
sum(value) total_user_mem
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name)
order by 3 desc),
(select sum(value) total_mem
from v$statname c, v$sesstat a
where a.statistic# = c.statistic#
and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username,
total_user_cpu,
round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username,
sum(value) total_user_cpu
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name = 'CPU used by this session'
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name)
order by 3 desc),
(select sum(value) total_cpu
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name = 'CPU used by this session')
where rownum < 2;
cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('-----------------');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('----------');
for rec in c2
loop
dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('-------------');
for rec in c5
loop
dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('-----------------');
for rec in c6
loop
dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('-----------------------');
for rec in c2 loop
dbms_output.put_line( rec.starttime );
end loop;
dbms_output.put_line( chr(13) );
for b in
(select total, active, inactive, system, killed
from
(select count(*) total from v$session)
, (select count(*) system from v$session where username is null)
, (select count(*) active from v$session where status = 'ACTIVE' and username is not null)
, (select count(*) inactive from v$session where status = 'INACTIVE')
, (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('---------------');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line( 'Sessions Waiting' );
dbms_output.put_line( chr(13) );
dbms_output.put_line('Count Event Name');
dbms_output.put_line('----- -----------------------------------------------------');
for rec in c4
loop
dbms_output.put_line(rec.cnt||' '||rec.event);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('----- -----------------------------------------------------');
dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('---------------');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;
dbms_output.put_line('------------------------------------------------------------------');
dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('---------------');
for rec in c8
loop
dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);
dbms_output.put_line ('---------------');
end loop;
end;
/
SELECT sid, seq#, event, wait_time, p1, p2, p3
2 FROM v$session_wait_history
3 WHERE sid = 154
4 ORDER BY seq#;
SELECT sid, state, event, seconds_in_wait siw,
2 sql_address, sql_hash_value hash_value, p1, p2, p3
3 FROM v$session
4 WHERE sid = 154;
SELECT sid, blocking_session, username,
2 event, seconds_in_wait siw
3 FROM v$session
4 WHERE blocking_session_status = 'VALID';
Session wise Load
------------------
select event, sid, seq#,
wait_time,
seconds_in_wait,
/* state,
p1text, p1, p1raw,
6 p2text, p2, p2raw,
7 p3text, p3, p3raw
8 p1text || ' = ' || p1 parm1,
9 p2text || ' = ' || p2 parm2,
10 p3text || ' = ' || p3 parm3
11 */
decode( p1text, null, null,
p1text || ' = ' || p1 ) ||
decode( p2text, null, null,
', ' || p2text || ' = ' || p2 ) ||
decode( p3text, null, null,
', ' || p3text || ' = ' || p3 )
parameters
from v$session_wait
where
--event not in ( 'pmon timer', 'rdbms ipc message', 'smon timer','WMON goes to sleep', 'SQL*Net message from client' )
WAIT_CLASS<>'Idle'
order by event, p1, p2
select s.sid, s.value, n.name
from v$sesstat s, v$statname n
where n.statistic# = s.statistic#
and s.value <> 0
and s.sid like '&sid'
and n.name like '%'
and s.statistic# like '%'
order by sid, n.name
Which segments have top Logical I/O & Physical I/O
---------------------------------------------------
Do you know which segments in your Oracle Database have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and helps to target tuning efforts on these segments:
SELECT ROWNUM AS Rank, Seg_Lio.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'logical reads'
ORDER BY St.VALUE DESC) Seg_Lio
WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_r.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Reads' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads'
ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_w.* FROM
(SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Writes' AS Unit
FROM V$segment_Statistics St
WHERE St.Statistic_Name = 'physical writes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE ROWNUM <= 10;
Switch to different schema
----------------------------
If you have to constantly use tables owned by a different schema and there aren’t any synonyms on
the table, you may be forced to use the schema qualifier in front of every table name. For example,
you might need to use scott.emp to refer to the emp table owned by the user scott. To avoid this,
you can simply use the ALTER SESSION SET SCHEMA statement, as shown here:
SQL> CONNECT mansoor@beta
SQL> ALTER SESSION SET CURRENT_SCHEMA = NCSSLIVE;
SQL> SELECT * FROM SECURITY;
The use of the ALTER SESSION statement here doesn’t confer any automatic object privileges. In
order to query the emp table without any schema qualifier, as shown in the preceding example, the
user must have SELECT privileges on the emp table.
