Monday, November 18, 2013

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;

No comments:

Post a Comment