Copying Stats Between Partitions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Copying Stats Between Partitions

  1. #1
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152

    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 ?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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
  •  



Click Here to Expand Forum to Full Width