OS환경 : Oracle Linux 6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
쿼리 : 오라클 오래걸리는 쿼리 LONG 쿼리 찾기
실행시간 5초 이상 걸린 쿼리 조회
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
SQL>
SELECT * FROM(
SELECT ROUND(ELAPSED_TIME/EXECUTIONS/1000000,3) AS ELAPSED_TIME
, LAST_ACTIVE_TIME
, HASH_VALUE
, SQL_TEXT
FROM V$SQL
WHERE parsing_schema_name = '스키마명'
AND LAST_ACTIVE_TIME >= TO_DATE('20180307', 'YYYYMMDD')
)
WHERE ELAPSED_TIME > 5 --초 단위 입력
order by ELAPSED_TIME desc;
ELAPSED_TIME LAST_ACTI HASH_VALUE
------------ --------- ----------
SQL_TEXT
----------------------------------------------------------------------------------------------------
3.556 19-DEC-18 887234468
select substr(a.HOST_NAME,1,decode(instr(a.HOST_NAME,'.',1)-1, -1, length(a.HOST_NAME), instr(a.HOST
_NAME,'.',1)-1))||'.RDBMS|prsjedb|'|| a.INSTANCE_NAME||'.'|| b.name ||'='||b.value from gv$insta
nce a, gv$parameter b where a.inst_id = b.inst_id and lower(b.name) in ('diagnostic_dest', 'user_
dump_dest', 'background_dump_dest')
2.961 19-DEC-18 3216559737
delete from WRH$_SYSMETRIC_HISTORY tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :e
nd_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b
where (tab.dbid = b.dbid) and (tab.snap_id >= b.start_snap_id) and
(tab.snap_id <= b.end_snap_id))
2.51 19-DEC-18 2216300800
insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, cpu_count, cpu_core_
count, cpu_socket_count) select :dbid, :version, :bind1, gv.gv_cpu_count, gv.gv_cpu_core_coun
t, gv.gv_cpu_socket_count from (select sum(cpu_count_current) as gv_cpu_count, s
um(cpu_core_count_current) as gv_cpu_core_count, sum(cpu_socket_count_current) as gv_cpu
_socket_count from GV$LICENSE) gv where (nvl(gv_cpu_count, -1), nvl(gv_cpu_core
_count, -1), nvl(gv_cpu_socket_count, -1)) not in (select nvl(cpu_count, -1) as c
u_cpu_count, nvl(cpu_core_count, -1) as cu_cpu_core_count, nvl(c
pu_socket_count, -1) as cu_cpu_socket_count from (select cpu_count, cpu_core_count, cpu_so
cket_count from WRI$_DBU_CPU_USAGE order by timestamp desc)
where rownum <= 1)
1.995 17-DEC-18 2216300800
insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, cpu_count, cpu_core_
count, cpu_socket_count) select :dbid, :version, :bind1, gv.gv_cpu_count, gv.gv_cpu_core_coun
t, gv.gv_cpu_socket_count from (select sum(cpu_count_current) as gv_cpu_count, s
um(cpu_core_count_current) as gv_cpu_core_count, sum(cpu_socket_count_current) as gv_cpu
_socket_count from GV$LICENSE) gv where (nvl(gv_cpu_count, -1), nvl(gv_cpu_core
_count, -1), nvl(gv_cpu_socket_count, -1)) not in (select nvl(cpu_count, -1) as c
u_cpu_count, nvl(cpu_core_count, -1) as cu_cpu_core_count, nvl(c
pu_socket_count, -1) as cu_cpu_socket_count from (select cpu_count, cpu_core_count, cpu_so
cket_count from WRI$_DBU_CPU_USAGE order by timestamp desc)
where rownum <= 1)
|
결과에서 나온 쿼리로 실행계획 확인
|
1
2
3
4
|
SQL> explain plan for
위에서 나온 쿼리
SQL> select * from table(dbms_xplan.display);
|
실제 실행 예시
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
SQL> explain plan for
2 SELECT substr(a.host_name,1,decode(Instr(a.host_name,'.',1)-1,
-1, Length(a.host_name),
instr(a.host_name,'.',1)-1))
||'.RDBMS|prsjedb|'
|| a.instance_name
||'.'
|| b.name
||'='
||b.value
FROM gv$instance a,
gv$parameter b
WHERE a.inst_id = b.inst_id
AND lower(b.name) IN ('diagnostic_dest',
'user_dump_dest',
'background_dump_dest'); 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Explained.
SJEDB1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 900221977
----------------------------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |I
N-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 100 | 206K| 5 (100)| 00:00:01 | |
| |
|* 1 | HASH JOIN | | 100 | 206K| 5 (100)| 00:00:01 | |
| |
| 2 | PX COORDINATOR | | 1 | 2057 | 0 (0)| 00:00:01 | |
| |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 2115 | 0 (0)| 00:00:01 | Q1,00 |
P->S | QC (RAND) |
|* 4 | VIEW | GV$PARAMETER | | | | | Q1,00 |
PCWP | |
|* 5 | HASH JOIN | | 1 | 2115 | 0 (0)| 00:00:01 | Q1,00 |
PCWP | |
|* 6 | FIXED TABLE FULL | X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 | Q1,00 |
PCWP | |
| 7 | FIXED TABLE FULL | X$KSPPCV | 100 | 198K| 0 (0)| 00:00:01 | Q1,00 |
PCWP | |
| 8 | PX COORDINATOR | | 10000 | 556K| 4 (100)| 00:00:01 | |
| |
| 9 | PX SEND QC (RANDOM) | :TQ20000 | 10000 | 888K| 4 (100)| 00:00:01 | Q2,00 |
P->S | QC (RAND) |
| 10 | VIEW | GV$INSTANCE | | | | | Q2,00 |
PCWP | |
| 11 | MERGE JOIN CARTESIAN | | 10000 | 888K| 4 (100)| 00:00:01 | Q2,00 |
PCWP | |
| 12 | MERGE JOIN CARTESIAN| | 100 | 9100 | 0 (0)| 00:00:01 | Q2,00 |
PCWP | |
|* 13 | FIXED TABLE FULL | X$KVIT | 1 | 34 | 0 (0)| 00:00:01 | Q2,00 |
PCWP | |
| 14 | BUFFER SORT | | 100 | 5700 | 0 (0)| 00:00:01 | Q2,00 |
PCWP | |
| 15 | FIXED TABLE FULL | X$KSUXSINST | 100 | 5700 | 0 (0)| 00:00:01 | Q2,00 |
PCWP | |
| 16 | BUFFER SORT | | 100 | | 4 (100)| 00:00:01 | Q2,00 |
PCWP | |
| 17 | FIXED TABLE FULL | X$QUIESCE | 100 | | 0 (0)| 00:00:01 | Q2,00 |
PCWP | |
----------------------------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."INST_ID"="B"."INST_ID")
4 - filter(LOWER("B"."NAME")='diagnostic_dest' OR LOWER("B"."NAME")='user_dump_dest' OR
LOWER("B"."NAME")='background_dump_dest')
5 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",
5)>0)
6 - filter(BITAND("KSPPIFLG",268435456)=0 AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%' AND
(LOWER("KSPPINM")='diagnostic_dest' OR LOWER("KSPPINM")='user_dump_dest' OR
LOWER("KSPPINM")='background_dump_dest'))
13 - filter("KVITTAG"='kcbwst')
Note
-----
- statement not queuable: gv$ statement
41 rows selected.
|
참조 : https://positivemh.tistory.com/344
ORA-01476: divisor is equal to zero
OS환경 : Oracle Linux 6.8(64bit) DB 환경 : Oracle Database 11.2.0.4 에러 : ORA-01476: divisor is equal to zero 쿼리 조회 시 에러 발생 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT * FROM( SELECT ROUND..
positivemh.tistory.com
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| v$active_session_history뷰를 통해 Top 5 wait events 확인 (0) | 2018.12.24 |
|---|---|
| library cache pin wait 높을 때 점검 쿼리 (0) | 2018.12.24 |
| [스크랩] SQL튜닝 방법론 (1) | 2018.12.18 |
| 오라클 11g 자동통계정보수집 table lock , unlock (0) | 2018.12.13 |
| DISK IO를 많이 발생시키는 SQL 문장을 찾는 쿼리 (0) | 2018.12.12 |