Re: Measure Perfomance of a Declared Cursor

On Aug 4, 10:41 pm, Dion Cho <ukja.d..._at_gmail.com> wrote:
> 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)- Hide quoted text -
>
> - Show quoted text -

Dion offered a good solution but if running the code is an issue then another approach would be to copy the cursor out of the code and set up a routine to just run the cursor with timing statements added to the test routine.

HTH -- Mark D Powell -- Received on Tue Aug 05 2008 - 08:15:49 CDT