Oracle Scratchpad

January 6, 2010

Copy Stats

Filed under: Oracle,Statistics — Jonathan Lewis @ 2:24 pm GMT Jan 6,2010

In a recent posting on OTN, someone was having trouble copying stats from one index to another using the import_index_stats and export_index_stats procedures from package dbms_stats modifying the contents of their “stat table” between the export and import.

Part of the problem with this approach is that you’re not really supposed to do what they were trying to do (or so I was informed by a client who had been told by Oracle Support that they should not modify the stat table). But if you want to do something similar it’s quite easy (and “legal”) to write a little bit of code that (run by a user with the appropriate privileges to the data dictionary) does a simple pair of calls to  get_index_stats/set_index_stats:

        m_numrows               number;
        m_numlblks              number;
        m_numdist               number;
        m_avglblk               number;
        m_avgdblk               number;
        m_clstfct               number;
        m_indlevel              number;
                ownname         => 'SOURCE_SCHEMA',
                indname         => 'SOURCE_IND',
                numrows         => m_numrows,
                numlblks        => m_numlblks,
                numdist         => m_numdist,
                avglblk         => m_avglblk,
                avgdblk         => m_avgdblk,
                clstfct         => m_clstfct,
                indlevel        => m_indlevel
--      Something here to change a few variables
--      e.g. m_indlevel := 1;
                ownname         => 'DEST_SCHEMA',
                indname         => 'DEST_INDEX',
                numrows         => m_numrows,
                numlblks        => m_numlblks,
                numdist         => m_numdist,
                avglblk         => m_avglblk,
                avgdblk         => m_avgdblk,
                clstfct         => m_clstfct,
                indlevel        => m_indlevel

The same sort of code, with a few minor changes, can obviously be used for (composite) partitioned indexes and secondary indexes on IOTs (index organized tables); and it’s not difficult to wrap a pl/sql loop around this type of code, possibly driven by a look-up table, to copy stats on a much larger scale.

Then there’s the trivial example, of course, where you can change the stats on an index simply by making the source_schema/source_index and dest_schema/dest_index the same in the above code – which is something I do from time to time for critical tests.



  1. Hi Jonathan,

    That’s superb… very valuable information sir. Jonathan when you will come for INDIA. ?? Very much to eager to meet you and to attend one of your classes if they are free…

    Comment by Pavan Kumar N — January 6, 2010 @ 2:33 pm GMT Jan 6,2010 | Reply

    • Pavan,

      I’ve arranged to do a (short) web-presentation for members of the All India Oracle User Group next wednesday (13th Jan) at 5:00 pm IST. If it works well, then it will become the first of a short series. The AIOUG are also working to arrange an event in early September 2010 which I’ve said I would support – but that’s still in the early planning stages so no promises or details yet.

      Comment by Jonathan Lewis — January 7, 2010 @ 7:51 am GMT Jan 7,2010 | Reply

      • Just became a member to attend this conferance of yours (on 13th jan 5 PM IST). These guys take 3 days to activate the membership So I should be in time to attend your webcast. (Its still 4 days to go)

        At the risk of clubbing two unrelated things together.

        I somewhere read (blog/interview/internet) that you were planning to make your “Practical oracle 8i” Book available for download free of cost (Just like “Thinking in java” by Bruce Eckel). It would be a big boost for everybody in Oracle community.

        Comment by Sunil Choudhary — January 8, 2010 @ 5:28 am GMT Jan 8,2010 | Reply

      • Hi Jonathan,

        I tried to purchase the book “Practical oracle 8i”, but could not able to find out in any stores. I searched nearly for 6 months in different cities -but a hard copy of book is not available. It would be great if you avail a free copy of that book for us. I am dieing to study the book of yours – at least a pdf copy from your side. It’s a personal request sir.

        Thanking you in Advance.

        Comment by Pavan Kumar N — January 8, 2010 @ 5:57 am GMT Jan 8,2010 | Reply

      • Pavan, Sunil,

        I made a comment somewhere that the copyright belongs to Addison-Wesley, but when they find they aren’t selling any more copies I’d ask them if they would let me have copyright back so that I could publsh on the web under “Creative Commons” as they did with James Morle and his book – Scaling Oracle 8i.

        At present, though, they’re still selling paper copies.

        Comment by Jonathan Lewis — January 8, 2010 @ 8:06 am GMT Jan 8,2010 | Reply

  2. Hi Jonathan Lewis,

    Yes I heard the same thing that you will be invited to India. I am a big Fan of you :) Expecting positively with out fail , a request from all our Indian fans.



    Comment by Balakrishna — January 7, 2010 @ 12:43 pm GMT Jan 7,2010 | Reply

  3. Hi jonathan,

    Even I had become a member of AIOUG n order to attend the session, still the user is not activated. I must whether my luck is there to reach and attend the seminar or not. Dieing to attend to your sessions. Once in a life time we will get a chance. Just waiting…!!

    Comment by Pavan Kumar N — January 8, 2010 @ 5:46 am GMT Jan 8,2010 | Reply

  4. […] Lewis clarifies copy stats. “ . . . someone was having trouble,” he writes, “copying […]

    Pingback by Log Buffer #174: a Carnival of the Vanities for DBAs | The Pythian Blog — January 8, 2010 @ 7:56 pm GMT Jan 8,2010 | Reply

  5. […] 11-How to copy stats between objects ? Jonathan Lewis-Copy Stats […]

    Pingback by Blogroll Report 01/01/2009 – 08/01/2010 « Coskan’s Approach to Oracle — January 20, 2010 @ 3:46 pm GMT Jan 20,2010 | Reply

  6. […] them) it was by using dbms_stats.set_index_stats() to change the stored statistics – see “Copy Stats” for an example of the type of code needed. In cases like this, where I have better information […]

    Pingback by Local Indexes – 2 « Oracle Scratchpad — November 14, 2010 @ 5:42 pm GMT Nov 14,2010 | Reply

  7. […] index to 3. (This isn’t just whimsy, it helps to emulate Doug’s problem.) I’ve previously published the type of code to make this possible; in this case I only set the table-level stats because the queries I’m […]

    Pingback by Upgrade issues « Oracle Scratchpad — February 3, 2011 @ 6:42 pm GMT Feb 3,2011 | Reply

  8. […] 3: For an example of the type of code that will adjust the blevel of an index see this URL. (Note, the example talks about copying stats from one place to another – but the principle […]

    Pingback by blevel=1 « Oracle Scratchpad — August 24, 2011 @ 9:17 am BST Aug 24,2011 | Reply

  9. […] I had the time (and the data set) I’d start playing with dbms_stats.set_index_stats() to see the effect of changing num_distinct and num_rows to see if my initial guess was […]

    Pingback by CBOddity | Oracle Scratchpad — August 7, 2020 @ 1:31 pm BST Aug 7,2020 | Reply

  10. […] Although Oracle has supplied a fix to a problem I highlighted in CBO-F, I can understand why it’s not enabled by default, and I don’t think I’d want to take advantage of it in a production system given the way it’s a session setting at stats gather time. The number of times it really matters I’d probably add hints to the SQL to stop the optimizer from using the index incorrectly, or do something at a stats-gathering moment to call dbms_stats.set_index_stats(). […]

    Pingback by Index FFS Cost 2 | Oracle Scratchpad — October 6, 2020 @ 12:58 pm BST Oct 6,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: