Re: Why DDL will not flush relative cursor out of shared pool in 11g
Date: Tue, 12 Jul 2011 09:45:32 -0700 (PDT)
Message-ID: <1310489132.97515.YahooMailClassic_at_web80601.mail.mud.yahoo.com>
> it may [be] 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;
> /
Indeed, my test on 10.2.0.1 and 11.1.0.1 both running on my laptop shows that the parse times are actually about the same. Interestingly, the parse time in the 10g case varies more than 11g (i.e. 11g has more consistent parse time in multiple runs).
> In 10g , all DDL will invalidate object related cursors in the shared
> pool, such as GRANT. In 11g, oracle doesn't want to do the same and
> still keep invalidated cursors in the shared_pool.
I think you mean both versions invalidate cursors but 10g frees their memory while 11g keeps them around.
> 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.
This overloaded procedure may be available in 11.2.0.2 for Linux only?
SQL> desc dbms_shared_pool
...
PROCEDURE PURGE
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SCHEMA VARCHAR2 IN OBJNAME VARCHAR2 IN NAMESPACE NUMBER IN HEAPS NUMBER IN
Unfortunately, it doesn't work as I thought, passing names of schema and object (such as table).
Yong Huang
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 12 2011 - 11:45:32 CDT