| 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
Darrell,
So, to be clear, is this true? You are saying that /none/ of the p1 and =
p2
values for your 'db file.*' events indicate the file and block numbers =
of
blocks that are members of the largest participating table in your =
query?
And that the same statement is true of all the table's indexes. Right?
If this /is/ what you're saying, then I would take the trace data at =
face
value. It's saying that all the blocks that were required from those
segments (the table and its indexes) were already in your buffer cache. =
In
other words, the Oracle kernel simply didn't execute OS read calls for =
those
blocks during the execution of this program.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 =
Charlotte
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----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 2:26 PM
To: oracle-l_at_freelists.org
Subject: 10046 / wait events question
Am I missing something?
=20
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). =20
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.
=20
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).
=20
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.
=20
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.
=20
Trace file:
http://webpages.charter.net/dands1300/oracle/dwrp_ora_14958_DWL_TEST.trc
=20
Trace analyzer output:
http://webpages.charter.net/dands1300/oracle/TRCANLZR_dwrp_ora_14958_DWL_=
TES
T.LOG
=20
Trace exec output:
http://webpages.charter.net/dands1300/oracle/TRCAEXEC_1_16_0.TXT
=20
=20
Any thoughts?
Thanks,
Darrell
=20
=20
=20
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=3DSKU.SKU )
AND ( ORG_FLAT_INV.STORE_NUM=3DSALES.STORE )
AND ( ITEM_GROUP.SKU=3DSKU.SKU )
AND (
( ITEM_GROUP.ITEM_GROUP =3D 'PRICE_BREAK' AND SALES.TRANS_DATE =
BETWEEN
ITEM_GROUP.BEGIN_DATE AND ITEM_GROUP.END_DATE )
AND SKU.DEPT !=3D 19
AND ITEM_GROUP.DIVISION =3D 30
AND ORG_FLAT_INV.DIVISION =3D 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;
=20
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
=20
The sales table desc:
TRANS_DATE NOT =NULL
STORE =NOT
TRANS_NUMBER NOT =NULL
SEQ_NUMBER NOT =NULL
TENDER_CODE NOT =NULL
PAYMENT_PLAN_CODE NOT =NULL
=20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- 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 Tue Aug 10 2004 - 21:06:08 CDT
 |
 |