Re: Measuring PLSQL-SQL context switches

I have demonstrated once the price of the context switches to our developers
by giving the example with bulk collect , tracing the sessions and showing
the result of the trace file.
Example:
 •-- Without bulk collect
Declare
Cursor c is select my_id
 from my_table ;
M_id number;
BEGIN
 open c;
 Loop
  fetch c into m_id;
  exit when c%NOTFOUND;
End loop;

-- With bulk collect
Declare
Cursor c is select my_id
from my_table  ;
TYPE tabid IS TABLE OF number;
m_tabid tabid;
BEGIN
 OPEN c;
   FETCH c BULK COLLECT INTO m_tabid;
END;

Trace results clearly showed the overhead of context switches:

 •Without bulk collect
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          6          3           0
Execute      1      0.00       0.00          0         19          2           0
Fetch   379042      2.12      15.54          0          0          0      379041
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   379044      2.12      15.58          0         25          5      379041


With bulk collect
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          6          3           0
Execute      1      0.00       0.02          0         19          2           0
Fetch        1      0.43       7.63          0          0          0      379041
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.43       7.66          0         25          5      379041

Thank you

Michael

On Fri, Sep 12, 2008 at 3:43 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:

> 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Best Regards
Michael Elkin

Other related posts: