RE: Measuring PLSQL-SQL context switches

Hemant,

I'm pretty sure the statistic you're after is 'recursive calls'. Each time PL/SQL does a context switch to SQL, it's counted as a recursive call.

This was discussed, I thought, on the OTN Database forum, but I just spent more than 30 minutes looking for the discussion, with no luck....

-Mark

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak_at_proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale Sent: Thursday, September 11, 2008 11:15 AM To: oracle-l_at_freelists.org
Subject: Measuring PLSQL-SQL context switches

Is there a method to measure or estimate the impact of context switches when executing SQL inside a PLSQL block -- particularly when the PLSQL block runs the same SQL statement very many times inside a loop ?

I can't seem to find a relevent statistic when I look at the list of statistics in the 10.2 Reference.

I know I could use timers to time the difference in execution time if I were to rewrite the PLSQL block but I would like to be able to measure and/or estimate the impact on execution time and/or CPU time given an existing piece of code .

Hemant K Chitale

http://hemantoracledba.blogspot.com

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 11 2008 - 11:34:24 CDT