Oracle DBMS SQL TOP 조회 쿼리
안녕하세요. 유니원아이앤씨입니다.
Oracle DBMS 성능분석 관련하여 각 항목별로 SQL TOP을 조회하는 쿼리 포스팅합니다.
항목은 CPU 사용량, 실행시간, 처리량 기준입니다.
각 고객사 환경에 대비하여 DBA_HIST_ACTIVE_SESS_HISTORY 사용은 배제하여
모든 DBMS 적용 가능한 V$SQLAREA를 기준으로 작성되었습니다.
1. Grapth 형태 Query
- 눈으로 확인 가능하고 필요한 간략한 정보만을 나타내는 쿼리로 빠르게 정보수집이 가능할것으로 보입니다.
- 모든 Ratio 관련 컬럼의 총량 기준은, 전체 쿼리를 합해 나눈 백분율 값 입니다.
- 현재 SQL_FULL_TEXT의 내용은 최대 40자까지 표현되며 이는 해당 쿼리의 주석내용 정도만 파악하기 위함입니다, 내용이 더 필요할때는 set long 값을 늘려서 주시면 됩니다.
1.1 CPU 사용량 기준
set pages 400 lines 400
col graph format a30
col SQL_FULL_TEXT for a40
col username for a15
col module for a20
col exec for 999999999
set long 40
SELECT /* Finding SQL sort by CPU time usage */
main.sql_id, main.parsing_schema_name username, module, to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_ACTIVE_TIME, main.executions exec,main.cpu_time/1000 "CPU_TIME(Sec)", main.cpu_time_ratio
, '['||rpad(nvl(lpad('*', main.cpu_time_ratio/5, '*'), '-'), 20, '-')||']' "Graph"
, (SELECT -- listagg( sql_text , ' ' ) WITHIN GROUP ( ORDER BY piece)
rtrim(xmlagg(xmlelement(e, sql_text ,' ').extract('//text()') order by piece).GetClobVal(),' ')
FROM v$sqltext b WHERE b.sql_id = main.sql_id GROUP BY sql_id ) as sql_full_text
FROM ( SELECT sql_id ,
plan_hash_value ,
parsing_schema_name ,
executions,
buffer_gets,
disk_reads ,
direct_writes,
cpu_time ,
elapsed_time ,
LAST_ACTIVE_TIME,
module,
round(cpu_time/sum(cpu_time) OVER()*100,1) cpu_time_ratio ,
round(elapsed_time/sum(elapsed_time) OVER () *100 ,1) elapsed_time_ratio
FROM v$sqlarea a
WHERE executions > 1 and
parsing_schema_name not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and
NOT (module LIKE 'Golden%'
OR module LIKE 'TOAD%'
OR module LIKE 'toad%'
OR module LIKE 'Orange%'
OR module LIKE 'exp%'
OR module LIKE 'SQL*Plus%'
OR module LIKE 'SQL Developer%'
OR module LIKE 'oracle@%'
OR module LIKE 'sqlplus@%'
)
AND NOT (TRIM(SQL_TEXT) LIKE '%:Q%'
OR TRIM(SQL_TEXT) LIKE 'declare%'
OR TRIM(SQL_TEXT) LIKE 'DECLARE%'
OR TRIM(SQL_TEXT) LIKE 'truncate%'
OR TRIM(SQL_TEXT) LIKE 'TRUNCATE%'
OR TRIM(SQL_TEXT) LIKE '%dba\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE '%DBA\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE 'SQLGateMain%'
OR TRIM(SQL_TEXT) LIKE 'ALTER SESSION%'
OR TRIM(SQL_TEXT) LIKE '%v$%'
OR TRIM(SQL_TEXT) LIKE '%x$%')
ORDER BY cpu_time/sum(cpu_time) OVER()*100 desc ) main
WHERE rownum <= 20 ;
사용 예시)
1.2 실행시간 기준
set pages 400 lines 400
col graph format a30
col SQL_FULL_TEXT for a40
col username for a15
col module for a20
col exec for 999999999
set long 40
SELECT /* Finding SQL sort by elapsed time */
main.sql_id, main.parsing_schema_name username, module, to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_ACTIVE_TIME, main.executions exec, main.elapsed_time/1000 "ELAPSED_TIME(Sec)", main.elapsed_time_ratio
, '['||rpad(nvl(lpad('*', main.elapsed_time_ratio/5, '*'), '-'), 20, '-')||']' "Graph"
, (SELECT -- listagg( sql_text , ' ' ) WITHIN GROUP ( ORDER BY piece)
rtrim(xmlagg(xmlelement(e, sql_text ,' ').extract('//text()') order by piece).GetClobVal(),' ')
FROM v$sqltext b WHERE b.sql_id = main.sql_id GROUP BY sql_id ) as sql_full_text
FROM ( SELECT sql_id ,
plan_hash_value ,
parsing_schema_name ,
executions,
buffer_gets,
disk_reads ,
direct_writes,
cpu_time ,
elapsed_time ,
LAST_ACTIVE_TIME,
module,
round(cpu_time/sum(cpu_time) OVER()*100,1) cpu_time_ratio ,
round(elapsed_time/sum(elapsed_time) OVER () *100 ,1) elapsed_time_ratio
FROM v$sqlarea a
WHERE executions > 1 and
parsing_schema_name not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and
NOT (module LIKE 'Golden%'
OR module LIKE 'TOAD%'
OR module LIKE 'toad%'
OR module LIKE 'Orange%'
OR module LIKE 'exp%'
OR module LIKE 'SQL*Plus%'
OR module LIKE 'SQL Developer%'
OR module LIKE 'oracle@%'
OR module LIKE 'sqlplus@%'
)
AND NOT (TRIM(SQL_TEXT) LIKE '%:Q%'
OR TRIM(SQL_TEXT) LIKE 'declare%'
OR TRIM(SQL_TEXT) LIKE 'DECLARE%'
OR TRIM(SQL_TEXT) LIKE 'truncate%'
OR TRIM(SQL_TEXT) LIKE 'TRUNCATE%'
OR TRIM(SQL_TEXT) LIKE '%dba\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE '%DBA\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE 'SQLGateMain%'
OR TRIM(SQL_TEXT) LIKE 'ALTER SESSION%'
OR TRIM(SQL_TEXT) LIKE '%v$%'
OR TRIM(SQL_TEXT) LIKE '%x$%')
ORDER BY elapsed_time_ratio desc ) main
WHERE rownum <= 20 ;
사용 예시)
1.3 처리량 기준
set pages 400 lines 400
col graph format a30
col SQL_FULL_TEXT for a40
col username for a15
col module for a20
col exec for 999999999
set long 40
SELECT /* Finding SQL sort by Logical Reads usage */
main.sql_id, main.parsing_schema_name username, module, to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_ACTIVE_TIME,main.executions exec, main.buffer_gets, main.buffer_gets_ratio
, '['||rpad(nvl(lpad('*', main.buffer_gets_ratio/5, '*'), '-'), 20, '-')||']' "Graph"
, (SELECT -- listagg( sql_text , ' ' ) WITHIN GROUP ( ORDER BY piece)
rtrim(xmlagg(xmlelement(e, sql_text ,' ').extract('//text()') order by piece).GetClobVal(),' ')
FROM v$sqltext b WHERE b.sql_id = main.sql_id GROUP BY sql_id ) as sql_full_text
FROM ( SELECT a.sql_id ,
plan_hash_value ,
parsing_schema_name ,
executions,
buffer_gets,
disk_reads ,
direct_writes,
cpu_time ,
elapsed_time ,
LAST_ACTIVE_TIME,
module,
round(buffer_gets/sum(buffer_gets) OVER()*100,1) buffer_gets_ratio,
round(cpu_time/sum(cpu_time) OVER()*100,1) cpu_time_ratio ,
round(elapsed_time/sum(elapsed_time) OVER () *100 ,1) elapsed_time_ratio
FROM v$sqlarea a
WHERE executions > 1 and
parsing_schema_name not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and
NOT (module LIKE 'Golden%'
OR module LIKE 'TOAD%'
OR module LIKE 'toad%'
OR module LIKE 'Orange%'
OR module LIKE 'exp%'
OR module LIKE 'SQL*Plus%'
OR module LIKE 'SQL Developer%'
OR module LIKE 'oracle@%'
OR module LIKE 'sqlplus@%'
)
AND NOT (TRIM(SQL_TEXT) LIKE '%:Q%'
OR TRIM(SQL_TEXT) LIKE 'declare%'
OR TRIM(SQL_TEXT) LIKE 'DECLARE%'
OR TRIM(SQL_TEXT) LIKE 'truncate%'
OR TRIM(SQL_TEXT) LIKE 'TRUNCATE%'
OR TRIM(SQL_TEXT) LIKE '%dba\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE '%DBA\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE 'SQLGateMain%'
OR TRIM(SQL_TEXT) LIKE 'ALTER SESSION%'
OR TRIM(SQL_TEXT) LIKE '%v$%'
OR TRIM(SQL_TEXT) LIKE '%x$%')
ORDER BY buffer_gets_ratio desc ) main
WHERE rownum <= 20 ;
사용 예시)
2. Detail Query
- 1번 항목에 대한 자세한 항목들을 출력하는 쿼리입니다.
2.1 SQL쿼리가 CPU 사용량 기준 TOP 20
SELECT /* Finding SQL sort by CPU time usage */
main.*
, (SELECT -- listagg( sql_text , ' ' ) WITHIN GROUP ( ORDER BY piece)
rtrim(xmlagg(xmlelement(e, sql_text ,' ').extract('//text()') order by piece).GetClobVal(),' ')
FROM v$sqltext b WHERE b.sql_id = main.sql_id GROUP BY sql_id ) as sql_full_text
, (SELECT -- listagg( name || ' = ' || nvl(value_string,'NULL') , ' / ') within group (order by sql_id)
rtrim(xmlagg(xmlelement(e,name || ' = ' || nvl(value_string,'NULL'),' / ').extract('//text()') order by sql_id).GetClobVal(),' / ')
FROM V$SQL_BIND_CAPTURE c
WHERE c.was_captured = 'YES'
AND c.sql_id = main.sql_id
AND (sql_id , last_captured) IN (SELECT sql_id , max(last_captured) as last_captured FROM V$SQL_BIND_CAPTURE d WHERE was_captured = 'YES' AND d.sql_id = main.sql_id GROUP BY sql_id )
GROUP BY sql_id ) as bind_values
FROM ( SELECT sql_id ,
plan_hash_value ,
parsing_schema_name ,
executions,
buffer_gets,
disk_reads ,
direct_writes,
cpu_time ,
elapsed_time ,
round(cpu_time/sum(cpu_time) OVER()*100,1) cpu_time_ratio ,
round(elapsed_time/sum(elapsed_time) OVER () *100 ,1) elapsed_time_ratio
FROM v$sqlarea a
WHERE executions > 1 and
parsing_schema_name not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and
NOT (module LIKE 'Golden%'
OR module LIKE 'TOAD%'
OR module LIKE 'toad%'
OR module LIKE 'Orange%'
OR module LIKE 'exp%'
OR module LIKE 'SQL*Plus%'
OR module LIKE 'SQL Developer%'
OR module LIKE 'oracle@%'
OR module LIKE 'sqlplus@%'
)
AND NOT (TRIM(SQL_TEXT) LIKE '%:Q%'
OR TRIM(SQL_TEXT) LIKE 'declare%'
OR TRIM(SQL_TEXT) LIKE 'DECLARE%'
OR TRIM(SQL_TEXT) LIKE 'truncate%'
OR TRIM(SQL_TEXT) LIKE 'TRUNCATE%'
OR TRIM(SQL_TEXT) LIKE '%dba\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE '%DBA\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE 'SQLGateMain%'
OR TRIM(SQL_TEXT) LIKE 'ALTER SESSION%'
OR TRIM(SQL_TEXT) LIKE '%v$%'
OR TRIM(SQL_TEXT) LIKE '%x$%')
ORDER BY cpu_time/sum(cpu_time) OVER()*100 desc ) main
WHERE rownum <= 20 ;
2.2 평균 수행시간 기준 Top 20
SELECT /* Finding SQL sort by elapsed time */
main.*
, (SELECT -- listagg( sql_text , ' ' ) WITHIN GROUP ( ORDER BY piece)
rtrim(xmlagg(xmlelement(e, sql_text ,' ').extract('//text()') order by piece).GetClobVal(),' ')
FROM v$sqltext b WHERE b.sql_id = main.sql_id GROUP BY sql_id ) as sql_full_text
, (SELECT -- listagg( name || ' = ' || nvl(value_string,'NULL') , ' / ') within group (order by sql_id)
rtrim(xmlagg(xmlelement(e,name || ' = ' || nvl(value_string,'NULL'),' / ').extract('//text()') order by sql_id).GetClobVal(),' / ')
FROM V$SQL_BIND_CAPTURE c
WHERE c.was_captured = 'YES'
AND c.sql_id = main.sql_id
AND (sql_id , last_captured) IN (SELECT sql_id , max(last_captured) as last_captured FROM V$SQL_BIND_CAPTURE d WHERE was_captured = 'YES' AND d.sql_id = main.sql_id GROUP BY sql_id )
GROUP BY sql_id ) as bind_values
FROM ( SELECT sql_id ,
plan_hash_value ,
parsing_schema_name ,
executions,
buffer_gets,
disk_reads ,
direct_writes,
cpu_time ,
elapsed_time ,
round(cpu_time/sum(cpu_time) OVER()*100,1) cpu_time_ratio ,
round(elapsed_time/sum(elapsed_time) OVER () *100 ,1) elapsed_time_ratio
FROM v$sqlarea a
WHERE executions > 1 and
parsing_schema_name not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and
NOT (module LIKE 'Golden%'
OR module LIKE 'TOAD%'
OR module LIKE 'toad%'
OR module LIKE 'Orange%'
OR module LIKE 'exp%'
OR module LIKE 'SQL*Plus%'
OR module LIKE 'SQL Developer%'
OR module LIKE 'oracle@%'
OR module LIKE 'sqlplus@%'
)
AND NOT (TRIM(SQL_TEXT) LIKE '%:Q%'
OR TRIM(SQL_TEXT) LIKE 'declare%'
OR TRIM(SQL_TEXT) LIKE 'DECLARE%'
OR TRIM(SQL_TEXT) LIKE 'truncate%'
OR TRIM(SQL_TEXT) LIKE 'TRUNCATE%'
OR TRIM(SQL_TEXT) LIKE '%dba\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE '%DBA\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE 'SQLGateMain%'
OR TRIM(SQL_TEXT) LIKE 'ALTER SESSION%'
OR TRIM(SQL_TEXT) LIKE '%v$%'
OR TRIM(SQL_TEXT) LIKE '%x$%')
ORDER BY elapsed_time / executions desc ) main
WHERE rownum <= 20 ;
2.3 처리량 기준 Top 20
SELECT /* Finding SQL sort by Logical Reads usage */
main.*
, (SELECT -- listagg( sql_text , ' ' ) WITHIN GROUP ( ORDER BY piece)
rtrim(xmlagg(xmlelement(e, sql_text ,' ').extract('//text()') order by piece).GetClobVal(),' ')
FROM v$sqltext b WHERE b.sql_id = main.sql_id GROUP BY sql_id ) as sql_full_text
, (SELECT -- listagg( name || ' = ' || nvl(value_string,'NULL') , ' / ') within group (order by sql_id)
rtrim(xmlagg(xmlelement(e,name || ' = ' || nvl(value_string,'NULL'),' / ').extract('//text()') order by sql_id).GetClobVal(),' / ')
FROM V$SQL_BIND_CAPTURE c
WHERE c.was_captured = 'YES'
AND c.sql_id = main.sql_id
AND (sql_id , last_captured) IN (SELECT sql_id , max(last_captured) as last_captured FROM V$SQL_BIND_CAPTURE d WHERE was_captured = 'YES' AND d.sql_id = main.sql_id GROUP BY sql_id )
GROUP BY sql_id ) as bind_values
FROM ( SELECT sql_id ,
plan_hash_value ,
parsing_schema_name ,
executions,
buffer_gets,
disk_reads ,
direct_writes,
cpu_time ,
elapsed_time ,
round(cpu_time/sum(cpu_time) OVER()*100,1) cpu_time_ratio ,
round(elapsed_time/sum(elapsed_time) OVER () *100 ,1) elapsed_time_ratio
FROM v$sqlarea a
WHERE executions > 1 and
parsing_schema_name not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
and
NOT (module LIKE 'Golden%'
OR module LIKE 'TOAD%'
OR module LIKE 'toad%'
OR module LIKE 'Orange%'
OR module LIKE 'exp%'
OR module LIKE 'SQL*Plus%'
OR module LIKE 'SQL Developer%'
OR module LIKE 'oracle@%'
OR module LIKE 'sqlplus@%'
)
AND NOT (TRIM(SQL_TEXT) LIKE '%:Q%'
OR TRIM(SQL_TEXT) LIKE 'declare%'
OR TRIM(SQL_TEXT) LIKE 'DECLARE%'
OR TRIM(SQL_TEXT) LIKE 'truncate%'
OR TRIM(SQL_TEXT) LIKE 'TRUNCATE%'
OR TRIM(SQL_TEXT) LIKE '%dba\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE '%DBA\_%' escape '\'
OR TRIM(SQL_TEXT) LIKE 'SQLGateMain%'
OR TRIM(SQL_TEXT) LIKE 'ALTER SESSION%'
OR TRIM(SQL_TEXT) LIKE '%v$%'
OR TRIM(SQL_TEXT) LIKE '%x$%')
ORDER BY buffer_gets / executions desc ) main
WHERE rownum <= 20 ;
'기술이야기 > 전문가 기술기고' 카테고리의 다른 글
| [부하테스트] Hammer DB 참조 자료 (0) | 2020.02.10 |
|---|---|
| 19c OGG 구성 테스트 (0) | 2020.02.04 |
| Flashback Versions Query & Transaction Query (0) | 2017.12.22 |
| Oracle DB 12c R2 국산 암호화 알고리즘(ARIA, SEED) (0) | 2017.11.27 |
| [Linux] IBM SpectrumScale 설치 가이드 (0) | 2017.10.31 |