Re: 10053 trace for sql fired from pl/sql (stored code)

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: 2005-12-28 22:22:09
Message-id: 20051228212209.9636.qmail@web32815.mail.mud.yahoo.com

Raj,

Look like you are right. I toyed with this a little bit, and it seems that keeping a package doesn't guarantee for the embedded sql (much less its plan) to stay around. On the other hand, after keeping this specific cursor it survived my "pushing garbage" procedure.

Here's the code. Very rough, but worked for me more or less (you might need to adjust it if you decide to run it). Number of distinct "garbage" statements I had to push to the single granule shared pool on my Oracle 9206 on Linux was about 200

/********* Setup:
alter system set shared_pool_size=1m scope=memory /* rounds up to 4m */
;

select * from v$sgastat where pool='shared pool' and name='free memory';

drop table boris.t1;
create table boris.t1 (i int);
exec dbms_stats.gather_table_stats( 'boris', 't1') **********/
set echo off
create or replace package boris.pck_test1 as

	procedure prc_insert;
	procedure prc_garbage ( p_iterations number);

end pck_test1;
/

create or replace package body boris.pck_test1 as

	procedure prc_insert is 
	begin 
		execute immediate 'insert into boris.t1 select /*
my_precious_sql */ * from boris.t1';
	end prc_insert;

	procedure prc_garbage (p_iterations number) is
	begin
		for i in 1..p_iterations loop
			execute immediate 'insert into boris.t1 select /*
garbage */ ' || i || ' from boris.t1 where rownum=1';
		end loop;
	end prc_garbage;

end pck_test1;
/                 

alter session set "_close_cached_open_cursors"=true; set echo on ver off
exec boris.pck_test1.prc_insert
-- assuming a single child cursor only:

col hash_value 	new_value my_precious_shv
col address 	new_value my_precious_address

select hash_value, address from v$sql where sql_text like '%my_precious_sql%' and sql_text not like 'select hash_value%';
select count(1) from v$sql_plan where
address='&my_precious_address' and
hash_value='&my_precious_shv';
select count(1) from v$sql_plan_statistics where address='&my_precious_address' and
hash_value='&my_precious_shv';

exec pck_test1.prc_garbage(300)
select hash_value, address from v$sql where sql_text like '%my_precious_sql%' and sql_text not like 'select hash_value%';
select count(1) from v$sql_plan where
address='&my_precious_address' and
hash_value='&my_precious_shv';
select count(1) from v$sql_plan_statistics where address='&my_precious_address' and
hash_value='&my_precious_shv';

set echo off

--> Ensure precious cursor and/or plan are kicked out

• exec dbms_shared_pool.keep( 'boris.pck_test1')
• select * from v$db_object_cache where kept='YES' and name='PCK_TEST1' and owner='BORIS';
• ... and repeat
  --> Still kicked out, keeping a package didn't help
  
• exec dbms_shared_pool.keep( '555FC28C, 2922649476', 'c')
  --> Keeping specific cursor makes it stay
  
  • rjamya wrote:

> No sure, that's why I specifically mentioned keeping
> the cursor instead of
> the whole package. Keeping the package makes the
> code be 'kept'. However it
> is my understanding that cursors invoked from within
> the package don't
> inherit the 'keep' part.
> 
> Raj
> 
> On 12/28/05, Boris Dali  wrote:
> >
> > Raj,
> >
> > Right. But keeping a whole package would probably
> > achieve the same thing, would it not? It is the
> second
> > part I am not sure about - the plan - would it be
> kept
> > (using either option)? After all, this keeping
> thing
> > is for shared pool space management, not for plan
> > keeping, but would be nice it had this side effect
> > (even with a price of setting of
> > cursor_space_for_time)
> >
> > Thanks,
> > Boris Dali.
> >
> > --- rjamya  wrote:
> >
> > > Not tried this, but Boris can you use
> > > dbms_shared_pool.keep to "keep" the
> > > cursor in the shared_pool and probably that
> would
> > > cause the execution plan
> > > to also remain?? Not keeping the package, but
> just
> > > the cursor in question
> > > ... i.e. flag => 'C'
> > >
> > > Just a theory though
> > > Raj



	

	
		
__________________________________________________________ 

Find your next car at http://autos.yahoo.ca

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 28 2005 - 22:22:09 CST