Top SQL

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 ;