Oracle "Explain Plan"

You really want to tune your slow SQL? Then firstly you must know how it runs. That's is explain plan/statistics!

Table statistics and column statistics' collections by default are setted t to be runned 10 p.m everyday. At a non-collecting period for example 9 a.m, oracle can conduct a dynamic sampling to collect some statistics in memory temporarily. but When you are creating index in oracle , it will auto-collect statistics.

Normally there're 6 ways to run & see explain plan/statistics :

1 explain plan for select * from t1; select * from table(dbms_xplan.display()); // not so accurate

2 set autotrace on(result and statistics) //It will really run the SQL , but you can't see how many times a table is visited

3 select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) //really run sql as well, but if it runs for quite long time, we must wait for it to finish, then we can see the result, time-consuming

4 select * from table(dbms_xplan.display_cursor('sql_id')); // NOTE: sql_id comes from "select * from v$sql"

5 // 10046trace

Alter session set events ‘10046 trace name context forever, level 2’; // open trace
 //run your sql here
        Alter session set events ‘10046 trace name context off’ // close trace
 //find the file produced

6 @?/rdbms/admin/awrsqrpt.sql // this is a tool in oracle

Recommend :

• Normally you can use method 1 and 2,  
• if multiple sqls are to be runned, you can use 4 and 6.
• if lots of functions will be triggered and called, you can only use 5. 
• if you want to check how many time table is visited , please use 3

anything unclear, please leave msgs!