-
查看执行计划:
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文件地址 -
得到真实的执行计划
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 -
执行计划的顺序
先从最开头一直连续往右看,直到看到最右边的并列的地方;对于不并列的,靠右的先执行;如果见到并列的,就从上往下看,对于并列的部分,靠上的先执行 -
常见的执行计划
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 -
执行计划的稳定
使用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)
Oracle--《基于Oracle的SQL优化》第二章总结回顾
最新推荐文章于 2024-08-09 21:25:57 发布