oracle执行计划是查看sql语句性能的必要技能。
查看解释计划示例:
1.explain plan for select * from student where sex='男'
select * from table(dbms_xplan.display);
2.分解、查看解释计划:
explain plan for select * from student s,teacher t where s.tid=t.tid;
select * from table(dbms_xplan.display);
select id,parent_id,lpad(' ',level)|| operation || ' ' || options || ' ' || object_name as operation
from plan_table
start with id=0
connect by prior id =parent_id;
上面查询出每个步骤的父步骤,如Step的父Step是1,此处原则在于每一个子运算将会把数据传递给父Step。
关于计划中的几种运算:
a.加工运算(父子运算)
b.迭代运算(会出现iterator,all)
c.传递运算(例如:view视图)。
3.关于orcl索引注意事项:
使用转换函数时将不会使用索引,当出现类型不匹配时,oracle将进行隐式转换使查询不能使用索引,所以使用索引要充分考虑数据类型,下面隐式转换例子:
原始:select * form student where sid='1';
隐式转换:select * form student where sid=to_number('1');
4.执行计划
可查询视图:v$sql_plan,v$sql_plan_statistics
查询示例:
4.1select * from student s,teacher t where s.tid=t.tid;
set serveroutput off;
select * from table(dbms_xplan.display(null,null,'ALLSTATS LAST'));
4.2标识SQL语句取回计划,当执行计划出现缓存时建议用sql标识能更准确的得出执行计划:
select /* id_number */ /*+ gather_plan_statistics */* from student;
select sql_text,sql_id,child_number from v$sql where sql_text like '%id_number%';
select * from table(dbms_xplan.display_cursor('2qm4sjcw8rttz',0,'ALLSTATS LAST'));
4.3关于dbms_xplan的使用,设置不同的值进行提示查询
explain plan for select /*+ gather_plan_statistics */* from student;
select * from table(dbms_xplan.display(format=>'ALL'));
select * from table(dbms_xplan.display(null,null,format=>'ALLSTATS LAST COST BYTES'));
variable v_sex varchar2;
exec :v_sex:='男';
explain plan for select * from student where sex=:v_sex;
select * from table(dbms_xplan.display(null,null,format=>'+PEEKED_BINDS'));
4.4执行计划的合理性,主要查看A-Rows的值a.此值相对于表中总行数越越好. b.父表访问的A-Rows与子表A-Rows相比,不能相差太远,这两种情况都有索引问题。
4.5当执行计划表中的数据过期可采用方法,使用dbms_stats统计包:
exec dbms_stats.gather_table_stats(user,'STUDENT',estimate_percent=>100,cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE AUTO');