How are SQL Profiles calculated?
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Jan 2009 10:02:33 -0600
I realize Oracle has some significant Intellectual Property Rights tied up
with the SQL Tuning package. However, I am trying to tune a particular query
by hand and finding the SQL Tuning package to produce some interesting
results. I would be happy to provide the query, explain plan, 10053 trace
and results from SQLPROF$ATTR (granted, the last would be in the 10053
trace), but they would exceed the size quota for freelists.
What is most interesting to me is that the profile establishes metrics for
access methods that are not even used in the query plan. And all the
OPT_ESTIMATE hints scale down the number of rows. How does Oracle come up
with those scale numbers? How are those scale numbers more significant than
gathering a complete statistical picture?
For instance, a portion of the query does a self-join on a table called
TBRACCD:
select ....
from tbraccd chg, tbraccd pay, ...
where pay.pidm = :b6
and chg.pidm = pay.pidm
and pay.balance < 0
and chg.balance > 0
....
Obviously, I am oversimplifying the query and there is a bit going on. The
balance column is heavily populated with 0 values (not null or empty, but
literally, 0), about 18.5 millions rows out of 19 million. The primary key
index has pidm and two other columns. There is a vendor-delivered index
called "TBRACCD_APPL_INDEX" that is comprised of balance and pidm (in that
order). The vendor-delivered query has a hint for the primary key (to avoid
going to the appl_index, I can only guess). However, the SQL Profile scales
the rows such that we do a index range scan on the appl_index. We are in the
midst of testing performance to see if that actually helps or not, but my
main goal is to understand what is going on with this query, and to
determine if perhaps we have some stats that are out of whack.
SQL Profile:
OPT_ESTIMATE(@"SEL$1", TABLE, "CHG"@"SEL$1", SCALE_ROWS=0.0157467437)
OPT_ESTIMATE(@"SEL$1", INDEX_FILTER, "PAY"@"SEL$1", TBRACCD_CREDITS_INDEX,
SCALE_ROWS=0.01289999782)
OPT_ESTIMATE(@"SEL$1", INDEX_FILTER, "PAY"@"SEL$1", TBRACCD_INVOICE_INDEX,
SCALE_ROWS=0.01289999782)
OPT_ESTIMATE(@"SEL$1", TABLE, "PAY"@"SEL$1", SCALE_ROWS=0.1749069997)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "PAY"@"SEL$1", TBRACCD_APPL_INDEX,
SCALE_ROWS=0.1427123937)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "CHG"@"SEL$1", TBRACCD_APPL_INDEX,
SCALE_ROWS=0.0157467437)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "PAY"@"SEL$1",
TBRACCD_INVOICE_INDEX, SCALE_ROWS=0.01289999782)
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "PAY"@"SEL$1", TBRACCD_APPL_INDEX,
SCALE_ROWS=5.701129889e-07)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "PAY"@"SEL$1",
TBRACCD_CREDITS_INDEX, SCALE_ROWS=0.01289999782)
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "CHG"@"SEL$1", TBRACCD_APPL_INDEX,
SCALE_ROWS=6.290570064e-08)
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE(default)
IGNORE_OPTIM_EMBEDDED_HINTS
Any ideas would be appreciated. =)
--
Charles Schultz
Other related posts:
