Re: Re: Recursive statements slowing down batch processing

Hi Stefan,

thanks for responding. We will definitly do 10051. Actually the packages specs do contain ref cusor types, but such ref cursor is not necessarily a parameter. Most of the time parameters are number and varchar only. I wonder if we got it to do with a "functor" (http://docs.roguewave.com/stingray/current/Stingray_Studio_HTML_User_Guide/index.html#page/Stingray%20Studio/otug-otatl.087.06.html). I agree, this flexibility (describe etc) is not needed. Not that I really git whatr a functor should be and when it should be used.

Keep you posted and see you at DOAG.

Regards

Lothar
----Ursprüngliche Nachricht----
Von : contact_at_soocs.de
Datum : 11/10/2016 - 14:40 (UTC)
An : oracle-l_at_freelists.org, l.flatz_at_bluewin.ch Betreff : Re: Recursive statements slowing down batch processing

Hey Lothar,
your client is right. This recursive SQL did not exist prior Oracle 12.1.

11.2.0.3.6

---
T11DB [oracle_at_OEL trace]$ grep 'type\$' /oracle/oratrace/T11DB/diag/rdbms/t11db/T11DB/trace/T11DB_ora_3867.trc

12.1.0.1
---
T12DB [oracle_at_OEL trace]$ grep 'type\$' /oracle/oratrace/T12DB/diag/rdbms/t12db/T12DB/trace/T12DB_ora_4177.trc
select count(*) from type$ where package_obj#=:1
select toid from type$ where package_obj#=:1 order by typ_name




> 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".


Can you enable an OPI trace (events '10051 trace name context forever, level 1') to verify the corresponding OPI calls? I would expect some describe
OPI calls (e.g. V8 Describe Any) then - it would be good to know for further analysis.




> The software vendor 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." 


I don't get his description completely. What is a "machine oriented select"? Do they issue OCI describes (e.g. OCIDescribeAny) in this case? And if
yes - why? The code seems to be known at compile time and no ref cursor in this package parameters.

Based on your OTN thread (https://community.oracle.com/thread/3976393) i would assume some implementation like described in MOS ID #112676.1, but this
does not make really sense based on your provided PL/SQL package call with just some (assumed ??) IN parameters. However pretty much of guessing here
without knowing something in detail about PL/SQL package and OCI/OPI :-)
 
Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
 


> "l.flatz_at_bluewin.ch" <l.flatz_at_bluewin.ch> hat am 11. Oktober 2016 um 13:46 geschrieben:

> 

>  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



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