| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> HELP - Terrible query performance on newly partitioned table
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
0 SELECT STATEMENT CHOOSE 1 NESTED LOOPS OUTER
4 NESTED LOOPS OUTER
5 NESTED LOOPS OUTER
6 NESTED LOOPS OUTER
7 NESTED LOOPS OUTER
8 NESTED LOOPS OUTER
9 NESTED LOOPS OUTER
10 TABLE ACCESS BY INDEX ROWID
TRADE_CREDIT_ACTIVITY
11 INDEX RANGE SCAN TCA_SET_ID_IDX
12 TABLE ACCESS BY INDEX ROWID TRANSACTION_CODE
13 INDEX UNIQUE SCAN TRANSACTION_CODE_PK
14 TABLE ACCESS BY INDEX ROWID FUND_LEGAL
15 INDEX UNIQUE SCAN FUND_LEGAL_PK
16 TABLE ACCESS BY INDEX ROWID ACCOUNT
17 INDEX UNIQUE SCAN ACCOUNT_PK
18 TABLE ACCESS BY INDEX ROWID SOCIAL_CODE
19 INDEX UNIQUE SCAN SOCIAL_CODE_PK
20 TABLE ACCESS BY INDEX ROWID ERSON
21 INDEX UNIQUE SCAN ERSON_PK
22 TABLE ACCESS BY INDEX ROWID
REP_CODE
23 INDEX UNIQUE SCAN REP_CODE_PK
24 TABLE ACCESS BY INDEX ROWID
OFFICE_LOCATION
25 INDEX UNIQUE SCAN
OFFICE_LOCATION_PK
26 TABLE ACCESS BY INDEX ROWID
DEALER_BRANCH
27 INDEX UNIQUE SCAN
DEALER_BRANCH_PK
28 TABLE ACCESS BY INDEX ROWID
FIRM
29 INDEX UNIQUE SCAN
FIRM_PK
0 SELECT STATEMENT CHOOSE 1355448325983060000
1235199408987460000000000000 2221581670840750000000000
1 HASH JOIN OUTER 1355448325983060000
1235199408987460000000000000 2221581670840750000000000
2 HASH JOIN OUTER 393326579885342
67357813106444600000000 124736690937860000000
3 HASH JOIN OUTER 271971689054 36482736969702800000
71394788590416500
4 HASH JOIN OUTER 272416084 24108738095628800
53102947347200
5 HASH JOIN OUTER 4827070 25207807505664 64305631392
6 HASH JOIN OUTER 4332 624513340773 1749337089
7 HASH JOIN OUTER 147 457277010 1365006
8 HASH JOIN OUTER 33 13645422 46413
9 HASH JOIN OUTER 7 3086656 11348
10 TABLE ACCESS BY GLOBAL INDEX ROWID TRADE_CREDIT_ACTIVITY
2 350028 1389
11 INDEX RANGE SCAN TCA_SET_ID_IDX 1
1389
12 TABLE ACCESS FULL FUND_LEGAL 2 16340
817
13 TABLE ACCESS FULL SOCIAL_CODE 1 8998
409
14 TABLE ACCESS FULL TRANSACTION_CODE 6 120581
2941
15 TABLE ACCESS FULL REP_CODE 239 2819432
128156
16 TABLE ACCESS FULL FIRM 7 128660
3676
17 TABLE ACCESS FULL OFFICE_LOCATION 154 5119898
82579
18 TABLE ACCESS FULL PERSON 250 7663422
134446
19 TABLE ACCESS FULL DEALER_BRANCH 325 5066706
174714
20 TABLE ACCESS FULL ACCOUNT ANALYZED 15546
28496272 1781017
Received on Fri Jan 04 2002 - 09:58:58 CST
 |
 |