Niall's Oracle Pages – Oracle Opinion since 2004

Archive for November 15th, 2004

without comments

Sometimes it can be easy for proponents of time based tuning (and I am one) to focus on the time breakdown, rather than the business action that is taking too long. Mark Rittman has a concrete example of this here.

Rewriting Marks results as a resource profile would give you something like this:

SELECT [list of customer address columns]

PX Deq: Signal ACK 89.70
PX Deq Credit: send blkd 16.52
CPU Time 6.11
PX Deq: Execute Reply 2.05
enqueue 1.40
PX Deq: Join ACK 1.16
PX qref latch 1.00
PX Deq: Parse Reply 0.98
PX Deq: Table Q Normal 0.08
Unaccounted For -2.09

Total 116.91

Now faced with such a resource profile, it would be very easy to conclude either;

  • that not much could be done because the 2 prime consumers of response time are so-called idle events related to the fact that the operation is being done in parallel, or
  • to take the ,slightly better, view that there is pretty much no such thing as an idle event and take a look at how appropriate parallel query is in this case.
  • However Mark took the better approach – that of asking what the business was trying to achieve – at which point it becomes apparent that in fact the query would be better off not being executed at all. The problem was in fact being caused in this case by attempting to do too much procedural processing in the application when a single sql statement could be used to replace multiple statements from the client. The story is available in full at Mark’s site but anyone who sees code like the pseudo code following being executed against their database might find a dialog with the app developers useful before diving into tuning the individual statements.

    select [primary key values] from master where [some condition]

    for each id in [list obtained above]
    select [something] from detail where primary_id = id
    select s[something else from sub_detail where [some join to detail table]
    select [something else] from detail2 where primary id = id

    end loop

    Possibly Related Posts:

    • No Related Posts found

    Written by Niall Litchfield

    November 15th, 2004 at 10:54 am

    Posted in Uncategorized