Re: testing sql and perf

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 03 May 2004 10:55:35 -0600
Message-id: <40967987.B2745BA1@sun.com>

To quote the eminent Mr. Lewis "notes inline"

David Green wrote:
>
> > David,
> >
> > By output differences, do you mean time and stats related to
> > logical i/o and physical i/o? Or do you mean execution plans,
> > row counts, etc.
>
> Elapsed: 00:05:22.29 versus subsequent runs taking 9 and 10 seconds
> First Run
> Statistics
> ----------------------------------------------------------
> 1818 recursive calls

Here is one area to look at. Why 1818 recursive calls? A 10046 trace will tell you more. In subsequent runs it is 0. IMHO, worry less about flushing the buffer cache and worry more about these calls.

> 4 db block gets
> 718045 consistent gets
> 68575 physical reads

No suprise here. The physical reads are 'disk' access.

> 24 sorts (memory)

Not sure, but I would think that these sorts are related to the recursive calls and not the called sql.

> 0 sorts (disk)
> 16 rows processed

Good news, this does not change between executions!

> Unless I am mistaken the flush shared_pool only flushes the shared pool
> and not the buffer caches. The have Xylogic, so there is no storage cache
> in the equation to speak of. I did try flush shared pool and get a 9
> second execution time implying all data was in the buffer cache.

While you can also flush the shared pool with specific commands, I referenced the 10g command to flush the buffer cache.

> I can't take tablespace offline as other are accessing.
> How do developers perform the same without the ability to offline tbs and
> even flush caches, etc.

Well then don't worry about it. Your previous idea of shutting down/restarting would have an even worse impact.

A better focus would be on the row counts from each step of the operation. Work on reducing the logical i/os in the statement and the performance will improve.

To summarize (and get around the quoting restrictions),

1) Address the 1818 recursive calls on the first execution.
2) Address reduction of logical i/os.
3) Don't worry about the time differences in the executions at

this point as issue #1 is probably skewing the stats and you can't do anything about flushing the buffer cache in the current configuration anyway.

Daniel

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 03 2004 - 11:55:38 CDT