Statistiques d'une instance sur l'autre

**genio** · 17/10/2006, 10h02

Bonjour les forumistes...
est-il possible (comme en DB2) de prendre les statistiques d'une instance A et de les charger sur une instance B...

Merci pour vos réponses...

**LeoAnderson** · 17/10/2006, 10h09

Oui, on peut !

http://download-east.oracle.com/docs...s.htm#i1036059

EXPORT_SCHEMA_STATS Procedure

This procedure retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics tables identified by stattab.

Syntax

DBMS_STATS.EXPORT_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);

Parameters

Table 103-23 EXPORT_SCHEMA_STATS Procedure Parameters
Parameter Description
ownname Name of the schema
stattab User statistics table identifier describing where to store the statistics
statid Identifier (optional) to associate with these statistics within stattab
statown Schema containing stattab (if different than ownname)

**genio** · 17/10/2006, 11h17

Merci beaucoup... je testerai ça demain !

PS : As-tu un exemple ?

**LeoAnderson** · 17/10/2006, 11h30

Google est ton ami !

http://www.oracle-base.com/articles/...Statistics.php

Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
      SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
      SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
      SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
      SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');