SEO

I have this sql:

select D.* from tcustomer_history a, tdate, tcustomer b, taccount c,
TPOLICY_SUMMARY D where A.month_sk = date_sk
and year_month_ct = 200704 and a.CUSTOMER_SK = b.CUSTOMER_SK and
b.ACCOUNT_SK = c.ACCOUNT_SK
and c.SPECIAL_HANDLING = 'VIP' AND A.MONTH_SK = D.MONTH_SK AND
A.POLICY_SK = D.POLICY_SK
AND SNAPSHOT_TYPE_IN = 0

It takes about 26 seconds to retrieve the results and I am hoping to
improve the performance.

This is the explain plan I got:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 19 K 15653
HASH JOIN 19 K 2 M 15653
TABLE ACCESS FULL MISP.TACCOUNT 15 K 140 K 55
HASH JOIN 107 K 10 M 15533
TABLE ACCESS FULL MISP.TCUSTOMER 113 K 1 M 235
HASH JOIN 107 K 9 M 15088
HASH JOIN 1 M 36 M 782
TABLE ACCESS BY INDEX ROWID MISP.TDATE 30 270 2
INDEX RANGE SCAN MISP.IDX_TDATE_YEAR_MONTH_CT 1 1
INDEX FAST FULL SCAN MISP.SYS_C003642 4 M 56 M 766
TABLE ACCESS FULL MISP.TPOLICY_SUMMARY 5 M 350 M 6887

I have an index on TPOLICY_SUMMARY table on MONTH_SK, POLICY_SK and
SNAPSHOT_TYPE_IN columns. I use that to join this table in this query.
The query optimizer still invokes a a full table scan. Why should it
go for a FTS in this case?

Are there any other tricks to make this query faster?

On Thu, 17 May 2007 10:55:25 -0700, aravind.kanda@gmail.com wrote:

> Are there any other tricks to make this query faster?

Yesterday, I was at Jonathan's presentation where he demonstrated
the /*+ go_faster */ hint. He might be able to tell more.

--
http://www.mladen-gogala.com

On May 17, 12:55 pm, "aravind.ka...@gmail.com"
<aravind.ka...@gmail.com> wrote:
> I have this sql:
>
> select D.* from tcustomer_history a, tdate, tcustomer b, taccount c,
> TPOLICY_SUMMARY D where A.month_sk = date_sk
> and year_month_ct = 200704 and a.CUSTOMER_SK = b.CUSTOMER_SK and
> b.ACCOUNT_SK = c.ACCOUNT_SK
> and c.SPECIAL_HANDLING = 'VIP' AND A.MONTH_SK = D.MONTH_SK AND
> A.POLICY_SK = D.POLICY_SK
> AND SNAPSHOT_TYPE_IN = 0
>
> It takes about 26 seconds to retrieve the results and I am hoping to
> improve the performance.
>
> This is the explain plan I got:
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 19 K 15653
> HASH JOIN 19 K 2 M 15653
> TABLE ACCESS FULL MISP.TACCOUNT 15 K 140 K 55
> HASH JOIN 107 K 10 M 15533
> TABLE ACCESS FULL MISP.TCUSTOMER 113 K 1 M 235
> HASH JOIN 107 K 9 M 15088
> HASH JOIN 1 M 36 M 782
> TABLE ACCESS BY INDEX ROWID MISP.TDATE 30 270 2
> INDEX RANGE SCAN MISP.IDX_TDATE_YEAR_MONTH_CT 1 1
> INDEX FAST FULL SCAN MISP.SYS_C003642 4 M 56 M 766
> TABLE ACCESS FULL MISP.TPOLICY_SUMMARY 5 M 350 M 6887
>
> I have an index on TPOLICY_SUMMARY table on MONTH_SK, POLICY_SK and
> SNAPSHOT_TYPE_IN columns. I use that to join this table in this query.
> The query optimizer still invokes a a full table scan. Why should it
> go for a FTS in this case?
>
> Are there any other tricks to make this query faster?


>From your description it sounds like you have two indexes on
TPOLICY_SUMMARY.
1) MONTH_SK, POLICY_SK
2) SNAPSHOT_TYPE (assuming that column is from the TPOLICY_SUMMARY
table, but not sure since it is not qualified.)

The first index may or may not be used. There are no filter
predicates on this table in the query directly, so any use of the
index will depend on the computed cardinality of the table it is
joined to, which is tcustomer_history.
Based on the execution plan, it looks like the TDATE table is hit
first by columns (date_sk, year_month_ct) expecting 30 rows.
It joins that to tcustomer_history by a system named index
SYS_C003642, expecting a join cardinality of 1M.
Based on that 1M the cbo determined a FTS of the 5M in TPOLICY_SUMMARY
was the better choice over using the index.

That's what I see based on the information. Probably need more
information to determine completely.
db version, values of optimizer_index_cost_adj, table and column
statistics.
It may be a matter of statistics or it may just be that the cbo is
doing what it has to do to get the request.
Does reality match with what the cbo expects? Is it really returning
19K rows or does it return 5? If the stats do not match reality then
look at the stats.

Andrew

On May 17, 1:55 pm, "aravind.ka...@gmail.com"
<aravind.ka...@gmail.com> wrote:
> I have this sql:
>
> select D.* from tcustomer_history a, tdate, tcustomer b, taccount c,
> TPOLICY_SUMMARY D where A.month_sk = date_sk
> and year_month_ct = 200704 and a.CUSTOMER_SK = b.CUSTOMER_SK and
> b.ACCOUNT_SK = c.ACCOUNT_SK
> and c.SPECIAL_HANDLING = 'VIP' AND A.MONTH_SK = D.MONTH_SK AND
> A.POLICY_SK = D.POLICY_SK
> AND SNAPSHOT_TYPE_IN = 0
>
> It takes about 26 seconds to retrieve the results and I am hoping to
> improve the performance.
>
> This is the explain plan I got:
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 19 K 15653
> HASH JOIN 19 K 2 M 15653
> TABLE ACCESS FULL MISP.TACCOUNT 15 K 140 K 55
> HASH JOIN 107 K 10 M 15533
> TABLE ACCESS FULL MISP.TCUSTOMER 113 K 1 M 235
> HASH JOIN 107 K 9 M 15088
> HASH JOIN 1 M 36 M 782
> TABLE ACCESS BY INDEX ROWID MISP.TDATE 30 270 2
> INDEX RANGE SCAN MISP.IDX_TDATE_YEAR_MONTH_CT 1 1
> INDEX FAST FULL SCAN MISP.SYS_C003642 4 M 56 M 766
> TABLE ACCESS FULL MISP.TPOLICY_SUMMARY 5 M 350 M 6887
>
> I have an index on TPOLICY_SUMMARY table on MONTH_SK, POLICY_SK and
> SNAPSHOT_TYPE_IN columns. I use that to join this table in this query.
> The query optimizer still invokes a a full table scan. Why should it
> go for a FTS in this case?
>
> Are there any other tricks to make this query faster?

How did you generate this explain plan? Is it generated from the row
source lines in a 10046 trace, (assuming Oracle 10g) the plan that
appears in a 10053 trace, DBMS_XPLAN (what options specified), EXPLAIN
PLAN FOR..., or some other method? If the plan is not from a 10046
trace file or DBMS_XPLAN called with ALLSTATS LAST, compare the plan
that you posted with the execution plan indicated in a 10046 trace
file or the execution plan from DBMS_XPLAN called with ALLSTATS LAST.
How close is the predicted cardinality with the actual cardinality?
With that information, you may find that a hint is appropriate to
correct the execution plan, or you may find that the the performance
problem is caused by resource limitations/problems in the database
instance (insufficient HASH_AREA_SIZE causing temp tablespace usage,
for instance).

A 10053 trace will indicate why Oracle determined not to use the index
on the MONTH_SK, POLICY_SK and SNAPSHOT_TYPE_IN columns.

Side note: it is easier for most people to read the SQL statement and
plan if you use meaningful aliases for table names, rather than A, B,
C, and D.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.