RE: Long-running SQL

Cherie,

It is using the index. Range scans are not "smart" so to speak. In Harrison's sql tuning book, he states that a range scan is not intelligent. Like this: The index probe will start down the b-tree looking for account number. It will then continue the search for what it is looking for (applying the conditions to the org level fields) by scanning one way or the other in the b-tree. Sometimes it works fine and sometimes it doesn't.

I am sorry I don't have any sure-fire fix suggestions. Sounds like Dennis and whoever that clever person was that suggested defaulting the values may be the best solutions to try. Unfortunately they involve more changes than an easy fix (add an index, change the index, etc.)

Just an offhanded thought: Have you tried an index just on account number? Or how about a partitioned index, on account number? That may be an easier quick-try fix than partitioning the table. I'm not saying it won't be worthwhile to partition the table, but partitioning an index doesn't involve changing the table.

Good luck Cherie.

Lisa Koivu
Oracle Database Baby Oven
Fairfield Resorts, Inc.
954-935-4117

> -----Original Message-----
> From: DENNIS WILLIAMS [SMTP:DWILLIAMS_at_LIFETOUCH.COM]
> Sent: Wednesday, March 27, 2002 12:15 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Long-running SQL
>
> Cherie - This means that on the average the query is retrieving 366
> values?
> I had something similar on our data warehouse. Indexed retrievals and full
> table scans took about the same amount of time. I ended up partitioning
> the
> table on that key and the queries flew. Interestingly enough, the weekly
> loads were speeded up by a magnitude.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Wednesday, March 27, 2002 10:40 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Lisa,
>
> I'm not positive that it is actually using the index. This load ran last
> night so I was not able to trace as it happened.
> Following is the explain plan that Precise monitoring tool states that it
> should have used:
>
> Count (stopkey)
> Table access (by index rowid) EDM_DBO.EXP_COST_CENTER_DIM
> Index (range scan) EDM_DBO.EXP_COST_CENTER_DIM_IDX1
>
> If I have to, I will get up tonight and do a trace.
>
> There are 998 unique values for account number column out of about 365,000
> rows.
>
> This statement has always been bad and caused problems for the load.
> Since
> I could never recommend
> an improvement for it, I just looked at other things. However, the
> nightly loads have been running outside
> the window now with an increase in amount of data loaded and things are
> now
> at a crisis point.
>
> Thanks,
>
> Cherie
>
>
>
>
>
> "Koivu, Lisa"
>
> <lisa.koivu_at_efair To:
> "'ORACLE-L_at_fatcity.com'"
> <ORACLE-L_at_fatcity.com>,
> field.com> "'cherie_machler_at_gelco.com'"
> <cherie_machler_at_gelco.com>
> cc:
>
> 03/27/02 10:16 AM Subject: RE: Long-running
> SQL
>
>
>
>
>
>
>
>
> Hi Cherie,
> do you know for sure it is using the index? Have you traced it? Can you
> post the explain plan? What's the cardinality of account_number in this
> table?
>
> I can just hear those people over there screaming at you saying it's your
> problem...
>
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 954-935-4117
>
>
> > -----Original Message-----
> > From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> > Sent: Wednesday, March 27, 2002 9:33 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Long-running SQL
> >
> >
> > We have a statement that I feel takes too long to run in a nightly data
> > load.
> > The table it runs against has 386,000 records. It runs for about 10
> > seconds
> > on average. We're only loading about 50,000 records a night but this
> > statement
> > is running during the majority of the 9-hour load time. This is
> causing
> > the
> > load to run longer than our allowable window and causing me untold
> > headaches.
> > If anyone has any suggestions to make this run faster, I'd be greatly
> > appreciative.
> >
> > The columns in the where statement are all part of an index. However,
> > the functions on the columns add additional execution time and
> complexity.
> >
> > This is an 8.0.4 database so I can not make this a function-based index.
> >
> > I put this in a couple of SQL tuning tools and came up with no valid
> > alternatives.
> > I can't help thinking that the statement could be rewritten into a
> couple
> > of statements
> > so that it would be more efficient. However, I'm not skilled enough
> with
> > SQL to
> > do it. Perhaps someone else is. Here's the code.
> >
> > SELECT /*+ INDEX(EXP_COST_CENTER_DIM EXP_COST_CENTER_DIM_IDX1) + */
> > EXP_COST_CENTER_KEY
> > FROM EXP_COST_CENTER_DIM
> >
> > WHERE ACCOUNT_NUMBER = :b1 AND
> > NVL(ORG_LEVEL_1_VALUE,'NONE') = NVL(:b2,'NONE') AND
> > NVL(ORG_LEVEL_2_VALUE,'NONE') = NVL(:b3,'NONE') AND
> > NVL(ORG_LEVEL_3_VALUE,'NONE') = NVL(:b4,'NONE') AND
> > NVL(ORG_LEVEL_4_VALUE,'NONE') = NVL(:b5,'NONE') AND
> > NVL(ORG_LEVEL_5_VALUE,'NONE') = NVL(:b6,'NONE') AND
> > NVL(ORG_LEVEL_6_VALUE,'NONE') = NVL(:b7,'NONE') AND
> > NVL(ORG_LEVEL_7_VALUE,'NONE') = NVL(:b8,'NONE') AND
> > NVL(ORG_LEVEL_8_VALUE,'NONE') = NVL(:b9,'NONE') AND
> > ROWNUM = 1
> >
> >
> > SQL> desc exp_cost_center_dim
> > Name Null? Type
> > ------------------------------- -------- ----
> > EXP_COST_CENTER_KEY NOT NULL NUMBER(7)
> > ACCOUNT_NUMBER NOT NULL NUMBER(9)
> > BATCH_WINDOW_DATE_KEY NOT NULL NUMBER(5)
> > ORG_LEVEL_1_VALUE VARCHAR2(20)
> > ORG_LEVEL_2_VALUE VARCHAR2(20)
> > ORG_LEVEL_3_VALUE VARCHAR2(20)
> > ORG_LEVEL_4_VALUE VARCHAR2(20)
> > ORG_LEVEL_5_VALUE VARCHAR2(20)
> > ORG_LEVEL_6_VALUE VARCHAR2(20)
> > ORG_LEVEL_7_VALUE VARCHAR2(20)
> > ORG_LEVEL_8_VALUE VARCHAR2(20)
> > DATA_SOURCE_MOD_DATETIME NOT NULL DATE
> > DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE
> > DATA_MART_MOD_DATETIME NOT NULL DATE
> >
> >
> > SQL> select column_name from dba_ind_columns where index_name
> > ='EXP_COST_CENTER_D
> > IM_IDX1';
> >
> > COLUMN_NAME
> >
> --------------------------------------------------------------------------
> > ------
> > ACCOUNT_NUMBER
> > ORG_LEVEL_1_VALUE
> > ORG_LEVEL_2_VALUE
> > ORG_LEVEL_3_VALUE
> > ORG_LEVEL_4_VALUE
> > ORG_LEVEL_5_VALUE
> > ORG_LEVEL_6_VALUE
> > ORG_LEVEL_7_VALUE
> > ORG_LEVEL_8_VALUE
> >
> >
> > SQL> select column_name from dba_ind_columns where index_name
> > ='EXP_COST_CENTER_D
> > IM_PK';
> >
> > COLUMN_NAME
> >
> --------------------------------------------------------------------------
> > ------
> > EXP_COST_CENTER_KEY
> >
> >
> >
> >
> > Thanks,
> >
> > Cherie Machler
> > Oracle DBA
> > Gelco Information Network
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Cherie_Machler_at_gelco.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:
> INET: Cherie_Machler_at_gelco.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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.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).