Message-ID: <F001.0047167A.20020531120732@fatcity.com>
Date: Fri, 31 May 2002 12:07:32 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Mercadante, Thomas F" <NDATFM@labor.state.ny.us>
Subject: RE: Stubborn Table
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Kevin,

If you do not include a "where" clause, how do you expect to use an index?

Not gonna happen - sorry.

Tom Mercadante
Oracle Certified Professional


-----Original Message-----
Sent: Friday, May 31, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L


Well, it's time to call for the cavalry.

I have a table where the optimizer stubbornly insists on doing full table
scans for practically every operation in spite of the fact that full table
scans have gruesome performance.  Every hint I have tried has either been
ignored or doesn't help (and yes, I have used hints before and have
carefully checked my syntax).  The only way I have gotten the optimizer to
even use an index on one query was to jam the session settings
OPTIMIZER_INDEX_CACHING=100 plus OPTIMIZER_INDEX_COST_ADJ=1 which is not a
healthy way to do things.  I would appreciate some help in psychoanalyzing
the optimizer.

Oracle 8.1.7.3 on Solaris 2.8, all files on a single volume RAID-5 array (I
know, I know but I can't do anything about it at the moment).

MDMA_INPUT_FILE is a high transaction table used for data loading and
validation.  The table is badly denormalized due to decisions made long
before I started working here.  It has 15 indexes to support the validation
GUI (yes, I know, lots of indexes on a high transaction table is insane and
I have plans to deal with that in a month or two).  At the moment, the table
holds over 800K rows.  The table has been analyzed.

Here are a couple examples of loony optimizer behavior:

###############################################
SELECT Record_Type, Archive_Input_File
FROM MDMA_Input_File
GROUP BY Record_Type, Archive_Input_File

SELECT STATEMENT Hint=CHOOSE		162  	 	7500
  SORT GROUP BY		162  	6 K	7500
    TABLE ACCESS FULL	MDMA_INPUT_FILE	839 K	31 M	1882

There is a valid index where these two columns are the first of 5 columns.
I've tried most permutations of INDEX hints and they are all ignored.

Actually, this statement stemmed from working around a problem of reading
the full table ordered by the 5 index columns.  The optimizer chose to do a
full table scan plus sort (with resultant RAID-5 ugly performance).
Apparently, it feels the sort would be quicker than index access -- which
might be true on a non-IO bound system.

###############################################
UPDATE mdma_input_file mif
   SET partial_day_hold = :b1,
       ok_to_process = :b2,
       vee_usage_end = to_date(:b3)
 WHERE EXISTS (SELECT 1
               FROM st_vee_input_file
		  WHERE mif.rowid=mdma_rowid)

st_vee_input_file is a session temporary table with 96 rows.

UPDATE STATEMENT Hint=CHOOSE		41 K	 	1882	 
  UPDATE	MDMA_INPUT_FILE
    FILTER
      TABLE ACCESS FULL	MDMA_INPUT_FILE	41 K	409 K	1882

      TABLE ACCESS FULL	ST_VEE_INPUT_FILE	82  	574  	7

Unless I can figure this out, I foresee reading the temp table into an array
then doing the update in a forall loop.  Shouldn't have to do this much
coding to work around the optimizer.

Thanks for any help you can give (or sympathy if help is unavailable),
Kevin Kennedy
First Point Energy Corporation 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kkennedy
  INET: kkennedy@firstpoint.com

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@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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM@labor.state.ny.us

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@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).