Why DDL will not flush relative cursor out of shared pool in 11g
- From: du shenglin <shenglin.du@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Jul 2011 11:39:30 +0800
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@YING: SQL> alter system flush shared_pool;
System altered.
SYS@YING: SQL> create table sdu_test (id int);
Table created.
SYS@YING: SQL> select hash_value,invalidations from v$sql where sql_text
like 'select * from sdu_test where id=1';
no rows selected
SYS@YING: SQL> select * from sdu_test where id=1;
no rows selected
SYS@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@YING: SQL> drop table sdu_test; -- I did DDL here
Table dropped.
SYS@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@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@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
Other related posts:
