cursor_sharing为force时带来的一个执行计划的假象

最新推荐文章于 2021-01-19 01:23:24 发布
最新推荐文章于 2021-01-19 01:23:24 发布
阅读量99 收藏
点赞数
有时候可能存在评估的执行计划和真实的执行计划不一样，查看执行计划的方法如下:
a.explain plan ,可通过dbms_xplan.display来查看执行计划
b.set autot
c.v$sql_plan
d.dbms_xplan.display_cursor

a,b的执行计划都是根据explain plan得到的，因此都不是真实的(基表是sql_plan)，而3,4的执行计划都是真实的(基表是 V$SQL_PLAN),cursor_sharing为force+直方图的时候可能存在一个假象,就是set autot trace给你显示的执行计划很完美，而真实的执行计划是当作绑定变量处理的,第一次查询的时候可能存在bind peeking的影响,而之后的如果没有新的cursor产生,那么执行计划都是重用第一次的,[@more@]

做了个测试如下:


SQL> conn l5m/l5m @dev107_c203
Connected.
SQL> show parameter cursor

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
cursor_sharing string
EXACT
cursor_space_for_time boolean
FALSE
open_cursors integer
500
session_cached_cursors integer
20
SQL> alter session set cursor_sharing=force;

Session altered.

SQL> create table kk1(id int);

Table created.

SQL> insert into kk1 select 1 from dual;

1 row created.

SQL> insert into kk1 select 2 from dba_objects;

63413 rows created.

SQL> create index i_kk1_1 on kk1(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'KK1',method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> set autot trace
SQL> select count(*) from kk1 where id=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 1026585023

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| I_KK1_1 | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=1)


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


SQL> /


Execution Plan
----------------------------------------------------------
Plan hash value: 1026585023

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| I_KK1_1 | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=1)


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


SQL> select count(*) from kk1 where id=2;
//这里显示的执行计划是假的，真正的执行计划是index range scan


Execution Plan
----------------------------------------------------------
Plan hash value: 1408337260

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 26 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| KK1 | 62313 | 182K| 26 (8)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=2)


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

下面的真正的执行计划
SQL> set autot off
SQL> select sql_text,sql_id,child_number from v$sql
2 where sql_text like 'select count(*) from kk1%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
--------------------------------------- ------------
select count(*) from kk1 where id=:"SYS_B_0"
1fnfa7pqtz9h6 0


SQL> select * from table(dbms_xplan.display_cursor('1fnfa7pqtz9h6','0'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1fnfa7pqtz9h6, child number 0
-------------------------------------
select count(*) from kk1 where id=:"SYS_B_0"

Plan hash value: 1026585023

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| I_KK1_1 | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=:SYS_B_0)


19 rows selected.



SQL> set autot trace

可以发现下面的执行计划是假的，和加index(t)的提示的consistent gets是一致的
SQL> select count(*) from kk1 where id=2; //走索引逻辑读125


Execution Plan
----------------------------------------------------------
Plan hash value: 1408337260

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 26 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| KK1 | 62313 | 182K| 26 (8)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=2)


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



SQL> select /*+ index(t) */ count(*) from kk1 t where id=2;


Execution Plan
----------------------------------------------------------
Plan hash value: 1026585023

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 126 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| I_KK1_1 | 62313 | 182K| 126 (2)| 00:00:02 |
-----------------------------------------------------------------------------

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

2 - access("ID"=2)


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



SQL> select /*+ full(t) */ count(*) from kk1 t where id=2;
//全表逻辑读107


Execution Plan
----------------------------------------------------------
Plan hash value: 1408337260

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 26 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| KK1 | 62313 | 182K| 26 (8)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("ID"=2)


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



下面看看dbms_xplan.display与dbms_xplan.display_cursor两个包的结果是否一致

SQL> explain plan set statement_id='NO' for
2 select count(*) from kk1 where id=2;

SQL> select plan_table_output
2 from table(dbms_xplan.display('PLAN_TABLE','NO','ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1408337260

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 26 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| KK1 | 62313 | 182K| 26 (8)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / KK1@SEL$1

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

2 - filter("ID"=2)

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

25 rows selected.




SQL> select sql_text,sql_id,child_number from v$sql where sql_text like 'select count(*) from kk1%';


SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
--------------------------------------- ------------
select count(*) from kk1 where id=:"SYS_B_0"
1fnfa7pqtz9h6 0


SQL> select * from table(dbms_xplan.display_cursor('1fnfa7pqtz9h6','0'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1fnfa7pqtz9h6, child number 0
-------------------------------------
select count(*) from kk1 where id=:"SYS_B_0"

Plan hash value: 1026585023

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| I_KK1_1 | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - access("ID"=:SYS_B_0)


19 rows selected.


由于dbms_xplan.display表的基表是plan_table,而dbms_xplan.display_cursor的基表是v$sql_plan，看出结果是不一致的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1020411/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1020411/

ckomuo086028
关注
oracle cursor_sharing 参数设置测试
木子的博客
08-06 2524
题目：Oracle的cursor_sharing参数告诉Oracle数据库何时重用SQL语句，有Exact、force和similar三种设置，请阐述三种设置的区别，并通过一个例子距离说明三者之间的区别。最后请设计一个实际应用场景，执行一系列操作，说明这个参数设置的意义（提示，可以设计一个循环操作语句来验证），分别说明在什么情况下用何种参数设置。     答： Cursor_sharing
CONSOLE_CURSOR_INFO类型详解
qq_42591783的博客
12-02 5421
光标信息的结构体类型 typedef struct _CONSOLE_CURSOR_INFO { DWORD dwSize; BOOL bVisible; } CONSOLE_CURSOR_INFO, *PCONSOLE_CURSOR_INFO; 成员类型 其中typedef unsigned long DWORD; typedef int BOOL; 成员代表的含义...
浅谈cursor_sharing取值对SQL共享的影响（下）
ciqu9915的博客
08-18 131
上篇我们介绍了Oracle SQL共享游标的机制，以及cursor_sharing参数EXACT取值作用。本篇我们继续介绍cursor_sharing参数的其他两个取值：FORCE和SIMILAR的实际含义。 4、...
补充：小心设置cursor_sharing=force参数
04-24 110
转载自：http://www.itpub.net/351520,2.html摘自：expert one-on-one oracle (P554)設置cussor_sharing=force之後，會有以下的情況： • Op...
小心设置cursor_sharing=force参数
03-28 186
原文：http://www.itpub.net/339204.html我们公司的数据库，开发商没有很好地使用绑定变量，我星期天我打开了这个参数，想看看结果如何，结果出现严重的问题，今天早上忙呼一阵。1.执行像如下的sql语句...
cursor_sharing=force导致的ora-600错误的分析
weixin_34290390的博客
02-18 173
1、在alert_lxdb.log日志中报600错误 Errors in file /u01/app/oracle/admin/lxdb/udump/lxdb_ora_50379.trc: ORA-00600: internal error code, arguments: [kkslhsh1], [101], [], [], [], [], [], [] 注意这个问题...
11G的BUG，cursor_sharing=force不好用了
ct827519的博客
10-20 248
11.2.0.1: SUNDOG315>select * from v$version; BANNER -------------------------------------------------...
python执行mysql多个sql语句_python – MySQLdb.cursor.execute无法运行多个查...
weixin_39627455的博客
12-18 2192
我们尝试将包含多个插入语句的SQL文件作为单个查询运行,但是当任何语句包含错误时,似乎回滚失败.sql_mode = STRICT_ALL_TABLESdefault-storage-engine = innodbPython代码：from contextlib import closingimport MySQLdbdatabase_connection = MySQLdb.connect(ho...
【Oracle优化】通过SQL_PROFILE固化执行计划
个人公众号：Alen的数据库学习笔记
05-04 2301
在生产环境中，随着数据量的变化、查询条件的不同，有时执行计划发生了突变而导致SQL性能急剧下降。此时如果调整代码，或者修改业务逻辑，一方面时间上可能来不及，另一方面可能成本较高，那么有没有办法，可以在不调整代码或业务逻辑的情况下，让SQL语句使用计划突变之前的执行计划呢？本篇将介绍通过SQL_PROFILE来固化执行计划的三种方法。 一 绑定已存在的执行计划 DECLARE v_ar_pr...
mysql存储过程 cursor_MySql存储过程 CURSOR循环
weixin_34965737的博客
01-19 2275
游标游标(Cursor)是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。使用步骤声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)打开定义的游标:open 游标名称;获得下一行数据:FETCH 游标名称 into field_1, field_2;需要执行的...
Oracle一些参数的理解 cursor_sharing
cmhaqkcrx827119593的博客
06-24 178
参数session_cached_cursors解释------------------------------------------------自己一直感觉对oracle的内存这一块儿理解不够深刻。今天看到关于session...
Cursor_sharing(转)
baobaojinjin的博客
01-29 216
一、Cursor_sharing简介：     这个参数是用来告诉Oracle在什么情况下可以共享游标，即SQL重用。     Cursor_sharing参数有3个值可以设置：      1)、EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。      2)、SIMILAR：similar是在Orac...
游标共享之cursor_sharing=force
关注系统性能调优
11-05 5053
cursor sharing=force ， oracle将2条类似的SQL的谓词用一个变量代替，同时将它们看做同一条SQL语句处理。这种方式很粗暴，极可能造成执行计划的不准确。         适用场景：在无法将应用的代码修改为绑定变量情况下，oracle提供的一种解决方法。         注意：游标共享特性有个不太好的名声，就是它不是很稳定，过去数年，大量与之相关的bug被发现和确认
cursor：mutex S事件查看
sxj1069425501的专栏
04-18 3173
select sql_id,version_count from v$sqlarea where version_count> 500 order by 2 desc; SQL_ID        VERSION_COUNT ------------- ------------- 50u4uqg9vjk78           1705 select count(C
cursor_sharing案例解析
weixin_34007906的博客
11-25 97
前段时间有给学员上课的时候，有学员提出这么一个发生在生产环境上的问题，客户的数据库硬件是很不错的，内存有64G，其中20G给oracle数据库使用，数据库开始运行的时候查询挺快，但是运行一段时间时候，就变得很慢，cup的使用率一直很高，重新启动数据库，又恢复正常，经过分析发现，发现sql的命中率很低，大部分sql都做硬解析，损耗大量的缓存在存放sql和执行计划，并且做硬解析对...
常见问题：'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'类型的等待事件 (文档 ID 1525791.1)
mybluetiankong的专栏
01-20 2383
In this Document   Purpose   Questions and Answers   什么是 'cursor: ' 等待事件？   最常见的等待事件是什么？   等待事件最常见的原因是什么？   如何避免这些等待事件？
oracle event 'cursor: mutex S'
hellojoy的博客
08-18 2112
execute DBMS_STATS.GATHER_TABLE_STATS(ownname => '***', tabname => '*****', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree
oracle11g cursor：mutex S导致的load过高的追踪过程详解
demonson的专栏
03-30 1475
--> 问题背景同事电话我，说oracle数据库很慢，很卡，cpu99%了。 1，查看问题现场，cpu高单核不高，load高多核，看到每个核只有20%左右使用了1/5，所以数据库应该没事，没有到故障级别，只是load高已经接近90了，E:\u\azure_cloud\oracle\05.png： 2，看io读写比较低，磁盘io不是瓶颈实时观察，才148，可以看出io性能没有问题，毕竟是ssd...
cursor_sharing
最新发布
09-29
当cursor_sharing参数设置为SIMILAR或FORCE时，Oracle会尝试将相似或相同的SQL语句共享同一个游标，以减少内存使用和提高性能。这个参数可以帮助解决变量绑定问题，提高系统性能。 然而，需要注意的是，在将cursor_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值