RE: Measuring PLSQL-SQL context switches
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <hkchital@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Sep 2008 20:43:32 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hemant K Chitale
Sent: Thursday, September 11, 2008 11:15 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
