RE: Measuring PLSQL-SQL context switches

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 11 Sep 2008 20:43:32 -0400
Message-ID: <4A4BBEACE54C41D28E0A02BCB98E4765@rsiz.com>


Others have referenced previous discussions and where the metric shows up. I'm addressing when to care...

If you are executing a very expensive sql, it is not a big deal.

If you are executing a very cheap sql, then the context switch might well be a significant proportion of the elapsed time and cost.

To assess the worst case overhead, grab one of those superfast versions of select * from dual, measure the time of that query alone, slap it in place of a PL/SQL block that loops on it the same number of times as in your real PL/SQL program.

Subtract the number of iterations times the cost of select * from dual from that execution, and that is the cost of the context switching.

(Okay, I'm leaving out the time to increment a loop counter, which is only a big number in femtoseconds).

mwf
-----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 - 19:43:32 CDT

Original text of this message