RE: Measuring PLSQL-SQL context switches

Sort of. You can use the DBMS_PROFILER package to see the context switching that goes on. It is very enlightening to see how often context switches take place, the bottom line is more often then you think. The problem with this information is it doesn't show you how long the context switches take. It just shows you "when" they happen but no timing information.

The package will show times for how long each LINE takes to run as well, this will likely be more useful then knowing how many context switches took place.

I did a presentation on this for the SEMOP (Michigan User group) a while back I don't know if it's still on the web site.

Here is a good place to start reading up on the package. It can be a little confusing at first to use, but over all its pretty simple.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_profil. htm#BJEFDBBC

I hope this helps.

• Ric Van Dyke Hotsos Enterprises, Ltd.

-----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 - 12:23:55 CDT