锐客网

  1. 首页 > it技术 > >

数据库入门|Oracle--《基于Oracle的SQL优化》第二章总结回顾

数据库Oracle技术知识数据库入门

  1. 查看执行计划：
    1.1.explain plan for 目标SQL
    1.2 dbms_xplan包
    dbms_xplan.display(先执行explain plan for 目标SQL)–可能不准确
    dbms_xplan.display_cursor(null,null,‘ADVANCED’/‘ALL’)（先执行目标SQL,且执行计划未被age out出shared pool）
    dbms_xplan.display_cursor(目标SQL_ID/目标SQL的HASH_VALUE,目标SQL的child_number,‘ADVANCED’/‘ALL’)
    (先执行目标SQL,且执行计划未被age out出shared pool)
    dbms_xplan.display_awr(目标SQL_ID).(执行计划被age out出shared pool ,然后执行exec dbms_workload_repository.create_snapshot());
    1.3 set autotrace/autot 参数
    off
    on
    traceonly/trace
    traceonly explain/trace exp
    traceonly statistics/trace stat
    然后执行目标SQL
    1.4 10046事件与tkprof命令
    alter session set events ‘10046 trace name context forever,level 12’
    执行目标SQL
    alter session set events’10046 trace name context off’
    或者
    oradebug setmypid
    oradebug event 10046 trace name context forever,level 12
    执行目标SQL
    oradebug event 10046 trace name context off
    oradebug tracefile_name
    最后执行美化命令(oradebug tracefile_name 得到的trace文件地址)
    tkprof trace文件地址
  2. 得到真实的执行计划
    10046事件比较准备
    explain plan for 可能不准：
    dbms_xplan.display 可能不准
    dbms_xplan.display_cursor(null,null,‘ADVANCED’/‘ALL’) 准
    dbms_xplan.display_cursor(目标SQL_ID/目标SQL的HASH_VALUE,目标SQL的child_number,‘ADVANCED’/‘ALL’) 准
    dbms_xplan.display_awr(目标SQL_ID) 准
    set autotrace/autot 参数 都可能不准，（取决于explain plan for）
    off
    on 准
    traceonly/trace 准
    traceonly explain/trace exp select 不准，dml准
    traceonly statistics/trace stat
  3. 【数据库入门|Oracle--《基于Oracle的SQL优化》第二章总结回顾】执行计划的顺序
    先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行
  4. 常见的执行计划
    TABLE ACCESS FULL
    TABLE ACCESS BY USER ROWID
    TABLE ACCESS BY INDEX ROWID
    INDEX UNIQUE SCAN
    INDEX FULL SCAN
    INDEX FAST FULL SCAN
    INDEX RANGE SCAN
    BITMAP INDEX SINGLE VALUE
    BITMAP INDEX RANGE SCAN
    BITMAP INDEX FULL SCAN
    BITMAP INDEX FAST FULL SCAN
    BITMAP AND
    BITMAP OR
    BITMAP MINUS
    BITMAP CONVERSION TO ROWIDS
    MERGE JOIN
    NESTED LOOPS
    HASH JOIN
    HASH JOIN ANTI
    MERGE JOIN ANTI
    NESTED LOOPS ANTI
    HASH JOIN SEMI
    MERGE JOIN SEMI
    NESTED LOOPS SEMI
    AND-EQUAL(INDEX MERGE)
    INDEX JOIN
    VIEW
    FILTER(改良的NESTED LOOPS)
    SORT AGGREGATE
    SORT UNIQUE
    SORT JOIN
    SORT GROUP BY
    SORT ORDER BY
    BUFFER SORT(不一定会排序，statistics中的sorts(memory)和sorts(disk)有可能不准，要看Column Projection Information 中#keys的值)
    排序可以通过10032事件（"Comparisons performed by in-memory sort”和“Total number of comparisons performed"的值来具体查看）
    UNION(UNION-ALL和“SORT UNIQUE”)/UNION ALL
    CONCAT(IN-List扩展（IN-List Expansion）或OR扩展（OR Expansion)
    CONNECT BY
  5. 执行计划的稳定
    使用SQL Profile和SPM来稳定执行计划:
    SQL Profile：
    **Automatic:** dbms_sqltune.create_tuning_task(); dbms_sqltune.execute_tuning_task(); dbms_sqltune.report_tuning_task(); dbms_sqltune.accept_sql_profile(); (FORCE_MATCH:TRUE) dbms_sqltune.drop_sql_profile(); Manual: coe_xfr_sql_profile.sql --目标SQL的SQL_ID,PLAN_HASH_VALUE，得到文件1 coe_xfr_sql_profile.sql --优化SQL的SQL_ID,PLAN_HASH_VALUE，得到文件2 将文件1中HINT更换为文件2中的HINT,并执行文件1

    SPM (SQL Plan Management):
    Automatic: optimizer_use_sql_plan_baselines optimizer_capture_sql_plan_baselines 将要启用的baseline操作：exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle,plan_name ,verify => 'NO', commit => 'YES'); 将要弃用的baseline操作：exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle,plan_name ,attribute_name=> 'ENABLED', attribute_value=> 'NO'); Manual: dbms_spm.load_plan_from_cursor_cache(原目标sql_id,原目标plan_hash_value) dbms_spm.load_plan_from_cursor_cache(将要启用sql_id,将要启用plan_hash_value,原目标sql_handle) dbms_spm.drop_sql_plan_baseline(原目标sql_handle,原目标plan_name)

    推荐阅读


    上一篇：洛谷P5471 NOI2019弹跳

    下一篇：UCOS|UCOS III 中断管理