Solved

Can statistics be copied between tables

Posted on 2009-05-20
5
1,775 Views
Last Modified: 2013-12-18
Is it possible to copy statistics between tables, specifically large partitioned tables?

We are using Oracle 10g (10.2.0.4.0) which has much of the functionality included in the first release of 11g (as I've just found out).

We have a situation where large partitioned summary tables are rebuilt every night from source data using the following sequence of events:
   create NEW_MYTAB table from source data
   delete OLD_MYTAB table
   rename MYTAB table to OLD_MYTAB table
   rename NEW_MYTAB table to MYTAB
The result is that the newly created table is now called MYTAB, but it doesn't have any statistics on it.  Currently it takes about an hour to gather stats (partition and global) on this table.  I would like to copy the statistics from the OLD_MYTAB table to the MYTAB table as these will be accurate enough.

One thought is to export the OLD_MYTAB statistics into a stats table, update the "C1" column, which appears to be the table name (from OLD_MYTAB to MYTAB), and then import the statistics into the MYTAB table.

I was wondering if there was a better way of doing this.
   
0
Comment
Question by:Milleniumaire
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24434675
Yes, check out the DBMS_STATS package.
 
 
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 400 total points
ID: 24434709

1) Export MYTAB stats.
2) Do your thing (Create, Delete, Rename, ...)
3) Import MYTAB stats
 
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 24435372
Oracle Data Pump can export statistics only but there are some complaints:

  Importing statistics takes unreasonable amounts of time. We had to export and import one schema. Collecting statistics on this schema takes around 5 minutes. Importing the statistics using Data Pump took over 20 minutes. Oracles documentation fails to mention this little bit of information. I really hope this issue is fixed in 11g.

Work around: import with exclude=statistics flag and collect statistics later.

So a workaround is to  copy the statistics to a stattab using dbms_stats, export that table, import the table in the other database, and use dbms_stats to import the statistics in the other dictionary.


create stats table to hold exported stats data.
EXEC DBMS_STATS.create_stat_table('SCHEMA1','ST_TABLE');

export stats to stats table
EXEC DBMS_STATS.export_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');

transfer the stats table ST_TABLE to destinate location using method of choice, like exp/imp, create tabel as select ..., sqlplus copy etc.

import the stats into schema
EXEC DBMS_STATS.import_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24435387
Sorry MikeOM_DBA, I didnt read carefully your post!
Excuse me!
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24435984
No problemo, I use this a lot.
I compute statistics in the Reporting database, export to statstab. expdp/impdp stats table into production and import stats -- works like a charm.
 
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now