oracle sql 子游标_Oracle SQL 硬解析和子游标

最新推荐文章于 2022-05-18 10:50:24 发布

weixin_39682301

最新推荐文章于 2022-05-18 10:50:24 发布

阅读量135

点赞数

文章标签： oracle sql 子游标

本文链接：https://blog.csdn.net/weixin_39682301/article/details/111528656

版权

阅读导航

Oracle SQL 硬解析和子游标

硬解析和产生子游标的原因

如何避免

Oracle SQL 硬解析和子游标

What reasons will be happening sql hard parse and generating new child cursors

在一个繁忙的系统中，发现一个复杂且非常长的查询，产生40多个子游标和大量的硬解析，占用很多的内存、CPU资源；

SQL> @sql 3168229204

Show SQL text, child cursors and execution stats for SQL hash value 3168229204 child GGT report

HASH_VALUE CH# PLAN_HASH FIRST_LOAD_TIME LAST_LOAD_TIME SQL_PROFIL

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

3168229204 0 1144031096 2016-09-21/15:52:45 2016-11-03/16:43:40

3168229204 1 1144031096 2016-09-21/15:52:45 2016-11-03/17:39:50

3168229204 2 1144031096 2016-09-21/15:52:45 2016-11-03/18:52:26

3168229204 3 1144031096 2016-09-21/15:52:45 2016-11-04/08:41:15

3168229204 4 1144031096 2016-09-21/15:52:45 2016-11-05/08:12:52

3168229204 5 1144031096 2016-09-21/15:52:45 2016-11-07/08:00:49

3168229204 6 1144031096 2016-09-21/15:52:45 2016-11-07/13:15:24

3168229204 7 1144031096 2016-09-21/15:52:45 2016-11-08/08:07:12

3168229204 8 1144031096 2016-09-21/15:52:45 2016-11-09/08:11:57

3168229204 9 1144031096 2016-09-21/15:52:45 2016-11-09/08:31:15

3168229204 10 1144031096 2016-09-21/15:52:45 2016-11-09/08:46:13

3168229204 11 532057913 2016-09-21/15:52:45 2016-11-09/09:01:21

3168229204 12 1144031096 2016-09-21/15:52:45 2016-10-26/08:10:30

3168229204 13 1144031096 2016-09-21/15:52:45 2016-10-27/08:06:34

3168229204 14 1144031096 2016-09-21/15:52:45 2016-10-27/10:30:49

3168229204 15 1144031096 2016-09-21/15:52:45 2016-10-28/08:06:48

3168229204 16 1144031096 2016-09-21/15:52:45 2016-10-31/08:00:14

3168229204 17 1144031096 2016-09-21/15:52:45 2016-10-29/11:15:32

3168229204 18 1144031096 2016-09-21/15:52:45 2016-11-01/08:02:00

3168229204 19 1144031096 2016-09-21/15:52:45 2016-11-01/08:16:02

3168229204 44 532057913 2016-09-21/15:52:45 2016-10-25/08:36:46

21 rows selected.

CH# PARENT_HANDLE OBJECT_HANDLE PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED LIOS PIOS SORTS CPU_MS ELA_MS USERS_EXECUTING

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

0 000000099DC30528 000000099DC62120 1 117 1 11 20619 563097 16037 0 6707.98 1777858.92 0

1 000000099DC30528 000000099EC8B478 1 114 1 11 20795 539435 1030 0 3436.478 59351.813 0

2 000000099DC30528 000000099DE9FE00 3 109 3 33 62385 6765790 6028872 0 87585.686 927030.91 0

3 000000099DC30528 000000099FC011E8 8 105 8 82 155431 22164287 21124804 0 295961.008 6440049.63 0

4 000000099DC30528 000000099F5D9880 44 103 44 315 572091 134332996 129689322 0 1627595.57 26658408 0

5 000000099DC30528 000000099EC73B98 104 100 104 565 1007037 318502972 310719053 0 3833473.23 32819296.8 0

6 000000099DC30528 000000099F426050 21 95 21 30 25387 1980211 9151 0 11131.307 691583.17 0

7 000000099DC30528 000000099E1C8A58 31 91 31 81 134024 82881335 75710067 0 830793.701 12330642 0

8 000000099DC30528 000000099FAC91F8 51 86 51 221 399552 156405150 151167773 0 1859173.36 34943618.3 0

9 000000099DC30528 000000099F6D67B8 1 84 1 5 9331 545117 19 0 1828.722 2107.133 0

10 000000099DC30528 000000099FCF3EE8 1 78 1 5 9386 547695 188 0 2588.606 10211.348 0

11 000000099DC30528 000000099F50D9C8 32 76 32 203 372484 98467223 94342488 0 1153776.61 19565473.3 1

12 000000099DC30528 000000099FA1ED18 1 72 1 862 8610 626229 35266 0 8491.715 736156.11 0

13 000000099DC30528 000000099F0DA4C0 51 69 51 54046 540160 156744017 150198327 0 1901325.93 31480771.6 0

14 000000099DC30528 000000099E680C90 10 65 10 6566 65606 25179760 22590318 0 251589.755 3495357.72 0

15 000000099DC30528 000000099EF0DF50 42 57 42 36991 369806 115460484 102958163 0 1152703.76 15607683.6 0

16 000000099DC30528 000000099F5ACBC8 63 53 63 60623 606007 167981225 155721272 0 1724758.8 21204621.2 0

17 000000099DC30528 000000099FA0A6A0 1 53 1 888 8879 193856 1047 0 1283.808 2972.2 0

18 000000099DC30528 000000099E7B52D8 142 51 142 81062 810103 239175636 226077041 0 2483807.37 18198010.6 0

19 000000099DC30528 000000099DA92AA0 15 46 15 12766 127575 1847753 5046 0 15149.692 457626.043 0

44 000000099DC30528 000000099E6EBA18 48 1 48 37672 376331 149384376 144111692 0 1825119.51 31195023.6 0

而且由于某些原因优化器不能够做出正确的评估，导致执行计划不一样，产生了大量的物理读等待事件；所以作为开发人员我们要了解清楚硬解析和产生子游标的原因，做出必要的调整和优化，使优化器能够正确做出评估，巩固和保护执行计划，竭力避免重复硬解析和使用不正确的执行计划。

硬解析和产生子游标的原因

Oracle中有很多的原因导致硬解析和产生子游标，比如有两个用户USERA和USERB,它们都有相同的表TAB01,两个用户都执行了如下的查询操作；

select * from tab01;

这样就会在v$sqlarea,v$sql,v$sql_shared_cursor产生如下的记录；

SQL> select sql_text,hash_value,sharable_mem,buffer_gets,loads,fetches,executions,optimizer_mode,PARSING_SCHEMA_NAME from v$sqlarea where sql_id='5b42g2fkrrzss';

SQL_TEXT HASH_VALUE SHARABLE_MEM BUFFER_GETS LOADS FETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME

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

select * from tab01 2776366872 85836 220 2 2 2 ALL_ROWS USERB

SQL> select t.CHILD_NUMBER,sql_text,hash_value,sharable_mem,buffer_gets,loads,fetches,executions,optimizer_mode,t.PARSING_SCHEMA_NAME from v$sql t where sql_id='5b42g2fkrrzss';

CHILD_NUMBER SQL_TEXT HASH_VALUE SHARABLE_MEM BUFFER_GETS LOADS FETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME

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

0 select * from tab01 2776366872 44868 110 1 1 1 ALL_ROWS USERA

1 select * from tab01 2776366872 44868 110 1 1 1 ALL_ROWS USERB

SQL> select child_number,t.AUTH_CHECK_MISMATCH,t.TRANSLATION_MISMATCH from v$sql_shared_cursor t where sql_id='5b42g2fkrrzss';

CHILD_NUMBER AU TR

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

0 N N

1 Y Y

v$sqlarea中记录父游标，统计所有包括子游标的数据(buffer_gets,loads,fetches,executions)，PARSING_SCHEMA_NAME记录最后一次解析的用户；

v$sql中记录所有子游标，游标号码从0开始递增，每个游标记录自身的统计信息，这里需要注意，对于非长事务而言，oracle在运行完成后更新统计信息；但对于长事务，oracle每5秒钟更新一次统计信息；

v$sql_shared_cursor 中记录为什么子游标没有使用共享池里存在的游标而重新解析原因；上面的例子导致硬解析和产生子游标的原因是授权检查(AUTH_CHECK_MISMATCH)和对象检查(TRANSLATION_MISMATCH)失败；

其它还有非常多的原因导致硬解析和产生子游标，接下来会讨论一些日常开发中容易导致的原因；

create table tparse(

x number primary key,

y varchar2(30)

);

begin

dbms_stats.set_table_stats

(

user,'tparse',

numrows=>10000000,

numblks=>100000

);

end;

begin

dbms_stats.set_index_stats

(

user,'SYS_C0013113',

numrows=>10000000

);

end;

这里创建了tparse表，然后虚拟设置了表和索引的统计信息;接着在pl/sql里用不同的优化器环境和不同的条件下执行SQL；

declare

l_num_x number;

l_var_x varchar2(30);

l_var_x1 varchar2(300);

begin

execute immediate 'alter session set optimizer_mode=all_rows';

for i in (select * from tparse where x>l_num_x)loop null; end loop;

for i in (select * from tparse where x>l_var_x)loop null; end loop;

execute immediate 'alter session set optimizer_mode=first_rows_10';

for i in (select * from tparse where x>l_num_x)loop null; end loop;

for i in (select * from tparse where x>l_var_x)loop null; end loop;

for i in (select * from tparse where x>l_var_x1)loop null; end loop;

end;

成功执行pl/sql后，检查v$sql表；

col SQL_TEXT for a50

select

sql_id,CHILD_NUMBER,hash_value,SQL_TEXT ,

buffer_gets LIOS,

disk_reads PIOS,

sorts,

cpu_time/1000 cpu_ms,

elapsed_time/1000 ela_ms

from v$sql where sql_text like 'SELECT %TPARSE WHERE X%' order by CHILD_NUMBER ;

SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOS SORTS CPU_MS ELA_MS

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

1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 26 3 0 2 1.733

1dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 1.998 1.331

1dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 2 1.673

1dmmz4yh0hrzx 3 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 2.999 3.286

1dmmz4yh0hrzx 4 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 1 .783

这里产生了5条记录，sql_id,hash_value都相同，但是它们有不同之处；

第一次解析，optimizer_mode值为all_rows；谓语条件的值类型与主键值类型相同，此时共享池里没有匹配的已经共享的游标，oracle硬解析并共享游标；

第二次解析，optimizer_mode值为all_rows，谓语条件的值为类型为varchar，与主键值类型不相同；优化器隐形转换值类型,然后对比第一次共享的游标时因为值变量类型不同，所以硬解析和产生新游标;

第三次解析，optimizer_mode值为first_rows；谓语条件的值类型与主键值类型相同，优化器在对比第一次共享的游标时发现环境不一致，所以硬解析和产生新游标;

第四次解析，optimizer_mode值为first_rows，谓语条件的值类型为varchar，与主键值类型不相同；优化器在对比第一次共享的游标时发现环境和变量类型均不一致，所以硬解析和产生新游标;

第五次解析，optimizer_mode值为first_rows，谓语条件的值类型为varchar，与主键值类型不相同；并且长度改变为300;优化器在对比第一次共享的游标时发现环境、变量类型和值长度均不一致，所以硬解析和产生新游标;

这些原因都可以在v$sql_shared_cursor视图中找到原因；

select t.ADDRESS,t.CHILD_ADDRESS,child_number,t.BIND_MISMATCH,t.OPTIMIZER_MODE_MISMATCH,t.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor t where sql_id='1dmmz4yh0hrzx';

ADDRESS CHILD_ADDRESS CHILD_NUMBER BI OP BI

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

0000000069AC2D28 0000000062F19D70 0 N N N

0000000069AC2D28 00000000696F7E48 1 Y N N

0000000069AC2D28 000000006A3E05A8 2 N Y N

0000000069AC2D28 000000006636C6D8 3 Y Y N

0000000069AC2D28 0000000065AE2338 4 Y Y Y

对于第一次解析，由于共享池中不存在已经解析的游标，oracle必须硬解析SQL，然后共享，所以v$sql_shared_cursor视图中的mismatch值为N;

当第二次解析时，由于共享池中已经存在解析的游标，但由于变量类型与主键类型不同，对比第一次解析时发生BIND_MISMATCH，oracle再次硬解析;

第三次解析时，由于绑定值与主键值类型相同，但优化器的设置不同，对比第一次解析时发生OPTIMIZER_MODE_MISMATCH,oracle再次硬解析;

第四次解析时，由于绑定值与主键值类型不同，并且优化器的设置也不同，对比第一次解析发生BIND_MISMATCH和OPTIMIZER_MODE_MISMATCH,oracle再次硬解析;

;

第五次解析时，由于绑定值与主键值类型不同，优化器的设置不同，并且绑定值长度较之前发生了变化，对比第一次解析时发生BIND_MISMATCH、OPTIMIZER_MODE_MISMATCH和BIND_LENGTH_UPGRADEABLE,oracle再次硬解析;

到现在我们了解了产生硬解析和子游标的原因，我们看看优化器在生成执行计划时的不同；　　首先看第一次的执行计划；

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1dmmz4yh0hrzx',0));

PLAN_TABLE_OUTPUT

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

SQL_ID 1dmmz4yh0hrzx, child number 0

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

SELECT * FROM TPARSE WHERE X>:B1

Plan hash value: 3289637765

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

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

| 0 | SELECT STATEMENT | | | | 13 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| TPARSE | 500K| 14M| 13 (24)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C0013113 | 90000 | | 4 (50)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("X">:B1)

优化器使用了索引，谓语条件没有任何转换；

第二次

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1dmmz4yh0hrzx',1));

PLAN_TABLE_OUTPUT

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

SQL_ID 1dmmz4yh0hrzx, child number 1

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

SELECT * FROM TPARSE WHERE X>:B1

Plan hash value: 3289637765

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

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

| 0 | SELECT STATEMENT | | | | 13 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| TPARSE | 500K| 14M| 13 (24)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C0013113 | 90000 | | 4 (50)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("X">TO_NUMBER(:B1))

优化器同样使用了索引，谓语条件中值类型发生隐形转换；

第三次解析

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1dmmz4yh0hrzx',2,'outline'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1dmmz4yh0hrzx, child number 2

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

SELECT * FROM TPARSE WHERE X>:B1

Plan hash value: 3289637765

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| TPARSE | 10 | 300 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C0013113 | 90000 | | 2 (0)| 00:00:01 |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

FIRST_ROWS(10)

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "TPARSE"@"SEL$1" ("TPARSE"."X"))

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

2 - access("X">:B1)

优化器设置改变了，评估的基数因优化器设置而变低。

如何避免

通过上面的例子可以看出，使用最频繁的情况(变量类型改变,变量长度改变,优化器设置改变等)均会导致重复的解析和新游标产生，但复杂且非常长的SQL在系统中是司空见惯的，如果才能避免或减少重复硬解析和资源的使用，又在一定程度上保护执行计划呢?

10g以前有outline，但使用受限；10g及以后有sql profile；让我们以第一次解析来创建SQL profile，看会发生什么；

SQL> @sqlprofile/create_sql_profile.sql '1dmmz4yh0hrzx' 0

Enter value for sql_id: 1dmmz4yh0hrzx

Enter value for child_no (0):

Enter value for profile_name (PROF_sqlid_planhash):

Enter value for category (DEFAULT):

Enter value for force_matching (FALSE):

SQL> alter system flush shared_pool;

创建好SQL profile后清空共享池，然后再重新运行上面的PL/SQL;再观察v$sql；

col SQL_TEXT for a50

select

sql_id,CHILD_NUMBER,hash_value,SQL_TEXT ,

buffer_gets LIOS,

disk_reads PIOS,

sorts,

cpu_time/1000 cpu_ms,

elapsed_time/1000 ela_ms

from v$sql where sql_text like 'SELECT %TPARSE WHERE X%' order by CHILD_NUMBER ;

SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOS SORTS CPU_MS ELA_MS

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

1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 1010 22 0 20.996 24.27

1dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 3 2.783

1dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 2 2.473

select t.ADDRESS,t.CHILD_ADDRESS,child_number,t.BIND_MISMATCH,t.OPTIMIZER_MODE_MISMATCH,t.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor t where sql_id='1dmmz4yh0hrzx';

ADDRESS CHILD_ADDRESS CHILD_NUMBER BI OP BI

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

0000000069AC2D28 0000000062F19D70 0 N N N

0000000069AC2D28 00000000696F7E48 1 Y N N

0000000069AC2D28 000000006A3E05A8 2 Y N Y

仅产生2个子游标，一次因为变量类型改变了，一次为变量类型和变量值长度改变了；优化器环境改变并没有影响到优化器；再继续查询优化器的行为；

SQL> @sql 2684903421

Show SQL text, child cursors and execution stats for SQL hash value 2684903421 child 0

HASH_VALUE CH# PLAN_HASH SQL_TEXT FIRST_LOAD_TIME LAST_LOAD_TIME SQL_PROFILE

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

2684903421 0 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765

2684903421 1 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765

2684903421 2 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765

3 rows selected.

CH# PARENT_HANDLE OBJECT_HANDLE PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED LIOS PIOS SORTS CPU_MS ELA_MS USERS_EXECUTING

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

0 0000000069AC2D28 0000000062F19D70 3 7 2 2 0 1010 22 0 20.996 24.27 0

1 0000000069AC2D28 00000000696F7E48 2 7 2 2 0 2 0 0 3 2.783 0

2 0000000069AC2D28 000000006A3E05A8 0 7 2 2 0 4 0 0 2 2.473 0

三个游标均使用了同样的SQL Profile，执行计划因SQL Profile而受到保护。

weixin_39682301

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle sql 子游标_Oracle SQL 硬解析和子游标

阅读导航Oracle SQL 硬解析和子游标硬解析和产生子游标的原因如何避免Oracle SQL 硬解析和子游标What reasons will be happening sql hard parse and generating new child cursors在一个繁忙的系统中，发现一个复杂且非常长的查询，产生40多个子游标和大量的硬解析，占用很多的内存、CPU资源；SQL> @sq...
复制链接

扫一扫