dbms_shared_pool.purge工作原理猜测

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:dbms_shared_pool.purge工作原理猜测

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

思考为什么dbms_shared_pool.purge清理掉某条sql在shared pool中的信息,为什么当该sql再次执行的时候FIRST_LOAD_TIME时间没有发生改变
测试purge某条sql,再次加重该sql,FIRST_LOAD_TIME不变

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 16:44:00

SQL>  select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';     

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/16:43:59

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

ADDRESS  HASH_VALUE
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
2587FFAC  515825595
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual


SQL>  exec dbms_shared_pool.purge('2587FFAC,515825595','C');

PL/SQL procedure successfully completed.

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

no rows selected

SQL> !date
Tue Feb 12 16:55:15 CST 2013

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 16:55:23

SQL> select FIRST_LOAD_TIME FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

FIRST_LOAD_TIME
--------------------------------------
2013-02-12/16:43:59

这里可以看出来第一次执行sql语句的时候,FIRST_LOAD_TIME为2013-02-12/16:43:59,然后我使用dbms_shared_pool.purge”清除掉”了SQL语句在shared pool中的信息,但是当我再次执行执行相同的sql时候,查询发现FIRST_LOAD_TIME时间未发生改变.因为v$sql中对应的只有一张基表x$kglcursor_child,并没有where条件,而让记录不在v$sql中显示,证明是x$基表的东西发生了改变,而该基表是直接来自内存,从而个人猜测,oracle的dbms_shared_pool.purge是在shared pool该sql的内存某些部位增加了某些标记,从而使得该sql不能在v$sql等相关视图中显示,如果sql以前占用的内存区域没有被老化出shared pool,下次该sql再次访问的时候,优先启用该内存区域并修改相关值,从而出现了我们的FIRST_LOAD_TIME不改变的现象.

验证猜测

--session 1
SQL> exec dbms_shared_pool.purge('2587FFAC,515825595','C');

PL/SQL procedure successfully completed.

SQL> select FIRST_LOAD_TIME FROM V$SQLAREA where sql_id='46zkt5sgbxrxv';

no rows selected

SQL> declare
  2  begin 
  3  FOR a IN  1..10000000  
  4  LOOP  
  5  EXECUTE IMMEDIATE 'insert into t_xifenfei values ('||a||')';
  6  END LOOP;
  7  commit;
  8  end;  
  9  /

--session 2
SQL> select count(sql_text) from v$sql where sql_text like 'insert into t_xifenfei%'
  2  ;

COUNT(SQL_TEXT)
---------------
            444

SQL> /

COUNT(SQL_TEXT)
---------------
            445

SQL> /

COUNT(SQL_TEXT)
---------------
            444

SQL> /

COUNT(SQL_TEXT)
---------------
            442
--动态sql还在执行,但是共享池中的该sql不再增加,说明共享池已经满,
--部分历史的sql语句已经被刷新出共享池purge的sql语句肯定被老化出来了shared pool,然后再次执行该sql语句

--session 3
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 17:09:08

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/17:09:07

这里可以看到当shared pool发生部分数据被刷出来之时,而且根据先进先出的原则,我们可以知道开始被purge的sql语句肯定被老化出shared pool,从而当再次执行相同sql的时候,生成了新的FIRST_LOAD_TIME,从而验证了部分猜测.
在此也补充另外一个朋友的咨询问题:在什么情况下FIRST_LOAD_TIME会发生改变,我认为是当sql语句占用的内存区域被老化出去,然后再进入内存的时候会发生改变,flush shared_pool实现效果和老化出来一样

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 17:09:08

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/17:09:07

SQL> alter system flush shared_pool;

System altered.

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

no rows selected

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2013-02-12 18:52:33

SQL> select SQL_ID,FIRST_LOAD_TIME from v$sql where sql_text like 'select to_char(sysdate,%dual';

SQL_ID        FIRST_LOAD_TIME
------------- --------------------------------------
46zkt5sgbxrxv 2013-02-12/18:52:33

因为shared pool的东西很复杂,我这里也只是大概的初步猜测,没有深入到系统级别dump之类的方法分析,如果有兴趣的朋友可以深入研究并探讨.

此条目发表在 Oracle 分类目录,贴了 标签。将固定链接加入收藏夹。

dbms_shared_pool.purge工作原理猜测》有 1 条评论

  1. 惜分飞 说:

    GV$SQL视图对应基表sql语句

    /* Formatted on 2013/5/3 12:56:59 (QP5 v5.185.11230.41888) */
    SELECT inst_id,
           kglnaobj,
           kglfnobj,
           kglobt03,
             kglobhs0
           + kglobhs1
           + kglobhs2
           + kglobhs3
           + kglobhs4
           + kglobhs5
           + kglobhs6
           + kglobt16,
           kglobt08 + kglobt11,
           kglobt10,
           kglobt01,
           DECODE (kglobhs6, 0, 0, 1),
           DECODE (kglhdlmd, 0, 0, 1),
           kglhdlkc,
           kglobt04,
           kglobt05,
           kglobt48,
           kglobt35,
           kglobpc6,
           kglhdldc,
           SUBSTR (TO_CHAR (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
           kglhdivc,
           kglobt12,
           kglobt13,
           kglobwdw,
           kglobt14,
           kglobwap,
           kglobwcc,
           kglobwcl,
           kglobwui,
           kglobt42,
           kglobt43,
           kglobt15,
           kglobt02,
           DECODE (kglobt32,
                   0, 'NONE',
                   1, 'ALL_ROWS',
                   2, 'FIRST_ROWS',
                   3, 'RULE',
                   4, 'CHOOSE',
                   'UNKNOWN'),
           kglobtn0,
           kglobcce,
           kglobcceh,
           kglobt17,
           kglobt18,
           kglobts4,
           kglhdkmk,
           kglhdpar,
           kglobtp0,
           kglnahsh,
           kglobt46,
           kglobt30,
           kglobt09,
           kglobts5,
           kglobt48,
           kglobts0,
           kglobt19,
           kglobts1,
           kglobt20,
           kglobt21,
           kglobts2,
           kglobt06,
           kglobt07,
           DECODE (kglobt28, 0, TO_NUMBER (NULL), kglobt28),
           kglhdadr,
           kglobt29,
           DECODE (BITAND (kglobt00, 64), 64, 'Y', 'N'),
           DECODE (kglobsta,
                   1, 'VALID',
                   2, 'VALID_AUTH_ERROR',
                   3, 'VALID_COMPILE_ERROR',4,
                   'VALID_UNAUTH', 5,
                   'INVALID_UNAUTH', 6,
                   'INVALID'),
           kglobt31,
           SUBSTR (TO_CHAR (kglobtt0, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
           DECODE (kglobt33, 1, 'Y', 'N'),
           DECODE (BITAND (kglobacs, 1), 1, 'Y', 'N'),
           DECODE (BITAND (kglobacs, 2), 2, 'Y', 'N'),
           DECODE (BITAND (kglobacs, 4), 4, 'Y', 'N'),
           kglhdclt,
           kglobts3,
           kglobts7,
           kglobts6,
           kglobt44,
           kglobt45,
           kglobt47,
           kglobt49,
           kglobcla,
           kglobcbca,
           kglobt22,
           kglobt52,
           kglobt53,
           kglobt54,
           kglobt55,
           kglobt56,
           kglobt57,
           kglobt58,
           kglobt23,
           kglobt24,
           kglobt59,
           kglobt53 - ( (kglobt55 + kglobt57) - kglobt52)
      FROM x$kglcursor_child