大雑把なチェックSQL

●データベースの運行状況：
select
METRIC_NAME, VALUE
from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') AND
INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

METRIC_NAME VALUE
---------------------------------------------------------------- ----------
Database Wait Time Ratio 100
Database CPU Time Ratio 0

SQL>

●前の1時間内のシステム運行状況：
SELECT TO_CHAR(END_TIME, 'YYYY-MM-DD HH:MI:SS') END_TIME, VALUE
FROM SYS.V_$SYSMETRIC_HISTORY
WHERE METRIC_NAME = 'Database CPU Time Ratio'
ORDER BY 1;

END_TIME VALUE
------------------- ----------
2006-02-20 01:38:38 3
2006-02-20 01:39:36 0
...
2006-02-20 02:38:37 0
2006-02-20 02:38:37 0

74行が選択されました。

SQL>

●データベース効率の最大値、最小値、平均値
SELECT
CASE METRIC_NAME
WHEN 'SQL Service Response Time' THEN 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' THEN 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' THEN ROUND((MINVAL / 100),2)
WHEN 'Response Time Per Txn' THEN ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' THEN ROUND((MAXVAL / 100),2)
WHEN 'Response Time Per Txn' THEN ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' THEN ROUND((AVERAGE / 100),2)
WHEN 'Response Time Per Txn' THEN ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM SYS.V_$SYSMETRIC_SUMMARY
WHERE METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1;

METRIC_NAME MININUM MAXIMUM AVERAGE
----------------------------------- --------- ----------- ----------
CPU Usage Per Sec 0 46 1
CPU Usage Per Txn 0 116 9
Database CPU Time Ratio 0 3 0
Database Wait Time Ratio 97 100 99
Executions Per Sec 0 5622 445
Executions Per Txn 1 25522 1880
Response Time Per Txn (secs) .18 786.18 287.54
SQL Service Response Time (secs) 0 3.24 .61
User Transaction Per Sec 0 72 1

9行が選択されました。

SQL>

●どの活動が一番時間をかかっているかを調べる。
SELECT
CASE DB_STAT_NAME
WHEN 'parse time elapsed' THEN 'soft parse time'
ELSE DB_STAT_NAME
END DB_STAT_NAME,
CASE DB_STAT_NAME
WHEN 'sql execute elapsed time' THEN TIME_SECS - PLSQL_TIME
WHEN 'parse time elapsed' THEN TIME_SECS - HARD_PARSE_TIME
ELSE TIME_SECS
END TIME_SECS,
CASE DB_STAT_NAME
WHEN 'sql execute elapsed time' THEN ROUND(100 * (TIME_SECS - PLSQL_TIME) / DB_TIME,2)
WHEN 'parse time elapsed' THEN ROUND(100 * (TIME_SECS - HARD_PARSE_TIME) / DB_TIME,2)
ELSE ROUND(100 * TIME_SECS / DB_TIME,2)
END PCT_TIME
FROM
(SELECT STAT_NAME DB_STAT_NAME, ROUND((VALUE / 1000000),3) TIME_SECS
FROM SYS.V_$SYS_TIME_MODEL
WHERE STAT_NAME NOT IN ('DB time','background elapsed time', 'background cpu time','DB CPU')),
(SELECT ROUND((VALUE / 1000000),3) DB_TIME
FROM SYS.V_$SYS_TIME_MODEL
WHERE STAT_NAME = 'DB time'),
(SELECT ROUND((VALUE / 1000000),3) PLSQL_TIME
FROM SYS.V_$SYS_TIME_MODEL
WHERE STAT_NAME = 'PL/SQL execution elapsed time'),
(SELECT ROUND((VALUE / 1000000),3) HARD_PARSE_TIME
FROM SYS.V_$SYS_TIME_MODEL
WHERE STAT_NAME = 'hard parse elapsed time')
ORDER BY 2 DESC;

DB_STAT_NAME TIME_SECS PCT_TIME
-------------------------------------------------- ---------- ----------
sql execute elapsed time 1256493.55 1.15
hard parse elapsed time 26518.525 .02
PL/SQL execution elapsed time 10944.781 .01
soft parse time 9453.695 .01
PL/SQL compilation elapsed time 4141.709 0
connection management call elapsed time 1427.068 0
sequence load elapsed time 1107.645 0
failed parse elapsed time 594.186 0
hard parse (sharing criteria) elapsed time 257.132 0
hard parse (bind mismatch) elapsed time 12.931 0
inbound PL/SQL rpc elapsed time .384 0
failed parse (out of shared memory) elapsed time 0 0
Java execution elapsed time 0 0

13行が選択されました。

SQL>

●グローバル待ち時間の長いものを探す
SELECT A.SID, B.USERNAME, A.WAIT_CLASS, A.TOTAL_WAITS,
ROUND((A.TIME_WAITED / 100),2) TIME_WAITED_SECS
FROM SYS.V_$SESSION_WAIT_CLASS A, SYS.V_$SESSION B
WHERE B.SID = A.SID AND
B.USERNAME IS NOT NULL AND
A.WAIT_CLASS != 'Idle'
ORDER BY 5 DESC;

SID USERNAME WAIT_CLASS TOTAL_WAITS TIME_WAITED_SECS
---------- ---------- --------------- ----------- ----------------
13747 E0000001 Network 14671 1493.71
13607 D0000002 User I/O 52023 382.5
13375 D0000012 User I/O 35923 173.18
13609 E0000011 Application 59 70.44
13609 E0000001 Network 3692 42.54
13512 A0000003 User I/O 7075 25.17
13461 A0000009 User I/O 2844 23.56
13617 E0000021 Network 929 16.12
13461 A0000009 Network 330656 12.89
13747 E0000021 User I/O 575 11.95
.....
13420 E0343431 Concurrency 1 0
13373 E1111111 Network 41 0
13375 D1111112 Other 30 0

139行が選択されました。

経過: 00:00:00.50
SQL>

●待ち時間履歴を調べる
SELECT
TO_CHAR(A.END_TIME,'DD-MON-YYYY HH:MI:SS') END_TIME,
B.WAIT_CLASS,
ROUND((A.TIME_WAITED / 100),2) TIME_WAITED
FROM
SYS.V_$WAITCLASSMETRIC_HISTORY A,
SYS.V_$SYSTEM_WAIT_CLASS B
WHERE
A.WAIT_CLASS# = B.WAIT_CLASS# AND
B.WAIT_CLASS != 'Idle'
ORDER BY
1, 2;

END_TIME WAIT_CLASS TIME_WAITED
----------------------- --------------- -----------
20-FEB-2006 01:38:38 Application 0
20-FEB-2006 01:38:38 Commit 4.61
20-FEB-2006 01:38:38 Concurrency 0
20-FEB-2006 01:38:38 Configuration 0
20-FEB-2006 01:38:38 Network 46.59
20-FEB-2006 01:38:38 Other 670.66
20-FEB-2006 01:38:38 System I/O 8.3
20-FEB-2006 01:38:38 User I/O 2.61
20-FEB-2006 01:39:36 Application 0
.....

XXX行が選択されました。

SQL>

●特定時間以内のI/O状況を調べる
SELECT
SESS_ID,
USERNAME,
PROGRAM,
WAIT_EVENT,
SESS_TIME,
ROUND(100 * (SESS_TIME / TOTAL_TIME),2) PCT_TIME_WAITED
FROM
(SELECT
A.SESSION_ID SESS_ID,
DECODE(SESSION_TYPE,'background',SESSION_TYPE,C.USERNAME) USERNAME,
A.PROGRAM PROGRAM,
B.NAME WAIT_EVENT,
SUM(A.TIME_WAITED) SESS_TIME
FROM
SYS.V_$ACTIVE_SESSION_HISTORY A,
SYS.V_$EVENT_NAME B,
SYS.DBA_USERS C
WHERE
A.EVENT# = B.EVENT# AND
A.USER_ID = C.USER_ID AND
-- TO_CHAR(SAMPLE_TIME, 'YYYYMMDD HH:MI:SS') > '20060101 00:00:00' AND
-- TO_CHAR(SAMPLE_TIME, 'YYYYMMDD HH:MI:SS') < '20060405 00:00:00' AND
B.WAIT_CLASS = 'User I/O'
GROUP BY
A.SESSION_ID,
DECODE(SESSION_TYPE,'background',SESSION_TYPE,C.USERNAME),
A.PROGRAM,
B.NAME
),
(SELECT
SUM(A.TIME_WAITED) TOTAL_TIME
FROM
SYS.V_$ACTIVE_SESSION_HISTORY A,
SYS.V_$EVENT_NAME B
WHERE
A.EVENT# = B.EVENT# AND
-- TO_CHAR(SAMPLE_TIME, 'YYYYMMDD HH:MI:SS') > '20060101 00:00:00' AND
-- TO_CHAR(SAMPLE_TIME, 'YYYYMMDD HH:MI:SS') < '20060405 00:00:00' AND
B.WAIT_CLASS = 'User I/O'
)
ORDER BY
6 DESC;


●待ち時間が長いSQL
SELECT
*
FROM
(SELECT
SQL_TEXT,
SQL_ID,
ELAPSED_TIME,
CPU_TIME,
USER_IO_WAIT_TIME,
ROWS_PROCESSED
FROM
SYS.V_$SQLAREA
WHERE SQL_TEXT LIKE '%DRIVING_SITE(TABLE_NAME) ORDERED%'
ORDER BY
5 DESC
)
WHERE
ROWNUM < 20
/

SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME
------------------------------ ------------- ------------ ---------- -----------------
select i.obj#,i.ts#,i.file#,i. 1tjnwqfpzxnng 155992860 62783286 107760000
block#,i.intcols,i.type#,i.fla
......

SELECT /*+ USE_NL (MATRIX, DAT bjadu54ff4rkq 93384393 90 89990000
A) */ MATRIX.GROUPNAME,
MATRIX.NENREI_KBN_NAME,
......

SELECT MATRIX.GROUPNAME, 9rp94fcrc2653 87619484 251 81040000
MATRIX.NENREI_KBN_NAME,
......

SELECT MATRIX.GROUPNAME, MATRI bm392979yn9jd 66626981 83061 63700000
X.NENREI_KBN_NAME, MATRIX.NENS
......

......

9行が選択されました。

経過: 00:00:00.11
SQL>

●特定SQL_IDの実行時間が長い理由を探す
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 = '&SQL_ID' AND
A.CURRENT_OBJ# = B.OBJECT_ID AND
TIME_WAITED <> 0;

ブロックをを探す。

SELECT CHILD# "cCHILD"
, ADDR "sADDR"
, GETS "sGETS"
, MISSES "sMISSES"
, SLEEPS "sSLEEPS"
FROM v$latch_children
WHERE name = 'cache buffers chains' and rownum < 10
ORDER BY 5 desc, 4, 1, 2, 3;

次は、上のＳＱＬで一番大きいＳＰＬＥＥＰＳのＡＤＤＲを利用して

column segment_name format a35
SELECT /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
FROM
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
WHERE
x.hladdr = '&ADDR' AND
e.file_id = x.file# AND
x.hladdr = l.addr AND
x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1
ORDER BY x.tch desc ;

col GETS format 9999999999999
select NAME, GETS, MISSES, DECODE(GETS,0,'zero',(GETS - MISSES)/GETS,'other') HIT_RATIO,
SLEEP1,DECODE(MISSES,0,'zero',SLEEP1/MISSES,'other') "SLEEPS/MISS"
from v$latch where NAME like 'cache buffer%'