全网整合营销服务商

营销型网站+SEO优化+关键词快排=一站式服务

免费咨询热线:15959292472

「通州市网站搭建公司哪家好」查看Oracle执行计划的方法

  什么是执行计划?

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

  执行计划的选择:

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

  执行计划选定依据:

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

  统计信息:

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

  动态采样:

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

  

 

  set autotrace on
set linesize 1000
--执行SQL语句
--会出现dynamic sampling used for this statement(level=2)关键

  

 

  六种执行计划

  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如何收集统计信息:

  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 for

  

SQL> show user
 USER 为 "HR"
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 select *
3 from employees,jobs
4 where employees.job_id=jobs.job_id
5 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

  用法:

  命令作用:

  

 

  SET AUTOT[RACE] OFF 停止AutoTrace SET AUTOT[RACE] ON 开启AutoTrace,显示AUTOTRACE信息和SQL执行结果 SET AUTOT[RACE] TRACEONLY 开启AutoTrace,仅显示AUTOTRACE信息 SET AUTOT[RACE] ON EXPLAIN 开启AutoTrace,仅显示AUTOTRACE的EXPLAIN信息 SET AUTOT[RACE] ON STATISTICS 开启AutoTrace,仅显示AUTOTRACE的STATISTICS信息

  

SQL> set autotrace on
SQL> 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 calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 5040 bytes sent via SQL*Net to client 433 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 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 1000
SQL> 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 and
employees.department_id=50
Plan 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、MongoDBBuffers:每一步实际执行的逻辑读或一致性读;

  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,将会被列出,最好的seo公司!无处遁形;

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

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

  5、可以跟踪整个程序包

  缺点:

  1、步骤繁琐;

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

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

  推荐:Oracle数据库学习教程

  以上就是查看Oracle执行计划的方法的详细内容,更多请关注久澳传媒编程栏目其它相关文章!

「通州市网站搭建公司哪家好」查看Oracle执行计划的方法

您的项目需求

*请认真填写需求信息,我们会在24小时内与您取得联系。