Re: Measure Perfomance of a Declared Cursor

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Mon, 4 Aug 2008 19:41:46 -0700 (PDT)
Message-ID: <9f32d52a-eb1b-4af1-b63e-1702daff0f4a@o40g2000prn.googlegroups.com>


On 8¿ù5ÀÏ, ¿ÀÀü7½Ã36ºÐ, webtourist <webtour..._at_gmail.com> wrote:
> <10gR2>
>
> Given a package containing code like:
>
> <code>
> FOR i IN some_declared_cursor ( 'foo')
> LOOP
> ..... whole bunch of processing
>
> </code>
>
> I need to measure the performance of JUST the cursor
> "some_declared_cursor"
> How to time it ?
> And what would be the best method ?
>
> thanks

I would just prefer 10046 event.(aka extended sql trace)

@trace_on 10046 1

begin
  for idx in (select level from dual connect by level <= 10000) loop     null;
  end loop;
end;
/

@trace_off
@trace_file
@tkprof trc.out

ed trc.out

begin
  for idx in (select level from dual connect by level <= 10000) loop     null;
  end loop;
end;

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------
Parse        1      0.10       0.10          0          0
0           0
Execute      1      0.00       0.00          0          0
0           1
Fetch        0      0.00       0.00          0          0
0           0

------- ------ -------- ---------- ---------- ---------- ----------
total        2      0.10       0.11          0          0
0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61


SELECT LEVEL
FROM
 DUAL CONNECT BY LEVEL <= 10000

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch      101      0.07       0.07          0          0
0       10000

------- ------ -------- ---------- ---------- ---------- ----------
total      103      0.07       0.07          0          0
0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
  10000  CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=42003 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=13 us)
Received on Mon Aug 04 2008 - 21:41:46 CDT

Original text of this message