Monday, November 15, 2004
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]
FROM CUSTOMER_ACCOUNTS WHERE CUSTOMERID = :b1
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;
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
