重庆小潘seo博客

当前位置:首页 > 重庆网络营销 > 小潘杂谈 >

小潘杂谈

查看Oracle执行计划的方法

时间:2020-09-24 01:20:07 作者:重庆seo小潘 来源:
什么是执行计划? SQL是一种傻瓜式语言,每一个条件就是一个需求,访问的顺序不同就形成了不同的执行计划。Oracle必须做出选择,一次只能有一种访问路径。执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。 执行计划的选择: 通常一条SQL有多个

查看Oracle执行计划的方法

什么是执行计划?

SQL是一种傻瓜式语言,每一个条件就是一个需求,访问的顺序不同就形成了不同的执行计划。Oracle必须做出选择,一次只能有一种访问路径。执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。

执行计划的选择:

通常一条SQL有多个执行计划,那我们如何选择?那种执行开销更低,就意味着性能更好,速度更快,我们就选哪一种,这个过程叫做Oracle的解析过程,然后Oracle会把更好的执行计划放到SGA的Shared Pool里,后续再执行同样的SQL只需在Shared Pool里获取就行了,不需要再去分析。

执行计划选定依据:

根据统计信息来选择执行计划。

统计信息:

什么是统计信息: 记录数、块数等,具体查看dba_tables / dba_indexes

动态采样:

Oracle正常情况下会在每天的某段时间收集统计信息,对于新建的表,Oracl如何收集统计信息?采用动态采样。

六种执行计划

Oracle提供了6种执行计划获取方法,各种方法侧重点不同:

选择时一般遵循以下规则:

1.如果sql执行很长时间才出结果或返回不了结果,用方法1:explain plan for

2.跟踪某条sql最简单的方法是方法1:explain plan for,其次是方法2:set autotrace on

3.如果相关察某个sql多个执行计划的情况,只能用方法4:dbms_xplan.display_cursor或方法6:awrsqrpt.sql

4.如果sql中含有函数,函数中有含有sql,即存在多层调用,想准确分析只能用方法5:10046追踪

5.想法看到真实的执行计划,不能用方法1:explain plan for和方法2:set autotrace on

6.想要获取表被访问的次数,只能用方法3:statistics_level = all

查看Oracle执行计划的方法

Oracle如何收集统计信息:

1、Oracle会选择在一个特定的时间段收集表和索引的统计信息(默认周一至周五:22:00,周六周日:06:00),用户可自行调整,主要为了避开高峰期;

2、表与索引的分析有阈值限制,超过阈值才会自动进行分析。如果数据变化量不大,Oracle是不会去分析的;

3、收集方式灵活。可针对分区表的某个分区进行,可采用并行机制来收集表和索引的信息;

如何收集统计信息:

--收集表统计信息exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>10,method_opt => 'for all indexed columns');--收集索引统计信息exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>10,degree => '4');--收集表与索引的统计信息exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>10,method_opt => 'for all indexed columns',cascade => true);(1)explain plan forSQL> show userUSER 为 "HR"SQL> set linesize 1000SQL> set pagesize 2000SQL> explain plan for2 select *3 from employees,jobs4 where employees.job_id=jobs.job_id5 and employees.department_id=50;已解释。 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 303035560------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 || 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 || 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 ||* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 ||* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)已选择19行。优点:无需真正执行,快捷方便;

缺点:

1、没有输出相关统计信息,例如产生了多少逻辑读,多少次物理读,多少次递归调用的情况;

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

3、无法判断表执行了多少次

(2)set autotrace on

用法:

命令作用:SQL> set autotrace onSQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;--输出结果(略)-- ...已选择45行。 执行计划----------------------------------------------------------Plan hash value: 303035560------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 || 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 || 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 ||* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 ||* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)统计信息----------------------------------------------------------0 recursive calls0 db block gets13 consistent gets0 physical reads0 redo size5040 bytes sent via SQL*Net to client433 bytes received via SQL*Net from client4 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)45 rows processed优点:

1、可以输出运行时的相关统计信息(产生多少逻辑读、多少次递归调用、多少次物理读等);

2、虽然要等语句执行完才能输出执行计划,但是可以有traceonly开关来控制返回结果不打屏输出;

缺点:

1、必须要等SQL语句执行完,才出结果;

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

(3)statistics_level=all

步骤一:ALTER SESSION SET STATISTICS_LEVEL=ALL;

步骤二:执行待分析的SQL

步骤三:select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,null,'allstats last'));SQL> alter session set statistics_level=all;SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;--输出结果--...已选择45行。 SQL> set linesize 1000SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------SQL_ID d8jzhcdwmd9ut, child number 0-------------------------------------select * from employees,jobs where employees.job_id=jobs.job_id andemployees.department_id=50Plan hash value: 303035560----------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | || |PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------| 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | || || 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | || || 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | || ||* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 |6144 | 6144 (0)||* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | || |----------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)已选择25行。关键字解读:

1、starts:SQL执行的次数;

2、E-Rows:执行计划预计返回的行数;

3、R-Rows:执行计划实际返回的行数;

4、A-Time:每一步执行的时间(HH:MM:SS.FF),根据这一行可知SQL耗时在哪些地方;

5、Buffers:每一步实际执行的逻辑读或一致性读;

6、Reads:物理读;

优点:

1、可以清晰的从starts得出表被访问多少次;

2、可以从E-Rows和A-Rows得到预测的行数和真实的行数,从而可以准确判断Oracle评估是否准确;

3、虽然没有准确的输出运行时的相关统计信息,但是执行计划中的Buffers就是真实的逻辑读的数值;

缺点:

1、必须要等执行完后才能输出结果;

2、无法控制结果打屏输出,不像autotrace可以设置traceonly保证不输出结果;

3、看不出递归调用,看不出物理读的数值

(4)dbms_xplan.display_cursor获取

步骤1:select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到

注释:

1、还有1种方法,select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取

2、如果有多个执行计划,可用以下方法查出:select * from table(dbms_xplan.display_cursor('&sql_id',0));select * from table(dbms_xplan.display_cursor('&sql_id',1));*/SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 5hkd01f03y43d, child number 0-------------------------------------select * from test where table_name = 'LOG$'Plan hash value: 2408911181--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)|| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)||* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("TABLE_NAME"='LOG$')19 rows selected注释:如何查看1个sql语句的sql_id,可直接查看v$sql优点:

1、知道sql_id即可得到执行计划,与explain plan for一样无需执行;

2、可得到真实的执行计划

缺点:

1、没有输出运行的统计相关信息;

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

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

(5)事件10046 trace跟踪

步骤1:alter session set events '10046 trace name context forever,level 12'; --开启追踪

步骤2:执行sql语句;

步骤3:alter session set events '10046 trace name context off'; --关闭追踪

步骤4:找到跟踪后产生的文件(开启10046前先用‘ls -lrt’看一下文件,执行结束后再看哪个是多出来的文件即可)

步骤5:tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

优点:

1、可以看出sql语句对应的等待事件;

2、如果函数中有sql调用,函数中有包含sql,将会被列出,无处遁形;

3、可以方便的看处理的行数,产生的逻辑物理读;

4、可以方便的看解析时间和执行时间;

5、可以跟踪整个程序包

缺点:

1、步骤繁琐;

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

3、执行计划中的谓词部分不能清晰的展现出来

推荐:Oracle数据库学习教程以上就是查看Oracle执行计划的方法的详细内容,更多请关注小潘博客其它相关文章!