Kevin,
Fair enough... but ouch on that query!
Rachel
- kkennedy <kkennedy_at_firstpoint.com> wrote:
> Hi Rachel,
>
> Actually, the group by is because I only want one copy of each key.
> You see the original query:
> SELECT <<stuff>> FROM MDMA_INPUT_FILE
> WHERE <<unindexed_value>>=1
> ORDER BY record_type, archive_input_file, meter_identifier, units,
> data_time_stamp;
> did a full table scan and a gruesome sort taking 45 minutes or more.
>
> By selecting the first two keys in the aforementioned query and
> building an outer loop and doing
> SELECT <<stuff>> FROM MDMA_INPUT_FILE
> WHERE <<unindexed_value>>=1
> and record_type=outer_record_type
> and archive_input_file=outer_archive_input_file
> ORDER BY record_type, archive_input_file, meter_identifier, units,
> data_time_stamp;
> I was able to force use of the index, avoid writing the sort stuff
> out to a bandwidth limited device, and cut the run time better than
> in half.
>
> I'm likely to be punished in this life or the next but I do what I
> gotta do.
>
> Kevin
>
> -----Original Message-----
> Sent: Friday, May 31, 2002 1:07 PM
> To: Multiple recipients of list ORACLE-L
>
>
> your first statement
>
> SELECT Record_Type, Archive_Input_File
> FROM MDMA_Input_File
> GROUP BY Record_Type, Archive_Input_File
>
>
>
> why GROUP BY and not ORDER BY? I mean, what are you grouping?
>
> I *think*, vague recollections, of reading that group by will force a
> full table scan. would be interesting to see plan if you change group
> by to order by
>
>
> --- kkennedy <kkennedy_at_firstpoint.com> wrote:
> > 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_at_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_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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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_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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: kkennedy
> INET: kkennedy_at_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_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).
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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_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 Fri May 31 2002 - 22:18:18 CDT
