1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
...
...
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
SELECT EXP (SUM (LN (GREATEST (LEVEL - 1, 1))) OVER ())
/ EXP (SUM (LN (GREATEST (LEVEL - 1, 1))) OVER (ORDER BY LEVEL))
/ EXP (SUM (LN (GREATEST (&n - LEVEL, 1))) OVER (ORDER BY LEVEL DESC)) pascal
FROM DUAL
CONNECT BY LEVEL <= &n
/
土曜日, 9月 09, 2006
SQLTIP－－Fibonacci
SELECT ( POWER ((1 + SQRT (5)) / 2, LEVEL)
- POWER ((1 - SQRT (5)) / 2, LEVEL)
)
/ SQRT (5) fibonacci
FROM DUAL
CONNECT BY LEVEL <= 20
/
SELECT ( POWER ((1 + SQRT (5)) / 2, LEVEL)
- POWER ((1 - SQRT (5)) / 2, LEVEL)
)
/ SQRT (5) fibonacci,
(5 + 3 * SQRT (5)) * POWER ((1 + SQRT (5)) / 2, LEVEL)
/ 10
+ (5 - 3 * SQRT (5)) * POWER ((1 - SQRT (5)) / 2, LEVEL) / 10
- 1 sum_n
FROM DUAL
CONNECT BY LEVEL <= 20
/
- POWER ((1 - SQRT (5)) / 2, LEVEL)
)
/ SQRT (5) fibonacci
FROM DUAL
CONNECT BY LEVEL <= 20
/
SELECT ( POWER ((1 + SQRT (5)) / 2, LEVEL)
- POWER ((1 - SQRT (5)) / 2, LEVEL)
)
/ SQRT (5) fibonacci,
(5 + 3 * SQRT (5)) * POWER ((1 + SQRT (5)) / 2, LEVEL)
/ 10
+ (5 - 3 * SQRT (5)) * POWER ((1 - SQRT (5)) / 2, LEVEL) / 10
- 1 sum_n
FROM DUAL
CONNECT BY LEVEL <= 20
/
SQLTIP－－文字列を特定数の単語単位で改行する。
str
--------------------------------
This is not a verry long test string
Happy new year !
id string
----------------------
1 This is not a
1 verry long test
1 string
2 Happy new year
2 !
SELECT ID, STRING
FROM (SELECT level#, rnum ID,
SUBSTR (str, INSTR (str, base, 1, level#) + len,
INSTR (SUBSTR (str, INSTR (str, base, 1, level#) + len,
limit# + 1), base, -1) - 1) STRING,
INSTR (str, base, 1, level#) + len - 1 start#,
INSTR (SUBSTR (str, INSTR (str, base, 1, level#) + len,
limit# + 1), base, -1) +
INSTR (str, base, 1, level#) + len - 1 end#
FROM (SELECT LEVEL level#, limit#, base, len
FROM (SELECT MAX ( LENGTH (str)
- LENGTH (REPLACE (str, ' ')) + 1) max_part,
15 limit#, ' ' base, LENGTH (' ') len
FROM TEST)
CONNECT BY LEVEL <= max_part),
(SELECT ROWNUM rnum, ' ' || str || ' ' str,
LENGTH (str) - LENGTH (REPLACE (str, ' ')) + 1 part
FROM TEST)
WHERE level# <= part)
START WITH start# = 1
CONNECT BY PRIOR end# = start# AND PRIOR ID = ID
GROUP BY ID, level#, STRING
/
--------------------------------
This is not a verry long test string
Happy new year !
id string
----------------------
1 This is not a
1 verry long test
1 string
2 Happy new year
2 !
SELECT ID, STRING
FROM (SELECT level#, rnum ID,
SUBSTR (str, INSTR (str, base, 1, level#) + len,
INSTR (SUBSTR (str, INSTR (str, base, 1, level#) + len,
limit# + 1), base, -1) - 1) STRING,
INSTR (str, base, 1, level#) + len - 1 start#,
INSTR (SUBSTR (str, INSTR (str, base, 1, level#) + len,
limit# + 1), base, -1) +
INSTR (str, base, 1, level#) + len - 1 end#
FROM (SELECT LEVEL level#, limit#, base, len
FROM (SELECT MAX ( LENGTH (str)
- LENGTH (REPLACE (str, ' ')) + 1) max_part,
15 limit#, ' ' base, LENGTH (' ') len
FROM TEST)
CONNECT BY LEVEL <= max_part),
(SELECT ROWNUM rnum, ' ' || str || ' ' str,
LENGTH (str) - LENGTH (REPLACE (str, ' ')) + 1 part
FROM TEST)
WHERE level# <= part)
START WITH start# = 1
CONNECT BY PRIOR end# = start# AND PRIOR ID = ID
GROUP BY ID, level#, STRING
/
SQLTIP－－任意の範囲の数値、日付を出力する。
1から100まで出力
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100
/
20060730から20060830まで出力
SELECT (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) day#
FROM DUAL
CONNECT BY (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) <=
TO_DATE ('20060830', 'YYYYMMDD')
/
或は
SELECT *
FROM (SELECT (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) day# FROM DUAL
CONNECT BY (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) <=
TO_DATE ('20060830', 'YYYYMMDD'))
/
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100
/
20060730から20060830まで出力
SELECT (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) day#
FROM DUAL
CONNECT BY (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) <=
TO_DATE ('20060830', 'YYYYMMDD')
/
或は
SELECT *
FROM (SELECT (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) day# FROM DUAL
CONNECT BY (TO_DATE ('20060730', 'YYYYMMDD') + LEVEL - 1) <=
TO_DATE ('20060830', 'YYYYMMDD'))
/
SQLTIP－－first、lastの利用
SELECT MAX (ename) KEEP (DENSE_RANK FIRST ORDER BY sal DESC) ename, deptno,
MAX (sal) sal
FROM emp
GROUP BY deptno
SELECT MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename, deptno,
MAX (sal) sal
FROM emp
GROUP BY deptno
MAX (sal) sal
FROM emp
GROUP BY deptno
SELECT MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename, deptno,
MAX (sal) sal
FROM emp
GROUP BY deptno
SQLTIP－－LOTO 6番号生成
SELECT MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 1, num)) no1,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 2, num)) no2,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 3, num)) no3,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 4, num)) no4,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 5, num)) no5,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 6, num)) no6
FROM (SELECT grp, num
FROM (SELECT grp, num, MOD (ROWNUM - 1, 45) + 1 rnum
FROM (SELECT CEIL (LEVEL / 45) grp,
MOD (LEVEL - 1, 45) + 1 num
FROM DUAL
CONNECT BY LEVEL <= 45 * 10
ORDER BY 1, DBMS_RANDOM.VALUE))
WHERE rnum <= 6
ORDER BY grp, num)
GROUP BY grp
/
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 2, num)) no2,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 3, num)) no3,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 4, num)) no4,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 5, num)) no5,
MIN (DECODE (MOD (ROWNUM - 1, 6) + 1, 6, num)) no6
FROM (SELECT grp, num
FROM (SELECT grp, num, MOD (ROWNUM - 1, 45) + 1 rnum
FROM (SELECT CEIL (LEVEL / 45) grp,
MOD (LEVEL - 1, 45) + 1 num
FROM DUAL
CONNECT BY LEVEL <= 45 * 10
ORDER BY 1, DBMS_RANDOM.VALUE))
WHERE rnum <= 6
ORDER BY grp, num)
GROUP BY grp
/
SQLTIP－－文字列から単語の数を計算
SELECT
(LENGTH ('THIS IS TEST STRING')
- LENGTH (REPLACE ('THIS IS TEST STRING', 'IS'))
)
/ LENGTH ('IS') cnt
FROM DUAL
/
(LENGTH ('THIS IS TEST STRING')
- LENGTH (REPLACE ('THIS IS TEST STRING', 'IS'))
)
/ LENGTH ('IS') cnt
FROM DUAL
/
SQLTIP－－文字列分割
123,11,4567,8,99999,000,123456
「,」で分割
123
11
4567
...
...
SELECT SUBSTR (aa,
INSTR (aa, ',', 1, LEVEL) + 1,
INSTR (aa, ',', 1, LEVEL + 1) - INSTR (aa, ',', 1, LEVEL)
- 1
) sub
FROM (SELECT ',' || '123,11,4567,8,99999,000,123456' || ',' aa
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (aa) - LENGTH (REPLACE (aa, ',')) - 1
2)
区分記号が2バイト以上の場合
SELECT SUBSTR (str,
INSTR (str, base, 1, LEVEL) + len,
INSTR (str, base, 1, LEVEL + 1)
- INSTR (str, base, 1, LEVEL)
- len
) sub
FROM (SELECT '#&&#' base, '#&&#' || '1#&(#&・#&?' || '#&&#' str,
LENGTH ('#&&#') len
FROM DUAL)
CONNECT BY LEVEL <= (LENGTH (str) - LENGTH (REPLACE (str, base))) / len - 1
3)
複数の行が存在する場合は
COL1
--------------------------------------------------
1/2/3
44/555
3/77
8/77/8
「/」 による分割
1
2
3
44
555
3
77
8
77
8
SELECT sub
FROM (SELECT DISTINCT rn, LEVEL,
SUBSTR (str,
INSTR (str, base, 1, LEVEL) + len,
INSTR (str, base, 1, LEVEL + 1)
- INSTR (str, base, 1, LEVEL)
- len
) sub
FROM (SELECT ROWNUM rn, '/' base, '/' || col1 || '/' str,
LENGTH ('/') len
FROM tab2)
CONNECT BY LEVEL <=
(LENGTH (str) - LENGTH (REPLACE (str, base)))
/ len
- 1)
「,」で分割
123
11
4567
...
...
SELECT SUBSTR (aa,
INSTR (aa, ',', 1, LEVEL) + 1,
INSTR (aa, ',', 1, LEVEL + 1) - INSTR (aa, ',', 1, LEVEL)
- 1
) sub
FROM (SELECT ',' || '123,11,4567,8,99999,000,123456' || ',' aa
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (aa) - LENGTH (REPLACE (aa, ',')) - 1
2)
区分記号が2バイト以上の場合
SELECT SUBSTR (str,
INSTR (str, base, 1, LEVEL) + len,
INSTR (str, base, 1, LEVEL + 1)
- INSTR (str, base, 1, LEVEL)
- len
) sub
FROM (SELECT '#&&#' base, '#&&#' || '1#&(#&・#&?' || '#&&#' str,
LENGTH ('#&&#') len
FROM DUAL)
CONNECT BY LEVEL <= (LENGTH (str) - LENGTH (REPLACE (str, base))) / len - 1
3)
複数の行が存在する場合は
COL1
--------------------------------------------------
1/2/3
44/555
3/77
8/77/8
「/」 による分割
1
2
3
44
555
3
77
8
77
8
SELECT sub
FROM (SELECT DISTINCT rn, LEVEL,
SUBSTR (str,
INSTR (str, base, 1, LEVEL) + len,
INSTR (str, base, 1, LEVEL + 1)
- INSTR (str, base, 1, LEVEL)
- len
) sub
FROM (SELECT ROWNUM rn, '/' base, '/' || col1 || '/' str,
LENGTH ('/') len
FROM tab2)
CONNECT BY LEVEL <=
(LENGTH (str) - LENGTH (REPLACE (str, base)))
/ len
- 1)
SQLTIP－－10進数から2進数へ、2進数から10進数へ
-- 10進数から 2進数へ
SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (bit, ',')), ',') bin
FROM (SELECT COUNT (*) OVER () - LEVEL + 1 level#,
DECODE (TRUNC (deci / POWER (2, LEVEL)),
ROUND (deci / POWER (2, LEVEL)), 0,
1
) bit
FROM (SELECT 699 deci
FROM DUAL)
CONNECT BY POWER (2, LEVEL - 1) <= deci)
START WITH level# = 1
CONNECT BY PRIOR level# = level# - 1
/
-- 2進数から 10進数へ
SELECT SUM (SUBSTR (bin, LEVEL, 1) * POWER (2, LENGTH (bin) - LEVEL)) deci
FROM (SELECT 11101 bin
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (bin)
/
SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (bit, ',')), ',') bin
FROM (SELECT COUNT (*) OVER () - LEVEL + 1 level#,
DECODE (TRUNC (deci / POWER (2, LEVEL)),
ROUND (deci / POWER (2, LEVEL)), 0,
1
) bit
FROM (SELECT 699 deci
FROM DUAL)
CONNECT BY POWER (2, LEVEL - 1) <= deci)
START WITH level# = 1
CONNECT BY PRIOR level# = level# - 1
/
-- 2進数から 10進数へ
SELECT SUM (SUBSTR (bin, LEVEL, 1) * POWER (2, LENGTH (bin) - LEVEL)) deci
FROM (SELECT 11101 bin
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (bin)
/
SQLTIP－－カレンダー生成
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
SELECT MIN (DECODE (TO_CHAR (days, 'd'), 1, TO_CHAR (days, 'fmdd'))) 日,
MIN (DECODE (TO_CHAR (days, 'd'), 2, TO_CHAR (days, 'fmdd'))) 月,
MIN (DECODE (TO_CHAR (days, 'd'), 3, TO_CHAR (days, 'fmdd'))) 火,
MIN (DECODE (TO_CHAR (days, 'd'), 4, TO_CHAR (days, 'fmdd'))) 水,
MIN (DECODE (TO_CHAR (days, 'd'), 5, TO_CHAR (days, 'fmdd'))) 木,
MIN (DECODE (TO_CHAR (days, 'd'), 6, TO_CHAR (days, 'fmdd'))) 金,
MIN (DECODE (TO_CHAR (days, 'd'), 7, TO_CHAR (days, 'fmdd'))) 土
FROM (SELECT base_mon + LEVEL - 1 days,
( TRUNC (base_mon + LEVEL - 1, 'd')
- TRUNC (TRUNC (base_mon + LEVEL - 1, 'y'), 'd')
) / 7 + 1 week_grp
FROM (SELECT TO_DATE ('200605', 'yyyymm') base_mon
FROM DUAL)
CONNECT BY base_mon + LEVEL - 1 <= LAST_DAY (base_mon))
GROUP BY week_grp
ORDER BY week_grp
;
月 火 水 木 金 土 日
-- -- -- -- -- -- --
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
SELECT MIN (DECODE (TO_CHAR (days, 'd'), 1, TO_CHAR (days, 'fmdd'))) 日,
MIN (DECODE (TO_CHAR (days, 'd'), 2, TO_CHAR (days, 'fmdd'))) 月,
MIN (DECODE (TO_CHAR (days, 'd'), 3, TO_CHAR (days, 'fmdd'))) 火,
MIN (DECODE (TO_CHAR (days, 'd'), 4, TO_CHAR (days, 'fmdd'))) 水,
MIN (DECODE (TO_CHAR (days, 'd'), 5, TO_CHAR (days, 'fmdd'))) 木,
MIN (DECODE (TO_CHAR (days, 'd'), 6, TO_CHAR (days, 'fmdd'))) 金,
MIN (DECODE (TO_CHAR (days, 'd'), 7, TO_CHAR (days, 'fmdd'))) 土
FROM (SELECT base_mon + LEVEL - 1 days,
( TRUNC (base_mon + LEVEL - 1, 'd')
- TRUNC (TRUNC (base_mon + LEVEL - 1, 'y'), 'd')
) / 7 + 1 week_grp
FROM (SELECT TO_DATE ('200605', 'yyyymm') base_mon
FROM DUAL)
CONNECT BY base_mon + LEVEL - 1 <= LAST_DAY (base_mon))
GROUP BY week_grp
ORDER BY week_grp
;
月 火 水 木 金 土 日
-- -- -- -- -- -- --
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
大雑把なチェック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%'
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%'
テーブル縮小
■自動拡張されたテーブルスペースをどこまで縮小可能か？
-- 9i以上用
SELECT FILE_NAME, ((E.BLOCK_ID - 1) * T.BLOCK_SIZE + E.BYTES) / 1024 BYTE_K
FROM DBA_TABLESPACES T,DBA_DATA_FILES D,(
SELECT
DISTINCT
FILE_ID
,TABLESPACE_NAME
,MAX(BLOCK_ID) OVER (PARTITION BY FILE_ID) BLOCK_ID
,FIRST_VALUE(BYTES) OVER (PARTITION BY FILE_ID ORDER BY BLOCK_ID
DESC) BYTES
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = UPPER('&&TSNAME')
) E
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
AND D.TABLESPACE_NAME = E.TABLESPACE_NAME
AND D.FILE_ID = E.FILE_ID
AND T.TABLESPACE_NAME = UPPER('&&TSNAME')
;
-- 8i用
SELECT D.NAME, ((E.BLOCK_ID - 1) * D.BLOCK_SIZE + E.BYTES) /1024 BYTE_K
FROM DBA_TABLESPACES T,V$DATAFILE D,DBA_EXTENTS E
WHERE D.FILE# = E.FILE_ID
AND T.TABLESPACE_NAME = E.TABLESPACE_NAME
AND T.TABLESPACE_NAME = UPPER('&&TSNAME')
AND (E.FILE_ID,E.BLOCK_ID) IN (
SELECT FILE_ID,MAX(BLOCK_ID)
FROM DBA_EXTENTS S
WHERE T.TABLESPACE_NAME = S.TABLESPACE_NAME
GROUP BY FILE_ID)
;
-- 9i以上用
SELECT FILE_NAME, ((E.BLOCK_ID - 1) * T.BLOCK_SIZE + E.BYTES) / 1024 BYTE_K
FROM DBA_TABLESPACES T,DBA_DATA_FILES D,(
SELECT
DISTINCT
FILE_ID
,TABLESPACE_NAME
,MAX(BLOCK_ID) OVER (PARTITION BY FILE_ID) BLOCK_ID
,FIRST_VALUE(BYTES) OVER (PARTITION BY FILE_ID ORDER BY BLOCK_ID
DESC) BYTES
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = UPPER('&&TSNAME')
) E
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
AND D.TABLESPACE_NAME = E.TABLESPACE_NAME
AND D.FILE_ID = E.FILE_ID
AND T.TABLESPACE_NAME = UPPER('&&TSNAME')
;
-- 8i用
SELECT D.NAME, ((E.BLOCK_ID - 1) * D.BLOCK_SIZE + E.BYTES) /1024 BYTE_K
FROM DBA_TABLESPACES T,V$DATAFILE D,DBA_EXTENTS E
WHERE D.FILE# = E.FILE_ID
AND T.TABLESPACE_NAME = E.TABLESPACE_NAME
AND T.TABLESPACE_NAME = UPPER('&&TSNAME')
AND (E.FILE_ID,E.BLOCK_ID) IN (
SELECT FILE_ID,MAX(BLOCK_ID)
FROM DBA_EXTENTS S
WHERE T.TABLESPACE_NAME = S.TABLESPACE_NAME
GROUP BY FILE_ID)
;
一時表領域
■一時表領域について
●V$SORT_USAGE
ソート状況の確認が出来る。
SELECT * FROM V$SORT_USAGE;
SELECT SID,OSUSER,MACHINE,BLOCKS,PROGRAM
FROM V$SESSION A,V$SORT_USAGE B
WHERE A.SADDR = B.SESSION_ADDR
ORDER BY BLOCKS DESC;
SELECT USER,B.SQL_TEXT,SUM(BLOCKS)
FROM V$SORT_USAGE A,V$SQLTEXT B
WHERE A.SQLADDR=B.ADDRESS
AND SQLHASH=B.HASH_VALUE
GROUP BY USER,B.SQL_TEXT;
●V$SORT_SEGMENT
SELECT * FROM V$SORT_SEGMENT;
●V$TEMPSEG_USAGE
SELECT * FROM V$TEMPSEG_USAGE;
レコードが選択されませんでした。
SQL>
●DBA_TEMP_FILES
SELECT * FROM DBA_TEMP_FILES;
*********************************************
●V$SORT_USAGE
SELECT * FROM V$SORT_USAGE;
レコードが選択されませんでした。
SQL>
SELECT TABLESPACE_NAME, BYTES, BLOCKS, MAXBYTES, MAXBLOCKS, USER_BYTES, USER_BLOCKS
FROM DBA_TEMP_FILES;
TABLESPACE_NAME BYTES BLOCKS MAXBYTES MAXBLOCKS USER_BYTES USER_BLOCKS
------------------------------ ---------- ---------- ---------- ---------- ---------- -----------
TEMP 5242880000 640000 0 0 5241831424 639872
SQL>
■一時表領域の再作成
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
次のファイルを作成します。
DELTEMP.SQL及びCREATETEMP.SQLをREPTEMP.BATと
同一パスに配置し、バッチファイルを実行します。
※パス及びサービス名、ファイルサイズ等は使用環境に従い修正します。
SQLPLUS /NOLOG @DELTEMP.SQL
DEL D:\ORACLE\ORADATA\TEMP01.DBF
SQLPLUS /NOLOG @CREATETEMP.SQL
DEL D:\ORACLE\ORADATA\TEMPX01.DBF
SET ECHO ON
CONNECT SYS/CHANGE_ON_INSTALL@???? AS SYSDBA
CREATE TEMPORARY TABLESPACE TEMPX
TEMPFILE 'D:\ORACLE\ORADATA\TEMPX01.DBF' SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPX;
DROP TABLESPACE TEMP;
EXIT
SET ECHO ON
CONNECT SYS/CHANGE_ON_INSTALL@???? AS SYSDBA
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE 'D:\ORACLE\ORADATA\TEMP01.DBF' SIZE 40M REUSE AUTOEXTEND ON
NEXT 640K MAXSIZE UNLIMITED;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMPX;
EXIT
●V$SORT_USAGE
ソート状況の確認が出来る。
SELECT * FROM V$SORT_USAGE;
SELECT SID,OSUSER,MACHINE,BLOCKS,PROGRAM
FROM V$SESSION A,V$SORT_USAGE B
WHERE A.SADDR = B.SESSION_ADDR
ORDER BY BLOCKS DESC;
SELECT USER,B.SQL_TEXT,SUM(BLOCKS)
FROM V$SORT_USAGE A,V$SQLTEXT B
WHERE A.SQLADDR=B.ADDRESS
AND SQLHASH=B.HASH_VALUE
GROUP BY USER,B.SQL_TEXT;
●V$SORT_SEGMENT
SELECT * FROM V$SORT_SEGMENT;
●V$TEMPSEG_USAGE
SELECT * FROM V$TEMPSEG_USAGE;
レコードが選択されませんでした。
SQL>
●DBA_TEMP_FILES
SELECT * FROM DBA_TEMP_FILES;
*********************************************
●V$SORT_USAGE
SELECT * FROM V$SORT_USAGE;
レコードが選択されませんでした。
SQL>
SELECT TABLESPACE_NAME, BYTES, BLOCKS, MAXBYTES, MAXBLOCKS, USER_BYTES, USER_BLOCKS
FROM DBA_TEMP_FILES;
TABLESPACE_NAME BYTES BLOCKS MAXBYTES MAXBLOCKS USER_BYTES USER_BLOCKS
------------------------------ ---------- ---------- ---------- ---------- ---------- -----------
TEMP 5242880000 640000 0 0 5241831424 639872
SQL>
■一時表領域の再作成
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
次のファイルを作成します。
DELTEMP.SQL及びCREATETEMP.SQLをREPTEMP.BATと
同一パスに配置し、バッチファイルを実行します。
※パス及びサービス名、ファイルサイズ等は使用環境に従い修正します。
SQLPLUS /NOLOG @DELTEMP.SQL
DEL D:\ORACLE\ORADATA\TEMP01.DBF
SQLPLUS /NOLOG @CREATETEMP.SQL
DEL D:\ORACLE\ORADATA\TEMPX01.DBF
SET ECHO ON
CONNECT SYS/CHANGE_ON_INSTALL@???? AS SYSDBA
CREATE TEMPORARY TABLESPACE TEMPX
TEMPFILE 'D:\ORACLE\ORADATA\TEMPX01.DBF' SIZE 10M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPX;
DROP TABLESPACE TEMP;
EXIT
SET ECHO ON
CONNECT SYS/CHANGE_ON_INSTALL@???? AS SYSDBA
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE 'D:\ORACLE\ORADATA\TEMP01.DBF' SIZE 40M REUSE AUTOEXTEND ON
NEXT 640K MAXSIZE UNLIMITED;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMPX;
EXIT
メモリ
■メモリ
各メモリーの大きさの妥当性を監視
１ 共有プール(SHARED_POOL_SIZE)
. ライブラリ・キャッシュ
. ディクショナリキャッシュ
2 データベースバッファキャッシュ(DB_BLOCK_BUFFERS)
3 REDOログ・バッファ(LOG_BUFFER)
●ライブラリ・キャッシュ
select sum(reloads)/sum(pins) from v$librarycache;
SUM(RELOADS)/SUM(PINS)
----------------------
.002784741
reload率が１％を上回る場合は、初期化パラメータSHARED_POOL_SIZEの値を増やす
●ディクショナリキャッシュ
select sum(getmisses)/sum(gets) from v$rowcache;
SUM(GETMISSES)/SUM(GETS)
------------------------
.016589166
ミス率が10～１５％を上回る場合は、初期化パラメータSHARED_POOL_SIZEの値を増やす
●データベースバッファキャッシュ(DB_BLOCK_BUFFERS)
データベース・バッファ・キャッシュのヒット率の計算：
1 - (physical reads)/( db block gets + consistent gets )
データディクショナリ v$sysstatの項目
physical reads：48
db block gets：41
consistent gets：44
select 1 - (physicalreads.value)/(dbblockgets.value + consistentgets.value) as buffercache_hit_raito
from
(select value from v$sysstat where STATISTIC# = 48) physicalreads,
(select value from v$sysstat where STATISTIC# = 41) dbblockgets,
(select value from v$sysstat where STATISTIC# = 44) consistentgets
BUFFERCACHE_HIT_RAITO
---------------------
.976029012
ヒット率が 60 ～ 70 % (90%以下の方がよい？)を下回る場合は、初期化パラメータDB_BLOCK_BUFFERSの値を増やす
●ユーザグローバル領域で最大メモリを消費しているユーザを検索
SELECT USERNAME, SUM(VALUE) "TOTAL UGA MEMORY(BYTES)"
FROM V$SESSION SE, V$SESSTAT ST, V$STATNAME NM
WHERE SE.SID = ST.SID
AND ST.STATISTIC# = NM.STATISTIC#
AND NM.NAME = 'session uga memory max'
GROUP BY USERNAME;
各メモリーの大きさの妥当性を監視
１ 共有プール(SHARED_POOL_SIZE)
. ライブラリ・キャッシュ
. ディクショナリキャッシュ
2 データベースバッファキャッシュ(DB_BLOCK_BUFFERS)
3 REDOログ・バッファ(LOG_BUFFER)
●ライブラリ・キャッシュ
select sum(reloads)/sum(pins) from v$librarycache;
SUM(RELOADS)/SUM(PINS)
----------------------
.002784741
reload率が１％を上回る場合は、初期化パラメータSHARED_POOL_SIZEの値を増やす
●ディクショナリキャッシュ
select sum(getmisses)/sum(gets) from v$rowcache;
SUM(GETMISSES)/SUM(GETS)
------------------------
.016589166
ミス率が10～１５％を上回る場合は、初期化パラメータSHARED_POOL_SIZEの値を増やす
●データベースバッファキャッシュ(DB_BLOCK_BUFFERS)
データベース・バッファ・キャッシュのヒット率の計算：
1 - (physical reads)/( db block gets + consistent gets )
データディクショナリ v$sysstatの項目
physical reads：48
db block gets：41
consistent gets：44
select 1 - (physicalreads.value)/(dbblockgets.value + consistentgets.value) as buffercache_hit_raito
from
(select value from v$sysstat where STATISTIC# = 48) physicalreads,
(select value from v$sysstat where STATISTIC# = 41) dbblockgets,
(select value from v$sysstat where STATISTIC# = 44) consistentgets
BUFFERCACHE_HIT_RAITO
---------------------
.976029012
ヒット率が 60 ～ 70 % (90%以下の方がよい？)を下回る場合は、初期化パラメータDB_BLOCK_BUFFERSの値を増やす
●ユーザグローバル領域で最大メモリを消費しているユーザを検索
SELECT USERNAME, SUM(VALUE) "TOTAL UGA MEMORY(BYTES)"
FROM V$SESSION SE, V$SESSTAT ST, V$STATNAME NM
WHERE SE.SID = ST.SID
AND ST.STATISTIC# = NM.STATISTIC#
AND NM.NAME = 'session uga memory max'
GROUP BY USERNAME;
ヒストグラム
●ヒストグラムの見方
SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME = '&tablename'
AND COLUMN_NAME = '&columnname'
/
●ヒストグラムの収集（DBMS_STATS.GATHER_TABLE_STATS）
SQL>で以下のコマンド実行
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'username', -
TABNAME=>'tablename', ESTIMATE_PERCENT=>100, -
METHOD_OPT=>'FOR COLUMNS NENREI_KBN SIZE 254', -
CASCADE=>TRUE)
SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME = '&tablename'
AND COLUMN_NAME = '&columnname'
/
●ヒストグラムの収集（DBMS_STATS.GATHER_TABLE_STATS）
SQL>で以下のコマンド実行
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'username', -
TABNAME=>'tablename', ESTIMATE_PERCENT=>100, -
METHOD_OPT=>'FOR COLUMNS NENREI_KBN SIZE 254', -
CASCADE=>TRUE)
チューニング関連（EXPLAIN）
●EXPLAIN実行
EXPLAIN PLAN
SET STATEMENT_ID = 'SAI1'
FOR
SELECT /*+ INDEX(SAMPLE1 SAMPLE1_IX01) */
COL1, COL2, COL3, COL4, COL5
FROM SAMPLE1
WHERE COL1 = 'A'
AND COL2 = 'B'
AND COL4 = 'C'
/
●結果出力
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'sai1','TYPICAL'));
EXPLAIN PLAN
SET STATEMENT_ID = 'SAI1'
FOR
SELECT /*+ INDEX(SAMPLE1 SAMPLE1_IX01) */
COL1, COL2, COL3, COL4, COL5
FROM SAMPLE1
WHERE COL1 = 'A'
AND COL2 = 'B'
AND COL4 = 'C'
/
●結果出力
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'sai1','TYPICAL'));
チューニング関連（SQL Plus）
１．PLAN_TABLE作成
２．DBAユーザにPLUSTRACE権限付与
※：PLUSTRACEロールはSYSDBAユーザでログインし
「@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL」を実行することで作成可能
作成後はDBAロールを持つユーザにそのロール(PLUSTRACE)を付与する必要ある。
「GRANT PLUSTRACE TO DBAUSER」
set autot on;
set autot trace;
set autot trace explain;
set timing on;
２．DBAユーザにPLUSTRACE権限付与
※：PLUSTRACEロールはSYSDBAユーザでログインし
「@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL」を実行することで作成可能
作成後はDBAロールを持つユーザにそのロール(PLUSTRACE)を付与する必要ある。
「GRANT PLUSTRACE TO DBAUSER」
set autot on;
set autot trace;
set autot trace explain;
set timing on;
チューニング関連（SQL TRACEの使い方②）
■イベント－－10046
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
LEVEL 1 SQL_TRACE機能と同等
LEVEL 4 LEVEL 1の情報に追加して、バインド変数情報が出力される
LEVEL 8 LEVEL 1の情報に追加して、待機イベント情報が出力される
LEVEL 12 LEVEL 1の情報に追加して、バインド変数情報、待機イベント情報が出力される
パラメータ：SID、SERIAL#、トレース開始/停止
１．SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,TRUE);
２．SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,FALSE);
別セッションに対して、EVENT 10046のSQLトレースを設定する場合には、
手順の3.と4.で以下のプロシージャを実行します。
イベント開始
３．SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,12,'');
イベント停止
４．SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,'');
■イベント－－10053
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
LEVTL : 1 OR 2 (1 is more detailly)
【FOR TABLES:】
--------------------------------------------------------------------------------------------
Trace label dba_tables column
--------------------------------------------------------------------------------------------
CDN NUM_ROWS The cardinality = number of rows of the table
NBLKS BLOCKS The number of blocks below the high water mark
TABLE_SCAN_CST The estimated cost in I/O to full-table-scan the table
AVG_ROW_LEN AVG_ROW_LEN The average length of a row
【FOR INDEXES:】
-------------------------------------------------------------------------------------------
Trace label dba_indexes column
--------------------------------------------------------------------------------------------
Index#, col# The object# of the index and the column_id of the columns.
Oracle 9 brings an improvement by using the index name
rather than index#
LVLS BLEVEL The height of the index b-tree
#LB LEAF_BLOCKS The number of leaf blocks
#DK DISTINCT_KEYS The number of distinct keys of the index
LB/K AVG_LEAF_BLOCKS_PER_KEY The average number of leaf blocks per key
DB/K AVG_DATA_BLOCKS_PER_KEY The average number of data blocks per key
CLUF CLUSTERING_FACTOR The clustering factor of the index
【BASE ACCESS PLANS】
--------------------------------------------------------------------------------------------
This is a list of the basic plans to access a single table or, more accurately,
a row source. I have practically only seen plans 2, 3, 4, 5, and 23
--------------------------------------------------------------------------------------------
0 parallel hint | 11 rowid range scan | 22 noparallel_index hint
1 no access path spec | 12 driving_site hint | 23 index fast full scan
2 table scan | 13 | 24 swap inputs to join
3 index unique | 14 cache hint | 25 fact table
4 index range | 15 nocache hint | 26 not a fact table
5 index and-equal | 16 partitions hint | 27 merge of this view
6 order by using an index | 17 nopartitions hint | 28 don't push join predicate into this view
7 open cluster | 18 anti-join | 29 push join predicate into this view
8 hash cluster | 19 index rowid range scan | 30 no_merge of this view
9 rowid lookup | 20 bitmap index | 31 semi-join
10 range scan backwards | 21 parallel_index hint |
【SINGLE TABLE ACCESS PATH】
--------------------------------------------------------------------------------------------
Trace label dba_tables column
--------------------------------------------------------------------------------------------
NDV NUM_DISTINCT Number of distinct values for the column
NULLS NUM_NULLS Number of rows with a null “value” for the column
DENS DENSITY “Density” of the column. Without histogram this is = 1/NDV
LO LOW_VALUE The lowest value for the column (only for numeric columns)
HI HIGH_VALUE The highest value for the column (only for numeric columns)
CMPTD CDN = ORIG CDN * FF (FF:Filter Factor)
D_F_M_R_C = DB_FILE_MULTI_BLOCK_READ_COUNT
TSC = TABLE_SCAN_COST
( TSC != NBLKS / D_F_M_R_C )
( TSC = NBLKS / k ) k = = 1.6765x0.65810
FF = 1/NDV = Density
Density : 密度, 濃度, 比重
Predicate : 【文法】述語（の）, 述部（の）;
Ramification : 分枝, 分岐; （普通pl.） 支脈, 分派;
【WITHOUT BIND VARIABLES:】
--------------------------------------------------------------------------------------------
Predicate Filter Factor
--------------------------------------------------------------------------------------------
c1 = value c1.density
c1 like value c1.density
c1 > value (Hi - value) / (Hi - Lo)
c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct
c1 < value (value - Lo) / (Hi - Lo)
c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct
c1 between val1 and val2 (val2 - val1) / (Hi - Lo) + 2 * 1/c1.num_distinct
WHEN C1 IS NULL :
c1.density * ( 1 - num_nulls/num_rows)
【WHEN USING BIND VARIABLES:】
--------------------------------------------------------------------------------------------
predicate Filter factor
--------------------------------------------------------------------------------------------
c1 = :b1 c1.density
c1 {like | > | >= | < | <=} :b1 {5.0000e-02 | c1.density }5
c1 between :b1 and :b2 5.0000e-02 * 5.0000e-02
【COMBINING PREDICATES / FILTER FACTORS:】
--------------------------------------------------------------------------------------------
predicate Filter factor
--------------------------------------------------------------------------------------------
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 - FF1 * FF2
For the calculation of the filter factors for ranges of string literals,
the value of the literal is the weighted sum of the ASCII values of its
characters. Strings of different lengths are “right padded” with zeros:
ADAMS = 65*256@4 + 68*256@3 + 65*256@2 + 77*256@1 + 83*256@0 = 2.8032e+11
ward = 119*256@4 + 97*256@3 + 114*256@2 + 100*256@1 + 0*256@0 = 5.1274e+11
James = 74*256@4 + 97*256@3 + 109*256@2 + 101*256@1 + 115*256@0 = 3.1946e+11
【INDEX ACCESS COSTS】
COST ：blevel + FF*leaf_blocks + FF*clustering_factor
0 + 1/362 * 1 + 1/362 * 76 = 2?
The formula essentially equates to the count of blocks that Oracle has to traverse in order to get all qualifying rows:
From the root page follow the tree down to the leaf page blevel blocks
Access all qualifying leaf blocks leaf blocks * filter factor
Access all qualifying data blocks clustering factor * filter factor
The cost calculations for other index access methods are:
Unique scan blevel+1
Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 )
Index-only blevel + FF*leaf_blocks
■イベント－－？？？
SQL>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME BUFFERS LEVEL6';
-- レベル6だと全バッファ、レベル4だとなに？
DUMPによるデータBLOCKチェック方法（BLOCK内データを確認可能）
SELECT EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = '&EXTENT_NAME'
/
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
----------- --------- --------- ------
0 9 209 87
最初のBLOCK_IDはBLOCK HEADERであるため、次のBLOCK_IDから取得する。
SQL> alter system dump datafile 9 block 210;
■イベント－－6502
event="6502 trace name errorstack level 12"
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
LEVEL 1 SQL_TRACE機能と同等
LEVEL 4 LEVEL 1の情報に追加して、バインド変数情報が出力される
LEVEL 8 LEVEL 1の情報に追加して、待機イベント情報が出力される
LEVEL 12 LEVEL 1の情報に追加して、バインド変数情報、待機イベント情報が出力される
パラメータ：SID、SERIAL#、トレース開始/停止
１．SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,TRUE);
２．SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,FALSE);
別セッションに対して、EVENT 10046のSQLトレースを設定する場合には、
手順の3.と4.で以下のプロシージャを実行します。
イベント開始
３．SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,12,'');
イベント停止
４．SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,'');
■イベント－－10053
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
LEVTL : 1 OR 2 (1 is more detailly)
【FOR TABLES:】
--------------------------------------------------------------------------------------------
Trace label dba_tables column
--------------------------------------------------------------------------------------------
CDN NUM_ROWS The cardinality = number of rows of the table
NBLKS BLOCKS The number of blocks below the high water mark
TABLE_SCAN_CST The estimated cost in I/O to full-table-scan the table
AVG_ROW_LEN AVG_ROW_LEN The average length of a row
【FOR INDEXES:】
-------------------------------------------------------------------------------------------
Trace label dba_indexes column
--------------------------------------------------------------------------------------------
Index#, col# The object# of the index and the column_id of the columns.
Oracle 9 brings an improvement by using the index name
rather than index#
LVLS BLEVEL The height of the index b-tree
#LB LEAF_BLOCKS The number of leaf blocks
#DK DISTINCT_KEYS The number of distinct keys of the index
LB/K AVG_LEAF_BLOCKS_PER_KEY The average number of leaf blocks per key
DB/K AVG_DATA_BLOCKS_PER_KEY The average number of data blocks per key
CLUF CLUSTERING_FACTOR The clustering factor of the index
【BASE ACCESS PLANS】
--------------------------------------------------------------------------------------------
This is a list of the basic plans to access a single table or, more accurately,
a row source. I have practically only seen plans 2, 3, 4, 5, and 23
--------------------------------------------------------------------------------------------
0 parallel hint | 11 rowid range scan | 22 noparallel_index hint
1 no access path spec | 12 driving_site hint | 23 index fast full scan
2 table scan | 13 | 24 swap inputs to join
3 index unique | 14 cache hint | 25 fact table
4 index range | 15 nocache hint | 26 not a fact table
5 index and-equal | 16 partitions hint | 27 merge of this view
6 order by using an index | 17 nopartitions hint | 28 don't push join predicate into this view
7 open cluster | 18 anti-join | 29 push join predicate into this view
8 hash cluster | 19 index rowid range scan | 30 no_merge of this view
9 rowid lookup | 20 bitmap index | 31 semi-join
10 range scan backwards | 21 parallel_index hint |
【SINGLE TABLE ACCESS PATH】
--------------------------------------------------------------------------------------------
Trace label dba_tables column
--------------------------------------------------------------------------------------------
NDV NUM_DISTINCT Number of distinct values for the column
NULLS NUM_NULLS Number of rows with a null “value” for the column
DENS DENSITY “Density” of the column. Without histogram this is = 1/NDV
LO LOW_VALUE The lowest value for the column (only for numeric columns)
HI HIGH_VALUE The highest value for the column (only for numeric columns)
CMPTD CDN = ORIG CDN * FF (FF:Filter Factor)
D_F_M_R_C = DB_FILE_MULTI_BLOCK_READ_COUNT
TSC = TABLE_SCAN_COST
( TSC != NBLKS / D_F_M_R_C )
( TSC = NBLKS / k ) k = = 1.6765x0.65810
FF = 1/NDV = Density
Density : 密度, 濃度, 比重
Predicate : 【文法】述語（の）, 述部（の）;
Ramification : 分枝, 分岐; （普通pl.） 支脈, 分派;
【WITHOUT BIND VARIABLES:】
--------------------------------------------------------------------------------------------
Predicate Filter Factor
--------------------------------------------------------------------------------------------
c1 = value c1.density
c1 like value c1.density
c1 > value (Hi - value) / (Hi - Lo)
c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct
c1 < value (value - Lo) / (Hi - Lo)
c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct
c1 between val1 and val2 (val2 - val1) / (Hi - Lo) + 2 * 1/c1.num_distinct
WHEN C1 IS NULL :
c1.density * ( 1 - num_nulls/num_rows)
【WHEN USING BIND VARIABLES:】
--------------------------------------------------------------------------------------------
predicate Filter factor
--------------------------------------------------------------------------------------------
c1 = :b1 c1.density
c1 {like | > | >= | < | <=} :b1 {5.0000e-02 | c1.density }5
c1 between :b1 and :b2 5.0000e-02 * 5.0000e-02
【COMBINING PREDICATES / FILTER FACTORS:】
--------------------------------------------------------------------------------------------
predicate Filter factor
--------------------------------------------------------------------------------------------
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 - FF1 * FF2
For the calculation of the filter factors for ranges of string literals,
the value of the literal is the weighted sum of the ASCII values of its
characters. Strings of different lengths are “right padded” with zeros:
ADAMS = 65*256@4 + 68*256@3 + 65*256@2 + 77*256@1 + 83*256@0 = 2.8032e+11
ward = 119*256@4 + 97*256@3 + 114*256@2 + 100*256@1 + 0*256@0 = 5.1274e+11
James = 74*256@4 + 97*256@3 + 109*256@2 + 101*256@1 + 115*256@0 = 3.1946e+11
【INDEX ACCESS COSTS】
COST ：blevel + FF*leaf_blocks + FF*clustering_factor
0 + 1/362 * 1 + 1/362 * 76 = 2?
The formula essentially equates to the count of blocks that Oracle has to traverse in order to get all qualifying rows:
From the root page follow the tree down to the leaf page blevel blocks
Access all qualifying leaf blocks leaf blocks * filter factor
Access all qualifying data blocks clustering factor * filter factor
The cost calculations for other index access methods are:
Unique scan blevel+1
Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 )
Index-only blevel + FF*leaf_blocks
■イベント－－？？？
SQL>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME BUFFERS LEVEL6';
-- レベル6だと全バッファ、レベル4だとなに？
DUMPによるデータBLOCKチェック方法（BLOCK内データを確認可能）
SELECT EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = '&EXTENT_NAME'
/
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
----------- --------- --------- ------
0 9 209 87
最初のBLOCK_IDはBLOCK HEADERであるため、次のBLOCK_IDから取得する。
SQL> alter system dump datafile 9 block 210;
■イベント－－6502
event="6502 trace name errorstack level 12"
チューニング関連（SQL TRACEの使い方）
■SQL TRACEの使い方
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
●SID、SERIAL#、PADDR取得：
select sid, serial#, username, paddr, osuser, machine, program
from v$session
where username = '&username'
/
●SPID取得：
select spid from v$process
where addr = (select paddr from v$session where sid = &SID and serial# = &SERIAL)
/
●1回で取得
SELECT SPID
FROM V$PROCESS
WHERE ADDR IN (SElECT PADDR
FROM V$SESSION
WHERE USERNAME = '&username')
/
★1回で取得
SELECT P.SPID FROM V$PROCESS P, V$SESSION S
WHERE S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR
/
SPID
------------
12682
●トレースファイル確定：
SID_ora_SPID.trc --> instance_ORA_12682.trc
●TIMED_STATISTICSパラメータ確認
SHOW PARAMETER TIMED_STATISTICS;
ALTER SESSION SET TIMED_STATISTICS=TRUE;
●トレースファイル名確定：
Alter session set tracefile_identifier = 'SAI';
●トレース開始
ALTER SESSION SET SQL_TRACE = TRUE;
●SQLの実行
（略）
●トレース中止
ALTER SESSION SET SQL_TRACE = FALSE;
●分析
$ORACLE_BASE$/ADMIN/udump/配下からトレースファイルを取得
TKPROF SID_ora_SPID.trc SID_ora_SPID.prf aggregate=no sys=no explain=scott/tiger
ソードと追加して上位のものをとる
TKPROF SID_ora_SPID.trc SID_ora_SPID.prf aggregate=no sys=no sort=(fchela,exeela,prsela) explain=scott/tiger print=20
●TKPROF分析ポイント：
１．cpu << elapsed → I/Oのボトルネック
２．1 - (disk / ( query + current )) … メモリキャッシュのヒット率
３．( query + current ) / rows → 処理した行数に対する、アクセスブロック数
（1行を取得するために何ブロックを読んでいるか？）
( query + current ) / rows >> 20 → 索引の使用を検討 (20はあくまでも目安)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
●SID、SERIAL#、PADDR取得：
select sid, serial#, username, paddr, osuser, machine, program
from v$session
where username = '&username'
/
●SPID取得：
select spid from v$process
where addr = (select paddr from v$session where sid = &SID and serial# = &SERIAL)
/
●1回で取得
SELECT SPID
FROM V$PROCESS
WHERE ADDR IN (SElECT PADDR
FROM V$SESSION
WHERE USERNAME = '&username')
/
★1回で取得
SELECT P.SPID FROM V$PROCESS P, V$SESSION S
WHERE S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR
/
SPID
------------
12682
●トレースファイル確定：
SID_ora_SPID.trc --> instance_ORA_12682.trc
●TIMED_STATISTICSパラメータ確認
SHOW PARAMETER TIMED_STATISTICS;
ALTER SESSION SET TIMED_STATISTICS=TRUE;
●トレースファイル名確定：
Alter session set tracefile_identifier = 'SAI';
●トレース開始
ALTER SESSION SET SQL_TRACE = TRUE;
●SQLの実行
（略）
●トレース中止
ALTER SESSION SET SQL_TRACE = FALSE;
●分析
$ORACLE_BASE$/ADMIN/udump/配下からトレースファイルを取得
TKPROF SID_ora_SPID.trc SID_ora_SPID.prf aggregate=no sys=no explain=scott/tiger
ソードと追加して上位のものをとる
TKPROF SID_ora_SPID.trc SID_ora_SPID.prf aggregate=no sys=no sort=(fchela,exeela,prsela) explain=scott/tiger print=20
●TKPROF分析ポイント：
１．cpu << elapsed → I/Oのボトルネック
２．1 - (disk / ( query + current )) … メモリキャッシュのヒット率
３．( query + current ) / rows → 処理した行数に対する、アクセスブロック数
（1行を取得するために何ブロックを読んでいるか？）
( query + current ) / rows >> 20 → 索引の使用を検討 (20はあくまでも目安)
テーブルスペース
●空き容量
SELECT * FROM USER_TS_QUOTAS;
SELECT TABLESPACE_NAME, USERNAME,
BYTES/1024/1024 'USED(MB)',
MAX_BYTES/1024/1024 'MAX(MB)',
BYTES/MAX_BYTES * 100 'USED(%)'
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE 'USERNAME'
ORDER BY TABLESPACE_NAME
/
●テーブルスペースことの空き容量確認
select tablespace_name, sum(bytes)/1024/1024 as freeMB
from user_free_space
group by tablespace_name
/
●使用状況確認
SQL> select * from dba_tablespace_usage_metrics;
●使用済みテーブル容量：
SELECT substr(segment_name, 1, 30), bytes/1024/1024 as "Size in MB"
FROM user_segments
WHERE tablespace_name = 'TBS4001';
●セグメント容量確認
SELECT SUM(bytes)/1024/1024 as "SUMMARY SIZE"
FROM user_segments
WHERE tablespace_name = 'TBS4001'
/
SUMMARY SIZE
------------
542.75
SQL>
●空き容量確認
SELECT
A.TABLESPACE_NAME,
USED_KB,
AVAILABLE_KB,
ROUND(USED_KB/AVAILABLE_KB*100)||'%' PERCENTAGE
FROM (SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024 AVAILABLE_KB
FROM DBA_DATA_FILES
WHERE STATUS='AVAILABLE'
GROUP BY TABLESPACE_NAME
) A,
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024 USED_KB
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
;
TABLESPACE_NAME USED_KB AVAILABLE_KB PERCENTAGE
------------------------- ---------- ------------ -----------------------------------------
SYSAUX 412352 1024000 40%
SYSTEM 459136 2048000 22%
TBS1002 15087872 20480000 74%
TBS1003 13953792 20480000 68%
TBS1004 19832768 20480000 97%
TBS1005 13305664 20480000 65%
UNDO01 4928 3072000 0%
WORK 137024 3072000 4%
8行が選択されました。
SQL>
●テーブルスペース内の各ブロック確認
SELECT
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID AS START_BLOCK,
BLOCK_ID + BLOCKS - 1 AS END_BLOCK,
BYTES AS SIZE_BYTES,
BLOCKS AS BLOCK_CNT
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'TBS4001'
ORDER BY BLOCK_ID
SELECT * FROM USER_TS_QUOTAS;
SELECT TABLESPACE_NAME, USERNAME,
BYTES/1024/1024 'USED(MB)',
MAX_BYTES/1024/1024 'MAX(MB)',
BYTES/MAX_BYTES * 100 'USED(%)'
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE 'USERNAME'
ORDER BY TABLESPACE_NAME
/
●テーブルスペースことの空き容量確認
select tablespace_name, sum(bytes)/1024/1024 as freeMB
from user_free_space
group by tablespace_name
/
●使用状況確認
SQL> select * from dba_tablespace_usage_metrics;
●使用済みテーブル容量：
SELECT substr(segment_name, 1, 30), bytes/1024/1024 as "Size in MB"
FROM user_segments
WHERE tablespace_name = 'TBS4001';
●セグメント容量確認
SELECT SUM(bytes)/1024/1024 as "SUMMARY SIZE"
FROM user_segments
WHERE tablespace_name = 'TBS4001'
/
SUMMARY SIZE
------------
542.75
SQL>
●空き容量確認
SELECT
A.TABLESPACE_NAME,
USED_KB,
AVAILABLE_KB,
ROUND(USED_KB/AVAILABLE_KB*100)||'%' PERCENTAGE
FROM (SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024 AVAILABLE_KB
FROM DBA_DATA_FILES
WHERE STATUS='AVAILABLE'
GROUP BY TABLESPACE_NAME
) A,
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024 USED_KB
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
;
TABLESPACE_NAME USED_KB AVAILABLE_KB PERCENTAGE
------------------------- ---------- ------------ -----------------------------------------
SYSAUX 412352 1024000 40%
SYSTEM 459136 2048000 22%
TBS1002 15087872 20480000 74%
TBS1003 13953792 20480000 68%
TBS1004 19832768 20480000 97%
TBS1005 13305664 20480000 65%
UNDO01 4928 3072000 0%
WORK 137024 3072000 4%
8行が選択されました。
SQL>
●テーブルスペース内の各ブロック確認
SELECT
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID AS START_BLOCK,
BLOCK_ID + BLOCKS - 1 AS END_BLOCK,
BYTES AS SIZE_BYTES,
BLOCKS AS BLOCK_CNT
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'TBS4001'
ORDER BY BLOCK_ID
パーティション
●パーティション検索
SELECT
TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, HIGH_VALUE
PARTITION_POSITION, TABLESPACE_NAME, NUM_ROWS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = '&TABLE_NAME'
/
●索引化されたパーティションの検索(USER_IND_PARTITIONS)
SELECT * FROM USER_IND_PARTITIONS
ORDER BY INDEX_NAME, PARTITION_NAME
/
●テーブルのパーティションキー(USER_PART_KEY_COLUMNS)
SELECT * FROM ALL_PART_KEY_COLUMNS
WHERE OBJECT_TYPE = 'TABLE'
OR OBJECT_TYPE = 'INDEX'
/
SELECT
TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, HIGH_VALUE
PARTITION_POSITION, TABLESPACE_NAME, NUM_ROWS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = '&TABLE_NAME'
/
●索引化されたパーティションの検索(USER_IND_PARTITIONS)
SELECT * FROM USER_IND_PARTITIONS
ORDER BY INDEX_NAME, PARTITION_NAME
/
●テーブルのパーティションキー(USER_PART_KEY_COLUMNS)
SELECT * FROM ALL_PART_KEY_COLUMNS
WHERE OBJECT_TYPE = 'TABLE'
OR OBJECT_TYPE = 'INDEX'
/
ユーザ
/**
* ユーザ実行SQL確認
*/
SELECT B.ADDRESS, B.HASH_VALUE, B.SQL_TEXT
FROM V$OPEN_CURSOR A, V$SQLTEXT B
WHERE A.SID = &SID AND A.ADDRESS = B.ADDRESS AND A.HASH_VALUE = B.HASH_VALUE
ORDER BY B.ADDRESS, B.HASH_VALUE, B.COMMAND_TYPE, B.PIECE
/**
* ログインユーザ確認
*/
SELECT SID, STATUS, USERNAME, SCHEMANAME, OSUSER, MACHINE, PROGRAM, SERIAL#, COMMAND
FROM V$SESSION
WHERE TYPE = 'USER'
SQL PLUSでの確認
SHOW USER
/**
* ユーザ統計情報確認
*/
SELECT A.STATISTIC#, B.CLASS, B.NAME, A.VALUE
FROM V$SESSTAT A, V$STATNAME B
WHERE A.SID = &SID AND A.STATISTIC# = B.STATISTIC#
/**
* ユーザ確認
*/
SELECT USERNAME
FROM ALL_USERS
ORDER BY 1
★USERENV関数
SELECT USERENV('SESSIONID') FROM DUAL;
パラメータ：CLIENT_INFO, ENTRYID, ISDBA, LANG, LANGUAGE, SESSIONID, TERMINAL
* ユーザ実行SQL確認
*/
SELECT B.ADDRESS, B.HASH_VALUE, B.SQL_TEXT
FROM V$OPEN_CURSOR A, V$SQLTEXT B
WHERE A.SID = &SID AND A.ADDRESS = B.ADDRESS AND A.HASH_VALUE = B.HASH_VALUE
ORDER BY B.ADDRESS, B.HASH_VALUE, B.COMMAND_TYPE, B.PIECE
/**
* ログインユーザ確認
*/
SELECT SID, STATUS, USERNAME, SCHEMANAME, OSUSER, MACHINE, PROGRAM, SERIAL#, COMMAND
FROM V$SESSION
WHERE TYPE = 'USER'
SQL PLUSでの確認
SHOW USER
/**
* ユーザ統計情報確認
*/
SELECT A.STATISTIC#, B.CLASS, B.NAME, A.VALUE
FROM V$SESSTAT A, V$STATNAME B
WHERE A.SID = &SID AND A.STATISTIC# = B.STATISTIC#
/**
* ユーザ確認
*/
SELECT USERNAME
FROM ALL_USERS
ORDER BY 1
★USERENV関数
SELECT USERENV('SESSIONID') FROM DUAL;
パラメータ：CLIENT_INFO, ENTRYID, ISDBA, LANG, LANGUAGE, SESSIONID, TERMINAL
索引
■索引
●索引カラムを見る
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE INDEX_NAME = '&INDEX_NAME'
ORDER BY COLUMN_POSITION
/
●テーブルの索引を検索
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = '&TABLE_NAME'
/
●索引の再構築を検討する
ANALYZE INDEX &IDX_NAME VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, HEIGHT, PRE_ROWS, PRE_ROWS_LEN,
TRUNC(((DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100),2) "DEL_ROWS_LEN(%)",
TRUNC(((DEL_LF_ROWS/LF_ROWS)*100),2) "DEL_ROWS(%)"
FROM INDEX_STATS
/
HEIGHTが4以上のもの
DEL_PER_ROW_LENが20%以上のもの
DEL_PER_ROWが20%以上のものは
再構築の対象になる。
●再構築
ALTER INDEX &INDEX_NAME REBUILD ONLINE;
●ローカルパーティション索引作成：
CREATE INDEX PAR_DV210350_IX05 ON PAR_DV210350 (
XXXX_KBN1,
BRUI_CD_S1,
BRUI_CD_S2,
BRUI_CD_S3,
XXXX_YMD
) LOCAL (
PARTITION P01,
PARTITION P02,
PARTITION P03,
PARTITION P04,
PARTITION P05,
PARTITION P06,
PARTITION P07,
PARTITION P08
);
●索引カラムを見る
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE INDEX_NAME = '&INDEX_NAME'
ORDER BY COLUMN_POSITION
/
●テーブルの索引を検索
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = '&TABLE_NAME'
/
●索引の再構築を検討する
ANALYZE INDEX &IDX_NAME VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, HEIGHT, PRE_ROWS, PRE_ROWS_LEN,
TRUNC(((DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100),2) "DEL_ROWS_LEN(%)",
TRUNC(((DEL_LF_ROWS/LF_ROWS)*100),2) "DEL_ROWS(%)"
FROM INDEX_STATS
/
HEIGHTが4以上のもの
DEL_PER_ROW_LENが20%以上のもの
DEL_PER_ROWが20%以上のものは
再構築の対象になる。
●再構築
ALTER INDEX &INDEX_NAME REBUILD ONLINE;
●ローカルパーティション索引作成：
CREATE INDEX PAR_DV210350_IX05 ON PAR_DV210350 (
XXXX_KBN1,
BRUI_CD_S1,
BRUI_CD_S2,
BRUI_CD_S3,
XXXX_YMD
) LOCAL (
PARTITION P01,
PARTITION P02,
PARTITION P03,
PARTITION P04,
PARTITION P05,
PARTITION P06,
PARTITION P07,
PARTITION P08
);
登録:
投稿 (Atom)