Re: HELP - Terrible query performance on newly partitioned table

I can't see any immediate reason why the query should have shifted from NL to Hash. This can happen when you first partition a table, because the partitions can become so small that the single partition scan is very cheap - the numbers don't suggest is in your case though.

For the purpose of investigation, you could try the hints:

    ordered
    use_nl(firm)
    use_nl(dealer_branch)
    ...
to see if this has any effect.

Did you use dbms_stats to analyze the partiitoned table, or ANALYZE - the former may have done something odd to either the partition level, or global level statistics that could be the problem.

There are, by the way, a couple of anomalies in your strategy. In particular, I doubt if partitioning by trade_date is going to do anything for your performance if this is the typical type of query; secondly, since you have global indexes, much of the maintenance benefit will be lost - you won't be able to drop a partition without rebuilding the entire global index (at least until v9). Finally, this query doesn't have a star schema solution (as far as Oracle is concerned) when you have all those outer joins to the dimensions.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Jim Harrington wrote in message ...


>Hi,

>

>We're on 8.1.7, patch level 2 and have a star schema situation where a

query


>like the one listed below is very important.  The table

>TRADE_CREDIT_ACTIVITY holds security trade information and is partitioned

on


>the trade settle date.  There are foreign key constraints from

>TRADE_CREDIT_ACTIVITY to all the other tables mentioned in the from clause;

>indexes on TRADE_CREDIT_ACTIVITY for all the foreign keys and integer

>primary key indexes on all the other tables.  The table

>TRADE_CREDIT_ACTIVITY contains 6.5 million rows in our test schema and will

>ultimately hold 50 million rows.  Each of the detail tables contains

between


>1000 and 1000000 rows.

>

>What I expect in this situation is that the explain plan will hit all the

>indexes, which I get in another schema that we have that is at this point

>un-partitioned (see explain plan below).  The unpartitioned schema aslo

>contains only about 10% of the trade data as the partitioned schema.  In

the


>partitioned schema, all the index hits are dropped in favor of full table

>scans (plan also listed below).  Needless to say, the query runs

>dramatically slower on the partitioned schema.

>

>I've tried using the RULE hint and explicit INDEX hints to try to get the

>optomizer to pick up the primary key indexes on the detail tables, but

>nothing works.  I also tried these hints after having deleted statistics on

>the relevant tables (we typically don't use them anyway) and then tried

>calculating them again and tried the query with and without the hints.

>Nothing I do changes the plan.

>

>The really odd thing I noticed is that on the partitioned schema, if I

>replace the query's select clause with count(*), all the indexes are hit.

>If I add in the primary key of any of the related tables, the index to that

>table is still hit.  When I add in any non-key column from a related table

>to the select clause, the primary key to that table is no longer hit and a

>full table scan to that table shows up in the execution plan.

>

>Can anyone explain what's going on here and what to do about it?

>

>Thanks,

>

>    Jim Harrington

>

>[QUERY]

>

>SELECT TCA_ID, TCA_SET_ID, TCA_ORD_ID, TCA_OLR_ID, TCA_AMOUNT,

>       TCA_CREATE_USERID, TCA_CREATE_DATE, TCA_MAINT_USERID,

TCA_MAINT_DATE,


>       TCA_TRADE_DATE, TCA_SETTLED_DATE,

>       FIR_ID, FIR_NAME, FIR_VALIDATED_FLG,

>       DEB_ID, DEB_DEALER_NUM, DEB_BRANCH_CODE,

>       OFL_ID, OFL_ADDR_LINE1, OFL_CITY, OFL_STATE, OFL_ZIP,

>OFL_VALIDATED_FLG,

>       REC_ID, REC_REP_CODE,

>       PER_ID, PER_FIRST_NAME, PER_MIDDLE_NAME, PER_LAST_NAME,

>PER_VALIDATED_FLG,

>       SOC_ID, SOC_SOCIAL_CODE,

>       ACC_ID, ACC_CUST_ACCT_NUM,

>       FUL_ID, FUL_FUND_CODE,

>       TRC_ID, TRC_TRANS_CODE, TRC_TRANS_SUFFIX,

>TRC_SHARE_BALANCE_INDICATOR, TRC_TRANS_CODE_OVERRIDE

>FROM TRADE_CREDIT_ACTIVITY,

>     FIRM,

>     DEALER_BRANCH,

>     OFFICE_LOCATION,

>     REP_CODE,

>     PERSON,

>     SOCIAL_CODE,

>     ACCOUNT,

>     FUND_LEGAL,

>     TRANSACTION_CODE

>WHERE FIR_ID (+) = TCA_FIR_ID

>AND   DEB_ID (+) = TCA_DEB_ID

>AND   OFL_ID (+) = TCA_OFL_ID

>AND   REC_ID (+) = TCA_REC_ID

>AND   PER_ID (+) = TCA_PER_ID

>AND   SOC_ID (+) = TCA_SOC_ID

>AND   ACC_ID (+) = TCA_ACC_ID

>AND   FUL_ID (+) = TCA_FUL_ID

>AND   TRC_ID (+) = TCA_TRC_ID

>AND   TCA_SET_ID = 8245508

>