----- Original Message -----
>
> have you tried to disable hash joins explicitly via
> hash_join_enabled = false ?
No. I want to have hash joins. They are
darn useful for some of the SQL. But they
are contemptuous when they become too familiar. <g>
>
> Could you forward Anjo's explanation?
Anjo suggested the optimizer is basing most of its
calculations on number of logical reads, rather
than physical. This may be due to the particular
combination of table sizes and SQL in Peoplesoft.
When I changed my block size to half, I doubled
all the counts of logical disk reads.
These don't necessarily translate into double the
physical read operations, because of DB_FILE_MULTIBLOCK_READS.
And if the threshold that triggers HASH join all over
the place was overtaken, then I should see a blow out
of them. Which is consistent, since all of my middle
size tables suddenly were turned into hash joins
whenever used in a multi-table join! The really large
ones went into extremes and became MERGE JOIN CARTESIAN,
but that one I still have to rationalize. Bug?
He has also indicated he's seen all these probs
disappear in 9i, where the optimizer favours CPU +
real I/O for its calculations rather than logical
disk reads.
Makes absolute sense to me. I knew that my logical
disk reads had increased for the same SQL, from looking
at what was in V$SQLAREA. Even now when things are
long back to normal, I still see much increased logical
reads compared to the other instances where I still
have 16K and same data.
Live and learn...
Cheers
Nuno Souto
[EMAIL PROTECTED]
http://www.users.bigpond.net.au/the_Den
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nuno Souto
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
