Moving partition statistics
August 8th, 2007 by Peter Scott
Over in another place, Doug Burns has been writing on the joys of reproducing a full size database as test instance. He has even (graciously) put up with my wittering about things that not relevant to his articles. So as not to clutter his blog with a stream of off-(his)-topic posts here is a digression of my own, isolated.
One of things that concerns me greatly, that is a data warehouse designer, is query performance; I deal with monster reads and aggregations; rarely are my users interested in single fact table rows (but enough of them are to prevent me from ignoring those queries types). But the key thing is that I need the best query plans as even those a tad out when amplified by the sheer bulk of repetition waste resource and annoy users. Occasionally we run into CBO problems that need Oracle support to investigate and for this we need evidence and test cases (preferably without customer’s business data) I have written about this a couple of times and Tom Kyte may have mentioned it a bit too.
One of the irks about a small (half sized) test system is that not all of the data is loaded, and more importantly not all of the partitions are present. This is fine for a lot of tests but some need production volumes or least us to convince the system that we have the full data set. Often we can influence things by using some of the DBMS_STATS methods to export table stats to a stats table, then using conventional techniques such as exp/imp move its content to our test system and then use the DBMS_STATS.import_table_stats process to convince the optimiser that we have the same volume and distribution of data. However life is not so simple for partitioned tables. Even though a lot of my queries eliminate partitions on a join key or access several partitions, that is they are going to only use GLOBAL table statistics (partition stats are not even going to be considered by the CBO) there seems no easy way to just transfer global stats. I can specify an individual partition of statistics to export, but at table level it will always include all of the available partition stats in the export. Which means my import fails as the partitions are not there to receive the partition stats (OK, I know that is an oversimplification of the reality)
I do have ways round this problem, but that is not the point…
August 9th, 2007 at 3:24 pm
I have not tested it but this is what I read when I look into the comments of the dbms_stats package and specifically in the comments for the export_table_stats and import_table_stats procedures:
– partname - The name of the table partition. If the table is
– partitioned and partname is null, global and partition table
– statistics will be imported.
Is that one of your ‘workarounds’ or do I have to look for more detail?
August 9th, 2007 at 4:32 pm
Eric
The problem for me is that there is no option just to import the global statistics I can’t see a ready way just move global without bringing across all of the partition statistics too.
The simplest thing is create all of the partitions on the test but not populate them with data, this is a bit wasteful of space as they will all occupy a certain amount space. It may also be be possible to delete rows from the statistics table you are using for the export that relate to the partitions you do not have on the other system - I am not aware of a document that actually describes the format of this table
August 9th, 2007 at 8:44 pm
Peter,
I was thinking about the following method:
- gather global table stats for the partitioned table into a special stat table using dbms_stats.gather_table_stats (some_table,….,granularity=global…) (I have to look up the syntax, essential is the granularity)
- export these statistics
- import those into the database containing the non-partitioned table.
There must be some quirks to solve but I will try to cook-up a test case, just not tonight.
August 10th, 2007 at 9:24 am
Eric
Granualrity is not a documented argument to the export function
It could be that the documents are wrong though, it’s not unknown…
August 16th, 2007 at 4:07 pm
I have not found the time to get the script working completely (importing the stats was giving me trouble). But I have succeeded in getting global stats in a stat table. Indeed the export does not have the granularity option, but the gather does. Create a special stats table, gather only global stats into that stats table and you can export and (hopefully) import those stats. Now for the proof of the pudding ….
September 11th, 2007 at 1:02 pm
we have one big table in DW database, and this table has 13000 table partitions, each
partition contains millions records. And the nightly auto gather statistic job
can’t complete within the timeframe. So we think another alternative way to
work around this issue: we want to gather stat for one partition only and then
export it and import into the rest of partitions, (since the partitions have
similar volume and distributions).
So the following is our procedures: we want to export stats from P_20070817 and import into P_20070819. All procedures
complete sucessfully, but when we use the following sql statement to query on dba_tab_partitions for the being
imported partition (P_20070819), the last analyzed column still shows the old
date, we expect the difference since we import it with new stats (P_20070817). Am I right?
select * from dba_tab_partitions where table_name = ‘MB_FIX_MESSAGE’
and partition_name = ‘P_20070819′;
------------------
exec DBMS_STATS.CREATE_STAT_TABLE('SYS','MB_FIX_MESSAGE_STATS_TAB','SYSTEM');
exec dbms_stats.export_table_stats( 'TRDUSER', 'MB_FIX_MESSAGE',
'P_20070817','MB_FIX_MESSAGE_STATS_TAB','MY_P_20070817',TRUE, 'SYS');
exec dbms_stats.import_table_stats( 'TRDUSER', 'MB_FIX_MESSAGE',
'P_20070819','MB_FIX_MESSAGE_STATS_TAB','MY_P_20070817',TRUE, 'SYS');
no, procedure complete sucessfully.
Please help. do you think after import_table_stats, the last_analyzed column of dba_tab_partions should get
updated???
Thanks.
Megan
September 11th, 2007 at 2:32 pm
What version of Oracle? Do you collect histograms (column statistics)?
Are you saying that the procedure to create your statistics table, and then populate it fails? If so which user runs it?
Also - why do you think that statistics are collected on the whole table each night - do many of the partitions change each day?
I’ll have a think, but the answers to my questions may help me. By the way if you copy partition statistics between partitions you will get odd affects on the column stats for the partition key. And partition stats are only used by queries that access a single partition.
September 11th, 2007 at 4:03 pm
I fixed it by updating statistic table MB_FIX_MESSAGE_STATS_TAB c2 column to the partition name that we are going to import to.
problem is solved.
September 11th, 2007 at 8:35 pm
Ah, yes - the statistics table contains the name of the object that the statistics belong to! - It also contains the owner - this can cause problems if you want to copy stats between schemas
Another option if you are not using any column statistics is to use the dbms.stats.set_table_stats procedure and write values in directly
September 12th, 2007 at 10:02 pm
I don’t use export-import to move statistics from one partition to another or from one database to another. They are not really meant for that as you found out from all the problems you run into.
I use set_xxx_stats directly. You have much more control with it. In order to get a base I use export. It spits out the set_xxx_stats procedure calls for the statistics(unless you suppress it). All I need to do is extract them from the export file.