Response-Time Analysis

http://www.oracle.com/technetwork/articles/schumacher-analysis-099313.html

set pagesize 10000
set linesize 10000
select *
from
(select sql_text,
        sql_id,
        elapsed_time,
        cpu_time,
        user_io_wait_time
from    sys.v_$sqlarea
order by 5 desc)
where rownum < 6;

SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME
————————————————————
Qry1 b6usrg82hwsa3 1110292039 891522468 96892007
Qry2 6mcpb06rctk0x 174919780 80836716 56868981
Qry2 bxywuzvtp6wjg 114802163 92518938 21746740
Qry4 1xcngwj6ypnkx 51460279 35175653 15906337

 select event,
         time_waited,
         owner,
         object_name,
         current_file#,
         current_block#
 from    sys.v_$active_session_history a,
         sys.dba_objects b
 where   sql_id = '&1' and
         a.current_obj# = b.object_id and
         time_waited <> 0;

Enter value for 1: 1xcngwj6ypnkx
old 9: where sql_id = ‘&1’ and
new 9: where sql_id = ‘1xcngwj6ypnkx’ and

EVENT TIME_WAITED OWNER OBJECT_NAME CURRENT_FILE# CURRENT_BLOCK#
———————————————————————–
db file sequential read 2067 SYS SCHEDULER$_JOB_RUN_DETAILS 2 125206
db file sequential read 8041 SYS SCHEDULER$_JOB_RUN_DETAILS 2 177884
db file sequential read 3153 SYS SCHEDULER$_JOB_RUN_DETAILS 2 178155
db file sequential read 2906 SYS I_SCHEDULER_JOB_RUN_DETAILS 2 172321
db file sequential read 2784 SYS I_SCHEDULER_JOB_RUN_DETAILS 2 164998
db file sequential read 954 SYS I_SCHEDULER_JOB_RUN_DETAILS 2 116224

6 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.