Oracle SQL 硬解析和子游标

阅读导航

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

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   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

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   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

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   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而受到保护。

Oracle SQL 硬解析和子游标