tuning 11--SQL Statement Tuning

 --《cost based oracle foundamentals》

 

--optimizer_features_enable 
show parameter opti
select * from v$version;
--sql plan management

--diagnostic tools

staspack
explain plan
sql trace and tkprof
sql*plus autotrace feature
oracle sql analyze

--sql reports in staspack
sql ordered by gets  --读取buffer cache 的次数
sql ordered by reads --读取内存中的块到buffer cache的次数
sql ordered by executions  --执行的次数
sql ordered by parse calls --对sql的解析

--what is execution plan

--how to get a execution plan
explain plan
sql trace
automatic workload repository
V$sql_plan
sql*plus autotrace

--uses of execution plan
determing the current execution plan
identifying the effect of indexes
determing access paths
verifying the use of indexes
verifying which execution plan may be used

第44讲：Chapter 11--SQL Statement Tuning

@?/rdbms/admin/utlxplan.sql --创建plan_table 表
explain plan for
select employee_id,last_name,salary  from hr.employees where employee_id=120;

--query plan_table
query plan_table directly
use scrept utlxpls.sql  --@?/rdbms/admin/utlxpls.sql  --格式化输出
use the dbms_xplan package  --select * from table(dbms_xplan.display);

阅读执行计划的要诀：先由上到下，再由里到外