Oracle Scratchpad

January 6, 2010

Copy Stats

Filed under: Statistics — Jonathan Lewis @ 2:24 pm BST 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:

declare
	m_numrows		number;
	m_numlblks		number;
	m_numdist		number;
	m_avglblk		number;
	m_avgdblk		number;
	m_clstfct		number;
	m_indlevel		number;
begin
	dbms_stats.get_index_stats(
		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;
--
	dbms_stats.set_index_stats(
		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
	);
end;
/

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.

12 Comments »

  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 BST 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 BST 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 BST 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 BST 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 BST 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.

    Regards

    Bala

    Comment by Balakrishna — January 7, 2010 @ 12:43 pm BST 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 BST 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 BST 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 BST 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 BST 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 BST 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,164 other followers