Re: Performance debugging tips

On 1 Oct 2005 23:51:29 -0700, stacy_friedman_at_hotmail.com wrote:

>Hi everyone,
>
>I'm trying to debug an inconsistent performance problem with an app
>that runs against Oracle. The general gist is that it takes in many
>smallish records, does a bit of analysis on each, and returns a result
>based on some DB operations -- an insert, then some queries, for each
>record. Most records are handled on a <0.1 second timeframe, but every
>once in a while we see one jump into the 3+ second range.
>
>The inconsistent part is, of course, that *which* record takes >3
>seconds is always different. If we do multiple trials of the same 100K
>records, we get different "bad" ones each time. That tells me that
>it's probably *not* the data, which is the same each time, nor the
>application, but something in the DB design. I'm trying to debug this
>-- guessing that it could be some sort of internal hash collision or
>something, but where else should I be looking? I'm not 100% familiar
>with all of the DB-level tools that come with Oracle for this sort of
>debugging, so feel free to be verbose. :)
>
>Thanks in advance for any help,
>
>Stacy

Analysis should always start by answering the question 'What it is waiting for'. You would need to query v$waitstat as a start.

In your case I definitely would set event 10046 at level 12, so you can see *exactly* how your records are fetched. There are several methods to set event 10046, a crude one (on database level) is
alter system set event='10046 trace name context forever, level 12' this will result in trace files on the database server. The maximum dump file size is usually 5M You can disable it by
alter system set max_dump_filesize = unlimited

You can disable event 10046 by
alter system set event='10046 trace name context off'

Obviously you need to have alter system privilege to do this.

There are other methods to enable 10046 for a specific session, but they are version specific.
As you don't mention your version, and simply assume everyone is running your version, or Oracle never introduces any new functionality, I can't be more specific.

Finally: your assertion it is something in the DB design is very unlikely. Most likely you are fetching records one by one, instead of using the array interface, or you suffer from packet fragmentation in sql*net. The method above will establish whether you are using the array interface. If you aren't you will see fetch #<n> .... r=1
sqlnet wait
fetch #<n> ... r=1
etc.

Note also: after every fetch Oracle will issue consistent gets to make sure you get the correct data.

Hth

--
Sybrand Bakker, Senior Oracle DBA