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

Home -> Community -> Mailing Lists -> Oracle-L -> 10046 / wait events question

10046 / wait events question

From: Darrell Landrum <darrell_at_landrum.com>
Date: Sat, 7 Aug 2004 14:26:03 -0500
Message-ID: <020e01c47cb4$61cbf310$6401a8c0@otherrealm>


Am I missing something?  

I'm troubleshooting a decline in performance of a query (which I can fix with hints, but this is a app generated query, so I can't change it, more on the hint in a bit) and am finding an odd lack of some expected information in the trace file.

When running the referenced query <below>, with 10046 level12, the trace output has no wait events on the largest table in the query.

The explain plan, which is not immune from fibbing, shows it to be hitting the sales_trans_date_idx to find rows in the sales table. This is what I would want it to do. However, the raw trace shows waits on scattered and sequential reads of blocks from files for the other 3 tables (and/or related indexes), as well as direct writes/reads on temp files (sorting), but not the largest one, sales (nor sales indexes).

The sales table is range partitioned by month on the trans_date column and there are 1 to 7 million records for each partition for the past 3 years. This query is only interested in dates from the latest partition.

When executing it as is, it takes an average of 15 minutes. When executing it with an index hint for sales_trans_date_idx, it takes less than 2 minutes, which was normal until about a week ago.  

On a seperate, but possibly related note, a select count(trans_date) from sales chooses to scan the sa_unq_idx1, but it seems to me it should want to use the sales_trans_date_idx on that column (indexes / table information is below).  

I've analyzed all tables involved in the query (using dbms_stats), and experimented with varying options of dbms_stats on the sales table. I've tried it with estimate 5%, 30%, full compute, all indexed columns level 1, all columns level 1, all columns level 10, all columns level 75, and for column trans_date level 150.  

All that being said, my primary goal right now isn't necessarily the performance. I'm more interested in figuring out this 10046 trace output because if it isn't accurate OR if I'm not reading it right, I'm not exactly sure what the query is doing wrong.

The trace file and the trace analyzer output are too big to post here, so if interested.  

Trace file:

http://webpages.charter.net/dands1300/oracle/dwrp_ora_14958_DWL_TEST.trc  

Trace analyzer output:

http://webpages.charter.net/dands1300/oracle/TRCANLZR_dwrp_ora_14958_DWL_TEST.LOG  

Trace exec output:

http://webpages.charter.net/dands1300/oracle/TRCAEXEC_1_16_0.TXT    

Any thoughts?

Thanks,

Darrell      

The query:

SELECT   ORG_FLAT_INV.DIVISION,   ORG_FLAT_INV.DIVISION_NAME,   ORG_FLAT_INV.CHAIN,   ORG_FLAT_INV.CHAIN_NAME,   ORG_FLAT_INV.AREA,   ORG_FLAT_INV.AREA_NAME,   ORG_FLAT_INV.REGION,   ORG_FLAT_INV.REGION_NAME,   ORG_FLAT_INV.STORE_NUM,   SKU.DEPT,   SUM(0) TOTAL_SALES, SUM(0) TOTAL_MKDN,   SUM(0) CERT_SALES, SUM(0) CERT_MKDN,   SUM(0) TAG_SALES,SUM(0) TAG_MKDN,   sum(SALES.RETAIL_NET_SALE) PRICE_BREAK_SALES,

  sum(SALES.RETAIL_MD_AMT+SALES.RETAIL_MD_AMT2) PRICE_BREAK_MKDN

FROM   ORG_FLAT_INV,   SKU,   SALES,   ITEM_GROUP WHERE
( SALES.SKU=SKU.SKU )
  AND ( ORG_FLAT_INV.STORE_NUM=SALES.STORE )   AND ( ITEM_GROUP.SKU=SKU.SKU )   AND (
( ITEM_GROUP.ITEM_GROUP = 'PRICE_BREAK' AND SALES.TRANS_DATE BETWEEN ITEM_GROUP.BEGIN_DATE AND ITEM_GROUP.END_DATE )
  AND SKU.DEPT != 19   AND ITEM_GROUP.DIVISION = 30   AND ORG_FLAT_INV.DIVISION = 30   AND ( SALES.TRANS_DATE BETWEEN last_day(add_months(trunc(sysdate-1),-1)) + 1 and trunc (SYSDATE - 1) )

  )

GROUP BY   ORG_FLAT_INV.DIVISION,   ORG_FLAT_INV.DIVISION_NAME,   ORG_FLAT_INV.CHAIN,   ORG_FLAT_INV.CHAIN_NAME,   ORG_FLAT_INV.AREA,   ORG_FLAT_INV.AREA_NAME,   ORG_FLAT_INV.REGION,   ORG_FLAT_INV.REGION_NAME,   ORG_FLAT_INV.STORE_NUM,   SKU.DEPT;   The sales table indexes:

INDEX_NAME                                          COLUMN_NAME

----------------------------------------                      ----------------------------------------

SALES_JEWELRY_CLUB_IDX1                 JEWELRY_CLUB_NUMBER

SALES_POST_DATE_IDX                          POST_DATE

SALES_SKU_IDX                                      SKU

SALES_STORE_IDX                                  STORE

SALES_TRANS_DATE_IDX                        TRANS_DATE

SA_UNQ_IDX1                                          TRANS_DATE

                                                               STORE

                                                               TRANS_NUMBER

                                                               SEQ_NUMBER

                                                               TENDER_CODE

                                                               PAYMENT_PLAN_CODE

 

 

The sales table desc:

TRANS_DATE                                                        NOT NULL DATE

 STORE                                                                  NOT NULL NUMBER(4)

 TRANS_NUMBER                                                      NOT NULL NUMBER(10)

 SEQ_NUMBER                                                        NOT NULL NUMBER(3)

 SKU                                                                       NOT NULL NUMBER(8)

 POS_TYPE                                                                   NUMBER(2)

 BUS_TIME                                                                   VARCHAR2(8)

 POST_DATE                                                                  DATE

 SALES_EMP1                                                                 NUMBER(9)

 SALES_EMP2                                                                 NUMBER(9)

 RETAIL_UNITS                                                               NUMBER(10,3)

 RETAIL_NET_SALE                                                            NUMBER(9,2)

 RETAIL_TOTAL_SALE                                                          NUMBER(9,2)

 RETAIL_MD_CODE                                                             NUMBER(2)

 RETAIL_MD_AMT                                                              NUMBER(9,2)

 RETAIL_MD_CODE2                                                            NUMBER(2)

 RETAIL_MD_AMT2                                                             NUMBER(9,2)

 COST_NET_SALE                                                              NUMBER(9,2)

 COST_MD_AMT                                                                NUMBER(9,2)

 COST_PERM_MD_AMT                                                           NUMBER(9,2)

 RETAIL_OVERING_UNDERING                                                    NUMBER(9,2)

 PIERCING_STATUS                                                            VARCHAR2(3)

 JEWELRY_CLUB_NUMBER                                                        NUMBER(15)

 RETAIL_PERM_MD_AMT                                                         NUMBER(9,2)

 MD_AUTH_IND                                                                VARCHAR2(1)

 PIERCE_TYPE                                                                VARCHAR2(1)

 ALERT_DATE                                                                 DATE

 ALERT_TYPE                                                                 VARCHAR2(1)

 ALERT_SALES_ASSOCIATE                                                      VARCHAR2(9)

 TENDER_CODE                                                       NOT NULL NUMBER

 TENDER_FLAG                                                                VARCHAR2(1)

 PAYMENT_PLAN_CODE                                                 NOT NULL NUMBER

 RETAIL_MD_CODE3                                                            NUMBER(2)

 RETAIL_MD_AMT3                                                             NUMBER(9,2)

 RETAIL_MD_CODE4                                                            NUMBER(2)

 RETAIL_MD_AMT4                                                             NUMBER(9,2)

 

 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Sat Aug 07 2004 - 14:21:48 CDT

Original text of this message

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