Why DDL will not flush relative cursor out of shared pool in 11g

From: du shenglin <shenglin.du_at_gmail.com>
Date: Tue, 12 Jul 2011 11:39:30 +0800
Message-ID: <CAGpGboha_Y+GjAud=8eYTZ8TuaEWRi_ctf8L4STDBFwwneK-Sg_at_mail.gmail.com>

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Platform: SunOS qadb120 5.10 Generic_138888-05 sun4v sparc SUNW,SPARC-Enterprise-T5120

In 11g, after DDL even drop table, the sql can't be flushed out of shared_pool. Do you have idea why Oracle make that change in 11g? To reduce hardparse?

SYS_at_YING: SQL> alter system flush shared_pool; System altered.
SYS_at_YING: SQL> create table sdu_test (id int); Table created.
SYS_at_YING: SQL> select hash_value,invalidations from v$sql where sql_text like 'select * from sdu_test where id=1'; no rows selected
SYS_at_YING: SQL> select * from sdu_test where id=1; no rows selected
SYS_at_YING: SQL> select hash_value,SHARABLE_MEM,invalidations from v$sql where sql_text like 'select * from sdu_test where id=1'; HASH_VALUE SHARABLE_MEM INVALIDATIONS
---------- ------------ -------------

4263969598        15650             0
SYS_at_YING: SQL> drop table sdu_test;     -- I did DDL here

Table dropped.
SYS_at_YING: SQL> select hash_value,SHARABLE_MEM,invalidations from v$sql where sql_text like 'select * from sdu_test where id=1'; HASH_VALUE SHARABLE_MEM INVALIDATIONS
---------- ------------ -------------

4263969598        15650             1                                  --the

cursor is invalidated, however, it's still in shared pool

SYS_at_YING: SQL> select type,STATUS,INVALIDATIONS,CHILD_LATCH from V$DB_OBJECT_CACHE where name='select * from sdu_test where id=1';

TYPE                 STATUS                         INVALIDATIONS

CHILD_LATCH
-------------------- ------------------------------ -------------

---

CURSOR               INVALID_UNAUTH                             1
0
CURSOR               VALID                                      1

66366
SYS_at_YING: SQL>

We still need a hard parse for new cursor.

We can use dbms_shared_pool.purge to flush that cursor out, however, we can't flush all object related cursors in our version. (in Linux 11.2 version, dbms_shared_pool.purge has option to specify schema and object_name, however, it's not available in our version.

I checked this with Yong (http://yong321.freeshell.org/computer.html), he said it may used to reduce the hard parse time. However, using the following script, there is no obvious time saving.

 set timing on
declare c integer default dbms_sql.open_cursor; begin for i in 1..5000 loop
dbms_sql.parse(c, 'select * from testparsespeed', dbms_sql.native); execute immediate 'grant select on testparsespeed to dba'; end loop;
dbms_sql.close_cursor(c);
end;
/

I don't understand why Oracle design in this way. In some high activities ENV, the shared memory couldn't be released after DDL and once running out of shared pool, we may have more chances to see ORA-4031 or shared pool latch contension.

Any comments will welcome.

Thanks
Shenglin

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 11 2011 - 22:39:30 CDT