MSQL优化基础(正确取到执行计划)

1.前台 pl/sql F5 ， explain plan for 方式得到的计划，如果有绑定变量时候不准， 而且是预估值不是实际情况的

 

2.对于执行过的SQL：

    如果我们想获取该语句的实际执行计划，通过下列步骤：

a、查询v$sql视图，找到该语句的sql_id(注意哟，必须要确保你要查询的sql语句还在shared pool)：
   SQL> select sql_id from v$sql where sql_text= 'select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id';

   SQL_ID
   -------------
   c9cxqvr3q4tjd

b.、调用dbms_xplan包，查看该语句执行时的实现执行计划：
   SQL> select * from table(dbms_xplan.display_cursor('c9cxqvr3q4tjd'));

如果想要更详细的则  开启statistics_level=all时或使用gather_plan_statistics hint 可以获得执行计划中实时的统计信息

//显示最后一条执行的执行计划

select * from table (dbms_xplan.display_cursor(null, null,'allstats +alias +outline'));

//对于已经执行的，需要找到 SQL_ID , 并且还在 V$SQL 中

select sql_id,child_number, plan_hash_value, last_load_time,last_active_time from v$sql where sql_id='xxxx';
select * from table (dbms_xplan.display_cursor('sql_id', 'child_number','allstats+alias+outline'));

 注意： displan_cursor  V$SQL里有的才行

 

案例分析

create table t1 as select * from all_objects where 1=2;  --建表
create table t2 as select * from all_objects where 1=2;  --建表
exec dbms_stats.gather_table_stats(null,'t1');  --先收集统计信息
exec dbms_stats.gather_table_stats(null,'t2'); -- 先收集统计信息
insert into t1 select * from all_objects;  --插入数据
insert into t2 select * from all_objects;  --插入数据
commit;
show parameter level -- 记不得可以通过这个查一下
alter session set statistics_level=all   -- 收集一些附加的信息

//显示执行计划，对于执行过的SQL,而且还在v$sql里 可以通过 display_cursor 来显示
//select * from table (dbms_xplan.display_cursor(null , null ,'allstats +alias +outline')); 
//由于执行时间太长 这里用： explain plan for select object_name from t1 where object_id not in (select max(object_id) from t2 group by owner)
//这个执行计划是针对 插入数据之前的统计信息 作出的  错误的统计信息 作出不好的访问路径
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    30 |     5  (20)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |   FILTER             |      |       |       |            |          |
|   4 |    HASH GROUP BY     |      |     1 |    30 |     3  (34)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     1 |    30 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


explain plan for with a as  (select /*+MATERIALIZE*/max(object_id) max_obj from t1 group by owner) select object_name from t2 where object_id not in (select a.max_obj from a)
select * from table (dbms_xplan.display)
-------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                          |     1 |    30 |     7  (15)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          |
|   2 |   LOAD AS SELECT           | T2                       |       |       |            |          |
|   3 |    HASH GROUP BY           |                          |     1 |    30 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | T1                       |     1 |    30 |     2   (0)| 00:00:01 |
|*  5 |   FILTER                   |                          |       |       |            |          |
|   6 |    TABLE ACCESS FULL       | T2                       |     1 |    30 |     2   (0)| 00:00:01 |
|*  7 |    VIEW                    |                          |     1 |    13 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6601_946FB |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
explain plan for 和  dbms_xplan.display 是处理  plan_table 表中的数据