殊途同归:如何获得一个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 条评论
登录 后参与评论

相关文章

来自专栏Jackson0714

基础很重要~~04.表表达式-上篇

31512
来自专栏肖洒的博客

JDBC编程

安装驱动,下载mysql-connector-java。 将mysql-connector-java-5.1.42-bin.jar包拷到WEB-INF目录下。

682
来自专栏battcn

MySQL - EXPLAIN详解

EXPLAIN: 为 SELECT语句中使用到的每个表返回一条信息。它按照MySQL在处理语句时读取它们的顺序列出这些表。MySQL使用循环嵌套算法解析所有连接...

872
来自专栏岑玉海

RavenDb学习(二)简单的增删查改

在上一节当中已经介绍了RavenDb的文档设计模式,这一节我们要具体讲一讲如何使用api去访问RavenDb 1.连接RavenDb var docum...

3445
来自专栏技术碎碎念

sql server 2008 数据库的完整性约束

一、数据库完整性概述 1.数据库的完整性: ①数据库的完整性是指数据的正确性和相容性 ②数据库完整性是防止不合语义或不正确的数据进入数据库 ③完整性体现了是否真...

2934
来自专栏青枫的专栏

day44_Oracle学习笔记_03

先去Oracle官网去下载最新版本的sqldeveloper,下载地址:https://www.oracle.com/technetwork/developer...

612
来自专栏Java3y

移动商城第七篇【购物车增删改查、提交订单】

把商品加入购物车 接下来我们要做的就是将商品加入到购物车中。我们这次使用的是Cookie来将用户的信息存储起来。那为什么要用cookie呢?? 如果将购物车存储...

74213
来自专栏乐沙弥的世界

SQLserver 存储过程执行错误记录到表

401
来自专栏张善友的专栏

Entity Framework Core 实现MySQL 的TimeStamp/RowVersion 并发控制

将通用的序列号生成器库 从SQL Server迁移到Mysql 遇到的一个问题,就是TimeStamp/RowVersion并发控制类型在非Microsoft ...

2638
来自专栏乐沙弥的世界

检查及设置合理的undo表空间

      UNDO是用于实现并发控制以及构建一致性读,也就是在数据变更之前产生前镜像,以保证用户能够回滚或撤销对数据库所作的修改。是Oracle数据库完整性的...

682

扫码关注云+社区