5 Replies Latest reply on Jan 6, 2010 2:28 PM by Jonathan Lewis

    IMPORT_INDEX_STATS

    690963
      Hello Friends

      I am trying to copy index stats from one schema to another using DBMS_STATS.EXPORT_INDEX_STATS..below are the detailed steps. All the statements are geting executed successfully bt i dont see the stats really geting copied over to destination index which is in same database bt different schema.
      exec DBMS_STATS.CREATE_STAT_TABLE('dest_schema','STATS_TABLE' ,'tablespace');
      exec dbms_stats.export_index_stats(ownname=>'source_schema',indname=>'source_ind',stattab=>'STATS_TABLE',statown=>'dest_schema');
      exec dbms_stats.import_index_stats(ownname=>'dest_schema',indname=>'dest_index',stattab=>'STATS_TABLE',statown=>'dest_schema',force=>TRUE);
      After doing this....i see that last analyzed and num_rows value for both the indexes is different..
      Please help me out troubleshoot this issue

      Thanx
        • 1. Re: IMPORT_INDEX_STATS
          sb92075
          Please help me out troubleshoot this issue
          two different STATS_TABLE exist; one for "source_schema" & another for "dest_schema".

          It is a BAD idea to use lower case schema names.
          • 2. Re: IMPORT_INDEX_STATS
            690963
            I have changed the object names while posting..so dont worry about them...they all were in upper case while executing....Can you please elaborate on "different
            stats table for each schema"
            • 3. Re: IMPORT_INDEX_STATS
              sb92075
              user SCOTT can have table EMP
              user BRYCE can have table EMP.
              EMP table is different for each schema.
              The same can be true for STATS_TABLE.
              Each schema has its own STATS_TABLE.
              • 4. Re: IMPORT_INDEX_STATS
                690963
                Agreed...But if you look at the statements....the stats_table has been made to reside in the destination schema ....and the import statment executes without any errors....so that means it did find the stat table...because it finds the stats table in its own schema.
                • 5. Re: IMPORT_INDEX_STATS
                  Jonathan Lewis
                  njafri wrote:
                  exec DBMS_STATS.CREATE_STAT_TABLE('dest_schema','STATS_TABLE' ,'tablespace');
                  exec dbms_stats.export_index_stats(ownname=>'source_schema',indname=>'source_ind',stattab=>'STATS_TABLE',statown=>'dest_schema');
                  exec dbms_stats.import_index_stats(ownname=>'dest_schema',indname=>'dest_index',stattab=>'STATS_TABLE',statown=>'dest_schema',force=>TRUE);
                  It looks as if you're trying to import the stats for an index that Oracle won't be able to find in the stats_table.
                  If you want this to work you'll have to modify the contents of the stats table something like - update c1 where type = 'I' and c1 = 'SOURCE_IND'.

                  Oracle Corp. does not approve of this strategy - at least, one of my clients was told by an Oracle support analyst that they would not be supported if they had problems after using this technique.

                  Personally I would use dbms_stats.get_index_stats() to read the source set of stats, then dbms_stats.set_index_stats() to write them to the target index.

                  Update: I've just posted [http://jonathanlewis.wordpress.com/2010/01/06/copy-stats/] on my blog.


                  Regards
                  Jonathan Lewis

                  Edited by: Jonathan Lewis on Jan 6, 2010 2:26 PM
                  Added URL.