Re: 10053 trace for sql fired from pl/sql (stored code)
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: rjamya@xxxxxxxxx
- Date: Wed, 28 Dec 2005 16:22:09 -0500 (EST)
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 <rjamya@xxxxxxxxx> 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 <boris_dali@xxxxxxxx> 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 <rjamya@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
