World of Oracle statistics

                          World of Oracle statistics 

è Difference between analyze and dbms_stats

The difference is described in the Note:114671.1

-- Analyze cannot process virtual columns

-- ANALYZE can't retain old staticstics, dbms_stats can.

-- analyze doesn't collect global statistics for partitioned tables

-- use ANALYZE to LIST CHAINED ROWS

-- DBMS_STATS is able to parallelize its workload

-- DBMS_STATS allows you transfer stats between servers

è stats history :

select * from DBA_OPTSTAT_OPERATIONS  where lower(operation) like '%system%'

select * from DBA_OPTSTAT_OPERATIONS where START_TIME like '%MAR-12%'

AND TARGET IN ( 'ADVCLM.TRX_BATCH','ADVCLM.TRX','ADVCLM.TRX_BATCH_HANDLER','ADVCLM.TRX_BATCH_GROUP','ADVCLM.IXML_STATUS','ADVCLM.IXML_CLAIM')

 order by START_TIME ;

connect / as sysdba

pause see the opstat operations

col start_time for a32

col end_time for a32

col operation for a30

col target for a10

select *

from  dba_optstat_operations;

pause see stats history

col owner for a14

col table_name for a16

col partition_name for a10

col subpartition_name for a10

break on owner skip 1

select *

from dba_tab_stats_history;

clear break

############

DBA_OPTSTAT_OPERATIONS  --> see all gather stats run using dbms_stats

select * from DBA_OPTSTAT_OPERATIONS  where lower(operation) like '%system%'

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

---------------------------------------------------------------------------

26-NOV-10 10.23.55.988228000 PM +04:00

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name=’SM/OPTSTAT’;

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES

---------------------------------------------------------------- ------------------

Server Manageability - Optimizer Statistics History                          192576

SQL> exec dbms_stats.alter_stats_history_retention(40);

PL/SQL procedure successfully completed.

DBMS_STATS.GET_STATS_HISTORY_RETENTION

 RETURN NUMBER;

DBMS_STATS.FLUSH_SCHEMA_MONITORING_INFO (

   ownname VARCHAR2 DEFAULT NULL);

DBMS STATS.ALTER STATS HISTORY RETENTION

This procedure changes the statistics history retention value. Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure.

Syntax

DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (

   retention       IN     NUMBER);

Parameter        Description

retention         

The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:

0 - old statistics are never saved. The automatic purge will delete all statistics history

1 - statistics history is never purged by automatic purge.

NULL - change statistics history retention to default value

Usage Notes

To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

Table Stats History

 -- All stats history for user objects

 -- Sort: most recent at top

 SELECT do.owner,

        do.object_name,

        oth.*

   FROM SYS.WRI$_OPTSTAT_TAB_HISTORY oth join dba_objects do on oth.obj#=do.object_id

  WHERE OWNER NOT IN ('SYS','SYSTEM')

  ORDER BY savtime desc;

-- Get historical Statistics info for 1+ Tables

WITH TableList as (select object_id, object_name

               from dba_objects

              where object_type='TABLE'

                and object_name IN ('TABLE_1','TABLE_2',...))

select OBJECT_NAME as TABLE_NAME,

       TO_CHAR(ANALYZETIME,'MM/DD/YYYY HH24:MI') as AnalyzeTime_Formatted,

       OTH.*

  From SYS.WRI$_OPTSTAT_TAB_HISTORY OTH, TableList

 where OTH.OBJ# = TableList.OBJECT_ID

 ORDER BY table_name, analyzetime;

è default database statistics collection using  statistics_level:

Ø  statistics_level :

Ÿ default :

No advisory stats data is collect . we can set external parameter like time_statistics , db_cache_advice , etc.

Ÿ typical  :

This is default value . data is collectd for segment-level statistics , timesd statistics and all advisories

The value of all other statistics collection parameter are overridden .

Ÿ all :

All typical level statistics data , timed operation system statistics , row source execution stats are collected . value for other stats collection parameter are overridden ,

Select statistics_name, activation_level from v$statistics_level order by 2 ;

conn / as sysdba

pause

col activation_level for a10

col statistics_name for a38

col statistics_view_name for a30

desc v$statistics_level

pause

select activation_level, statistics_name, statistics_view_name,

session_status,session_settable,system_status

from v$statistics_level

order by 1;

EXEC DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');

exec dbms_stats.set_param('METHOD_OPT','FOR ALL INDEXED COLUMNS SIZE 254');

SELECT dbms_stats.get_param('METHOD_OPT') FROM dual;

exit

END

è table stats :

EXEC DBMS_STATS.GATHER_TABLE_STATS( OWNNAME=>'&OWNER',TABNAME=>'&TAB', partname => '&PART',DEGREE=>24, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT',NO_INVALIDATE=>FALSE);

execute dbms_stats.gather_table_stats(ownname => 'RELODS', tabname =>'WEBTOKEN', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO')

dbms_stats.gather_table_stats(ownname=>'VISION',tabname=>'PATIENT_PAYMENT',partname=>null,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,block_sample=>false,method_opt=>'for all columns size 1',

degree=>1,granularity=>'DEFAULT',cascade=>true,stattab=>null,statid=>null,statown=>null,no_invalidate=>false);

set pagesize 0

set heading off

set feedback off

spool c:\temp\alyz.sql

select

'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME||' ESTIMATE STATISTICS;'

from dba_tables

--where owner not in ('SYS', 'SYSTEM')

where owner = 'BMR'

order by owner;

spool off

set feedback on

set heading on

@@ C:\temp\alyz

@@ d:\boban\scr\validate.sql

set pages 140

set lines 180

select 'exec dbms_stats.gather_table_stats('''||TABLE_OWNER||''','''||TABLE_NAME||''',method_opt=>''for columns status size 75'',cascade=>true)'

from dba_ind_columns where column_name='STATUS' and column_position=1

/

select 'exec dbms_stats.gather_table_stats(ownname=>'||chr(39)||OWNER||chr(39)||',tabname=>'||chr(39)||table_name||chr(39)||',estimate_percent=>'||100||',degree=>'||4||',cascade=>TRUE );'

from dba_tables where owner=UPPER ('AMS');

select 'analyze table ''||owner||''.''|table_name||'' compute statistics;' from dba_tables where owner='HR';

analyze table abcd estimate statistics sample 5 percentage for all indexed columns ;

analyze index  abcd estimate statistics sample 5 percentage fro all indexed columns ;

analyze table emp compute statistics for all indexed columns ;

analyze table abcd delete statistics ;

è 11g set stats preference:

exec dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');

SELECT dbms_stats.get_prefs('STALE_PERCENT','HAFEEZ','SECURE')FROM dual;

exec dbms_stats.set_table_prefs('SH','CUSTOMERS','PUBLISH','false');

select * from DBA_TAB_STAT_PREFS ;

PREFERENCES ::

ESTIMATE_PERCENT

NO_INVALIDATE

METHOD_OPT

GRANULARITY

INCREMENTAL

PUBLISH

STALE_PERCENT

scope :

DATABASE LEVEL

SCHEMA LEVEL

TABLE LEVEL

STATEMENT LEVEL

GLOBAL LEVEL

Below can be used to set global preference 

SQL> column sname format a32
SQL> column spare4 format a32
SQL> select sname,spare4 from OPTSTAT_HIST_CONTROL$;

SNAME                 SPARE4
-------------------------------- --------------------------------
SKIP_TIME
STATS_RETENTION
TRACE                 0
DEBUG                 0
SYS_FLAGS             1
APPROXIMATE_NDV          TRUE
CASCADE              DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT         DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                 2
METHOD_OPT             FOR TABLE FOR ALL INDEXED COLUMN
                 S SIZE 254

NO_INVALIDATE             DBMS_STATS.AUTO_INVALIDATE
GRANULARITY             AUTO
PUBLISH              TRUE
STALE_PERCENT             10
INCREMENTAL             FALSE
INCREMENTAL_INTERNAL_CONTROL     TRUE
AUTOSTATS_TARGET         AUTO
CONCURRENT             TRUE

18 rows selected

select dbms_stats.get_prefs('method_opt') a from dual;

exec dbms_stats.set_global_prefs('method_opt','FOR ALL COLUMNS SIZE 1')

select dbms_stats.get_prefs('method_opt') a from dual;

è 11g pending stats :

SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

SQL> show parameter pending

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SAPSR3','MYTEST');

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_STATISTICS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_STATISTICS where table_name = 'MYTEST';

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_PENDING_STATS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_PENDING_STATS where table_name = 'MYTEST';

SQL> exec dbms_stats.set_table_prefs('SAPSR3','MYTEST','PUBLISH', 'false');

SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'SALES' ) FROM DUAL;

SQL> exec DBMS_STATS.SET_TABLE_STATS('SAPSR3','MYTEST',numblks=>'5000');

SQL> exec DBMS_STATS.SET_INDEX_STATS('SAPSR3','MYTEST_I',numlblks=> '5000');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_STATISTICS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_STATISTICS where table_name = 'MYTEST';

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_PENDING_STATS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_PENDING_STATS where table_name = 'MYTEST';

alter session set optimizer_use_pending_statistics = true;

Execute/ test / check plan   your workload from the same session.

exec dbms_stats.publish_pending_stats('SH','CUSTOMERS');

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;

alter session set optimizer_use_pending_statistics=FALSE;

SQL> exec dbms_stats.delete_pending_stats('ARUP','RES');

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_STATISTICS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_STATISTICS where table_name = 'MYTEST';

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_PENDING_STATS where table_name = 'MYTEST';

SQL>  select INDEX_NAME, LEAF_BLOCKS from DBA_IND_PENDING_STATS where table_name = 'MYTEST';

Note: To analyze the differences between the pending statistics and the current ones, you could

export the pending statistics to your own statistics table and then use the new

DBMS_STAT.DIFF_TABLE_STATS function.

è compare stats :

1)

dbms_stat.diff_table_stats_in_stattab:  Here is an example of the diff_table_stats procedure for statistics that have been saved into a table:

set longchunksize 99999;

select * from table(

   dbms_stat.diff_table_stats_in_stattab( 'SCOTT', 'EMP', 'STAT_TAB_OLD', 'STAT_TAB_NEW'));

2)

dbms_stat.diff_table_stats_in_history:  Here is an example of the diff_table_stats procedure for statistics that have been collected at a specific data in history:

set longchunksize 99999;

select *

from table(

   dbms_stats.diff_table_stats_in_history(

      ownname => 'SCOTT',

      tabname => 'EMP',

      time1 => systimestamp,

      time2 => to_timestamp(to_date('2012-04-12','yyyy-mm-dd')),

      pctthreshold => 10));

3)

dbms_stat.diff_table_stats_in_pending:   Here is an example of the diff_table_stats procedure for statistics that are current (or older with the time_stamp argument), compared with statistics that are in a pending state:

set longchunksize 99999;

select *

from table(

   dbms_stats.diff_table_stats_in_pending(

      ownname => 'SCOTT',

      tabname => 'EMP',

      time_stamp => NULL,

      pctthreshold => 10));

Note that the pctthreshold parameter is used to filter-out report data, showing only data that exceeds the threshold value in change, as a percentage.  For example, pctthreshold=10 will only report on statistics that are greater then 10% changed from the baseline statistics.

Compare the current object statistics with the previous ones.

SQL> SELECT *

  2  FROM table(dbms_stats.diff_table_stats_in_history(

  3               ownname => user,

  4               tabname => 't',

  5               time1 => localtimestamp,

  6               time2 => localtimestamp-to_dsinterval('0 00:00:15'),

  7               pctthreshold => 0

  8             ));

STATISTICS DIFFERENCE REPORT FOR:

.................................

TABLE         : T

OWNER         : OPS$CHA

SOURCE A      : Statistics as of 21-SEP-08 06.39.37.597595 PM +02:00

SOURCE B      : Statistics as of 21-SEP-08 06.39.22.597595 PM +02:00

PCTTHRESHOLD  : 0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:

.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE

...............................................................................

T                           T   A   1500000    40091      183        500000

                                B   500000     8614       116        500000

T_FEB_2008                  P   A   1000000    31477      216        1000000

                                B   NO_STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:

.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ

...............................................................................

D               A   31      .032258064 NO   0       8    786C0 786C0 1500000

                B   31      .032258064 NO   0       8    786C0 786C0 500000

ID              A   500000  .000002    NO   0       6    C4020 C404  1500000

                B   500000  .000002    NO   0       6    C4020 C4023 500000

N               A   11      .000001998 YES  749500  2    80    C10C  750500

                B   11      .000001998 YES  249750  2    80    C10B  250250

PAD             A   1       1          NO   0       168  2A2A2 2A2A2 1500000

                B   1       1          NO   0       101  2A2A2 2A2A2 500000

                              PARTITION: T_FEB_2008

                              .....................

D               A   29      .034482758 NO   0       8    786C0 786C0 1000000

                B   NO_STATS

ID              A   1000000 .000001    NO   0       6    C4030 C404  1000000

                B   NO_STATS

N               A   12      .000000999 YES  499750  2    80    C10C  500250

                B   NO_STATS

PAD             A   1       1          NO   0       201  2A2A2 2A2A2 1000000

                B   NO_STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:

.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ

...............................................................................

                                    INDEX: I

                                    ........

I               I   A   750500  1221    12      101   3332  39991   2   750500

                    B   250250  407     11      37    778   8565    1   250250

T_FEB_2008      P   A   500250  814     12      67    2618  31426   2   500250

                    B   NO_STATS

è stale statistics :

-->  statistics are consider to be stale when there is a 10% change

--> Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use     the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept     in the memory.

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

exec dbms_stats.flush_database_monitoring_info;

select * from sys.dba_tab_modifications where table_name = 'T_STAT';

select stale_stats from sys.dba_tab_statistics where table_name = 'T_STAT';

SELECT t.table_name,

           last_analyzed,

           SUM(inserts),

           SUM(updates),

           SUM(deletes)

      FROM user_tables t,

           user_tab_modifications m

     WHERE t.table_name = m.table_name

       AND timestamp > last_analyzed

       AND t.table_name = <your table name>

  GROUP BY t.table_name,

           last_analyzed

  /

è lock table and partition  stats :

exec dbms_stats.lock_partition_stats('OKC997','LOCATION_DETAILS','M201202');

lock/unlock stats :

begin

                DBMS_STATS.unlock_table_stats('INFO10','JRL_AG_DIAGRAMME');

                DBMS_STATS.GATHER_TABLE_STATS(

                OwnName        => 'INFO10'

,TabName        => 'JRL_AG_DIAGRAMME'

,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

,Degree            => NULL

,Cascade           => TRUE

,No_Invalidate     => FALSE);

                DBMS_STATS.lock_table_stats('INFO10','JRL_AG_DIAGRAMME');

END;

/

exec dbms_stats.lock_partition_stats('OKC997','LOCATION_DETAILS','M201202');

è copy partition stats

exec dbms_stats.copy_table_stats(ownname => 'OKC997',tabname =>'ISA_DETAILS',srcpartname =>'M201201',dstpartname =>'M201202');

exec dbms_stats.copy_table_stats(ownname => 'OKC997',tabname =>'ISA_DETAILS',srcpartname =>'M201201',dstpartname =>'M201202');

è set table stats :

exec dbms_stats.set_table_stats( user, 'MAP', numrows => 100000, numblks => 10000 );

SQL> exec DBMS_STATS.SET_TABLE_STATS('SAPSR3','MYTEST',numblks=>'5000');

SQL> exec DBMS_STATS.SET_INDEX_STATS('SAPSR3','MYTEST_I',numlblks=> '5000');

SQL> exec dbms_stats.set_table_stats('ADAM','SALES',numrows=>100,numblks=>1)

SQL> select num_rows,blocks from user_tables;

è gather partition stats :

exec DBMS_STATS.GATHER_TABLE_STATS('CLM','CLM_FIXED_FORMAT_INFO',estimate_percent=>10,cascade=>TRUE,degree=>12, granularity=>'PARTITION',method_opt=>'for all columns size 1',  partname=>'P201201');

exec DBMS_STATS.GATHER_TABLE_STATS('CLM','CLM_HOME_HEALTH',estimate_percent=>10,cascade=>TRUE,degree=>12, granularity=>'PARTITION',method_opt=>'for all columns size 1',  partname=>'P201201');

exec DBMS_STATS.GATHER_TABLE_STATS('CLM','AFILE',estimate_percent=>10,cascade=>TRUE,degree=>12, granularity=>'PARTITION',method_opt=>'for all columns size 1',  partname=>'P201112' );             

è gather index stats :

Execute DBMS_STATS.GATHER_INDEX_STATS (‘HR’,’LOC_COUNTRY_IX’);

Alter index hr.loc_country_ix rebuild compute statistics;

execute dbms_stats.gather_index_stats(ownname=>user, indname=>'i', granularity=>'global')

dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');

dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);

dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');

end loop;

end;

/

è table stats backup

select 'exec dbms_stats.create_stat_table(ownname => ' || '''PAYVAR''' || ', STATTAB  =>' || '''PAYVAR_'  || to_char(sysdate,'dd_MON_yyyy') ||  ''');' from dual;

EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname =>'RDB',tabname => 'D_PAYER', stattab => 'STAT_TAB',cascade => true,statown=>'RDB',statid=>'Feb18');

Statid : use the current date for easy identification

select 'exec dbms_stats.export_schema_stats(ownname => ' || '''PAYVAR''' || ', STATTAB =>' || '''PAYVAR_'  || to_char(sysdate,'dd_MON_yyyy') || ''');' from dual;

EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname =>'RDB',tabname => 'D_PAYER', stattab => 'STAT_TAB',cascade => true,statid=>'Feb18', no_invalidate => false);

exec dbms_stats.import_schema_stats ( ownname => 'hr' , stattab => 'hr' , statid => 'name_21nov09' , statown => 'own' );

è dictionary and fixed table stats :

EXEC DBMS_STATS.gather_dictionary_stats;

Identify if data dictionary statistics have been collected:

If there are performance problems due to large data dictionary access, it is advisable to check if data dictionary statistics have been collected. It can be easily displayed by querying data dictionary tables belonging to sys.

select table_name, last_analyzed

from dba_tables where owner='SYS'

and table_name='FET$';

This other query is useful to know how many sys tables have statistics:

select distinct trunc(last_analyzed), count(*)

from dba_tables

where owner='SYS'

group by trunc(last_analyzed)

order by 1 ;

Fixed object stats :

EXEC DBMS_STATS.gather_fixed_objects_stats;

execute  dbms_stats.gather_fixed_objects_stats();

It is important to make certain that the fixed object statistics are collected.  If the statistics are not collected, there is a strong chance that some queries of Oracle Database views will be slow, or even result in the crashing of the user’s session

to check if fixed object stats is not gathered :

-------------------------------------------------

select * from tab_stats$;

è database stats :

Exec dbms_stats.gather_database_stats(dbms_stats.auto_sample_size,method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>TRUE);

sqlplus -s /nolog << EOF

   connect system/$PWD@$TNS

   set feedback on

   set timing on

   set echo on

   BEGIN

     DBMS_STATS.GATHER_DATABASE_STATS(

       ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,

       OPTIONS=>'GATHER STALE',

       METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT',

       DEGREE=>4

    );

   END;

/

   BEGIN

     DBMS_STATS.GATHER_DATABASE_STATS(

       ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,

       OPTIONS=>'GATHER EMPTY',

       METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT',

       DEGREE=>4

    );

   END;

/

To gather the dictionary stats, execute one of following:-
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS

è system stats ( workload/ non-worklod ) :

System stats value :

exec   dbms_stats.gather_system_stats('START')

exec   dbms_stats.gather_system_stats('STOP')

select sname,pname,  pval1 , pval2 from   sys.aux_stats$  where  sname like 'SYSSTATS%' ;

select (select pval1

         from   sys.aux_stats$

          where  sname = 'SYSSTATS_MAIN' and

                 pname = 'MBRC') *

       (select to_number(value) block_size

         from   v$parameter

         where  name = 'db_block_size') /

      (select pval1

        from   sys.aux_stats$

         where  sname = 'SYSSTATS_MAIN' and

                pname = 'MREADTIM')

         /1024 sys_stat_mread_speed

from dual

  /

  

#########################  work lod stats ###################

Gathering Workload Statistics   :::::::::::::::::::

To gather workload statistics, either:

Run the dbms_stats.gather_system_stats('start') procedure at the beginning of the workload window, then the dbms_stats.gather_system_stats('stop') procedure at the end of the workload window.

Run dbms_stats.gather_system_stats('interval', interval=>N) where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics, run dbms_stats.delete_system_stats(). Workload statistics will be deleted and reset to the default noworkload statistics.

Workload statistics, this type of statistic takes into account the actual load on the system at a certain time interval. To collect this type of statistics gathering  mode INTERVAL  or  START and STOP is used by procedure DBMS_STATS.GATHER_SYSTEM_STATS.

SQL> begin

  2  sys.dbms_stats.gather_system_stats(

  3                                     gathering_mode=>'INTERVAL',

  4                                     interval=>720

  5                                    );

  6  end;

  7  /

SQL> exec sys.dbms_stats.gather_system_stats('START');

-- After some time

SQL> exec sys.dbms_stats.gather_system_stats('STOP');

mbrc

mreadtim

sreadtim

cpuspeed

maxthr

slavethr

###################  noworkload stats #################

Optimizer system statistics parameters computed by Noworkload collection are

cpuspeedNW

ioseektim

iotfrspeed

SQL> exec sys.dbms_stats.gather_system_stats();

è upgrade stats table :

exec dbms_stats.upgrade_stat_table('SYS','STAT_TABLE');

GIS_ADMIN

GIS_CC

SYS

SQL> BEGIN dbms_stats.export_schema_stats('GIS_ADMIN','STAT_TABLE'); EN

D;

*

ERROR at line 1:

ORA-20002: Version of statistics table GIS_ADMIN.STAT_TABLE is too old.  Please

try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 12222

ORA-06512: at line 1

Elapsed: 00:00:06.09

06:00:08 SQL> BEGIN dbms_stats.export_schema_stats('GIS_CC','STAT_TABLE'); END;

*

ERROR at line 1:

ORA-20002: Version of statistics table GIS_CC.STAT_TABLE is too old.  Please

try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 12222

ORA-06512: at line 1

Elapsed: 00:00:02.20

06:00:10 SQL> BEGIN dbms_stats.export_dictionary_stats('STAT_TABLE'); END;

*

ERROR at line 1:

ORA-20002: Version of statistics table SYS.STAT_TABLE is too old.  Please try

upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 26704

ORA-06512: at line 1

Elapsed: 00:00:01.93

  

è schema stats :

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'COND_DBA',ESTIMATE_PERCENT=>100, DEGREE=>4, CASCADE=>TRUE);

exec dbms_stats.gather_schema_stats

(ownname =>'DATA_FR',estimate_percent =>100,method_opt=>'for all indexed columns size 1',CASCADE=>TRUE,degree=>4);

EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

exec dbms_utility.analyze_schema('PIDB','COMPUTE');

exec dbms_stats.gather_schema_stats('MUREXSBY',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS size 1',options=>'GATHER');

EXEC DBMS_STATS.gather_schema_stats('CP',DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8 ,cascade=>TRUE,granularity =>'ALL');

EXEC DBMS_STATS.delete_schema_stats('SCOTT');

set lines 160

set pages 0

set echo off

set feedback off

spool gather_schema_stat_tn.sql

select 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||USERNAME||''',ESTIMATE_PERCENT=>10,METHOD_OPT=>''FOR ALL COLUMNS SIZE REPEAT'',CASCADE=>TRUE)'

from dba_users where username not in ('SYS','SYSTEM','SNAPXIUS','OUTLN','DBSNMP','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB','ANONYMOUS','WKSYS','WKPROXY','ODM','ODM_MTR','OLAPSYS')

/

spool off

set echo on

set pages 100

set timing on

set time on

set feedback on

@gather_schema_stat_tn.sql

--exit

exec dbms_utility.analyze_schema(schema=>'&schema_owner',method=>'&method', estimate_rows=>&est_rows, estimate_percent=>&est_pct);

  

 è Restoring old stats gathered by Oracle from 10g

old version of stats are saved automatically in 10g before  gatering new stats .

so we can restore old stats  using below . use DBA_OPTSTAT_OPERATIONS and  DBA_TAB_STATS_HISTORY  to see stats history .

exec dbms_stats.restore_table_stats({owner} ,{table name}, {timestamp});

alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd:hh24:mi:ss';

select table_name,stats_update_time from user_tab_stats_history;

begin

dbms_stats.restore_table_stats('ADAM','SALES',

to_timestamp('2010-05-18:09:47:38','yyyy-mm-dd:hh24:mi:ss'));

end;

/

alter system flush shared_pool;