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: Long-running SQL

RE: Long-running SQL

From: <Cherie_Machler_at_gelco.com>
Date: Wed, 27 Mar 2002 09:41:06 -0800
Message-ID: <F001.00434FFC.20020327094106@fatcity.com>

Dennis,

When you partitioned the table, how did you determine what the limits on the partition should be?
In this case, there are not always values for the other columns of the key so I'm not sure if we could
partition on the full key.

Cherie

                                                                                                                    
                    DENNIS WILLIAMS                                                                                 
                    <DWILLIAMS_at_LIFE       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    TOUCH.COM>            cc:                                                                       
                    Sent by:              Subject:     RE: Long-running SQL                                         
                    root_at_fatcity.co                                                                                 
                    m                                                                                               
                                                                                                                    
                                                                                                                    
                    03/27/02 11:15                                                                                  
                    AM                                                                                              
                    Please respond                                                                                  
                    to ORACLE-L                                                                                     
                                                                                                                    
                                                                                                                    




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: 
  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).
Received on Wed Mar 27 2002 - 11:41:06 CST

Original text of this message

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