Oracle SQLチューニング Season2（第7回）第2章　SQLチューニング対象選定方法（4/8）

[V$SQLAREA活用スクリプト］

SELECT ROWNUM cnt 、
       t1.*。
FROM (
        SELECT t1.parsing_schema_name 、
               t1.module 、
               t1.sql_id 、
               t1.hash_value 、
               t1.substr_sqltext 、
               t1.executions 、
               t1.buffer_gets 、
               t1.disk_reads 、
               t1.rows_processed 、
               t1.lio 、
               t1.elapsed_sec 、
               t1.cpu_sec 、
               ROUND( t1.cpu_time /t1.cpu_time_total*100 , 1 ) ratio_cpu 、
               ROUND( t1.elapsed_time /elapsed_time_total * 100 , 1 ) ratio_elapsed
        FROM (
                SELECT parsing_schema_name 、
                       MODULE 、
                       sql_id 、
                       hash_value 、
                       SUBSTR( sql_text , 1 , 100 ) substr_sqltext 、
                       実行 、
                       buffer_gets 、
                       disk_reads 、
                       rows_processed 、
                       cpu_time 、
                       elapsed_time 、
                       ROUND( buffer_gets / executions ) , 1 ) lio 、
                       ROUND( elapsed_time / executions ) /1000000 , 1 ) elapsed_sec 、
                       ROUND( cpu_time / executions ) /1000000 , 1 ) cpu_sec 、
                       SUM( cpu_time ) over( ) cpu_time_total 、
                       SUM( elapsed_time ) over( ) elapsed_time_total
                FROM v$sqlarea s
               ) t1
        WHERE t1.executions > 0
        AND t1.parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN' )
        ORDER BY ratio_cpu DESC
       ) t1
WHERE ROWNUM <= 100

[DBA_HIST_SQLSTATを活用したスクリプト ]

SELECT sql_id 、
       schema_name 、
       MODULE 、
       ela_ratio 、
       ela_tot 、
       cpu_ratio 、
       cpu_tot 、
       exec_ratio 、
       exec_tot 、
       lio_ratio 、
       lio_tot 、
       pio_ratio 、
       pio_tot 、
       rows_ratio 、
       rows_tot
FROM (
        SELECT sql_id 、
               parsing_schema_name schema_name 、
               NVL( SUBSTR( b.module , 1 , 15 ) , '-' ) MODULE 、
               ROUND( RATIO_TO_REPORT(SUM(b.elapsed_time_delta)) over() * 100, 1 ) AS ela_ratio 、
               ROUND( SUM( b.elapsed_time_delta ) /1000000 , 0 ) AS ela_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.cpu_time_delta)) over() * 100, 1 ) AS cpu_ratio 、
               ROUND( SUM( b.cpu_time_delta ) /1000000 , 0 ) AS cpu_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.executions_delta)) over() * 100, 1 ) AS exec_ratio 、
               SUM( b.executions_delta ) AS exec_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.buffer_gets_delta)) over() * 100, 1 ) AS lio_ratio 、
               SUM( b.buffer_gets_delta ) AS lio_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.disk_reads_delta)) over() * 100, 1 ) AS pio_ratio 、
               SUM( b.disk_reads_delta ) AS pio_tot 、
               ROUND( RATIO_TO_REPORT(SUM(b.rows_processed_delta)) over() * 100, 1 ) AS rows_ratio、
               SUM( b.rows_processed_delta ) AS rows_tot
        FROM dba_hist_snapshot a 、
               dba_hist_sqlstat b
        WHERE a.instance_number=1
        AND a.begin_interval_time >= TO_DATE( :b1 , 'YYYY-MM-DD' )
        AND a.end_interval_time <= TO_DATE( :b2 , 'YYYY-MM-DD' ) +0.99999
        AND a.dbid=b.dbid
        AND b.parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN')
        AND a.instance_number = b.instance_number
        AND a.snap_id=b.snap_id
        GROUP BY b.sql_id 、
               b.parsing_schema_name 、
               b.モジュール
        ORDER BY cpu_ratio DESC
       )
WHERE rownum<=100 ；