Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer and block size changes = trouble...

Re: Optimizer and block size changes = trouble...

From: Nuno Souto <>
Date: Thu, 10 May 2001 05:11:14 -0700
Message-ID: <>

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...
Nuno Souto

Please see the official ORACLE-L FAQ:
Author: Nuno Souto

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: (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).
Received on Thu May 10 2001 - 07:11:14 CDT

Original text of this message