学优化首先要懂执行计划怎么看，提供3种方法

最新推荐文章于 2021-04-04 08:02:18 发布

老农民挖数据

最新推荐文章于 2021-04-04 08:02:18 发布

阅读量2.1k

点赞数

分类专栏： oracle_sql tuning 文章标签：优化 statistics loops access nested iterator

本文链接：https://blog.csdn.net/shushugood/article/details/7942114

版权

oracle_sql tuning 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

方法一

一、启用Autotrace功能。
任何以SQL*PLUS连接的session都可以用Autotrace,不过还是要做一些设置的，否则可能报错。

1、报错示例：

SQL :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

*******************************************************************************

SQL> set autotrace on;

SP2-0613: 无法验证 PLAN_TABLE 格式或实体

SP2-0611: 启用EXPLAIN报告时出现错误

SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色

SP2-0611: 启用STATISTICS报告时出现错误

该错误的的主要原因是由于当前用户下没有PLAN_TABLE这张表及相应的PLUSTRACE角色权限。

2、解决方法：

A．以SYS用户登录：

oracle>sqlplus '/ as sysdba';

B．运行utlxplan.sql(rdbms/admin下) 脚本创建 PLAN_TABLE；

SQL>@ D:oracleora81RDBMSADMINutlxplan.sql

C．通过执行 plustrce.sql(ORACLE_HOME/sqlplus/admin/plustrce.sql)脚本创建 plustrace 角色，这将V$ 视图上的选择权限授予该角色，也将 plustrace 角色授予DBA 角色，脚本部份内容如下：

drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;

D．将 plustrace 角色授予当前用户(无 DBA 角色的用户)。

二、设置Autotrace的命令。

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON	产生结果集和解释计划并列出统计
3	SET AUTOTRACE ON EXPLAIN	显示结果集和解释计划不显示统计
4	SETAUTOTRACE TRACEONLY	显示解释计划和统计，尽管执行该语句但您将看不到结果集
5	SET AUTOTRACE TRACEONLY STATISTICS	只显示统计

Eg：SET AUTOTRACE ON, set timing on, alter session set time_statistics=true;

三、Autotrace执行计划的各列的涵义

序号	列名	解释
1	ID_PLUS_EXP	每一步骤的行号
2	PARENT_ID_PLUS_EXP	每一步的Parent的级别号
3	PLAN_PLUS_EXP	实际的每步
4	OBJECT_NODE_PLUS_EXP	Dblink或并行查询时才会用到

四、AUTOTRACE Statistics常用列解释

序号	列名	解释
1	db block gets	从buffer cache中读取的block的数量
2	consistent gets	从buffer cache中读取的undo数据的block的数量
3	physical reads	从磁盘读取的block的数量
4	redo size	DML生成的redo的大小
5	sorts (memory)	在内存执行的排序量
7	sorts (disk)	在磁盘上执行的排序量

以下是测试案例：

SQL> SELECT /*+ parallel(8)   */ dt.TIMEMARK "#TIMEMARK",dt.GRIDX "#GRIDX",dt.GRIDY "#GRIDY",dt.CGI "#CGI",
  2  sum(MRTotal) MRTotal
,sum(MOCMRCount) MOCMRCount
,sum(MTCMRCount) MTCMRCount
,sum(MRTotal)/1000/60/60*480 Erl
,count(*) over(ORDER BY NULL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "@totalrows"
 FROM (
      SELECT /*+ parallel(8)   */dt.TIMEMARK,dt.GRIDX,dt.GRIDY,dt.CGI,
            sum(MRTotal) MRTotal
           ,sum(MOCMRCount) MOCMRCount
           ,sum(MTCMRCount) MTCMRCount
  3    4         FROM MRGrid_RxQualLev dt,CDR_VIPQuality cdrv
  5    6    7    8    9   10   11   12   13  WHERE dt.TIMEMARK >= '2012-09-04 13:00:00' and dt.TIMEMARK< '2012-09-04 14:00:00'
 AND dt.CGI=cdrv.LASTCGI
 AND cdrv.imsi= '432'
 and (dt.gridx>=0 and dt.gridx<=0) 
 and  (dt.gridy>=0 and dt.gridy<=0)
GROUP BY dt.TIMEMARK,dt.GRIDX,dt.GRIDY,dt.CGI
) dt
,cfg_cell cfg_cell_bscid  WHERE dt.cgi=cfg_cell_bscid.cgi
 and cfg_cell_bscid.bscid in (select bscid from cfg_bsc where mssid in(  select * from TABLE(broad_utility.f_str2varlist('655|309'))))
GROUP BY dt.TIMEMARK,dt.GRIDX,dt.GRIDY,dt.CGI; 14   15   16   17   18   19   20   21   22  

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2239786818

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				       | Name		     | Rows  | Bytes | Cost (%CPU)| Time     |	  TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			       |		     |	   1 |	 117 |	  12   (9)| 00:00:01 |	      |      |		  |
|   1 |  PX COORDINATOR 			       |		     |	     |	     |		  |	     |	      |      |		  |
|   2 |   PX SEND QC (RANDOM)			       | :TQ20004	     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,04 | P->S | QC (RAND)  |
|   3 |    WINDOW BUFFER			       |		     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,04 | PCWP |		  |
|   4 |     HASH GROUP BY			       |		     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,04 | PCWP |		  |
|   5 |      PX RECEIVE 			       |		     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,04 | PCWP |		  |
|   6 |       PX SEND HASH			       | :TQ20003	     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,03 | P->P | HASH	  |
|   7 |        HASH GROUP BY			       |		     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,03 | PCWP |		  |
|*  8 | 	HASH JOIN SEMI			       |		     |	   1 |	 117 |	  12   (9)| 00:00:01 |	Q2,03 | PCWP |		  |
|   9 | 	 PX RECEIVE			       |		     |	     |	     |		  |	     |	Q2,03 | PCWP |		  |
|  10 | 	  PX SEND HASH			       | :TQ20002	     |	     |	     |		  |	     |	Q2,02 | P->P | HASH	  |
|  11 | 	   NESTED LOOPS 		       |		     |	     |	     |		  |	     |	Q2,02 | PCWP |		  |
|  12 | 	    NESTED LOOPS		       |		     |	   1 |	 104 |	   2   (0)| 00:00:01 |	Q2,02 | PCWP |		  |
|  13 | 	     VIEW			       |		     |	   1 |	  91 |	   2   (0)| 00:00:01 |	Q2,02 | PCWP |		  |
|  14 | 	      HASH GROUP BY		       |		     |	   1 |	 113 |	   2   (0)| 00:00:01 |	Q2,02 | PCWP |		  |
|  15 | 	       PX RECEIVE		       |		     |	   1 |	 113 |	   2   (0)| 00:00:01 |	Q2,02 | PCWP |		  |
|  16 | 		PX SEND HASH		       | :TQ20001	     |	   1 |	 113 |	   2   (0)| 00:00:01 |	Q2,01 | P->P | HASH	  |
|  17 | 		 HASH GROUP BY		       |		     |	   1 |	 113 |	   2   (0)| 00:00:01 |	Q2,01 | PCWP |		  |
|  18 | 		  NESTED LOOPS		       |		     |	     |	     |		  |	     |	Q2,01 | PCWP |		  |
|  19 | 		   NESTED LOOPS 	       |		     |	   1 |	 113 |	   2   (0)| 00:00:01 |	Q2,01 | PCWP |		  |
|  20 | 		    PX BLOCK ITERATOR	       |		     |	     |	     |		  |	     |	Q2,01 | PCWC |		  |
|* 21 | 		     TABLE ACCESS FULL	       | CDR_VIPQUALITY      |	   2 |	  44 |	   2   (0)| 00:00:01 |	Q2,01 | PCWP |		  |
|* 22 | 		    INDEX RANGE SCAN	       | PK_MRGRID_RXQUALLEV |	   1 |	     |	   0   (0)| 00:00:01 |	Q2,01 | PCWP |		  |
|  23 | 		   TABLE ACCESS BY INDEX ROWID | MRGRID_RXQUALLEV    |	   1 |	  91 |	   0   (0)| 00:00:01 |	Q2,01 | PCWP |		  |
|* 24 | 	     INDEX UNIQUE SCAN		       | PK_CFG_CELL	     |	   1 |	     |	   0   (0)| 00:00:01 |	Q2,02 | PCWP |		  |
|  25 | 	    TABLE ACCESS BY INDEX ROWID        | CFG_CELL	     |	   1 |	  13 |	   0   (0)| 00:00:01 |	Q2,02 | PCWP |		  |
|  26 | 	 BUFFER SORT			       |		     |	     |	     |		  |	     |	Q2,03 | PCWC |		  |
|  27 | 	  PX RECEIVE			       |		     |	 106K|	1356K|	   9   (0)| 00:00:01 |	Q2,03 | PCWP |		  |
|  28 | 	   PX SEND HASH 		       | :TQ20000	     |	 106K|	1356K|	   9   (0)| 00:00:01 |	      | S->P | HASH	  |
|  29 | 	    VIEW			       | VW_NSO_1	     |	 106K|	1356K|	   9   (0)| 00:00:01 |	      |      |		  |
|* 30 | 	     HASH JOIN			       |		     |	 106K|	1043K|	   9   (0)| 00:00:01 |	      |      |		  |
|  31 | 	      PX COORDINATOR		       |		     |	     |	     |		  |	     |	      |      |		  |
|  32 | 	       PX SEND QC (RANDOM)	       | :TQ10000	     |	 209 |	1672 |	   3   (0)| 00:00:01 |	Q1,00 | P->S | QC (RAND)  |
|  33 | 		PX BLOCK ITERATOR	       |		     |	 209 |	1672 |	   3   (0)| 00:00:01 |	Q1,00 | PCWC |		  |
|  34 | 		 TABLE ACCESS FULL	       | CFG_BSC	     |	 209 |	1672 |	   3   (0)| 00:00:01 |	Q1,00 | PCWP |		  |
|  35 | 	      COLLECTION ITERATOR PICKLER FETCH| F_STR2VARLIST	     | 16360 | 32720 |	   5   (0)| 00:00:01 |	      |      |		  |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("CFG_CELL_BSCID"."BSCID"="BSCID")
  21 - filter("CDRV"."IMSI"='432')
  22 - access("DT"."TIMEMARK">=TIMESTAMP' 2012-09-04 13:00:00.000000000' AND "DT"."CGI"="CDRV"."LASTCGI" AND "DT"."GRIDX"=0 AND
	      "DT"."GRIDY"=0 AND "DT"."TIMEMARK"<TIMESTAMP' 2012-09-04 14:00:00.000000000')
       filter("DT"."GRIDX"=0 AND "DT"."GRIDY"=0 AND "DT"."CGI"="CDRV"."LASTCGI")
  24 - access("DT"."CGI"="CFG_CELL_BSCID"."CGI")
  30 - access("MSSID"=TO_NUMBER(VALUE(KOKBF$)))

Note
-----
   - Degree of Parallelism is 8 because of hint


Statistics
----------------------------------------------------------
	 12  recursive calls
	  0  db block gets
	 51  consistent gets
	  0  physical reads
	  0  redo size
	889  bytes sent via SQL*Net to client
	509  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

方法二

--查看sql执行计划

SQL> EXPLAIN PLAN FOR SELECT /*+ index(b,idx_CFG_IMEI_imei) */ * FROM ml_test1 a,CFG_IMEI b
  2    WHERE
  3   substr(a.imei,1,6)=b.imei;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1759924441
--------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |  9980 |   594K|
|   1 |  MERGE JOIN                   |                   |  9980 |   594K|
|   2 |   TABLE ACCESS BY INDEX ROWID | ML_TEST1          |  9980 |   165K|
|   3 |    INDEX FULL SCAN            | IDX_ML_TEST1_01   |  9980 |       |
|*  4 |   SORT JOIN                   |                   |   164 |  7216 |
|   5 |    TABLE ACCESS BY INDEX ROWID| CFG_IMEI          |   164 |  7216 |
|   6 |     INDEX FULL SCAN           | IDX_CFG_IMEI_IMEI |   164 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(SUBSTR("IMEI",1,6)="B"."IMEI")
       filter(SUBSTR("IMEI",1,6)="B"."IMEI")
 
19 rows selected
 
SQL>

老农民挖数据

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
学优化首先要懂执行计划怎么看，提供3种方法

方法一一、启用Autotrace功能。任何以SQL*PLUS连接的session都可以用Autotrace,不过还是要做一些设置的，否则可能报错。1、报错示例：SQL :> set autotrace on;SP2-0613: Unable to verify PLAN_TABLE format or existenceSP2-0611: Error enablin
复制链接

扫一扫

专栏目录