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