oracle获取执行计划

上一篇 / 下一篇  2021-10-21 10:51:36 / 个人分类:数据库



一、六种获取执行计划的应用场景及具体使用方法
  • 如果某 SOL 执行很长时间才出结果或返回不了结果,这时就只能用方法1
  • 跟踪某条 SOL 最简单的方法是方法 1,其次就是方法2
  • 如果想观察某条 SOL 多个执行计划的情况,只能用方法4 和方法6
  • 如果 SOL 中含函数,函数中又套 SOL 等,即存在多层调用,想准确分析只能用方法5
  • 要想确保看到真实的执行计划,不能用方法1 和方法2
  • 要想获取表被访问的次数,只能使用3
使用这些命令前先打开sqlplus窗口,在窗口标题右键,在属性\布局,增加窗口宽度和屏幕缓冲区宽度(二者保持一致),以免展示结果时不方便看,窗口大小调整完毕,登录splplus后先输入如下命令,设置查询结果的列宽和行数
set linesize 1000
set pagesize 2000

( 1 ) explain plan for 获取,PLSQL的F5查看到的执行计划同(1)
explain plan for
select pv.* from PV_ENCOUNTER pv,pv_op op where pv.pk_pv=op.pk_pv and pv.name_pi='王雨红';
select * from table (dbms_xplan. display()) ;
/*
优点 :无须真正执行,快捷方便,不是真实的执行计划
缺陷 :
1.没有输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读的情况) ;
         
2.无法判断处理了多少行;
         
3.无法判断表被访问了多少次。
*/ 

( 2 ) set autotrace XXX,此命令PLSQL不支持
set autotrace traceonly
select pv.* from PV_ENCOUNTER pv,pv_op op where pv.pk_pv=op.pk_pv and pv.name_pi='王雨红';
/*
set autotrace off-----------------此为默认值,即关闭Autotrace
set autotrace explain------------只显示执行计划
set autotracestatistics----------只显示执行的统计信息
set autotrace on-----------------显示查询结果、执行计划和统计信息
set autotrace traceonly--------与ON相似,但不显示语句的执行结果

优点:1. 可以输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读的情况),
         2. 虽然必须要等语句执行完毕后才可以输出执行计划 但是可以有 traceonly 开关来控制返回结果不打屏输出。
缺陷:1. 必须要等到语句真正执行完毕后,才可以出结果,
         2. 无法看到表被访问了多少次
         3.尽管有真实去执行SQL,但打印出来的执行计划并不一定准确,谓词和统计信息是真实的
*/

( 3 )set statistics_level=all
set autotrace off
alter session set statistics_level=all ;
select /*make_test*/ pv.* from PV_ENCOUNTER pv,pv_op op where pv.pk_pv=op.pk_pv and pv.name_pi='王雨红';
select sql_id,sql_text from v$sql where sql_text like '%make_test%';
select * from table(dbms_xplan.display_cursor('6ssx3ybzz6fah',null,'allstats')) ;

/*
Starts 为该 SQL 执行的次数。
E-Rows 为执行计划预计的行数。
A-Rows 为实际返回的行数。
E-Rows和A-Rows 做比较,就可以确定哪一步执行计划出了问题。
Time 为每一步实际执行的时间,根据这一行可以知道该 SQL 耗时在哪个地方。
Buffers 为每一步实际执行的逻辑读
Reads 为物理读

优点: 1. 可以清晰地从
Starts得出表被访问多少次;
          2. 可以清晰地从 E-ROWS和A-ROWS 中得到预测的行数和真实的行数 从而可以准确判断 Oracle 评估是否准确;虽然没有专门的输出运行时的相关统计信息 但是执行计划中的 BUFFERS 就是真实的逻辑读的数值。
缺陷
1. 必须要等到语句真正执行完毕后,才可以出结果,
*/

( 4 )dbms_xplan.display_cursor 获取
select * from table(dbms_xplan display_cursor '&sq_id'));
/*
优点:知道 sql id 立即可得到执行计划,它和 explain plan for 一样无须执行,但是它得到真实的执行计划。
缺陷:
1.没有输出运行时的相关统计信息(产生多少逻辑读,多少次递归调用,多少次物理读的情况)
         2. 无法判断处理了多少行;
         3. 无法判断表被访问了多少次。

*/


(5 ) 事件10046 trace 跟踪
/*
步骤 1: alter session set events '10046 trace name context forever,level 12'; (开启跟踪)
步骤 2: 执行你的语旬
步骤 3: alter session set events '10046 trace name context off'; (关闭跟踪)
步骤 4: exit;(找到跟踪后产生的文件)

步骤 5: tkprof trc文件 目标文件 sys=no
sort=prsela,exeela,fchela(格式化命令)
优点: 1. 可以看出 SQL 语旬对应的等待事件,
          2. 如果 SQL 吾旬中有函数调用,函数中又有 SQL ,将会被列出,无处遁形,
          3. 可以方便地看出处理的行数,产生的物理逻辑读,
          4.可以方便地看出解析时间和执行时间
          5. 可以跟踪整个程序包。
缺陷
1. 步骤烦琐,比较麻烦;
          2. 无法判断表被访问了多少次,
          3. 执行计划中的谓词部分不能清晰地展现出来。
*/ 


(6 ) awrsqrpt.sql
/*
步骤1:@?/rdbms/admin/awrsqrpt.sql
步骤2
选择你要的断点( begin snap 和end ·snap)
步骤3
输入你的 sql_id
*/ 

二、执行计划的字段描述

1. 基本字段(总是可用的)

  • Id            执行计划中每一个操作(行)的标识符。如果数字前面带有星号,意味着将在随后提供这行包含的谓词信息
  • Operation  对应执行的操作。也叫行源操作
  • Name        操作的对象名称

2. 查询优化器评估信息

  • Rows(E-Rows)     预估操作返回的记录条数
  • Bytes(E-Bytes)    预估操作返回的记录字节数
  • TempSpc            预估操作使用临时表空间的大小
  • Cost(%CPU)        预估操作所需的开销。在括号中列出了CPU开销的百分比。注意这些值是通过执行计划计算出来的。换句话说,父操作的开销包含子操作的开销
  • Time                  预估执行操作所需要的时间(HH:MM:SS)

3. 分区(仅当访问分区表时下列字段可见)

  • Pstart   访问的第一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)
  • Pstop    访问的最后一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)

4. 并行和分布式处理(仅当使用并行或分布式操作时下列字段可见)

  • Inst         在分布式操作中,指操作使用的数据库链接的名字
  • TQ          在并行操作中,用于从属线程间通信的表队列
  • IN-OUT    并行或分布式操作间的关系
  • PQ Distrib 在并行操作中,生产者为发送数据给消费者进行的分配

5. 运行时统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时,下列字段可见)

  • Starts      该 SQL 执行的次数
  • E-Rows 为执行计划预计的行数。
  • A-Rows   实际返回的行数,E-Rows和A-Rows 做比较,就可以确定哪一步执行计划出了问题。
  • A-Time    每一步实际执行的时间,根据这一行可以知道该 SQL 耗时在哪个地方。

6. I/O 统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时,下列字段可见)

  • Buffers    每一步实际执行的逻辑读数量
  • Reads     每一步实际执行的物理读数量
  • Writes     每一步实际执行的物理写数量         

7. 内存使用统计

  • OMem        最优执行所需内存的预估值
  • 1Mem        一次通过(one-pass)执行所需内存的预估值
  • 0/1/M        最优/一次通过/多次通过(multipass)模式操作执行的次数
  • Used-Mem  最后一次执行时操作使用的内存量
  • Used-Tmp  最后一次执行时操作使用的临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如,32k意味着32MB)
  • Max-Tmp   操作使用的最大临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如,32k意味着32MB)


TAG:

 

评分:0

我来说两句

日历

« 2024-03-24  
     12
3456789
10111213141516
17181920212223
24252627282930
31      

数据统计

  • 访问量: 20781
  • 日志数: 24
  • 书签数: 1
  • 建立时间: 2012-05-13
  • 更新时间: 2021-10-28

RSS订阅

Open Toolbar