如何获取真实的执行计划

验证explainplan命令与setautotrace命令是否为真实执行计划

0CONN/ASSYSDBA;

1createtablet1asselect*fromdba_objects;

2insertintot1select*fromt1;

3commit;

4selectcount(1)fromt1;

5createindexidx_t1ont1(object_id);

--收集统计信息

6execdbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',estimate_percent=>100,cascade=>true);

7select*fromtable(dbms_xplan.display);

8

VARXNUMBER;

VARYNUMBER;

EXEC:X:=0;

EXEC:Y:=100000;

--explain命令

EXPLAINPLANFORSELECTcount(*)fromt1whereobject_idbetween:xand:y;

select*fromtable(dbms_xplan.display);

显示走idx_t1索引范围（range）扫描

selectcount(*)fromt1whereobject_idbetween:xand:y;

--dbms_xplan.display_cursor(null,null,'ADVANCED')得到真实执行计划

select*fromtable(dbms_xplan.display_cursor(null,null,'ADVANCED'));

Indexfastfull快速全扫描

---setautotracetraceonly验证

Setautotracetraceonly

Selectcount(*)fromt1whereobject_idbetween:xand:y;

显示走idx_t1索引范围（range）扫描

结论:使用setautotrace,setautotrace源于explainplan是不准确的,特别是绑定变量下查询是不准确的

-dbms_xplan.display_cursor(null,null,'ADVANCED')

和10046事件获取真实的执行计划