ablog

不器用で落着きのない技術者のメモ

SQL の実行時間を調べる

Oracle

Oracle で SQL の実行時間を調べる方法をメモ。

  • SQL の実行時間を調べる
set timing on
select name from emp;

NAME
--------------------
scott

Elapsed: 00:00:00.00
  • 合計実行時間の長いSQLを調べる（トップ10）
SET PAGESIZE 1000
SET LINES 140
COL sql_text            FORM A140
COL buffer_per_run      FORM 999999999999
COL disk_per_run        FORM 999999999999
COL cpu_time            FORM 999999999999
COL elapsed_time        FORM 999999999999
SELECT * FROM
(SELECT
        sql_text,address,hash_value,parse_calls,executions,
        buffer_gets,disk_reads,
        buffer_gets/executions buffer_per_run,
        disk_reads/executions disk_per_run,cpu_time,
        elapsed_time
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc) 
WHERE rownum <= 10;    

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME
-------- ---------- ----------- ---------- ----------- ---------- -------------- ------------- ------------- -------------
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
:mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
60DDDA1C 1955997807          16         16       15607        700            975            44       1526086       5970754

BEGIN :success := dbms_ha_alerts_prvt.post_instance_up; END;
60E22E7C   39802087           1          1        3104        112           3104           112        281511       4149313

CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result)
60D7D748 3067446691           3         18        7582        360            421            20        805218       2871349
...
  • 平均実行時間の長いSQLを調べる（トップ10）
SET PAGESIZE 1000
SET LINES 140
COL sql_text            FORM A140
COL buffer_per_run      FORM 999999999999
COL disk_per_run        FORM 999999999999
COL cpu_time            FORM 999999999999
COL elapsed_time        FORM 999999999999
COL avg_elapsed_time    FORM 999999999999
SELECT * FROM
(SELECT
        sql_text,address,hash_value,parse_calls,executions,
        buffer_gets,disk_reads,
        buffer_gets/executions buffer_per_run,
        disk_reads/executions disk_per_run,cpu_time,
        elapsed_time,
        elapsed_time/executions avg_elapsed_time
FROM v$sql
WHERE executions>0
ORDER BY avg_elapsed_time desc) 
WHERE rownum <= 10; 

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME AVG_ELAPSED_TIME
-------- ---------- ----------- ---------- ----------- ---------- -------------- ------------- ------------- ------------- ----------------
BEGIN :success := dbms_ha_alerts_prvt.post_instance_up; END;
60E22E7C   39802087           1          1        3104        112           3104           112        281511       4149313          4149313

call dbms_scheduler.auto_purge (  )
60DDB6A0 4082332207           1          1        1718        158           1718           158        200000       2058650          2058650

select table_objno, primary_instance, secondary_instance, owner_instance from sys.aq$_queue_table_affinities a  where  a.owner_instance <> :
1 and dbms_aqadm_syscalls.get_owner_instance(a.primary_instance,       a.secondary_instance,       a.owner_instance) = :2  order by table_ob
jno for update of a.owner_instance skip locked
60EBC78C  162926978           1          1         759        111            759           111        230334       1996067          1996067
...


[参考]
http://634.ayumu-baby.com/oracle/oracle_timing.html
ＯＲＡＣＬＥ／オラクルＳＱＬリファレンス（SQLPLUS）
プログラムの実行時間を計測する - オラクル・Oracle PL/SQL 入門
チューニングが必要なSQLを洗い出す (2/3)：Oracle SQLチューニング講座（4） - ＠IT