All about Time Model in Oracle Database
Time Model top 7 Statistics
COL STAT_NAME FORMAT A43
COL TIME_MINUTES FORMAT 999,999,990.99
SELECT STAT_NAME,
ROUND(VALUE / 1e6 / 60, 2) AS TIME_MINUTES
FROM V$SYS_TIME_MODEL
WHERE VALUE <> 0
AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;
COL TIME_MINUTES FORMAT 999,999,990.99
SELECT STAT_NAME,
ROUND(VALUE / 1e6 / 60, 2) AS TIME_MINUTES
FROM V$SYS_TIME_MODEL
WHERE VALUE <> 0
AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;
Time Model by %
COL STAT_NAME FORMAT A43
SELECT STAT_NAME,ROUND(VALUE / 1e6 / 60, 2) AS TIME_MINUTES
, ROUND(VALUE/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT
FROM V$SYS_TIME_MODEL
WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;
SELECT STAT_NAME,ROUND(VALUE / 1e6 / 60, 2) AS TIME_MINUTES
, ROUND(VALUE/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT
FROM V$SYS_TIME_MODEL
WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')
ORDER BY VALUE DESC
FETCH FIRST 7 ROWS ONLY;
Time Model in Structure Level
col STAT_NAME format a60
SELECT LPAD(' ', 2*level-1)||STAT_NAME STAT_NAME,
TRUNC(VALUE/1000000,2) SECONDS
FROM (
SELECT 0 id, 9 pid, null STAT_NAME, null value FROM dual union
SELECT DECODE(STAT_NAME,'DB time',10) ID,
DECODE(STAT_NAME,'DB time',0) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'DB time' union
SELECT DECODE(STAT_NAME,'DB CPU',20) ID,
DECODE(STAT_NAME,'DB CPU',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'DB CPU' union
SELECT DECODE(STAT_NAME,'connection management call elapsed time',21) ID,
DECODE(STAT_NAME,'connection management call elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'connection management call elapsed time' union
SELECT DECODE(STAT_NAME,'sequence load elapsed time',22) ID,
DECODE(STAT_NAME,'sequence load elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'sequence load elapsed time' union
SELECT DECODE(STAT_NAME,'sql execute elapsed time',23) ID,
DECODE(STAT_NAME,'sql execute elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'sql execute elapsed time' union
SELECT DECODE(STAT_NAME,'parse time elapsed',24) ID,
DECODE(STAT_NAME,'parse time elapsed',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'parse time elapsed' union
SELECT DECODE(STAT_NAME,'hard parse elapsed time',30) ID,
DECODE(STAT_NAME,'hard parse elapsed time',24) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'hard parse elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',40) ID,
DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',30) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'hard parse (sharing criteria) elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',50) ID,
DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',40) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'hard parse (bind mismatch) elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse elapsed time',31) ID,
DECODE(STAT_NAME,'failed parse elapsed time',24) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'failed parse elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',41) ID,
DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',31) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'failed parse (out of shared memory) elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL execution elapsed time',25) ID,
DECODE(STAT_NAME,'PL/SQL execution elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'PL/SQL execution elapsed time' union
SELECT DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',26) ID,
DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'inbound PL/SQL rpc elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL compilation elapsed time',27) ID,
DECODE(STAT_NAME,'PL/SQL compilation elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'PL/SQL compilation elapsed time' union
SELECT DECODE(STAT_NAME,'Java execution elapsed time',28) ID,
DECODE(STAT_NAME,'Java execution elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'Java execution elapsed time' union
SELECT DECODE(STAT_NAME,'repeated bind elapsed time',29) ID,
DECODE(STAT_NAME,'repeated bind elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'repeated bind elapsed time' union
SELECT DECODE(STAT_NAME,'background elapsed time',60) ID,
DECODE(STAT_NAME,'background elapsed time',0) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'background elapsed time' union
SELECT DECODE(STAT_NAME,'background cpu time',61) ID,
DECODE(STAT_NAME,'background cpu time',60) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'background cpu time' union
SELECT DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',62) ID,
DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',61) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'RMAN cpu time (backup/restore)')
CONNECT BY PRIOR id = pid START WITH id = 0;
SELECT LPAD(' ', 2*level-1)||STAT_NAME STAT_NAME,
TRUNC(VALUE/1000000,2) SECONDS
FROM (
SELECT 0 id, 9 pid, null STAT_NAME, null value FROM dual union
SELECT DECODE(STAT_NAME,'DB time',10) ID,
DECODE(STAT_NAME,'DB time',0) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'DB time' union
SELECT DECODE(STAT_NAME,'DB CPU',20) ID,
DECODE(STAT_NAME,'DB CPU',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'DB CPU' union
SELECT DECODE(STAT_NAME,'connection management call elapsed time',21) ID,
DECODE(STAT_NAME,'connection management call elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'connection management call elapsed time' union
SELECT DECODE(STAT_NAME,'sequence load elapsed time',22) ID,
DECODE(STAT_NAME,'sequence load elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'sequence load elapsed time' union
SELECT DECODE(STAT_NAME,'sql execute elapsed time',23) ID,
DECODE(STAT_NAME,'sql execute elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'sql execute elapsed time' union
SELECT DECODE(STAT_NAME,'parse time elapsed',24) ID,
DECODE(STAT_NAME,'parse time elapsed',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'parse time elapsed' union
SELECT DECODE(STAT_NAME,'hard parse elapsed time',30) ID,
DECODE(STAT_NAME,'hard parse elapsed time',24) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'hard parse elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',40) ID,
DECODE(STAT_NAME,'hard parse (sharing criteria) elapsed time',30) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'hard parse (sharing criteria) elapsed time' union
SELECT DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',50) ID,
DECODE(STAT_NAME,'hard parse (bind mismatch) elapsed time',40) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'hard parse (bind mismatch) elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse elapsed time',31) ID,
DECODE(STAT_NAME,'failed parse elapsed time',24) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'failed parse elapsed time' union
SELECT DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',41) ID,
DECODE(STAT_NAME,'failed parse (out of shared memory) elapsed time',31) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'failed parse (out of shared memory) elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL execution elapsed time',25) ID,
DECODE(STAT_NAME,'PL/SQL execution elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'PL/SQL execution elapsed time' union
SELECT DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',26) ID,
DECODE(STAT_NAME,'inbound PL/SQL rpc elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'inbound PL/SQL rpc elapsed time' union
SELECT DECODE(STAT_NAME,'PL/SQL compilation elapsed time',27) ID,
DECODE(STAT_NAME,'PL/SQL compilation elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'PL/SQL compilation elapsed time' union
SELECT DECODE(STAT_NAME,'Java execution elapsed time',28) ID,
DECODE(STAT_NAME,'Java execution elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'Java execution elapsed time' union
SELECT DECODE(STAT_NAME,'repeated bind elapsed time',29) ID,
DECODE(STAT_NAME,'repeated bind elapsed time',10) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'repeated bind elapsed time' union
SELECT DECODE(STAT_NAME,'background elapsed time',60) ID,
DECODE(STAT_NAME,'background elapsed time',0) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'background elapsed time' union
SELECT DECODE(STAT_NAME,'background cpu time',61) ID,
DECODE(STAT_NAME,'background cpu time',60) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'background cpu time' union
SELECT DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',62) ID,
DECODE(STAT_NAME,'RMAN cpu time (backup/restore)',61) PID , STAT_NAME, VALUE
FROM v$sys_time_model
WHERE STAT_NAME = 'RMAN cpu time (backup/restore)')
CONNECT BY PRIOR id = pid START WITH id = 0;
Time Mode for sessions
set lines 200
set pages 100
col SID format 999999
col USERNAME format a15
col DB_MIN format 999,999.99
col WAIT_MIN format 999,999.99
col WAITPCT format 999.99
col ONCPU_PCT format 99.99
SELECT
M.SID,
S.USERNAME,
ROUND(VALUE/60000000, 2) AS DB_MIN,
ROUND(WAITTIME/60000000, 2) AS WAIT_MIN,
ROUND((WAITTIME/VALUE)*100, 2) AS WAITPCT,
ROUND(((VALUE-WAITTIME)/VALUE)*100, 2) AS ONCPU_PCT
FROM (
SELECT
SID,
STAT_NAME,
VALUE,
VALUE - (LEAD(VALUE,1) OVER(PARTITION BY SID ORDER BY STAT_NAME DESC)) AS WAITTIME
FROM V$SESS_TIME_MODEL
WHERE STAT_NAME IN ('DB time','DB CPU')
) M, V$SESSION S
WHERE M.SID = S.SID
AND M.STAT_NAME = 'DB time'
AND M.WAITTIME > 0
AND S.USERNAME IS NOT NULL
ORDER BY WAIT_MIN DESC;
Sub division of time consuming by events for SID
set pages 5000
col event format a40
col mins_waited format 999,999.99
col total_waits format 999,999,999
col avg_wait_ms format 999.99
SELECT
event,
ROUND(time_waited_micro / 60000000, 2) AS mins_waited,
total_waits,
ROUND((time_waited_micro / 1000) / DECODE(total_waits, 0, 1, total_waits), 2) AS avg_wait_ms
FROM v$session_event
WHERE sid = 1800
AND wait_class <> 'Idle'
ORDER BY mins_waited DESC;
Check Plan Hash Value for Active query
SELECT
s.sid,
s.sql_id,
s.sql_child_number as child,
q.plan_hash_value,
q.sql_plan_baseline,
q.executions
FROM v$session s
JOIN v$sql q
ON s.sql_id = q.sql_id
AND s.sql_child_number = q.child_number
WHERE s.sid = 1800;
Check Plan Hash Value from history
set pages 5000
set linesize 190
col first_seen for a30
col last_seen for a30
SELECT
ss.plan_hash_value,
COUNT(DISTINCT ss.snap_id) as num_snapshots,
SUM(ss.executions_delta) as total_execs,
-- Average Elapsed Time in Seconds
ROUND(SUM(ss.elapsed_time_delta) / DECODE(SUM(ss.executions_delta), 0, 1, SUM(ss.executions_delta)) / 1000000, 4) as avg_etime_secs,
-- Average Logical Reads (Buffer Gets)
ROUND(SUM(ss.buffer_gets_delta) / DECODE(SUM(ss.executions_delta), 0, 1, SUM(ss.executions_delta)), 0) as avg_lio,
-- Average Physical Reads
ROUND(SUM(ss.disk_reads_delta) / DECODE(SUM(ss.executions_delta), 0, 1, SUM(ss.executions_delta)), 0) as avg_pio,
MIN(s.begin_interval_time) as first_seen,
MAX(s.begin_interval_time) as last_seen
FROM
DBA_HIST_SQLSTAT ss
JOIN
DBA_HIST_SNAPSHOT s ON ss.snap_id = s.snap_id AND ss.instance_number = s.instance_number
WHERE
ss.sql_id = '4pa00tnr09b4r'
GROUP BY
ss.plan_hash_value
ORDER BY
avg_etime_secs ASC;
Comments
Post a Comment