job de statistic par defaut:

job de statistic  par defaut:
SQL>  SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

Faire  un disable

SQL> BEGIN
  DBMS_AUTO_TASK_ADMIN.disABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/    2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  DISABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

statistic oracle

statistic  oracle  :

SELECT dbms_stats.get_stats_history_retention() AS retention FROM dual; --31  jours par defaut
dbms_stats.alter_stats_history_retention(retention => 14):
dbms_stats.purge_stats(before_timestamp => systimestamp-14)

info  sur les stats
SELECT operation, start_time,
(end_time-start_time) DAY(1) TO SECOND(0) AS duration
FROM dba_optstat_operations  where trunc(start_time)=trunc(sysdate)
ORDER BY start_time DESC;

===================
export/import  stats
===================
Export / import de statistiques
On considère que l'on a une base de Production, avec plein de données dedans (...), et une base de développement, quasiment vide; ce qu'on souhaite, c'est faire en sorte que les plans d'exécutions de nos requêtes soient identiques. Comme on utilise l'optimiseur statistique (ben oui, on est en 10g, hé!), on va pouvoir gruger les traitements de la base de développements en leur faisant croire qu'il y a tout plein de données.

Exemple ici pour une table MATABLE, identique sur les 2 bases. On peut bien-sûr faire la même chose au niveau d'un schéma (dbms_stats.export_schema_stats).


Sur la base de données source
- calcul des stats
> analyze table matable compute statistics;

- création de la table de statistiques
exec dbms_stats.create_stat_table('SAPR3','stat_ARFCRSTATE');

- sauvegarde des stats dans la table MATABLEDESTATS
exec dbms_stats.EXPORT_TABLE_STATS(ownname=>'SAPR3',tabname=>'ARFCRSTATE',stattab=>'stat_ARFCRSTATE');
exec dbms_stats.EXPORT_SCHEMA_STATS(ownname=>'BALIFR',stattab=>'STATTAB');

exec dbms_stats.IMPORT_TABLE_STATS(ownname=>'SAPR3',tabname=>'ARFCRSTATE',stattab=>'stat_ARFCRSTATE');

- export de la table de sauvegarde des statistiques
$ exp MG/mgmg file=exp_matabledestats.dmp log=exp_matabledestats  tables=radairprd.matabledestats


Sur la base de données cible
- import de la table de sauvegarde des statistiques
$ imp MG/mgmg file=exp_matabledestats.dmp log=imp_matabledestats.log tables=ods.matabledestats

- restauration des statistiques
> exec dbms_stats.import_table_stats(ownname=>'ODS',tabname=>'ODSPNR_DET_PASSAGER',stattab=>'MATABLEDESTATS');


Trucs & astuces
- suppression d'une table de statistiques
> exec dbms_stats.drop_stat_table('MONUSER','MATABLEDESTATS);

- suppression du contenu d une table de statistiques
> exec dbms_stats.delete_table_stats(ownname=>'MONUSER',tabname=>'MATABLE',stattab=>'MATABLEDESTATS');

exec dbms_stats.LOCK_table_stats(ownname=>'MONUSER',tabname=>'MATABLE',stattab=>'MATABLEDESTATS');
exec dbms_stats.unLOCK_table_stats(ownname=>'MONUSER',tabname=>'MATABLE',stattab=>'MATABLEDESTATS');
exec dbms_stats.unLOCK_schema_stats(ownname=>'AIR_PP');


===================================
dbms_stats get  param
===================================

select   dbms_stats. ('autostats_target')   from  dual;
select    dbms_stats.get_param('estimate_percent')   from   dual;              
select    dbms_stats.get_param('method_opt')         from   dual;              
select    dbms_stats.get_param('degree')             from   dual;              
select    dbms_stats.get_param('cascade')            from   dual;              
select    dbms_stats.get_param('granularity')        from   dual;              
select    dbms_stats.get_param('autostats_target')   from   dual;              
select  dbms_stats.get_param('STALE_PERCENT')  from  dual;

set  params :
exec  dbms_stats.set_param('CASCADE', 'TRUE');
exec  dbms_stats.set_param('ESTIMATE_PERCENT','30');
exec  dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT'); --repeat ==>   si  histogram  il  recalcule avec  histogram
exec  dbms_stats.gather_schema_stats(ownname =>'REFMODEBATCH_QUALIF');
exec dbms_stats.set_param('STALE_PERCENT','2');



stats sans  histograme
exec dbms_stats.gather_schema_stats(ownname=>'PBSTSC_PI' ,cascade=>TRUE,estimate_percent=> 100,method_opt=>'FOR ALL COLUMNS SIZE 1',OPTIONS=>'GATHER');

stats  avec  historgrame  si la  table a deja un historgrame:
exec dbms_stats.gather_schema_stats(ownname=>'CREDO_INT',cascade=>TRUE,estimate_percent=> 30,method_opt=>'FOR ALL COLUMNS SIZE REPEAT',OPTIONS=>'GATHER STALE');

stats  avec  histogram sur une colonne:
exec dbms_stats.gather_table_stats(ownname =>'SAPR3',tabname =>'ARFCRSTATE',estimate_percent =>30,degree =>DBMS_STATS.DEFAULT_DEGREE
,method_opt =>'FOR  COLUMNS CODEVALEURISIN SIZE 254',granularity =>'ALL',cascade =>true);

enlever  les stat sur les tables:
 select 'dbms_stats.delete_table_stats(ownname => ''ADMDEO'',tabname =>''' || A.TABLE_NAME||''');'
  from   sys.dba_tables a where   a.owner='ADMDEO'   and and A.NUM_ROWS=0
 select distinct 'exec dbms_stats.gather_index_stats(ownname => '''|| owner||''',indname=>'''||index_name||''',estimate_percent=>100,stattab=>''STATTAB'||owner||''',statown=>'''||owner||''');'

stat system  a partir de  10g :
exec dbms_stats.gather_system_stats('noworkload');
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;


select    sname    , pname    , pval1    , pval2    from    sys.aux_stats$;

=========================================
-- Hakan
-- restauration de stats (10g)                                                                                                                    
-- pour voir la durée demandé                                                                                                                    
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;                                                                                          
-- pour voir la durée max a laquel on peut faire une restauration                                                                                
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;                                                                                      
                                                                                                                                                 
                                                                                                                                                 
select TABLE_NAME,PARTITION_NAME,STATS_UPDATE_TIME from DBA_TAB_STATS_HISTORY  where OWNER='RADAIR_QA'   and  table_name='E_INTERLINE_MSGS' order by STATS_UPDATE_TIME;
                                                                                                                                                 
select * from DBA_OPTSTAT_OPERATIONS order by START_TIME                                                                                          
                                                                                                                                                 

select sname,sval1,sval2,spare1,spare4 from sys.OPTSTAT_HIST_CONTROL$;                                                                                                                              

==============================

stats  oracle  table volatile

==============================

The first option is to set statistics to NULL. As part of Oracle’s query optimization, any table

with no statistics will have them generated dynamically via the dynamic sampling feature. This

“just-in-time” statistics generation ensures that no query will be executed without statistics. The

parameter OPTIMIZER_DYNAMIC_SAMPLING needs to be set to a value of 2 (the default) or higher

to enable this feature.

SQL> exec DBMS_STATS.DELETE_TABLE_STATS('ADMDEO','TRANSACTION_DATA');

PL/SQL procedure successfully completed

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('BUTERTB', 'VOLATILE_TABLE');

========================

histogram   dbms_stats

========================

dbms_stats.gather_table_stats(<schema_name>, <table_name>,

METHOD_OPT => FOR COLUMN SIZE <integer> <column_name>

exple histogram sur la  colonne  status  avec 254  buckets

exec  dbms_stats.delete_table_stats(ownname =>'ODS',tabname =>'ODSPDP_DOC_JOURNAL_PROD_HISTO');

exec dbms_stats.gather_table_stats(ownname =>'RADAIR_PPR',tabname =>'LOOKUP',cascade =>false ,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,degree =>DBMS_STATS.DEFAULT_DEGREE,

method_opt =>'FOR  COLUMNS STATUT SIZE 254 ',granularity =>'ALL',cascade =>true,stattab =>'STATTAB');

verif  histogram : 

SELECT a.owner,a.table_name,column_name, num_distinct, num_buckets,a.LAST_ANALYZED,DENSITY,num_rows,HISTOGRAM

   FROM dba_TAB_COL_STATISTICS a,dba_tables  b WHERE num_buckets>1   and A.table_name=b.table_name   and  A.owner ='RADAIR_PPR' ; and  a.table_name='LOOKUP'; 

   and b.table_name'CX_USER';

SELECT table_name,ENDPOINT_NUMBER, ENDPOINT_VALUE               

     FROM DBA_HISTOGRAMS                                   

    WHERE TABLE_NAME ='CX' AND COLUMN_NAME='STATUT' 

     ORDER BY ENDPOINT_NUMBER; 

 SELECT sum(case when max_cnt > 2 then 1 else 0 end) histograms,

  sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms

 FROM (

  SELECT table_name, max(cnt) max_cnt

  FROM (

  SELECT table_name, column_name, count(*) cnt

  FROM dba_tab_histograms

  GROUP BY table_name, column_name

  ) GROUP BY table_name

 );

              

              

select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed  

from dba_tab_col_statistics  where  owner='BSG' order by 1,2;   

SELECT table_name, sample_size, num_rows,

round(sample_size/num_rows*100,1) AS "%"

FROM dba_tables

WHERE num_rows > 0

ORDER BY table_name;

SELECT sname AS parameter, nvl(spare4,sval1) AS default_value

FROM sys.optstat_hist_control$

WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE',

'METHOD_OPT','NO_INVALIDATE','GRANULARITY');

wich table  are lock

SELECT table_name

FROM user_tab_statistics

WHERE stattype_locked IS NOT NULL;

===================================

OPTSTAT  historique  des rowcount

au  moment des stat oracle

===================================

pour les  tables  

select ie.table_name, ih.savtime, ih.rowcnt, ih.BLKCNT---*8192 "taille octets" 

from sys.WRI$_OPTSTAT_TAB_HISTORY ih, dba_objects ob, dba_tables ie

where ih.obj# = ob.object_id

and ob.object_name = ie.table_name

and ob.owner = ie.owner

and ob.object_type = 'TABLE'

--and ie.table_name = 'ADM_USER_ACTION'

and   ob.owner='RADAIR_DEV'

order by 2;

Pour les  indexes :

select ie.index_name, ih.savtime, ih.rowcnt, ih.blevel, ih.leafcnt

from sys.WRI$_OPTSTAT_IND_HISTORY ih, dba_objects ob, dba_indexes ie

where ih.obj# = ob.object_id

and ob.object_name = ie.index_name

and ob.owner = ie.owner

and ob.object_type = 'INDEX'

and ie.table_name = 'T5'

order by 2;

====================================================

How to find the tables that have stale statistics?

====================================================

DECLARE

ObjList dbms_stats.ObjectTab;

BEGIN

dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');

FOR i in ObjList.FIRST..ObjList.LAST

LOOP

dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);

END LOOP;

END;

/

================================

Stats  individual  thresholds

================================

In Oracle database 11g there is a new procedure in the DBMS_STATS package we can use to even set individual thresholds on segment level.

Here is  how it works:

lutz@ora11gBETA5 _SQL > exec dbms_stats.set_table_prefs('HR', 'EMPS', 'STALE_PERCENT', '15') 

The previous statement adjusts the threshold for the table EMPS owned by HR to 15%.  

All togehter there are three new parameters in DBMS_STATS:

- STALE_PERCENT which overwrites the default threshold of 10%

- INCREMENTAL for statistics on partitions incrementally gathered

- PUBLISH in order to populate the data dictionary with the newly gathered statistics or not, whcih can conme in handy if you want to test with the new statistics on another system before really making them effective on production.

I think that this is exactely what a lot of people have been waiting for since a long time

===================================================================================

SAP  extended  statistics  on function

You want to create statistics on an expression such as an user-created function.

You’re aware that certain columns from a table that are part of a join condition are correlated.

 You want to make the optimizer aware of this relationship.

===================================================================================

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'AUSP', '(MANDT, KLART, ATINN)') FROM DUAL;

-- BKPF --

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'BKPF', '(MANDT, BUKRS, BSTAT)') FROM DUAL;

-- HRP1001 --

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'HRP1001', '(RELAT, SCLAS, OTYPE, PLVAR)') FROM DUAL;

-- MSEG --

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, MATNR, WERKS, LGORT)') FROM DUAL;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, MBLNR, MJAHR)') FROM DUAL;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, WERKS, BWART)') FROM DUAL;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, WERKS, BWART, LGORT)') FROM DUAL;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, WERKS, LGORT)') FROM DUAL;

exec dbms_stats.seed_col_usage(null,null,1800);  --1800  secondes  as  sysdba

select dbms_stats.report_col_usage(user,'RES_PAYMENTS') from dual;  --as  radair_

stat oracle en fonction du taux de modifications

stat  oracle :

set  echo  off

set serveroutput on size 1000000

set  pages 10000

select   to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')   from dual;

select  'script : /appl/home/oracle/BIN/DEVstatnew.ksh'  from  dual; select  'script SQL: /appl/home/oracle/BIN/statnew.sql'  from  dual; select  'log : /appl/home/oracle/LOG/DEVstat.date.log'  from  dual; select  instance_name  from  v$instance; set  lines  200 set  head  on

        select *  from

  (select

  m.table_owner,

   m.table_name,

   --round ((select NVL(sum (m.inserts + m.updates + m.deletes),0)  from sys.dba_tab_modifications m where  m.table_name = t.table_name AND partition_name IS NULL),1) MG,

   round(( ((select NVL(sum (m.inserts + m.updates + m.deletes),0)  from sys.dba_tab_modifications m where  m.table_name = t.table_name AND partition_name IS NULL) /(t.num_rows +1 ))* 100),1) taux  from sys.dba_tab_modifications m,dba_tables t

      where m.table_owner = t.owner

    and m.table_name = t.table_name);

   

set  head  off

prompt =====================================================

prompt " liste  indexe   ayant  plus de 10% de  modifications"

prompt =====================================================

set  head   on

col  owner   for a20

col  index_name  for a45

col  table_name  for a50

col  num_rows  for 999999

col  ratio  for 99999

 select

 *  from (select

 a.owner

 ,a.index_name

 ,a.table_name

 ,a.num_rows

 , trunc ((SELECT NVL (SUM (inserts + updates + deletes), 0)  FROM sys.dba_tab_modifications m  WHERE     m.table_name = a.table_name )/ (a.num_rows + 1)* 100)ratio

 from sys.dba_tab_modifications m ,sys.dba_indexes a

      where m.table_owner = a.TABLE_OWNER

    and m.table_name = a.table_name)

    WHERE     ratio >=10  and num_rows != 0;

set  head  off

set timing on

DECLARE

  vOwner   sys.dba_indexes.owner%TYPE;            /* Index Owner            */

  vIdxName sys.dba_indexes.index_name%TYPE;       /* Index Name             */

  VRatio number;

  vAnalyze VARCHAR2(150);                     /* String of Analyze Stmt */

  vCursor  NUMBER;                            /* DBMS_SQL cursor        */

  vTNumRows INTEGER;                           /*table num rows   */

  vNumRows INTEGER;                           /* DBMS_SQL return rows   */

  CURSOR cGetIdx IS

  select

 *  from (select

 t.owner

 ,idx.index_name

 , t.num_rows

--,t.last_analyzed

 ,TRUNC ((SELECT NVL (SUM (inserts + updates + deletes), 0)  FROM sys.dba_tab_modifications m  WHERE     m.table_name = t.table_name AND partition_name IS NULL)/ (t.num_rows + 1)* 100)ratio

 from sys.dba_tab_modifications m ,dba_tables t, dba_indexes idx

      where m.table_owner = t.owner 

      and  idx.table_name=t.table_name

    and m.table_name = t.table_name)

    WHERE ratio >= 10 AND num_rows != 0; 

BEGIN

  OPEN cGetIdx;

  LOOP

     FETCH cGetIdx INTO vOwner,vIdxName,vTNumRows,Vratio;

     EXIT WHEN cGetIdx%NOTFOUND;

     /* Open DBMS_SQL cursor */

     vCursor := DBMS_SQL.OPEN_CURSOR;

      vAnalyze := 'begin dbms_stats.gather_index_stats( ''' || vOwner || ''',''' || vIdxName || ''' ,estimate_percent=>100, degree=>4);end ;';

     dbms_output.put_line('STAT IND : '|| vAnalyze);

     DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE); --

     vNumRows := DBMS_SQL.EXECUTE(vCursor); --

     /* Close DBMS_SQL cursor */

     DBMS_SQL.CLOSE_CURSOR(vCursor);

  END LOOP;

  CLOSE cGetIdx;

END;

/

prompt =====================================================

prompt "  table 10% de modification"

prompt =====================================================

     SELECT *

                                                        FROM (SELECT m.table_owner,

                                                                     m.table_name,

                                                                     ---round ((select NVL(sum (m.inserts + m.updates + m.deletes),0)  from sys.dba_tab_modifications m where  m.table_name = t.table_name AND partition_name IS NULL),1),

                                                                     ROUND (

                                                                        (  (  (SELECT NVL (SUM (m.inserts + m.updates + m.deletes),

                                                                                           0)

                                                                                 FROM sys.dba_tab_modifications m

                                                                                WHERE     m.table_name = t.table_name

                                                                                      AND partition_name IS NULL)

                                                                            / (t.num_rows + 1))

                                                                         * 100),

                                                                        1)

                                                                        ratio

                                                                FROM sys.dba_tab_modifications m, dba_tables t

                                                               WHERE m.table_owner = t.owner AND m.table_name = t.table_name)

                                                       WHERE ratio > 10;

                                                       

DECLARE

                  vOwner   sys.dba_tab_modifications.table_owner%TYPE;

                  vTblName  sys.dba_tab_modifications.table_name%TYPE;

                  vAnalyze15 VARCHAR2(180);

                  vAnalyze VARCHAR2(180);

                  vCursor  NUMBER;

                  vRatio  number;

                  vNumRows INTEGER;

                  vextents number;

                  vsize    number;

                  vQuery  VARCHAR2(180);

                  CURSOR cGetTbl IS

               SELECT *

                                                        FROM (SELECT m.table_owner,

                                                                     m.table_name,

                                                                     ---round ((select NVL(sum (m.inserts + m.updates + m.deletes),0)  from sys.dba_tab_modifications m where  m.table_name = t.table_name AND partition_name IS NULL),1),

                                                                     ROUND (

                                                                        (  (  (SELECT NVL (SUM (m.inserts + m.updates + m.deletes),

                                                                                           0)

                                                                                 FROM sys.dba_tab_modifications m

                                                                                WHERE     m.table_name = t.table_name

                                                                                      AND partition_name IS NULL)

                                                                            / (t.num_rows + 1))

                                                                         * 100),

                                                                        1)

                                                                        ratio

                                                                FROM sys.dba_tab_modifications m, dba_tables t

                                                               WHERE m.table_owner = t.owner AND m.table_name = t.table_name)

                                                       WHERE ratio > 10;

                   

                BEGIN

                  OPEN cGetTbl;

 LOOP

                    

                     FETCH cGetTbl INTO vOwner,vTblName,vRatio;

                     EXIT WHEN cGetTbl%NOTFOUND;

                    select  extents,bytes/1024/1024   into vextents,vsize  from   sys.dba_segments   where  segment_type='TABLE'  and segment_name=vTblName  and owner=vOwner;

                  

                     /* Open DBMS_SQL cursor */

                     vCursor := DBMS_SQL.OPEN_CURSOR;

                                if   vsize>300  and  Vextents >64  then

                                      vAnalyze15 := 'begin dbms_stats.gather_table_stats( ''' || vOwner || ''',''' || vTblName || ''' ,estimate_percent=>15, degree=>4,cascade=>false);end ;';

                                      dbms_output.put_line('STAT table 15 : '|| vAnalyze15);

                                      DBMS_SQL.PARSE(vCursor,vAnalyze15,DBMS_SQL.NATIVE);

                                      vNumRows := DBMS_SQL.EXECUTE(vCursor);

                                else

                                      vAnalyze := 'begin dbms_stats.gather_table_stats( ''' || vOwner || ''',''' || vTblName|| ''' ,estimate_percent=>50, degree=>4);end ;';

                                      dbms_output.put_line('STAT table 100 : '|| vAnalyze);

                                     DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE);

                                     vNumRows := DBMS_SQL.EXECUTE(vCursor);

                                end if;

                      DBMS_SQL.CLOSE_CURSOR(vCursor);  END LOOP;

                  CLOSE cGetTbl;

                END;

/

prompt ===================   

  select *  from

  (select

  m.table_owner,

   m.table_name,

   --round ((select NVL(sum (m.inserts + m.updates + m.deletes),0)  from sys.dba_tab_modifications m where  m.table_name = t.table_name AND partition_name IS NULL),1) MG,

   round(( ((select NVL(sum (m.inserts + m.updates + m.deletes),0)  from sys.dba_tab_modifications m where  m.table_name = t.table_name AND partition_name IS NULL) /(t.num_rows +1 ))* 100),1) taux  from sys.dba_tab_modifications m,dba_tables t

      where m.table_owner = t.owner

    and m.table_name = t.table_name);

   

prompt ===================     

select   to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')   from dual;

prompt =====================================================

prompt  "FIN"

prompt =====================================================

calcule de stats 11g

#!/usr/bin/ksh

#

# analyze schema

#

#

#

PATH=/opt/bin:$PATH

ORACLE_HOME=/appl/oracle/11.2.0.2

export ORACLE_HOME

export  TNS_ADMIN=$ORACLE_HOME/network/admin

PATH=$ORACLE_HOME/bin:$PATH

LOG=/appl/oracle/LOG

#

#

export ADMIN=/home/oracle

#

#

sqlplus -s  sys/FGFGFGG@XXXXcrs:1521/FRPERF as  sysdba<<EOF1 set echo off set feedback off column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,'yyMondd_hh24mi') timecol, '.log' spool_extension from sys.dual; column output new_value instance_name select value || '_' output from v\$parameter where name = 'instance_name'; spool /appl/oracle/LOG/compute_stats_&&instance_name&&timestamp&&suffix

--select  TO_char(SYSDATE,'Mon_Dy_DD@HH@HH24:MI:SS')   from  dual;

--exec dbms_stats.gather_schema_stats(ownname=>'$1' ,cascade=>TRUE,estimate_percent=> $2,method_opt=>'For All CoLUMNS SIZE 1',OPTIONS=>'GATHER',degree=>8);

--select  TO_char(SYSDATE,'Mon_Dy_DD@HH@HH24:MI:SS')   from  dual;

set timing on

set serveroutput on

set  lines 200

alter session set "_optimizer_join_factorization"=false;

alter  session set   NLS_DATE_FORMAT='DD-MM-YYYY:HH24:MI:SS';

set  pages 200

set  feed  on

select m.table_name, m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows, t.last_analyzed 

 from sys.dba_tab_modifications m, dba_tables t  

   where m.table_owner = t.owner

and m.table_name = t.table_name

and m.table_owner in ('APEX_040200','CTRL','R','R_SEC')

 group by m.table_name, m.timestamp, t.num_rows, t.last_analyzed  

 order by 1,2;

set  feed  off

---------

---index

---------

select a.owner,a.TABLE_NAME,a.index_name,a.NUM_ROWS ,last_analyzed from sys.dba_indexes a,sys.dba_tab_modifications b where  a.index_type not in ('IOT - TOP','LOB','DOMAIN') and   a.table_name=b.table_name

and a.owner in ('APEX_040200','CTRL','R','R_SEC')  order  by  last_analyzed; begin

for z   in (select a.owner,a.index_name from sys.dba_indexes a,sys.dba_tab_modifications b where  a.index_type not in ('IOT - TOP','LOB','DOMAIN') and   a.table_name=b.table_name and a.table_owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

  dbms_output.put_line('STAT IND  100 percent: '||z.owner||'.'||z.index_name);

  dbms_stats.gather_index_stats(z.owner, z.index_name,estimate_percent=>100, degree=>4); end loop; end; /

select a.owner,a.TABLE_NAME,a.index_name,a.NUM_ROWS ,last_analyzed from sys.dba_indexes a,sys.dba_tab_modifications b where  a.index_type not in ('IOT - TOP','LOB','DOMAIN') and   a.table_name=b.table_name

and a.owner in ('APEX_040200','CTRL','R','R_SEC')  order  by  last_analyzed;

--

--TABLE

--

begin

for z in ( select r.owner,b.table_name from sys.dba_segments r,sys.dba_tab_modifications b where r.owner =b.table_owner and r.segment_name=b.table_name and r.extents >64 and r.bytes/1024/1024 >300 and r.owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

  sys.dbms_output.put_line('STAT TAB 15 percent : '||z.owner||'.'||z.table_name);

  sys.dbms_stats.gather_table_stats(z.owner, z.table_name,estimate_percent=>15, degree=>4,cascade=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE REPEAT');

end loop;

end;

/

begin

for z in (select r.owner,b.table_name from sys.dba_segments r,sys.dba_tab_modifications b where   r.owner =b.table_owner  and  r.segment_name=b.table_name   and r.owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

  dbms_output.put_line('STAT TAB  100 percent : '||z.owner||'.'||z.table_name);

  dbms_stats.gather_table_stats(z.owner, z.table_name,estimate_percent=>100, degree=>4,cascade=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE REPEAT');

end loop;

end;

/

set  feed  on

select  *  from dba_tab_modifications b where b.table_owner in ('APEX_040200','CTRL','R','R_SEC');

set  feed  off

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

--  fin

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

exit

EOF1

find $LOG -name 'compute_stats*' -mtime  +5  -print