懂得Shared Pool 2

理解Shared Pool 2

Shared Pool的Sub Pool技术

由于硬解析时需要从Shared Pool重分配内存（分配内存是需要持有Shared Pool Latch）,所以在硬解析比较多的系统中，非常容易引起Shared Pool Latch的征用。为了减缓该Latch的征用,从Oracle 9i开始，共享池可分为多个子池(Sub Pool)来管理（最多7个）,下一节将主要讲解Shared Pool的Sub Pool技术.

Sub Pool数量受以下几个因素的影响:

系统CPU的数量.默认情况下,在Oracle中每4个CPU分配一个Sub Pool,最多不能超过7个.
共享池的大小.Sub Pool的最小容量随着Oracle版本的不同而不同
- 9i 128M
- 10g<10.2.0.3 256M
- 10.2.0.3 and higher 512M
隐含参数_kghdsidx_count值.当数据库启动时,Oracle优先根据_kghdsidx_count隐含参数设置Sub Pool数量.通过Dump Heap可以观察Sub Pool的数量,以下为Oracle 9i的四个Sub Pool

Oracle的思路是通过多CPU的特性来将一个很大的共享池划分为若干个对等的功能区,每个独立的区间都有相同的管理机制,可以并发访问,从而解决性能发面的瓶颈.

在具有子池的情况下,Oracle在共享池中分配空间的算法也会发生一些改变.比如在没有共享池的情况下,如果想在共享池中分配空间,必须先持有Shared Pool Latch，如果无法获得Latch,就需要等待.而在有子池的情况下,算法发生了改变.如果某个系统的共享池有6个子池,那么申请共享池閂锁可以从0号子池开始,一直到5号子池,每个子池都有一个共享池子闩锁.在申请0~4号共享池子闩锁的时候,采用不需要等待模式,一旦申请失败,不需要等待,直接返回;而申请5号子池的共享池子闩锁的时候,就需要采用等待模式,直到获得这个闩锁为止.

从Oracle 10g开始,每个Sub Pool由四个Sub Partition组成,如下所示：

cat PROD_ora_20486.trc|grep "sga heap"
HEAP DUMP heap name="sga heap" desc=0x200010b4
HEAP DUMP heap name="sga heap(1,0)" desc=0x2002b1cc
HEAP DUMP heap name="sga heap(1,1)" desc=0x2002be04
HEAP DUMP heap name="sga heap(1,2)" desc=0x2002ca3c
HEAP DUMP heap name="sga heap(1,3)" desc=0x2002d674
HEAP DUMP heap name="sga heap(2,0)" desc=0x200306f4
HEAP DUMP heap name="sga heap(2,1)" desc=0x2003132c
HEAP DUMP heap name="sga heap(2,2)" desc=0x20031f64
HEAP DUMP heap name="sga heap(2,3)" desc=0x20032b9c

Sub Partition的出现跟Shared Pool Duration的特性有关,其特性有隐含参数_enable_shared_pool_durations决定,默认为TRUE,即启用Shared Pool Duration特性.

当_enable_shared_pool_durations被设置为FALSE时,Sub Partition在Sub Pool中消失.在Oracle 10g,如果设置SGA_TARGET为0,或者在Oracle 10.2.0.5之前的版本中把cursor_space_for_time设置为True时,——enable_shared_pool_duration自动被设置为False

至于这个Shared Pool Duration特性到底是啥？我没有百度出来,或者说百度出来的都是copy的,或者一带而过的.如果哪位直到，还请告诉我，谢谢

我们可以通过想X$KSMSS视图查看Sub Pool的内存分配情况

查看各个子池分配的内存

sys@PROD>select 'shared pool('||nvl(decode(to_char(ksmdsidx),'0','0-Unused',ksmdsidx),'Total')||'):'subpool, sum(ksmsslen) bytes,round(sum(ksmsslen)/1048576,2) mb from x$ksmss where ksmsslen > 0 group by rollup (ksmdsidx) order by subpool asc;

SUBPOOL                                                     BYTES         MB
------------------------------------------------------ ---------- ----------
shared pool(1):                                         138412332        132
shared pool(2):                                         121639208        116
shared pool(Total):                                     260051540        248

查看各个子池剩余内存

sys@PROD>select subpool,name,sum(bytes),round(sum(bytes)/1048576,2) mb from (select 'shared pool(' || decode(to_char(ksmdsidx), '0','0-Unused',ksmdsidx) || '):' subpool,ksmssnam name,ksmsslen bytes from x$ksmss where ksmsslen>0 and lower(ksmssnam) like lower('%free memory%')) group by subpool,name order by subpool asc,sum(bytes) desc;

SUBPOOL                                                NAME                       SUM(BYTES)         MB
------------------------------------------------------ -------------------------- ---------- ----------
shared pool(0-Unused):                                 free memory                  79691776         76
shared pool(1):                                        free memory                  10876920      10.37
shared pool(2):                                        free memory                  13165708      12.56

通过X$KGHLU查看各SUB POOL发生ORA-04031的情况

column indx heading "indx|indx num"

column kghlurcr heading "RECURRENT|CHUNK"

column kghlutrn heading "TRANSIENT|CHUNK"

column kghlufsh heading "FLUSHED|CHUNK"

column kghluops heading "PINS AND|RELEASES"

column kghlunfu heading "ORA-4031|ERRORS"

column kghlunfs heading "LAST ERROR|SIZE"

select indx,kghlurcr ,kghlutrn ,kghlufsh, kghluops, kghlunfu, kghlunfs from x$kghlu where inst_id=userenv('Instance');

      indx RECURRENT TRANSIENT    FLUSHED   PINS AND   ORA-4031 LAST ERROR
indx num      CHUNK      CHUNK      CHUNK   RELEASES     ERRORS       SIZE
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0        975       5907     582888    1402121       1043       3200
         1       2756      20749     572392    1180944          1       4064

Library Cache内部结构

Shared Pool的内存由KGH(Kernel Generic Heap)管理,Library Cache则由KGL(Kernel Generic Library Cache)管理,KGL利用KGH分配得到必要的内存Chunk.Library Cache的内存结构如图所示

SQL经过Hash函数生成一个Hash值，然后根据这个Hash值去检索Library Cache是否存在相同的SQL,检索过程中需要持有Latch Cache Latch.如果不存在相同的SQL则将SQL文本分配到适当的Bucket上.具有相同的Hash值的SQL以Handle为单位,以Chain的形式挂载到同一个Bucket上.一个Handle管理一个Library Cache Object.Handle对实际的Library Cache Object起到指针的作用.Library Cache Object保存着SQL的实际信息.Bucket主要由Library Cache Latch保护；Handle由Library Cache Lock保护;Library Cache Object有Library Cache Pin保护.Library Cache Latch的数量默认与大于CPU数量的最小质数相同

另外,Library Cache Latch的数量受隐含参数_kgl_latch_count影响,Library Cache Bucket受隐含参数_kgl_bucket_count影响.一个Library Cache Latch管理者多个Library Cache Bucket.如果在申请Library Cache Latch过程中发生争用.则会发生Latch:Library Cache等待事件

Library Cache Object保存着Cursor具体信息,LCO的集体信息如下:

LCO包含众多信息,最关键是以下3个信息:

Dependency Table：保存当前LCO以来的其他LCO信息,比如SQL语句所以来的表、视图.
Child Table:保存当前LCO的子LCO信息.通俗点说，就是一条SQL 至少有一个和子游标.可能一些SQL由于某些原因无法共享子游标(比如不同用户拥有相同名字的表T,他们都执行select * from T.)，这样就会出现一个父游标和多个子游标的情况.即Version Count 很高。那么这种情况下.父游标里对应的所有子游标的指针都会保存在child table里面.父游标
Data Blocks:保存着SQL语句、执行计划、执行文本等信息.

Child Table存放指向子游标的指针,存在不同Version的SQL往往是由于不同用户执行了相同的SQL

最简单的理解就是：父游标的名字就是SQL TEXT而相同的SQL TEXT可能会有不同执行计划,于是产生了子游标

比如SCOTT用户和TOM用户都执行SELECT * FROM T,两个用户都有T表,但表的内容和结构完全不同,他们共用一个父游标.

Version Count过高(子游标过多)造成的后果就是定位子游标时间延长,持有Library Cache Latch的时间会增长,增加Library Cache Latch的争用.Oracle的BUG也会导致SQL的版本数异常增高.我们可以使用一下脚本定位SQL版本高的原因：

 SQL> SET PAGES 0

     SQL> SET HEADING OFF;

     SQL> SELECT 'UNBOUND_CURSOR:                 '||SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))),

                 'SQL_TYPE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))),

                 'OPTIMIZER_MISMATCH:             '||SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))),

                 'OUTLINE_MISMATCH:               '||SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))),

                 'STATS_ROW_MISMATCH:             '||SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))),

                 'LITERAL_MISMATCH:               '||SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))),

                 'FORCE_HARD_PARSE:               '||SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))),

                 'EXPLAIN_PLAN_CURSOR:            '||SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))),

                 'BUFFERED_DML_MISMATCH:          '||SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))),

                 'PDML_ENV_MISMATCH:              '||SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))),

                 'INST_DRTLD_MISMATCH:            '||SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))),

                 'SLAVE_QC_MISMATCH:              '||SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))),

                 'TYPECHECK_MISMATCH:             '||SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))),

                 'AUTH_CHECK_MISMATCH:            '||SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))),

                 'BIND_MISMATCH:                  '||SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))),

                 'DESCRIBE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))),

                 'LANGUAGE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))),

                 'TRANSLATION_MISMATCH:           '||SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))),

                 'BIND_EQUIV_FAILURE:             '||SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))),

                 'INSUFF_PRIVS:                   '||SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))),

                 'INSUFF_PRIVS_REM:               '||SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))),

                 'REMOTE_TRANS_MISMATCH:          '||SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))),

                 'LOGMINER_SESSION_MISMATCH:      '||SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ,

                 'INCOMP_LTRL_MISMATCH:           '||SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))),

                 'OVERLAP_TIME_MISMATCH:          '||SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))),

                 'EDITION_MISMATCH:               '||SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))),

                 'MV_QUERY_GEN_MISMATCH:          '||SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))),

                 'USER_BIND_PEEK_MISMATCH:        '||SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))),

                 'TYPCHK_DEP_MISMATCH:            '||SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))),

                 'NO_TRIGGER_MISMATCH:            '||SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))),

                 'FLASHBACK_CURSOR:               '||SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))),

                 'ANYDATA_TRANSFORMATION:         '||SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))),

                 -- NOTE: Next column only for 11.2.0.1

                 --       Please refer to http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3059.htm#REFRN30254

                 'INCOMPLETE_CURSOR:             '||SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))),

                 -- NOTE: Next column only for 11.2.0.2 and above

                 --       Please refer to http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3059.htm#REFRN30254

                 'PDDL_ENV_MISMATCH:              '||SUM(TO_NUMBER(DECODE(pddl_env_mismatch,'Y',1,'N','0'))),

                 'TOP_LEVEL_RPI_CURSOR:           '||SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))),

                 'DIFFERENT_LONG_LENGTH:          '||SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))),

                 'LOGICAL_STANDBY_APPLY:          '||SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))),

                 'DIFF_CALL_DURN:                 '||SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))),

                 'BIND_UACS_DIFF:                 '||SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))),

                 'PLSQL_CMP_SWITCHS_DIFF:         '||SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))),

                 'CURSOR_PARTS_MISMATCH:          '||SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))),

                 'STB_OBJECT_MISMATCH:            '||SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))),

                 'CROSSEDITION_TRIGGER_MISMATCH : '||SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))),

                 'PQ_SLAVE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))),

                 'TOP_LEVEL_DDL_MISMATCH:         '||SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))),

                 'MULTI_PX_MISMATCH:              '||SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))),

                 'BIND_PEEKED_PQ_MISMATCH:        '||SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))),

                 'MV_REWRITE_MISMATCH:            '||SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))),

                 'ROLL_INVALID_MISMATCH:          '||SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))),

                 'OPTIMIZER_MODE_MISMATCH:        '||SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))),

                 'PX_MISMATCH:                    '||SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))),

                 'MV_STALEOBJ_MISMATCH:           '||SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))),

                 'FLASHBACK_TABLE_MISMATCH:       '||SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))),

                 'LITREP_COMP_MISMATCH:           '||SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))),

                 'PLSQL_DEBUG:                    '||SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))),

                 'LOAD_OPTIMIZER_STATS:           '||SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))),

                 'ACL_MISMATCH:                   '||SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))),

                 'FLASHBACK_ARCHIVE_MISMATCH:     '||SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))),

                 'LOCK_USER_SCHEMA_FAILED:        '||SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))),

                 'REMOTE_MAPPING_MISMATCH:        '||SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))),

                 'LOAD_RUNTIME_HEAP_FAILED:       '||SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))),

                 'HASH_MATCH_FAILED:              '||SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))),

                 'PURGED_CURSOR:                  '||SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))),

                 'BIND_LENGTH_UPGRADEABLE:        '||SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0')))

          FROM   v$sql_shared_cursor

          WHERE  address IN (SELECT address

                             FROM   v$sqlarea

                             WHERE  sql_id = '<Value from Step 1>');

Example output:

        UNBOUND_CURSOR:                 0
        SQL_TYPE_MISMATCH:              0
        OPTIMIZER_MISMATCH:             0
        OUTLINE_MISMATCH:               0
        STATS_ROW_MISMATCH:             0
        LITERAL_MISMATCH:               0
        FORCE_HARD_PARSE:               0
        EXPLAIN_PLAN_CURSOR:            0
        BUFFERED_DML_MISMATCH:          0
        PDML_ENV_MISMATCH:              0
        INST_DRTLD_MISMATCH:            0
        SLAVE_QC_MISMATCH:              0
        TYPECHECK_MISMATCH:             0
        AUTH_CHECK_MISMATCH:            0
        BIND_MISMATCH:                  0
        DESCRIBE_MISMATCH:              0
        LANGUAGE_MISMATCH:              0
        TRANSLATION_MISMATCH:           0
        BIND_EQUIV_FAILURE:             0
        INSUFF_PRIVS:                   0
        INSUFF_PRIVS_REM:               0
        REMOTE_TRANS_MISMATCH:          0
        LOGMINER_SESSION_MISMATCH:      0
        INCOMP_LTRL_MISMATCH:           0
        OVERLAP_TIME_MISMATCH:          0
        EDITION_MISMATCH:               0
        MV_QUERY_GEN_MISMATCH:          0
        USER_BIND_PEEK_MISMATCH:        0
        TYPCHK_DEP_MISMATCH:            0
        NO_TRIGGER_MISMATCH:            0
        FLASHBACK_CURSOR:               0
        ANYDATA_TRANSFORMATION:         0
        INCOMPLETE_CURSOR:              0
        TOP_LEVEL_RPI_CURSOR:           0
        DIFFERENT_LONG_LENGTH:          0
        LOGICAL_STANDBY_APPLY:          0
        DIFF_CALL_DURN:                 0
        BIND_UACS_DIFF:                 0
        PLSQL_CMP_SWITCHS_DIFF:         0
        CURSOR_PARTS_MISMATCH:          0
        STB_OBJECT_MISMATCH:            0
        CROSSEDITION_TRIGGER_MISMATCH : 0
        PQ_SLAVE_MISMATCH:              0
        TOP_LEVEL_DDL_MISMATCH:         476
        MULTI_PX_MISMATCH:              0
        BIND_PEEKED_PQ_MISMATCH:        0
        MV_REWRITE_MISMATCH:            0
        ROLL_INVALID_MISMATCH:          0
        OPTIMIZER_MODE_MISMATCH:        0
        PX_MISMATCH:                    0
        MV_STALEOBJ_MISMATCH:           0
        FLASHBACK_TABLE_MISMATCH:       0
        LITREP_COMP_MISMATCH:           0
        PLSQL_DEBUG:                    0
        LOAD_OPTIMIZER_STATS:           0
        ACL_MISMATCH:                   0
        FLASHBACK_ARCHIVE_MISMATCH:     0
        LOCK_USER_SCHEMA_FAILED:        0
        REMOTE_MAPPING_MISMATCH:        0
        LOAD_RUNTIME_HEAP_FAILED:       0
        HASH_MATCH_FAILED:              0
        PURGED_CURSOR:                  0
        BIND_LENGTH_UPGRADEABLE:        0

This shows that the cursor is not shared because of TOP_LEVEL_DDL_MISMATCH which means this concerns a top DDL-statement and which would be expected behavior.

Conclusions and Summary of bugs due to reason code:

Look for the reason code you have identified by the above queries, check the affected version and use below table to identify what defects are potential to hit:

Reason Code	Bug	Description	Confirmed Affected Versions	Fixed	Support Article
BIND_MISMATCH	Bug:5705795	MANY CHILD CURSORS CREATED FOR HIGH WORKLOAD IN 10.2.0.3	10.2.0.3	10.2.0.4 11.1.0.7	Note:416727.1
PQ_SLAVE_MISMATCH	Bug:6981690	Cursor not shared when running PX query on mounted RAC system	10.2.0.3 10.2.0.4 11.1.0.7	10.2.0.4.4 (PSU) 10.2.0.5 11.1.0.7.1 (PSU) 11.2.0.1	Note:760777.1
AUTH_CHECK_MISMATCH and LANGUAGE_MISMATCH	Bug:7648406	CHILD CURSOR IS NOT SHARED IF SET NLS_LENGTH_SEMANTICS=CHAR.	10.2.0.3 10.2.0.4 11.1.0.7	10.2.0.5 11.1.0.7.4 (PSU) 11.2.0.1	Note:783120.1
USER_BIND_PEEK_MISMATCH	Bug:8981059	High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking	10.2.0.4 11.1.0.7 11.2.0.1	11.2.0.1.2 (PSU) 11.2.0.2 12.1	Note:968930.1
AUTH_CHECK_MISMATCH and INSUFF_PRIVS_REM	Unpublished Bug:8922013	ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434]	10.2.0.4 10.2.0.5		Note:973149.1
PX_MISMATCH	Unpublished Bug:9226905	STPA - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KKFDPAPRM]	11.1.0.7 11.2.0.1	11.2.0.2	Note:1340558.1
BIND_MISMATCH	Bug:9689310	SPORADIC BUNCHES OF ORA-600 [17059]	10.2.0.4 10.2.0.5 11.1.0.7 11.2.0.1	11.1.0.7.7 (PSU) 11.2.0.2 12.1	Note:9689310.8
INST_DRTLD_MISMATCH	Bug:10151017	MERGE STATEMENT NOT BEING SHARED UNDER SPECIFIC CIRCUMSTANCES	11.1.0.7 11.2.0.1 11.2.0.2	11.2.0.2.1 (PSU) 11.2.0.3 12.1	Note:1365227.1
AUTH_CHECK_MISMATCH	Bug:12320556	HIGH VERSION COUNTS OCCUR DUE TO AUTH_CHECK_MISMATCH, INSUFF_PRIVS_REM=Y	11.1.0.7 11.2.0.2	12.1	Note:12320556.8

Data Blocks包含SQL语句、执行计划、执行文本等信息,Oracle通过地址指向存储这些信息的Heap和Chunk.Heap 6存储着SQL的执行计划.从Oracle 10.2.0.4开始,可以用dbms_shared_pool.purge包清理出处在Heap 6中的执行计划.

dbms_shared_pool.purge用法：

首先我们在SCOTT和TOM用户都创建T表

scott@PROD>desc t
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OBJ_ID NUMBER
OBJ_NAME VARCHAR2(128)
OWNER VARCHAR2(29)

tom@PROD>desc t
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NAME VARCHAR2(10)
ID NUMBER

两个用户都执行查询'select * from t’

sys@PROD>select address,hash_value,executions,parse_calls,child_number,child_address from v$sql where sql_text like 'select * from t';

ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS CHILD_NUMBER CHILD_AD
-------- ---------- ---------- ----------- ------------ --------
4E251A00 1134051363 2 2 0 4E251924
4E251A00 1134051363 2 2 1 4E1033B0

使用dbms_shared_pool.purge包前先开启event 5614566

SQL> alter session set events '5614566 trace name context forever';

sys@PROD>exec dbms_shared_pool.purge('4E251A00,1134051363','C');

PL/SQL procedure successfully completed.

sys@PROD>select address,hash_value,executions,parse_calls,child_number,child_address from v$sql where sql_text like 'select * from t';

no rows selected

我们还可以对语句中的对象做个ddl操作也会把该执行计划踢出共享池.由于DDL语句,ORACLE认为该执行计划已废弃

未完

Example output:

Conclusions and Summary of bugs due to reason code:

相关推荐