殊途同归:如何获得一个SQL的真实执行计划

崔华,网名 dbsnake

Oracle ACE Director,ACOUG 核心专家

如何查看一个sql的真实执行计划呢?用dbms_xplan.display_cursor(‘hash_value’,‘child_number’, 'advanced')是其中的一种很重要的方法。

我负责的一个库,在移植了大量数据后,跑最后一个运维作业的时候这个运维作业始终阻塞在这样的一个sql上:

update saldat setsdaprs ='C',sdatno = :4 where

sdaprf = :1 and ((sdafrm = :2 andsdatkt =:3) or (sdactt = :5 and sdactp =:6))

在分区表saldat上是存在两个对应的组合global index的:

SQL> selectindex_name,table_name,column_name from dba_ind_columns

2 where index_name in('PK_SALDAT_SDATKT','IDX_SALDAT_SDACTT');

INDEX_NAME TABLE_NAME COLUMN_NAME

------------------------------------------------------------ ---------------------------

IDX_SALDAT_SDACTT SALDAT SDACTP

IDX_SALDAT_SDACTT SALDAT SDACTT

IDX_SALDAT_SDACTT SALDAT SDAPRF

PK_SALDAT_SDATKT SALDAT SDATKT

PK_SALDAT_SDATKT SALDAT SDAFRM

PK_SALDAT_SDATKT SALDAT SDAPRF

来看一下上述sql的真实执行计划:

SQL> selecthash_value, child_number, sql_text from v$sql where sql_text like 'updatesaldat%';

HASH_VALUE CHILD_NUMBERSQL_TEXT

---------- --------------------------------------------------------------------------------

2888063519 0 updatesaldat set sdaprs ='C',sdatno = :4 where sdaprf =

SQL> select * fromtable(dbms_xplan.display_cursor(2888063519, 0 , 'advanced'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

HASH_VALUE 2888063519, child number 0

--------------------------------------

update saldat setsdaprs ='C',sdatno = :4 where sdaprf =:1 and ((sdafrm =:2 and sdatkt =:3) or (sdactt = :5 and sdactp =:6))

Plan hash value:1829738473

----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |

----------------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 1 (100)| | |

| 1 | UPDATE | SALDAT | | | | | |

|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALDAT | 1 | 37 | 0 (0)| ROW L | RO

|* 3 | INDEX RANGE SCAN | PK_SALDAT_SDATKT | 1 | | 0 (0)| | |

----------------------------------------------------------------------------------------------------

Query Block Name /Object Alias (identified by operation id):

-------------------------------------------------------------

1 - UPD$1

2 - UPD$1 / SALDAT@UPD$1

3 - UPD$1 / SALDAT@UPD$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"UPD$1")

INDEX_RS_ASC(@"UPD$1""SALDAT"@"UPD$1" ("SALDAT"."SDAPRF""SALDAT"."SDAFRM" "SALDAT"."SDATKT")

*/

Peeked Binds(identified by position):

--------------------------------------

2 - :1 (VARCHAR2(30), CSID=852): '999'

3 - :2 (VARCHAR2(30), CSID=852): '160'

4 - :3 (VARCHAR2(30), CSID=852): '2332143'

5 - :5 (VARCHAR2(30), CSID=852): '160'

6 - :6 (VARCHAR2(30), CSID=852): '2332143'

Predicate Information(identified by operation id):

---------------------------------------------------

2 - filter((("SDAFRM"=:2AND "SDATKT"=:3) OR ("SDACTT"=:5 AND"SDACTP"=:6)))

3 -access("SDAPRF"=:1)

从结果里可以看到上述SQL阻塞的原因是:Oracle把本来应该用作驱动查询条件(即上述执行计划的access部分)的where条件当作了校验查询条件(即上述执行计划的filter部分)

知道了原因后,第一感觉就是尝试用use_concat提示,但实际的执行情况是不好的,用use_concat得不到我们想要的执行计划,Oracle这里选择了index skip scan:

UPDATE STATEMENT, GOAL= ALL_ROWS Cost=1276778 Cardinality=2 Bytes=70

UPDATE Objectowner=IPRA Object name=SALDAT

CONCATENATION

TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=IPRA Object name=SALDAT Cost=1276774 Cardinality=1 Bytes=35

INDEX SKIP SCAN Object owner=IPRA Objectname=IDX_SALDAT_SDACTT Cost=444676 Cardinality=6558757

TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=IPRA Object name=SALDAT Cost=4 Cardinality=1 Bytes=35

INDEX RANGE SCAN Object owner=IPRA Object name=IDX_SALDAT_SDACTT Cost=3 Cardinality=1

改变上述index skip scan的方法就是收集一下统计信息,上述分区表saldat没有子分区,所以不需要指定granularity,Oracle依然可以正确的收集global statistics:

SQL> exec dbms_stats.gather_table_stats(ownname=> 'IPRA',tabname => 'SALDAT',

estimate_percent => 30,cascade => TRUE,method_opt => 'FOR ALLCOLUMNS SIZE 1' );

PL/SQL proceduresuccessfully completed

收集完后会发现上述sql的执行计划还是未变,这是正常的,因为这里使用了绑定变量(后面我会专门用一节的内容指出绑定变量对执行计划的影响):

上述库现在还处于数据移植阶段,所以我们可以放心的flush shared pool,这里flush shared pool的目的就是让上述sql硬解析,屏蔽掉绑定变量对其执行计划的影响:

sys@IPRACA>alter system flush shared_pool;

System altered.

当执行完上述flush语句后,现在我们可以看到执行计划已经变成了期望的形式:

SQL> select * fromtable(dbms_xplan.display_cursor(2888063519, 0 , 'advanced'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

HASH_VALUE 2888063519, child number 0

--------------------------------------

update saldat setsdaprs ='C',sdatno = :4 where sdaprf =:1 and ((sdafrm = :2 and sdatkt=:3) or (sdactt = :5 and sdactp =:6))

Plan hash value:2813126325

----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

----------------------------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | | | 7 (100)| | | |

| 1 | UPDATE | SALDAT | | | | | | |

| 2 | CONCATENATION | | | | | | | |

| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALDAT | 1 | 35 | 3 (0)| 00:00:01 | RO

|* 4 | INDEX UNIQUE SCAN | PK_SALDAT_SDATKT | 1| | 2 (0)| 00:00:01 | | |

|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALDAT | 1 | 35 | 4 (0)| 00:00:01 | RO

|* 6 | INDEX RANGE SCAN | IDX_SALDAT_SDACTT | 1 | | 3 (0)| 00:00:01 | | |

----------------------------------------------------------------------------------------------------

Query Block Name /Object Alias (identified by operation id):

-------------------------------------------------------------

1 - UPD$1

3 - UPD$1_1 / SALDAT@UPD$1

4 - UPD$1_1 / SALDAT@UPD$1

5 - UPD$1_2 / SALDAT@UPD$1_2

6 - UPD$1_2 / SALDAT@UPD$1_2

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"UPD$1")

OUTLINE_LEAF(@"UPD$1_1")

USE_CONCAT(@"UPD$1" 8)

OUTLINE_LEAF(@"UPD$1_2")

OUTLINE(@"UPD$1")

INDEX_RS_ASC(@"UPD$1_1""SALDAT"@"UPD$1" ("SALDAT"."SDAPRF""SALDAT"."SDAFRM" "SALDAT"."SDATKT

INDEX_RS_ASC(@"UPD$1_2""SALDAT"@"UPD$1_2" ("SALDAT"."SDACTP""SALDAT"."SDAPRF" "SALDAT"."SDAC

*/

Peeked Binds(identified by position):

--------------------------------------

2 - :1 (VARCHAR2(30), CSID=852): '999'

3 - :2 (VARCHAR2(30), CSID=852): '160'

4 - :3 (VARCHAR2(30), CSID=852): '2332143'

5 - :5 (VARCHAR2(30), CSID=852): '160'

6 - :6 (VARCHAR2(30), CSID=852): '2332143'

Predicate Information(identified by operation id):

---------------------------------------------------

4 - access("SDAPRF"=:1AND "SDAFRM"=:2 AND "SDATKT"=:3)

5 - filter((LNNVL("SDATKT"=:3) ORLNNVL("SDAFRM"=:2)))

6 - access("SDACTP"=:6AND "SDAPRF"=:1 AND "SDACTT"=:5)

在作出上述修改后,运维作业顺利在规定时间内跑完。

在上面的内容中,我们已经描述了在Oracle 10g及其后续版本里可以使用dbms_xplan.display_cursor('hash_value', 'child_number','advanced')来得到一个sql的真实执行计划,但可惜的是dbms_xplan.display_cursor在Oracle 9i里是不存在的。那在Oracle 9i中如果我们想得到一个sql的真实执行计划,我们该怎么办呢?

答案是查询v$sql_plan。

本文提供一个存储过程printql,可以把SPID或者SID所对应的sql和其真实执行计划打印出来,而且9i/10g/11g都可以用。printsql的核心代码是来源于老熊的这篇文章——在Oracle 9i下的display_cursor脚本(http://www.laoxiong.net/oracle9i_display_cursor.html)。

我们来看一下printsql的用法:

/*

功能: 打印对应spid或sid所对应的sql以及其执行计划

作者:老熊,dbsnake

创建日期:2010-11-12

输入参数:i_n_id: 输入的spid或sid

i_vc_idtype : 输入的ID的类型,'SPID'表示输入的是spid,'SID'表示输入的是sid.

输出参数:无

输入输出参数:无

调用到的存储过程:无

*/

printsql的源码在原文链接下载,下面来看一个应用printsql的实际例子:

OS端top里显示SPID为1212576的进程占用了14.2%的CPU,现在我们想看看这个进程到底在做什么:

Name PID CPU% PgSp Owner ClientV2 0 Press:

oracle 1212576 14.2 6.0 oracle ServerV3 0 "h" for help

topas 294994 0.2 3.1 oracle ClientV3 0 "q" to quit

oracle 241960 0.1 4.8 oracle

直接执行一下printsql,传入1212576就可以看到结果了:

SQL> setserveroutput on size 1000000

SQL> execprintsql(1212576,'SPID');

--------------------------------------------------------------------------------------

select * from caipra.uplbas where ubatkt=2222222 and ubafrm=450

--------------------------------------------------------------------------------------

The session id is 356

The status is ACTIVE

The sql hash value is352212893

The child cursornumber is 0

The prev hash value is352212893

The prev child cursornumber is 0

The osuser is cuihua

The machine isWORKGROUP\Y8908CUIHUA

The terminal isY8908CUIHUA

The program issqlplus.exe

The event is db file scattered read

--------------------------------------------------------------------------------------

alter system kill session '356,652' immediate;

--------------------------------------------------------------------------------------

The hash_value is352212893

The child_number is 0

The plan_hash_value is4237911507

The execution is 2

The buffer_gets is 311300

The gets_per_exec is155650

The rows_processed is2

The rows_per_exec is 1

The disk_reads is 210483

The reads_per_exec is105241.5

The cpu_time is35.462323

The cpu_per_exec is17.7311615

The ELAPSED_TIME is72.283601

The ela_per_exec is36.1418005

--------------------------------------------------------------------------------------

HASH_VALUE: 352212893 CHILD_NUMBER: 0

-------------------------------------------------------------------------------------------------------------------

select * fromcaipra.uplbas where ubatkt=2222222 and ubafrm=450

Plan hash value:4237911507

------------------------------------------------------------------

| Id | Operation | Name | E-Rows | Pstart | Pstop |

------------------------------------------------------------------

| 1 | PARTITION RANGE ALL | | 1 | 1 | 24 |

| * 2 | TABLE ACCESS FULL | UPLBAS | 1 | 1 | 24 |

------------------------------------------------------------------

Predicate Information(identified by operation id):

---------------------------------------------------

2 - filter((TO_NUMBER("UBATKT")=2222222AND TO_NUMBER("UBAFRM")=450))

从结果里可以看到printsql已经帮你把这个process正在执行的sql,其真实的执行计划以及Kill这个session的语句打印出来了。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-05-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏张善友的专栏

IBatisNet 之 自动生成主关键字

很多系统支持自动生成主关键字。一些数据库厂商预先生成(oracle),一些数据库厂商之后生成(mssal mysql).。如果你在<insert>元素中使用<s...

1718
来自专栏乐沙弥的世界

NULL 值与索引(二)

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为,基于允许存在null值的索引列,其索引值不会被存储;其次 是由于这个特...

422
来自专栏xcywt

学习SQLite之路(四)

20160621 更新 参考: http://www.runoob.com/sqlite/sqlite-tutorial.html 1. SQLite   a...

1858
来自专栏Python

Mysql-2

用户操作与权限管理 MySQL用户操作 创建用户 方法一: CREATE USER语句创建 CREATE USER "用户名"@"IP地址" I...

1955
来自专栏坚毅的PHP

mysql DUPLICATE KEY UPDATE 问题

DUPLICATE KEY UPDATE batch执行时出死锁错误 背景知识 一、 mysql  insert 与 duplicate key: 典型的插入语...

3585
来自专栏帘卷西风的专栏

关于mysql存储过程创建动态表名及参数处理

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog) 

533
来自专栏散尽浮华

mysql操作命令梳理(4)-中文乱码问题

在平时的mysql运维操作中,经常会碰到插入中文字段后出现乱码的情况,产生中文乱码的原因一般有: 1)mysql的编码格式不对,是latin1编码。强烈推荐将m...

1748
来自专栏杨建荣的学习笔记

一条SQL语句的执行计划变化探究(r10笔记第9天)

继续上次分析的一个问题,一个简单的SQL语句执行计划有些奇怪,明明可以走唯一性索引但是却走了另外一个索引。 当然了,最后逐步定位,发现是在直方图...

2563
来自专栏about云

Spark Sql系统入门4:spark应用程序中使用spark sql

问题导读 1.你认为如何初始化spark sql? 2.不同的语言,实现方式都是什么? 3.spark sql语句如何实现在应用程序中使用? 为了...

3257
来自专栏机器学习原理

知识图谱(2)——neo4j的用法

先了解各个命令的用法 创建一个节点 CREATE (ee:Person { name: "Emil", from: "Sweden", klout: 99 ...

631

扫描关注云+社区