Recursive statements slowing down batch processing
From: <l.flatz_at_bluewin.ch>
Date: Tue, 11 Oct 2016 11:46:41 +0000 (GMT)
Message-ID: <2253616.22930.1476186401886.JavaMail.webmail_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." 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?
Date: Tue, 11 Oct 2016 11:46:41 +0000 (GMT)
Message-ID: <2253616.22930.1476186401886.JavaMail.webmail_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." 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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 11 2016 - 13:46:41 CEST