[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;

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

PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- 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

      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)

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

  COUNT(*) ----------          3

3.如果改用绑定变量看看：

variable x number ;

exec :x :=1; select * from v_emp where empno=:x; exec :x :=2; select * from v_emp where empno=:x; exec :x :=3; select * from v_emp where empno=:x;

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

PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- SQL_ID  97pta6n0k7wtv, child number 0

select * from v_emp where empno=:x

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

      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)

SCOTT@test> select sql_text from v$sql where sql_id='97pta6n0k7wtv'; SQL_TEXT ------------------------------------------------------------ select * from v_emp where empno=:x

--可以发现仅仅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;

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

PLAN_TABLE_OUTPUT ------------------------------------- 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]