-
Copying Stats Between Partitions
Oracle 9.0.2.6
I am trying to populate the Stats on a newly loaded date partition by copying the stats from the previous day. The old method used GET_TABLE/INDEX/COLUMN_STATS and SET_TABLE/INDEX/COLUMN_STATS but that was limited because it didn't copy any of the histogram data. This gives us severe performance problems because the optimizer keeps on choosing to use indexes with very bad cardinality.
Therefore I came up with a new method that involved exporting stats with the cascade option to a STATS table, updating the partition name (column C2) on the STATS table and then importing those stats to the new partition.
I've run this and looked at USER_TAB_PARTITIONS, USER_IND_PARTITIONS, USER_PART_COL_STATISTICS and USER_PART_HISTOGRAMS, and everything is identical between the two partitions. For all intents and purposes, the stats copy appears to have worked perfectly.
And yet...when I do run the same query with each partition, Oracle still chooses a different plan. I set up some skewed data with ID=1 having many values but ID=2 having few values. For the partition where I gathered stats, the optimizer correctly works out that it should use the index for ID=2 but a full table scan for ID=1. But for the other partition, it still wants to use an index scan for both. The data in the two partitions is identical.
Has anybody any suggestions on what could cause this ? If Oracle genuinely is using just the statistics to decide on the query path, why would it be different when the statistics are identical. Is there some hidden flag that is invalidating the statistics ?
-
Can you post the two queries and along with the explain plans, through the dbms_xplan method? Might have to resort to a 10053 trace after that
-
I've never come across DBMS_XPLAN. Is that in Oracle 9 ? The basic query is:
Code:
SELECT
rownum rec_no,
dr.row_id row_id
FROM
(SELECT
ROWID row_id
FROM
sfdh_test s
WHERE
business_date = "date" AND
src_sys_feed_id = "feed id" AND
src_sys_feed_type_id = 1 and
src_sys_feed_run_count = 1
ORDER BY
business_date,
src_sys_feed_id,
src_sys_feed_type_id , row_id
) dr;
Table is partitioned on business_date and there is an index on (business_date, src_sys_feed_id). The values of "date" flip between '30-jun-06' (the partition with gathered stats) and '01-jul-06' (the partition with copied stats). The values of src_sys_feed_id flip between 1 (0.5 million rows) and 2 (100 rows).
USER_PART_HISTOGRAMS for both partitions shows the data as:
Code:
COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE
SRC_SYS_FEED_ID 49898 1
SRC_SYS_FEED_ID 49908 2
That is quite reasonable because I analyzed 10% of the table, so you would expect 50000 ror '1' and about 10 for '2'.
For 01-jul-06 and '1', the plan is:
Code:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 498 K 2783
COUNT
VIEW 498 K 3 M 2783
SORT ORDER BY 498 K 11 M 2783
TABLE ACCESS FULL OPL.SFDH_TEST 498 K 11 M 336 2 2
For 01-jul-06 and '2', the plan is:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
Code:
SELECT STATEMENT Optimizer Mode=CHOOSE 100 9
COUNT
VIEW 100 700 9
SORT ORDER BY 100 2 K 9
TABLE ACCESS BY LOCAL INDEX ROWID OPL.SFDH_TEST 100 2 K 4 2 2
INDEX RANGE SCAN OPL.OPL_IE1_SFDHT 100 3 2 2
However, if I change the date to '30-jun-06', the plan for both values of src_sys_feed_id is the indexed version.
-
explain plan
set statement_id = 'X'
for
SELECT
BLAH
FROM
BLAH
WHERE
BLAH
/
select * from table(dbms_xplan.display);
Assistance is Futile...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|