System Workload Statistics (Gather,Export and Import)

As noworkload system statistics are initialized to default values at the first database startup, we need to first gather system WORKLOAD statistics. This will gather statistics during the current workload (which is supposed to be representative of actual system I/O and CPU workload on the DB and need to be gathered when there is heavy/good workload on database)

 

a)    Gather it :

SQL> execute dbms_stats.gather_system_stats('start');

Once the workload window ends after 3,4.. hours or whatever, system statistics gathering need to be stopped using below:

SQL> execute dbms_stats.gather_system_stats('stop');

 

OR

We can use time interval (minutes) instead of issuing start/stop command manually:

SQL> execute dbms_stats.gather_system_stats('interval',60);

 

Please note that gathering workload system statistics doesn't put additional load into system.

 

Check the system values collected:
--------------------------------------------
sql> col pname format a20
sql> col pval2 format a40
sql> select * from sys.aux_stats$;

 

b)    Create STAT table and export the system stats :

 

SQL> exec dbms_stats.create_stat_table(stattab=>'STATS_TABLE_NAME',ownname=>'schema',tblspace => 'tablespace_name');

SQL> EXEC dbms_stats.export_system_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');

 

Example –

SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats',tblspace => 'USERS');

SQL> exec dbms_stats.export_system_stats(statown => 'SYS', stattab => 'prod_stats');

 

 

c)     Move statistics from one database to another, copy the statistics table to the second database using datapump and finally import the statistics into the second database

d)    Import the statistics into data dictionary :

Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. In order to have the optimizer use the statistics in a user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.

 

SQL> exec dbms_stats.import_system_stats(stattab => 'prod_stats',statown => 'SYS');