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

I just read that hot topic about segment level checkpoint before truncate/drop. The Cross-DDL read-consistency only happen in special case, such as IOT with drop and the old query know the physical location(I didn't test that). However, i think that is not related to my topic. 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.

Thanks
Shenglin

On Tue, Jul 12, 2011 at 9:04 PM, Wang, Tao <twang_at_iso-ne.com> wrote:

> one possible reason might be the feature of " Cross-DDL read-consistency
> "… Jonathan Lewis talked about this a couple days ago…
>
>
>
>
> http://www.freelists.org/post/oracle-l/What-is-the-purpose-of-segment-level-checkpoint-before-DROPTRUNCATE-of-a-table,16
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *du shenglin
> *Sent:* Monday, July 11, 2011 11:40 PM
> *To:* Oracle L
> *Subject:* Why DDL will not flush relative cursor out of shared pool in
> 11g
>
>
>
> 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