| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10046 / wait events question
This is just a shot in the dark but do have timed statistics set to true?
Ruth
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Darrell Landrum
Sent: Saturday, August 07, 2004 3:26 PM
To: oracle-l_at_freelists.org
Subject: 10046 / wait events question
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_TES T.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 NULLDATE
STORE NOTNULL NUMBER(4)
TRANS_NUMBER NOT NULLNUMBER(10)
SEQ_NUMBER NOT NULLNUMBER(3) SKU
TENDER_CODE NOT NULLNUMBER TENDER_FLAG
PAYMENT_PLAN_CODE NOT NULLNUMBER RETAIL_MD_CODE3
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Mon Aug 09 2004 - 14:08:38 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
 |
 |