[20160201]db_link與子光标問題.txt

[20160201]db_link與子光标問題.txt

--生産系統遇到一個關于db_link産生大量子光标問題，當cursor_sharing=force的情況下，通過測試說明。

--注：這個問題我的測試僅僅存在10.2.0.4,11.2.0.4沒有這個問題。

1.環境：

SCOTT@test> @&r/ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> CREATE PUBLIC DATABASE LINK loopback USING '192.168.100.33:1521/test';

Database link created.

create or replace view v_emp as select * from emp@loopback;

create or replace view vv_emp as select * from v_emp;

create or replace view vvv_emp as select * from emp@loopback;

2.測試:

SCOTT@test> alter system flush shared_pool;

System altered.

SCOTT@test> alter session set cursor_sharing=force ;

Session altered.

--執行如下語句:

select * from emp@loopback where empno=1;

select * from emp@loopback where empno=2;

select * from emp@loopback where empno=3;

SCOTT@test> @ &r/dpc '' ''

PLAN_TABLE_OUTPUT

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

SQL_ID  4zzhmns2wsf4g, child number 2

select * from emp@loopback where empno=:"SYS_B_0"

NOTE: cannot fetch plan for SQL_ID: 4zzhmns2wsf4g, CHILD_NUMBER: 2

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.

SCOTT@test> select count(*) from v$sql where sql_id='4zzhmns2wsf4g';

  COUNT(*)

----------

         3

--當sql語句僅僅含有遠端表，無法通過dbms_xplan.display_cursor獲得執行計劃。

--可以發現産生了3個子光标。當大量相似的sql語句執行時(在cursor_sharing=force的情況下)會産生大量的子光标。

--換成如下語句依舊：

select * from v_emp where empno=1;

select * from v_emp where empno=2;

select * from v_emp where empno=3;

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

SQL_ID  ftmcqat5qjmzc, child number 2

select * from v_emp where empno=:"SYS_B_0"

NOTE: cannot fetch plan for SQL_ID: ftmcqat5qjmzc, CHILD_NUMBER: 2

SCOTT@test> select count(*) from v$sql where sql_id='ftmcqat5qjmzc';

3.如果改用綁定變量看看：

variable x number ;

exec :x :=1;

select * from v_emp where empno=:x;

exec :x :=2;

exec :x :=3;

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

SQL_ID  97pta6n0k7wtv, child number 0

select * from v_emp where empno=:x

NOTE: cannot fetch plan for SQL_ID: 97pta6n0k7wtv, CHILD_NUMBER: 0

SCOTT@test> select sql_text from v$sql where sql_id='97pta6n0k7wtv';

SQL_TEXT

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

--可以發現僅僅1個子光标。

4.如果在通路時加入本地表看看：

select a.* from (select * from v_emp where empno=1) a,dual;

select a.* from (select * from v_emp where empno=2) a,dual;

select a.* from (select * from v_emp where empno=3) a,dual;

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

SQL_ID  5puh2hnr82su0, child number 0

select a.* from (select * from v_emp where empno=:"SYS_B_0") a,dual

Plan hash value: 242706220

| Id  | Operation     | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|

|   1 |  NESTED LOOPS |      |      1 |    37 |     3   (0)| 00:00:01 |        |      |

|   2 |   FAST DUAL   |      |      1 |       |     2   (0)| 00:00:01 |        |      |

|   3 |   REMOTE      | EMP  |      1 |    37 |     1   (0)| 00:00:01 | LOOPB~ | R->S |

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

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

   1 - SEL$5C160134

   2 - SEL$5C160134 / DUAL@SEL$1

   3 - SEL$5C160134 / EMP@SEL$3

--可以發現child number 0，也沒有産生子光标。

--在生産系統由于産生大量子光标，導緻出現cursor: pin S wait on X等待事件，最終導緻出現ora-4031 錯誤，目前不得不重新開機資料庫。

Errors in file /u01/app/oracle/admin/mid/bdump/mid_smon_3832.trc:

ORA-04031: Message 4031 not found; No message file for product=RDBMS, facility=ORA; arguments: [4064] [shared pool] [lock table sys.mon_mods$ in ...] [sga heap(1,0)] [kglsim heap]

ORA-04031: Message 4031 not found; No message file for product=RDBMS, facility=ORA; arguments: [3896] [shared pool] [select ts#,file#,block#,cols...] [sga heap(1,0)] [kglsim object batch]