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@xxxxxxxxx
Datum : 11/10/2016 - 15:58 (UTC)
An : l.flatz@xxxxxxxxxx
Cc : oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx <l.flatz@xxxxxxxxxx>:
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."
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?
Can anybody positively confirm that Rock wave issues "describe" or simiar as
described above?
Thanks for your help
Lothar
