Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: Nuno Souto <nsouto_at_nsw.bigpond.net.au>
Date: Thu, 10 May 2001 05:11:14 -0700
Message-ID: <F001.002FF157.20010510050037@fatcity.com>

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
nsouto_at_nsw.bigpond.net.au
http://www.users.bigpond.net.au/the_Den

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nuno Souto
  INET: nsouto_at_nsw.bigpond.net.au

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: ListGuru_at_fatcity.com (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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US