How are SQL Profiles calculated?

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Thu, 8 Jan 2009 10:02:33 -0600
Message-ID: <7b8774110901080802u3a8fa4c6wf0cc57684e0887ec_at_mail.gmail.com>



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(_at_"SEL$1", TABLE, "CHG"_at_"SEL$1", SCALE_ROWS=0.0157467437) OPT_ESTIMATE(_at_"SEL$1", INDEX_FILTER, "PAY"_at_"SEL$1", TBRACCD_CREDITS_INDEX, SCALE_ROWS=0.01289999782)
OPT_ESTIMATE(_at_"SEL$1", INDEX_FILTER, "PAY"_at_"SEL$1", TBRACCD_INVOICE_INDEX, SCALE_ROWS=0.01289999782)
OPT_ESTIMATE(_at_"SEL$1", TABLE, "PAY"_at_"SEL$1", SCALE_ROWS=0.1749069997) OPT_ESTIMATE(_at_"SEL$1", INDEX_SKIP_SCAN, "PAY"_at_"SEL$1", TBRACCD_APPL_INDEX, SCALE_ROWS=0.1427123937)
OPT_ESTIMATE(_at_"SEL$1", INDEX_SKIP_SCAN, "CHG"_at_"SEL$1", TBRACCD_APPL_INDEX, SCALE_ROWS=0.0157467437)
OPT_ESTIMATE(_at_"SEL$1", INDEX_SKIP_SCAN, "PAY"_at_"SEL$1", TBRACCD_INVOICE_INDEX, SCALE_ROWS=0.01289999782) OPT_ESTIMATE(_at_"SEL$1", INDEX_SCAN, "PAY"_at_"SEL$1", TBRACCD_APPL_INDEX, SCALE_ROWS=5.701129889e-07)
OPT_ESTIMATE(_at_"SEL$1", INDEX_SKIP_SCAN, "PAY"_at_"SEL$1", TBRACCD_CREDITS_INDEX, SCALE_ROWS=0.01289999782) OPT_ESTIMATE(_at_"SEL$1", INDEX_SCAN, "CHG"_at_"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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 08 2009 - 10:02:33 CST

Original text of this message