Re: Measuring PLSQL-SQL context switches

From: Michael Elkin <melkin4u_at_gmail.com>
Date: Fri, 12 Sep 2008 06:17:51 +0300
Message-ID: <b37755ee0809112017x76437cgdf47fd02ab47c7b7@mail.gmail.com>

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_at_rsiz.com> 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_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
>
>
>

-- 
Best Regards
Michael Elkin

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

Received on Thu Sep 11 2008 - 22:17:51 CDT