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. 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 ;