| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_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
-- 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
 |
 |