STATISTICS dbms_stats

- gather statistics – SYS , system, dictionary , fixed_objects  

 

 exec dbms_stats.gather_system_stats('interval', interval=>180);

 exec dbms_stats.gather_dictionary_stats;

 exec dbms_stats.gather_fixed_objects_stats;

 exec dbms_stats.gather_schema_stats('SYS',degree=>30,-

   estimate_percent=>100,cascade=>TRUE);

  

-- gather database statistics

   exec DBMS_STATS.gather_database_stats(estimate_percent => 15);    

        

-- gather schema statistics

exec dbms_stats.delete_schema_stats('SCOTT');

exec dbms_stats.gather_schema_stats(USER,  cascade => TRUE);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT,   options =>'GATHER',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>TRUE);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT', -

     method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE -   1',estimate_percent => 15,granularity=>'ALL',cascade=>TRUE);

   

-- gather table, index  stats

exec dbms_stats.delete_table_stats(ownname =>'SCOTT',-

tabname =>'EMPLOYEE');

exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'for all columns size auto',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>true);

 exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => 15,cascade => TRUE );      

   with histogram on columns

     exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => dbms_stats.auto_sample_size,granularity=>'ALL',cascade=>TRUE);

   

   with no histogram on columns

     exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent => dbms_stats.auto_sample_size,granularity=>'ALL',cascade=>TRUE);

   for skewed columns        

     execute dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'for all columns size skewonly',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>TRUE);

using shell scipt

set time on

declare

cursor tab_cur IS select table_name from dba_tables where owner='SCOTT' order by table_name;

begin

  for tab_rec in tab_cur loop

    exit when tab_cur%NOTFOUND;

    dbms_stats.gather_table_stats(ownname =>'SCOTT',options=>'GATHER',tabname =>tab_rec.table_name,method_opt=>'for all columns size auto',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>true);

  end loop;

end;

/

exit

  

-- gather index stats

   exec dbms_stats.gather_index_stats( ownname=>'SCOTT',indname=>'ITEM_NDX1',-

granularity=>'ALL',estimate_percent=> 10,degree=>1);

using shell scipt
set time on
declare
cursor tab_cur IS select index_name from dba_indexes where owner='SCOTT' and last_analyzed < sysdate - 1  order by table_name;
begin
  for tab_rec in tab_cur loop
    exit when tab_cur%NOTFOUND;
    dbms_stats.gather_index_stats( ownname=>'MAXIMO',indname=>tab_rec.index_name,granularity=>'AUTO',estimate_percent=> dbms_stats.auto_sample_size,degree=>1);
end loop;
end;
/
exit

Hope this helps! Rupam

index statistics in oracle

Statistics generated include the index statistics following:

Table statistics

Number of rows

Number of blocks

Average row length

Column statistics

Number of distinct values (NDV) in column 

Number of nulls in column 

Data distribution (histogram) Index statistics 

Number of leaf blocks 

Levels 

Clustering factor System statistics

I/O performance and utilization

CPU performance and utilization

Hope this Helps! Rupam

Gather statistics of Partition Tables Part 2

--  collect global statistics on Partition Tables

Option # 1

set serveroutput on

set time on

declare

this_day       char(8);

part_name    char(10);

tab_name     varchar2(30);

v_num          number;

v_string        varchar2(4000);

cursor tab_cur IS select table_name from user_tables where PARTITIONED='YES'  and table_name in ('EVENT_LOG1','EVENT_LOG2');

begin

for table_rec in tab_cur loop

  exit when tab_cur%NOTFOUND;

  tab_name := table_rec.table_name;

  dbms_stats.gather_table_stats( ownname =>'SCOTT', tabname => tab_name, granularity=> 'GLOBAL', estimate_percent => 15, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', degree => 1, cascade => TRUE );

end loop;

end;

/

Note : granularity is GLOBAL, which means only GOBAL statustics on partition table  is going to be collected.

For other options, check Gather statistics of Partition Tables Part 1

Option # 2

begin

dbms_stats.gather_table_stats(ownname=>SCOTT',

tabname=>'EVENT_LOG1’,

granularity=>'ALL',

estimate_percent=>15,

method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree => 1, cascade=>TRUE); end;

/

Note : granularity is ALL, which means Gathers all (subpartition, partition, and global) statistics.

Hope this help. Regards Rupam

Gather statistics of Partition Tables Part 1

Granularity of statistics to collect (only pertinent if the table is partitioned).

Constant	Description
ALL	Gathers all (subpartition, partition, and global) statistics
AUTO	Determines the granularity based on the partitioning type. This is the default value
DEFAULT	Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality
GLOBAL	Gathers global statistics
GLOBAL AND PARTITION	gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
PARTITION	gathers partition-level statistics
SUBPARTITION	gathers subpartition-level statistics

Hope this help. Regards Rupam

Gather Statistics in Oracle part 2

-- collect statistics for missing statistics tables

declare

cursor miss_cur IS select table_name from user_tables where last_analyzed is null;

begin

for miss_rec in miss_cur loop

  exit when miss_cur%NOTFOUND;

  dbms_stats.Gather_Table_Stats('SCOTT',miss_rec.table_name,

           estimate_percent => 15,

           method_opt       =>'FOR ALL INDEXED COLUMNS SIZE 1',

           cascade          => TRUE);

  end loop;

end;

/

Hope this help. Regards Rupam

Gather Statistics in Oracle part 1

dbms_stats  package used for collecting statistics

summary

   gather statistics -   system, dictionary, fixed_objects , sys  

   gather schema statistics

   gather table stats

   gather index stats

Gather Statistics Intro

Gather Statistics Intro

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

SYSTEM related statistics in 10gr2

3 More SYSTEM related statistics in 10gr2 system, dictionary and fixed_objects

# Gather system statistics
execute dbms_stats.gather_system_stats('Start');

-- some time delay while the database is under a typical workload

execute dbms_stats.gather_system_stats('Stop');

-- OR

exec dbms_stats.gather_system_stats('interval', interval=>N);
where N is the number of minutes stats will be gathered

# collect OLTP and BATCH related system stats and import it when desired
exec dbms_stats.create_stat_table('SYS','CSX_STAT');

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLTP');
END;
/

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLAP');
END;
/

During the day, the following jobs import the OLTP statistics for the daytime run:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLTP'');'
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

During the night, the following jobs import the OLAP statistics for the nighttime run:

BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLAP'');'
SYSDATE + 0.5, 'SYSDATE + 1');
COMMIT;
END;
/


# Gather statistics on dictionay
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- or
begin
dbms_stats.gather_dictionary_stats(
comp_id => null,
estimate_percent => 15,
granularity => 'DEFAULT',
cascade => TRUE);

end;
/

# gather fixed object(synamic performance tables eg. x$) stats
exec dbms_stats.gather_fixed_objects_stats;
-- or
SQL> conn / as sysdba
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('sys','csxfixedtablestat');
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ('csxfixedtablestat');



# Flush statistics from memory
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

# view
USER_TAB_MODIFICATIONS

# system statistics views
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';

gather statistics, dbms_stats

Manage statistics using dbms_stats 
 
create stat table
begin
dbms_stats.create_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable',
tblspace=> 'tools');
end;

drop stat table
begin
dbms_stats.drop_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable');
end;

gather database Stat
begin
dbms_stats.gather_database_stats(
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete database stat
EXEC DBMS_STATS.delete_database_stats;

gather schema Stat
begin
dbms_stats.gather_schema_stats(
ownname => 'SYSADM',
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete schema stat
EXEC DBMS_STATS.delete_schema_stats('SCOTT');

export table stat
dbms_stats.export_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

import table stat
begin
dbms_stats.import_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

delete table stat
begin
dbms_stats.delete_table_stats(
ownname => 'SYSADM',
tabname =>'my_table');
end;

gather table stat
begin
dbms_stats.gather_table_stats(
ownname => 'SYSADM',
tabname => 'my_table',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade => TRUE );
end;

gather index stat
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

delete index stat
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

delete column stat
exec dbms_stats.delete_column_stats(USER, 'PERSON', 'PERSON_ID');

check stats
select table_name, num_rows from dba_tables
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select table_name,index_name, num_rows from dba_indexes
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select TABLE_NAME, COLUMN_NAME, NUM_BUCKETS from dba_tab_columns
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name, column_name;

select count(*) from PS_SCRTY_TBL_DEPT ;
select count(*) from PS_JOB ;
select count(*) from PS_NAMES ;
select count(*) from PS_PERSON ;


Steps

1. Export current stats
2. Gather or Import or Set Stats
3. trace
4. tkprof

More info



Hope this helps. Regards Rupam

How to Use DBMS_STATS to Move Statistics to a Different Database

Create stat table

SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');

Export statistics into stat table

SQL> exec dbms_stats.export_table_stats('SCOTT',-
'EMP',NULL,'STATS',NULL,TRUE);

Export stat table from source database

%exp scott/tiger tables=STATS file=expstat.dmp

Import stat table into target database

%imp scott/tiger file=expstat.dmp full=y log=implog.txt

Import statistics into table

SQL> exec dbms_stats.import_table_stats('SCOTT',-
'EMP',-NULL,'STATS',NULL,TRUE);

SQL >