• 转://利用从awr中查找好的执行计划来优化SQL


    原文地址:http://blog.csdn.net/zengxuewen2045/article/details/53495613

    同事反应系统慢,看下是不是有锁了,登入数据库检查,没有异常锁定,但发现有支SQL有30个会话执行,且出现异常等待事件:read by other session和db file sequential read.

    这两个等待事件描述应该是这样的:
    会话a在进行把磁盘上的数据块读到内存(data buffer cache)中这个操作,
    会话b和会话c 同时也请求这个数据块。因为会话a还未完全读入内存(data buffer cache),就导致了b,c read by other session
    所以会话a一般是db file sequential read  或 db file scattered read。
    这个是Oracle 10g 从oracle 9i的buffer busy waits中分离出来的,也是一种热块现象。

    SQL> select count(*) from gv$session where sql_id='bpgqf9b2ykqmu';

      COUNT(*)
    ----------
            30

    SQL> select distinct event from gv$session where sql_id='bpgqf9b2ykqmu';

    EVENT
    ----------------------------------------------------------------
    db file sequential read
    read by other session

    通过select * from table(dbms_xplan.display_cursor('bpgqf9b2ykqmu',''))查不到任何计划,再查awr:select * from table(dbms_xplan.display_awr('bpgqf9b2ykqmu','')),
    执行计划如下:

    PLAN_TABLE_OUTPUT

    SQL_ID  b93dmn0vjqyfn, child number 0
    -------------------------------------
    PLAN_TABLE_OUTPUT

    SQL_ID bpgqf9b2ykqmu
    --------------------
    SQL省略
     
    Plan hash value: 1664736063
     
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                    |                     |       |       |  1690K(100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL            |                     |       |       |            |          |
    |   2 |   FILTER                            |                     |       |       |            |          |
    |   3 |    FILTER                           |                     |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID     | SFC_WIP_LOG         |   122 | 26108 |  1690K  (1)| 05:38:06 |
    |   5 |      INDEX RANGE SCAN               | IDX_SFC_WIP_LOG_IX9 |  2245K|       | 83695   (1)| 00:16:45 |
    |   6 |    COLLECTION ITERATOR PICKLER FETCH|                     |     1 |     2 |    29   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------
     
    SQL_ID bpgqf9b2ykqmu
    --------------------
    SQL省略
     
    Plan hash value: 3882290683
     
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                    |                     |       |       |     5 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL            |                     |       |       |            |          |
    |   2 |   FILTER                            |                     |       |       |            |          |
    |   3 |    FILTER                           |                     |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID     | SFC_WIP_LOG         |     1 |   214 |     5   (0)| 00:00:01 |
    |   5 |      INDEX RANGE SCAN               | IDX_SFC_WIP_LOG_IX6 |     1 |       |     4   (0)| 00:00:01 |
    |   6 |    COLLECTION ITERATOR PICKLER FETCH|                     |     1 |     2 |    29   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------

    上面出现两个执行计划,在于选择的索引不同,索引IDX_SFC_WIP_LOG_IX6比IDX_SFC_WIP_LOG_IX9的选择性要好。

    采用下面方法绑定,并没有生效,因为执行计划是在awr中,并非在cursor中。
    DECLARE
      -- Local variables here
      i     INTEGER;
    BEGIN
      i := dbms_spm.load_plans_from_cursor_cache(sql_id=>'bpgqf9b2ykqmu',
    plan_hash_value => '3882290683');  
    END;

    先查看awr中好的执行计划及相关信息:
    set line 1000
    set pagesze 1000
    col inst_snapid_sqlid_plan for a50
    col Date_Time for a20
    col executions_delta for 999999
    col avg_etime for 99999
    col avg_cputime for 99999
    col avg_lio for 99999
    col avg_pio for 99999
    col avg_row for 99999
    SELECT 
    SQL.instance_number||','||s.snap_id||','||sql_id||','||PLAN_HASH_VALUE  inst_snapid_sqlid_plan,
    to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
    SQL.executions_delta,
     RTRIM(to_char(decode(SQL.executions_delta,
                          0,
                          to_number(null),
                          SQL.elapsed_time_delta / SQL.executions_delta / 1000000),
                   'fm99990.999'),
           '.') as avg_etime,
     RTRIM(to_char(decode(SQL.executions_delta,
                          0,
                          to_number(null),
                          SQL.cpu_time_delta / SQL.executions_delta / 1000000),
                   'fm99990.999999'),
           '.') as avg_cputime,          
     RTRIM(to_char(decode(SQL.executions_delta,
                          0,
                          to_number(null),
                          SQL.buffer_gets_delta / SQL.executions_delta ),
                   'fm999999990'),
           '.') as avg_lio,
     RTRIM(to_char(decode(SQL.executions_delta,
                          0,
                          to_number(null),
                          SQL.DISK_READS_DELTA / SQL.executions_delta ),
                   'fm999999990'),
           '.') as avg_pio,               
     RTRIM(to_char(decode(SQL.executions_delta,
                          0,
                          to_number(null),
                          SQL.ROWS_PROCESSED_DELTA / SQL.executions_delta ),
                   'fm999999990.9'),
           '.') as avg_row
    FROM
    dba_hist_sqlstat SQL,
    dba_hist_snapshot s
    WHERE 
    s.snap_id = SQL.snap_id and s.instance_number=sql.instance_number
    AND sql_id in ('52tsgf9s200h0') and  BEGIN_INTERVAL_TIME > trunc(sysdate - 2+1) 
     order by Date_Time, plan_hash_value,SQL.instance_number;
    INST_SNAPID_SQLID_PLAN                             DATE_TIME            EXECUTIONS_DELTA AVG_ETIME  AVG_CPUTIME   AVG_LIO    AVG_PIO    AVG_ROW
    -------------------------------------------------- -------------------- ---------------- ---------- ------------- ---------- ---------- ------------
    2,55718,bpgqf9b2ykqmu,3882290683                   12/05/16_1030_1045                  7 108.75     1.265896      176847     2525       18732
    1,55739,bpgqf9b2ykqmu,3882290683                   12/05/16_1545_1600                  0
    1,55740,bpgqf9b2ykqmu,3882290683                   12/05/16_1600_1615                  0
    1,55741,bpgqf9b2ykqmu,3882290683                   12/05/16_1615_1630                  0
    1,55742,bpgqf9b2ykqmu,3882290683                   12/05/16_1630_1645                  0
    1,55743,bpgqf9b2ykqmu,3882290683                   12/05/16_1645_1700                  0
    1,55744,bpgqf9b2ykqmu,3882290683                   12/05/16_1700_1715                  0
    1,55745,bpgqf9b2ykqmu,3882290683                   12/05/16_1715_1730                  0
    1,55747,bpgqf9b2ykqmu,3882290683                   12/05/16_1745_1800                  0
    1,55748,bpgqf9b2ykqmu,3882290683                   12/05/16_1800_1815                  0
    1,55749,bpgqf9b2ykqmu,3882290683                   12/05/16_1815_1830                  0
    1,55750,bpgqf9b2ykqmu,3882290683                   12/05/16_1830_1845                  0
    1,55751,bpgqf9b2ykqmu,3882290683                   12/05/16_1845_1900                  0
    1,55752,bpgqf9b2ykqmu,3882290683                   12/05/16_1900_1915                  8 113.469    1.584481      131377     18203      1461
    1,55753,bpgqf9b2ykqmu,3882290683                   12/05/16_1915_1930                  0
    1,55754,bpgqf9b2ykqmu,3882290683                   12/05/16_1930_1945                  0
    1,55755,bpgqf9b2ykqmu,3882290683                   12/05/16_1945_2000                  0
    1,55756,bpgqf9b2ykqmu,3882290683                   12/05/16_2000_2015                  1 913.61     12.511014     435522     163029     118967
    1,55757,bpgqf9b2ykqmu,3882290683                   12/05/16_2015_2030                  0
    1,55758,bpgqf9b2ykqmu,3882290683                   12/05/16_2030_2045                  0
    1,55759,bpgqf9b2ykqmu,3882290683                   12/05/16_2045_2100                  0
    1,55760,bpgqf9b2ykqmu,3882290683                   12/05/16_2100_2115                  0
    2,55761,bpgqf9b2ykqmu,1664736063                   12/05/16_2115_2130                  0
    1,55761,bpgqf9b2ykqmu,3882290683                   12/05/16_2115_2130                  0
    2,55761,bpgqf9b2ykqmu,3882290683                   12/05/16_2115_2130                  0
    2,55762,bpgqf9b2ykqmu,1664736063                   12/05/16_2130_2145                  0
    1,55762,bpgqf9b2ykqmu,3882290683                   12/05/16_2130_2145                  0
    2,55762,bpgqf9b2ykqmu,3882290683                   12/05/16_2130_2145                  0
    2,55763,bpgqf9b2ykqmu,1664736063                   12/05/16_2145_2200                  0
    1,55763,bpgqf9b2ykqmu,3882290683                   12/05/16_2145_2200                  1 709.036    15.165542     646579     197389     0
    2,55763,bpgqf9b2ykqmu,3882290683                   12/05/16_2145_2200                  0
    2,55764,bpgqf9b2ykqmu,1664736063                   12/05/16_2200_2215                  0
    2,55764,bpgqf9b2ykqmu,3882290683                   12/05/16_2200_2215                  0
    2,55765,bpgqf9b2ykqmu,1664736063                   12/05/16_2215_2230                  0
    2,55765,bpgqf9b2ykqmu,3882290683                   12/05/16_2215_2230                  0
    2,55766,bpgqf9b2ykqmu,1664736063                   12/05/16_2230_2245                  0
    2,55766,bpgqf9b2ykqmu,3882290683                   12/05/16_2230_2245                  0
    2,55767,bpgqf9b2ykqmu,1664736063                   12/05/16_2245_2300                  0
    2,55767,bpgqf9b2ykqmu,3882290683                   12/05/16_2245_2300                  0
    2,55768,bpgqf9b2ykqmu,1664736063                   12/05/16_2300_2315                  0
    2,55768,bpgqf9b2ykqmu,3882290683                   12/05/16_2300_2315                  0
    2,55769,bpgqf9b2ykqmu,1664736063                   12/05/16_2315_2330                  0
    2,55769,bpgqf9b2ykqmu,3882290683                   12/05/16_2315_2330                  0
    2,55770,bpgqf9b2ykqmu,1664736063                   12/05/16_2330_2345                  0
    2,55770,bpgqf9b2ykqmu,3882290683                   12/05/16_2330_2345                  0
    2,55771,bpgqf9b2ykqmu,1664736063                   12/05/16_2345_0000                  0
    2,55771,bpgqf9b2ykqmu,3882290683                   12/05/16_2345_0000                  0
    2,55772,bpgqf9b2ykqmu,1664736063                   12/06/16_0000_0015                  0
    2,55772,bpgqf9b2ykqmu,3882290683                   12/06/16_0000_0015                  0
    2,55773,bpgqf9b2ykqmu,1664736063                   12/06/16_0015_0030                  0
    2,55773,bpgqf9b2ykqmu,3882290683                   12/06/16_0015_0030                  0
    2,55774,bpgqf9b2ykqmu,1664736063                   12/06/16_0030_0045                  0
    2,55774,bpgqf9b2ykqmu,3882290683                   12/06/16_0030_0045                  0
    2,55775,bpgqf9b2ykqmu,1664736063                   12/06/16_0045_0100                  0
    2,55775,bpgqf9b2ykqmu,3882290683                   12/06/16_0045_0100                  0
    2,55776,bpgqf9b2ykqmu,1664736063                   12/06/16_0100_0115                  0
    2,55776,bpgqf9b2ykqmu,3882290683                   12/06/16_0100_0115                  0
    2,55777,bpgqf9b2ykqmu,1664736063                   12/06/16_0115_0130                  0
    2,55777,bpgqf9b2ykqmu,3882290683                   12/06/16_0115_0130                  0
    2,55778,bpgqf9b2ykqmu,1664736063                   12/06/16_0130_0145                  1 1483.15    22.431827     1007716    273778     0
    2,55778,bpgqf9b2ykqmu,3882290683                   12/06/16_0130_0145                  0
    2,55779,bpgqf9b2ykqmu,1664736063                   12/06/16_0145_0200                  0
    2,55779,bpgqf9b2ykqmu,3882290683                   12/06/16_0145_0200                  0
    2,55780,bpgqf9b2ykqmu,1664736063                   12/06/16_0200_0215                  0
    2,55780,bpgqf9b2ykqmu,3882290683                   12/06/16_0200_0215                  0
    2,55781,bpgqf9b2ykqmu,1664736063                   12/06/16_0215_0230                  0
    2,55781,bpgqf9b2ykqmu,3882290683                   12/06/16_0215_0230                  0
    2,55782,bpgqf9b2ykqmu,1664736063                   12/06/16_0230_0245                  0
    2,55782,bpgqf9b2ykqmu,3882290683                   12/06/16_0230_0245                  0
    2,55783,bpgqf9b2ykqmu,1664736063                   12/06/16_0245_0300                  0
    2,55783,bpgqf9b2ykqmu,3882290683                   12/06/16_0245_0300                  0
    2,55784,bpgqf9b2ykqmu,1664736063                   12/06/16_0300_0315                  1 851.443    6.204348      284531     95518      0
    2,55784,bpgqf9b2ykqmu,3882290683                   12/06/16_0300_0315                  0
    2,55800,bpgqf9b2ykqmu,1664736063                   12/06/16_0700_0715                  0
    2,55800,bpgqf9b2ykqmu,3882290683                   12/06/16_0700_0715                  0
    2,55801,bpgqf9b2ykqmu,1664736063                   12/06/16_0715_0730                  0
    2,55801,bpgqf9b2ykqmu,3882290683                   12/06/16_0715_0730                  0
    2,55802,bpgqf9b2ykqmu,1664736063                   12/06/16_0730_0745                  0
    2,55802,bpgqf9b2ykqmu,3882290683                   12/06/16_0730_0745                  0
    2,55803,bpgqf9b2ykqmu,1664736063                   12/06/16_0745_0800                  0
    2,55803,bpgqf9b2ykqmu,3882290683                   12/06/16_0745_0800                  0
    2,55804,bpgqf9b2ykqmu,1664736063                   12/06/16_0800_0815                  0
    2,55805,bpgqf9b2ykqmu,1664736063                   12/06/16_0815_0830                  0
    2,55806,bpgqf9b2ykqmu,1664736063                   12/06/16_0830_0845                  0
    2,55807,bpgqf9b2ykqmu,1664736063                   12/06/16_0845_0900                  0
    2,55808,bpgqf9b2ykqmu,1664736063                   12/06/16_0900_0915                  0
    2,55809,bpgqf9b2ykqmu,1664736063                   12/06/16_0915_0930                  0
    2,55810,bpgqf9b2ykqmu,1664736063                   12/06/16_0930_0945                  0
    2,55811,bpgqf9b2ykqmu,1664736063                   12/06/16_0945_1000                  0
    2,55812,bpgqf9b2ykqmu,1664736063                   12/06/16_1000_1015                  0
    2,55813,bpgqf9b2ykqmu,1664736063                   12/06/16_1015_1030                  0
    2,55814,bpgqf9b2ykqmu,1664736063                   12/06/16_1030_1045                  0
    2,55815,bpgqf9b2ykqmu,1664736063                   12/06/16_1045_1100                  0
    2,55816,bpgqf9b2ykqmu,1664736063                   12/06/16_1100_1115                  0
    2,55817,bpgqf9b2ykqmu,1664736063                   12/06/16_1115_1130                  0
    2,55818,bpgqf9b2ykqmu,1664736063                   12/06/16_1130_1145                  0
    2,55819,bpgqf9b2ykqmu,1664736063                   12/06/16_1145_1200                  0
    2,55820,bpgqf9b2ykqmu,1664736063                   12/06/16_1200_1215                  0
    2,55821,bpgqf9b2ykqmu,1664736063                   12/06/16_1215_1230                  0
    2,55822,bpgqf9b2ykqmu,1664736063                   12/06/16_1230_1245                  2 1426.785   27.32261      2413519    285441     0
    2,55823,bpgqf9b2ykqmu,1664736063                   12/06/16_1245_1300                  0
    2,55824,bpgqf9b2ykqmu,1664736063                   12/06/16_1300_1315                  0
    2,55825,bpgqf9b2ykqmu,1664736063                   12/06/16_1315_1330                  1 1028.804   11.08805      706540     186236     0
    2,55826,bpgqf9b2ykqmu,1664736063                   12/06/16_1330_1345                  0
    2,55827,bpgqf9b2ykqmu,1664736063                   12/06/16_1345_1400                  0
    2,55828,bpgqf9b2ykqmu,1664736063                   12/06/16_1400_1415                  0
    2,55829,bpgqf9b2ykqmu,1664736063                   12/06/16_1415_1430                  0
    2,55830,bpgqf9b2ykqmu,1664736063                   12/06/16_1430_1445                  0
    2,55831,bpgqf9b2ykqmu,1664736063                   12/06/16_1445_1500                  0
    2,55832,bpgqf9b2ykqmu,1664736063                   12/06/16_1500_1515                  0
    2,55833,bpgqf9b2ykqmu,1664736063                   12/06/16_1515_1530                  0
    2,55834,bpgqf9b2ykqmu,1664736063                   12/06/16_1530_1545                  0
    2,55835,bpgqf9b2ykqmu,1664736063                   12/06/16_1545_1600                  0
    2,55836,bpgqf9b2ykqmu,1664736063                   12/06/16_1600_1615                  0
    2,55837,bpgqf9b2ykqmu,1664736063                   12/06/16_1615_1630                  0
    2,55838,bpgqf9b2ykqmu,1664736063                   12/06/16_1630_1645                  0
    2,55839,bpgqf9b2ykqmu,1664736063                   12/06/16_1645_1700                  0
    2,55840,bpgqf9b2ykqmu,1664736063                   12/06/16_1700_1715                  0
    2,55841,bpgqf9b2ykqmu,1664736063                   12/06/16_1715_1730                  0
    2,55842,bpgqf9b2ykqmu,1664736063                   12/06/16_1730_1745                  0
    2,55843,bpgqf9b2ykqmu,1664736063                   12/06/16_1745_1800                  1 24788.559  216.954435    19870052   1089842    0
    2,55844,bpgqf9b2ykqmu,1664736063                   12/06/16_1800_1815                  0
    2,55845,bpgqf9b2ykqmu,1664736063                   12/06/16_1815_1830                  0
    2,55846,bpgqf9b2ykqmu,1664736063                   12/06/16_1830_1845                  0
    2,55847,bpgqf9b2ykqmu,1664736063                   12/06/16_1845_1900                  0
    2,55848,bpgqf9b2ykqmu,1664736063                   12/06/16_1900_1915                  0
    2,55849,bpgqf9b2ykqmu,1664736063                   12/06/16_1915_1930                  0
    2,55850,bpgqf9b2ykqmu,1664736063                   12/06/16_1930_1945                  0
    2,55851,bpgqf9b2ykqmu,1664736063                   12/06/16_1945_2000                  0
    2,55852,bpgqf9b2ykqmu,1664736063                   12/06/16_2000_2015                  0
    2,55853,bpgqf9b2ykqmu,1664736063                   12/06/16_2015_2030                  0

    从上面确认好的执行计划是:
    INST_SNAPID_SQLID_PLAN
    1,55752,bpgqf9b2ykqmu,3882290683到1,55753,bpgqf9b2ykqmu,3882290683这个区间。

    开始加载这个执行计划到baseline中:

    1).创建 sql tuning set (每个数据库库仅需执行一次)
    exec DBMS_SQLTUNE.CREATE_SQLSET('SPM_SQLS_ZENG'); 
    2) 从 awr 将计划加载入 sql set
    declare 
    baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR; 
    begin 
    open baseline_ref_cursor for 
    select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(55752, 55753, 
    'sql_id='||CHR(39)||'bpgqf9b2ykqmu'||CHR(39)||' and plan_hash_value=3882290683',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p; 
    DBMS_SQLTUNE.LOAD_SQLSET('SPM_SQLS_ZENG', baseline_ref_cursor); 
    end;

     
    /
    在执行时,输入 存在好的计划的 begin_snap, end_snap 的 snap_id 值,以及 语句的 sql_id 值,好的计划对应的 plan_hash_value 值
    则其自动将相应的计划加载入 sql set 中了。

    3) 确认 sqlset 中的执行计划
    SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET( 'SPM_SQLS_ZENG','bpgqf9b2ykqmu',SQLSET_OWNER =>'SYS')); 
    这里,输入语句的 sql_id 值,确认计划是期望的。

    SQL> SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET( 'SPM_SQLS_ZENG','bpgqf9b2ykqmu',SQLSET_OWNER =>'SYS'));

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL Tuning Set Name: SPM_SQLS_ZENG
    SQL Tuning Set Owner: SYS
    SQL_ID: bpgqf9b2ykqmu
    SQL Text: SQL省略
    --------------------------------------------------------------------------------

    Plan hash value: 3882290683

    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                    |                     |       |       |     5 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL            |                     |       |       |            |          |
    |   2 |   FILTER                            |                     |       |       |            |          |
    |   3 |    FILTER                           |                     |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID     | SFC_WIP_LOG         |     1 |   214 |     5   (0)| 00:00:01 |
    |   5 |      INDEX RANGE SCAN               | IDX_SFC_WIP_LOG_IX6 |     1 |       |     4   (0)| 00:00:01 |
    |   6 |    COLLECTION ITERATOR PICKLER FETCH|                     |     1 |     2 |    29   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------


    31 rows selected.

    若同时有多个语句需要从 AWR 历史中加载好计划入 baseline,则可以循环执行 2), 3) 步。

    4)从sqlset 加载入 baseline 
    declare
      v_number number;
    begin
      v_number:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET ('SPM_SQLS_ZENG','SYS', null, 'NO','YES');
      dbms_output.put_line(v_number);
    end;

    这里为一次性将 sql set 中所有语句加载入 baseline 中。

    5) 清理 sqlset
    在加载入 baseline 后,清除 sqlset 中的记录
    exec dbms_sqltune.DELETE_SQLSET('SPM_SQLS_ZENG');
    commit;
    在如此执行一次后,若未来,有新的语句需要处理,只需执行 2) - 5) 步即可。

    与开发确认将原有跑这支SQL的会话kill,先查出,然后批量kill。
    select 'alter system kill session '|| ''''||sid||','||serial#||','||inst_id||''''||' immediate;' from gv$session where sql_id='bpgqf9b2ykqmu';

    再重跑SQL。

    检查新执行计划是否有加载到baseline:
     select * from dba_sql_plan_baselines
     order by created desc

    确认新执行计划的性能情况:
    set linesize 3000 pagesize 50000
    col inst_id for 9
    col sql_id for a10
    column chld format 9
    column plan_hash format 9999999999
    column exe format 9
    column exes format 999
    column gets  format 99999999.99
    column reads format 999999.99
    column elaps_ms format 999999999.99
    column io_ms format 999999999.99
    column cpu_ms format 999999.99
    column user_id format 99
    column last_load_time format a20


    Select  inst_id, sql_id,  child_number chld, plan_hash_value plan_hash, executions exes,
            buffer_gets/executions gets,
            disk_reads/executions reads,
            rows_processed/executions rows_, cpu_time/executions/1000 cpu_ms,
            Elapsed_time/executions/1000 elaps_ms,
            USER_IO_WAIT_TIME/executions/1000 io_ms,
            last_load_time
        From gv$sql
    Where sql_id='bpgqf9b2ykqmu'
    and executions>0
    order by last_load_time;

    INST_ID SQL_ID     CHLD   PLAN_HASH EXES         GETS      READS      ROWS_     CPU_MS      ELAPS_MS         IO_MS LAST_LOAD_TIME
    ------- ---------- ---- ----------- ---- ------------ ---------- ---------- ---------- ------------- ------------- --------------------
          2 bpgqf9b2yk    1  1664736063   38  11913312.13  825208.79          0  137635.65   17514974.89   16207595.19 2016-12-05/21:14:59
            qmu

          1 bpgqf9b2yk    1  3882290683    6    151524.33    6594.33 51.6666667     864.50      37907.82      36507.76 2016-12-06/21:49:28
            qmu

  • 相关阅读:
    IOS10.8.2安装
    如何读本地资源图片
    SqlServer2000日志文件过大问题处理
    xcode 4.5.1 免证书开发 破解
    C#生成注册码
    去掉Html标签方法
    数组处理
    返回代码
    Javascript图像处理——图像金字塔
    Javascript图像处理——图像形态学
  • 原文地址:https://www.cnblogs.com/zfox2017/p/7323738.html
Copyright © 2020-2023  润新知