SQL执行计划

2019独角兽企业重金招聘Python工程师标准>>>

一、统计信息

--表的统计信息

select t.table_name,t.num_rows,t.blocks,t.last_analyzed  from user_tables t ;

--索引的统计信息

select t.table_name,t.index_name ,t.blevel ,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t

last_analyzed 字段 当某表一天记录变化量没有超过指定的阈值时，Oracle就不会对该表进行统计信息收集。

--收集表统计信息

exec dbms_stats.gather_table_stats(owner => 'LJB' ,tabname => 'TEST',estimate_percent => 10,method_opt => 'for all indexed columns');

--收集索引统计信息

exec dbms_stats.gather_index_stats(owner => 'LJB' ,indname => 'IDX_OBJECT_ID',estimate_percent => '10',degree => '4');

--收集表和索引统计信息

exec dbms_stats.gather_table_stats(owner => 'LJB' ,tabname => 'TEST',estimate_percent => 10,method_opt => 'for all indexed columns',cascade => TRUE);

 

--分区表（可以指定只收集某分区）

exec dbms_stats.gather_table_stats(owner => 'LJB' ,tabname => 'RANGE_PART_TAB',partname => 'p_201901',estimate_percent => 10,method_opt => 'for all indexed columns',cascade => TRUE);

二、获取执行计划的6种方法

1、explain plan for

> explain plan for 执行语句
> select * from table(dbms_xplan.display());

优点：无需真正执行，快捷方便

缺陷：1、没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读）

          2、无法判断处理了多少行

          3、无法判断表被访问了多少次

2、set autotrace on

优点：1、可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读）

           2、虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关开控制返回结果不 打屏输出

缺点：1、必须要等到语句真正完毕后，才可以出结果

           2、无法看到表被访问了多少次。

3、statistics_level=all

1>alter session set statistice_level=all;
2>在此处执行你的SQL
3> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

注：如果使用/*+ gather_plan_statistics */的方法可以省略步骤1，直接步骤2,3

4、dbms_xplan.display_cursor

select * from table (dbms_xplan.display_cursor('&sql_id')); (该方法是从共享池里得到)

select * from table (dbms_xplan.display_awr('&sql_id'));(该方法是从awr性能视图里获取)


注：
  如果有多个执行计划，可用类似方法查出
 select * from table (dbms_xplan.display_cursor('&sql_id',0));
 select * from table (dbms_xplan.display_cursor('&sql_id',1));

5、事件10046trace 跟踪

1>alter session set events '10046 trace name context forever,level 12';(开启跟踪)
2>执行语句
3>alter session set events '10046 trace context off';(关闭跟踪)
4>找到跟踪后产生的文件
5>tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela (格式化命令)

第4步的方法
 select d.value
 || '/'
 || lower(rtrim(i.instance,chr(0)))
 || '_ora_'
 ||k.SPID
 ||'.trc' trace_file_name 
  from (
    select p.spid  from v$mystat m,v$session s, v$process p 
    where m.STATISTIC#=1 and s.sid=m.sid and p.ADDR=s.PADDR) k,
    (select t.INSTANCE
    from v$thread t,v$parameter v
    where v.NAME='thread'
    and (v.value='0' or t.THREAD#=TO_NUMBER(REGEXP_REPLACE( v.VALUE,'[^0-9]','')) )) i,
    (select value 
    from v$parameter 
    where name='user_dump_dest') d;

6、awrsqrpt.sql

1>@?/rdbms/admin/awrsqrpt.sql
2>选择你要的断点（begin snap 和 end snap）
3>输入你的sql_id

 

 

转载于:https://my.oschina.net/liubaizi/blog/3031494