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

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: shenglin.du@xxxxxxxxx
  • Date: Tue, 12 Jul 2011 09:45:32 -0700 (PDT)

> 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 and 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 for Linux only?

SQL> desc dbms_shared_pool
 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

Other related posts: