Sorry
hit the return too quickly, Resuming
and
select a.aid, a.chnum, f
from rollup a
where a.effdt=(select max(b.effdt) from rollup b where
a.chnum=b.chnum)
Admittedly they are differ by the aggregate function
(sum). The one with the sum was killed by the user, but
statspack captured the following
EXECUTIONS DISK_READS
ROWS_PROCESSED SORTS PARSE_CALLS
BUFFER_GETS
-------------------- ------------------------- ---------------------------------- ---------- ------------------------ ----------------------- 1 2922 0 -7833 1 9262161 for the first query 1 1498 60 130 1 11450 for the second. It is obvious that the first query is more
expensive than the second. However the first query has a
better BHR than the second. So it should run faster :) I trap
v$session_waits for active sessions every 5 seconds. Admittedly not as
good as a trace, but it does give me some idea of what was going on when the
query was run.
I trapped 48 wait events for the first
query and only one for the second. The one wait trapped for the second
query was not significant, and nearly all the waits trapped for the second query
were not significant either, except perhaps in their number. I did
see a few "interesting" 'direct path read waits'
interspersed with 'db file sequential read' waits
indicating I/0 contention.
The query plans for the two statements were identical
save the extra group by for the
summation.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
In conclusion, If I just looked at the BHR for the
first state and were of the opinion. "the higher the better", there should
be no need to proceed further; .999684622 is pretty darn
good.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I don't pay daily attention to the BHR. Most of
my time is spent ferreting out bad SQL. So every morning I
look for statements involved with significant waits, look for high disk
reads, buffer gets, and check to make sure the full table scans are
legitimate. If there are problems less expensive transitive queries
are written or the developer is instructed to "materialize" a complex join
as part of the process. Drop table followed by CTAS can solve
innumerable problems.
Ian MacGregor
Stanford Linear Accelerator
Center
[MacGregor, Ian A.] -----Original Message----- From: MacGregor, Ian A. Sent: Thursday, August 08, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: missed Anjo's webcast..
|
Title: RE: missed Anjo's webcast..
- RE: missed Anjo's webcast.. Gogala, Mladen
- RE: missed Anjo's webcast.. Rajesh . Rao
- RE: missed Anjo's webcast.. Post, Ethan
- RE: missed Anjo's webcast.. MacGregor, Ian A.
- RE: missed Anjo's webcast.. Cary Millsap
- RE: missed Anjo's webcast.. Jesse, Rich
- Re: missed Anjo's webcast.. Mladen Gogala
- RE: missed Anjo's webcast.. MacGregor, Ian A.
- RE: missed Anjo's webcast.. Jared . Still
- RE: missed Anjo's webcast.. Freeman, Robert
- RE: missed Anjo's webcast.. Cary Millsap
- RE: missed Anjo's webcast.. Connor McDonald
- Re: missed Anjo's webcast.. Anjo Kolk
- Re: missed Anjo's webcast.. Anjo Kolk
- Re:RE: missed Anjo's webcast.. dgoulet
- RE: missed Anjo's webcast.. Fink, Dan
- RE: missed Anjo's webcast.. Cary Millsap
- RE: missed Anjo's webcast.. Karniotis, Stephen