Re: Re: Recursive statements slowing down batch processing

I had some luck with SQL Profiles - similar like described https://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/ or http://oracle-randolf.blogspot.co.at/2009/03/plan-stability-in-10g-using-existing.html but never tried if the hint result_cache is accepted by this method.

hth
 Martin

2016-10-11 16:20 GMT+02:00 l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>:

> Hi Martin,
>
> how would you do that? The hint won´t work if you do something like
> sql_patch. Hm, sql translate would, probably.
>
> Regards
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : martin.a.berger_at_gmail.com
> Datum : 11/10/2016 - 15:58 (UTC)
> An : l.flatz_at_bluewin.ch
> Cc : oracle-l_at_freelists.org
> Betreff : Re: Recursive statements slowing down batch processing
>
>
> Hi Lothar,
>
> have you tried to enable result cache for
> sqlid='9zstv91ddy78q' and sqlid='7avz6006nq0yj'?
>
> This might not solve the issue, but mitigate your customers pain.
>
>
> hth
> Martin
>
> 2016-10-11 13:46 GMT+02:00 l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>:
>
>> Hi,
>>
>> one of my customers has an issue with rcursive statements executed
>> thousends of times. The customer is on version 12.1.0.2.
>> They claim they did not have this issue with version 11.
>> We did a trace to investiage the issue.
>>
>> There are actually 2 statements:
>> sqlid='9zstv91ddy78q'
>> select count(*) from type$ where package_obj#=:1;
>>
>> sqlid='7avz6006nq0yj'
>>
>> select toid from type$ where package_obj#=:1 order by typ_name;
>>
>> called in that sequence. Actually the statements execution is slow,
>> because no suitable index exists. (There is index I_TYPE6 , but
>> package_obj# is in the third position).
>>
>> The next statement in the trace is a call to packacke via an anionymous
>> block:
>>
>> Something like:
>>
>> PARSING IN CURSOR #47355804067344 len=97 dep=0 uid=53 oct=47 lid=53
>> tim=1475831489511995 hv=1410455941 ad='65b7e1c70' sqlid='aa1wwcxa13qc5'
>> BEGIN package_name.procedure( param1=> :ph10, param2=> :ph11, param3=>
>> :ph12 ); END;
>> END OF STMT
>>
>> Actaully the package is matching the bind vairable from the recursive
>> statements.
>>
>> The packages are probably called from an external C program. When I am
>> trying to simulate the call from PL/SQL I do not produce the recursive
>> statements. The only thing the works is calling "describe package".
>>
>> The software ven"dor explains it like that:"the “describes” statements found
>> are linked to some Machine oriented selects run by Rock Wave every time
>> that a procedure signature is called."
>>
>>
>> 1. Since I have no version 11 available I ask you for some help..can
>> anybody run "describe dbms_output" in version 11 followed by "select
>> sum(executions) from v$sql where sql_id='7avz6006nq0yj';" to find out if
>> the recursive statements exist in version 11? Can you let me know the
>> execution plan in order to see if a better index existed in 11g?
>> 2. Can anybody positively confirm that Rock wave issues "describe" or
>> simiar as described above?
>>
>>
>>
>> Thanks for your help
>>
>>
>>
>> Lothar
>>
>>
>>
>
>

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