connect / as sysdba
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select name from v$database;
exec DBMS_STATS.UNLOCK_SCHEMA_STATS('ISADMIN');
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ISADMIN', tabname=>'CORRELATION_SET', ESTIMATE_PERCENT=> 10, DEGREE=> 12, CASCADE=> TRUE);
exec DBMS_STATS.LOCK_SCHEMA_STATS('ISADMIN');
quit
###############################
Database | Schema | Table | Index Statistics
###############################
Gather Database Stats:
====================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
degree => 4,
OPTIONS => 'GATHER STALE',
GATHER_SYS => TRUE
STATTAB => PROD_STATS);
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether
to collected or not.
DEGREE => 4 :Degree of parallelism.
OPTIONS =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats
objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT
STATISTICS section -last third in this post-.
Note: All parameters above are valid for all stats kind (schema,table,..) except Gather_SYS.
For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed
values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not
recommended to be gathered on all columns.
Removed "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU
overhead during gathering DB statistics.
This task became an automated task starting from 10g, To check the status of that task:
SQL> select * from dba_autotask_client where client_name = "auto optimizer stats collection" ;
Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'SCOTT',
estimate_percent=>10,
degree=>1,
cascade=>TRUE,
options=>'GATHER STALE');
Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';
SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'EMP',
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2 :Degree of parallelism.
options =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE :(DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO : you can specify one column between "" instead of all
columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for
columns already have histograms.
FOR ALL COLUMNS : collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : collect histo for columns have skewed value should test skewness first>.
FOR ALL INDEXED COLUMNS : collect histograms for columns have indexses only.
Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'SCOTT',
indname => 'EMP_I',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
###################
Fixed OBJECTS Statistics
###################
What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:
-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
-also note that performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representative statistics.
How to gather stats on fixed objects:
------ ------------------------------
Firstly Check the last analyzed date:
------ --------------------------
select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';
Secondly Export the current fixed stats in a table: (in case you need to revert back)
------ - --------------------------------------
EXEC DBMS_STATS.CREATE_STAT_TABLE('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
EXEC dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Thirdly Gather the fixed objects stats:
------ ---------------------------
exec dbms_stats.gather_fixed_objects_stats;
Note In case of reverting back to the old stats:
---- ----------------------------------------
In case you experienced a bad performance on fixed tables after gathering the new statistics:
exec dbms_stats.delete_fixed_objects_stats();
exec DBMS_STATS.import_fixed_objects_stats(stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER');
#################
SYSTEM STATISTICS
#################
What is system statistics:
-------------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.
Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.
There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):
NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD
statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats();
WORKLOAD statistics:
----------------------
This will gather statistics during the current workload -which supposed to be representative of actual system I/O and CPU workload on the DB-.
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);
Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$;
cpuspeedNW: Shows the noworkload CPU speed, in terms of the average number of CPU cycles per
second.
ioseektim: The sum of seek time, latency time, and OS overhead time.
iotfrspeed: I/O transfer speed,tells the optimizer how fast the database can read data in a single read
request.
cpuspeed: Stands for CPU speed during a workload statistics collection.
maxthr: The maximum I/O throughput.
slavethr: Average parallel slave I/O throughput.
sreadtim: The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim: The average time (seconds) for a sequential multiblock read.
mbrc: The average multiblock read count in blocks.
According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.
Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats();
####################
Data Dictionary Statistics
####################
Facts:
-----
>Dictionary tables are the tables owned by SYS and residing in the system tablespace.
>Normally data dictionary statistics in 9i is not required unless performance issues are detected.
>In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.
If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:
SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');
When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.
Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables where owner='SYS' and table_name like '%$' order by 2;
Gather Dictionary Statistics:
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; ->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS'); ->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE); ->Will gather stats on the whole DB+SYS schema.
#############
Extended Statistics "11g onwards"
#############
Extended statistics can be gathered on columns based on functions or column groups.
Gather extended stats on column function:
===================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott';
In order to make optimizer work with function based terms you need to gather extended stats:
1-Create extended stats:
>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;
2-Gather histograms:
>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SCOTT','EMP', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'EMP',
method_opt => 'for all columns size skewonly for columns (lower(ENAME))'
);
end;
To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');
Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement with the column group the optimizer will make use of column group statistics.
1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)') from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SH',
tabname => 'CUSTOMERS',
method_opt => 'for all columns size skewonly for columns (country_id,cust_state_province)'
);
end;
Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');
########
Histograms
########
What are Histograms?
------------------
>Holds data about values within a column in a table for number of occurrences for a specific value/range.
>Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
>Usually being used against columns have data being repeated frequently like country or city column.
>gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
>Two types of Histograms can be gathered:
>Frequency histograms: is when distinct values (buckets) in the column is less than 255 (# countries is always less than 254.
Height balanced histograms: are similar to frequency histograms in their design, but distinct values > 254
See Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms/
- Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
- Mainly being gathered on foreign key columns/columns in WHERE statement.
- Helps in SQL multi-table joins.
- Column histograms like statistics are being stored in data dictionary.
- If application exclusively uses bind variables, Oracle recommends deleting any existing Oracle histograms and disabling Oracle histograms generation.
Caution:
– Do not create them on Columns that are not queried.
– Do not create them on every column of every table.
– Do not create them on PK of a table.
Verify the existence of histograms:
----------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics where owner='SCOTT' and table_name='EMP';
Creating Histograms:
----------------------
e.g.
SQL> Exec dbms_stats.gather_table_stats(
ownname => '',
tabname => '',
estimate_percent => dbms_stats.auto_sample_size,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO ');
SQL> Exec dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
FOR COLUMNS SIZE AUTO => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT => to prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns .
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value .
FOR ALL INDEXES COLUMNS => collect histograms for columns have indexes.
Note: For AUTO & SKEWONLY Oracle will decide whatever to create Histograms or not.
Check existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms where table_name='SMFILECABINET' group by column_name;
Drop Histograms: 11g
-----------------
e.g.
SQL> Exec dbms_stats.delete_column_stats(ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=> HISTOGRAM);
Stop gathering Histograms: 11g
e.g.
SQL> Exec dbms_stats.set_table_prefs
('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for the PROD_ID column.
Drop Histograms: 10g
----------------
e.g.
SQL> exec dbms_stats.delete_column_stats( user, 'T', 'USERNAME' );
##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.
1-Create STATS TABLE:
- ----------------------
SQL> Exec dbms_stats.create_stat_table (ownname => 'SYSTEM', stattab => 'prod_stats', tblspace => 'USERS');
2-Export the statistics to the STATS table:
------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table:
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats (ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats (ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => 'SCOTT',stattab => 'prod_stats',statown => 'SYSTEM');
For Table stats and it's indexes:
SQL> Exec dbms_stats.import_TABLE_stats ( ownname => 'SCOTT', stattab => 'prod_stats', tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');
==============
Restore statistics: -From Dictionary-
==============
Old statistics are saved automatically in SYSAUX for 31 day.
Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1);
Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1);
Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1);
Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1);
Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1);
OR:
SQL> Exec dbms_stats.restore_schema_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');
Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=====
FACTS:
=====
To Check current Stats history retention period (days):
---------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
---------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60);
Purge statistics older than 10 days:
-------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:
Check Stats tables size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
/
Check Stats indexes size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/
Move Stats tables in same tablespace:
>>>>>>
select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
/
Rebuild Stats indexes:
>>>>>>
select 'alter index '||segment_name||' rebuild online;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
/
Check for un-usable indexes:
>>>>>>
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
Delete Statistics:
=============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS ();
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS ();
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS ();
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ();
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT');
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats (ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats ( ownname => 'SCOTT',indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.DELETE_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');
Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.
Pending Statistics: "11g onwards"
=============
Switch on pending statistics:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PENDING','TRUE');
Gather 11g statistics:
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES');
Test your critical SQL statement with the pending stats:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
When proven, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS();
set time on timing on echo on pagesize 200 linesize 200 feed on
show user
select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select name from v$database;
exec DBMS_STATS.UNLOCK_SCHEMA_STATS('ISADMIN');
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ISADMIN', tabname=>'CORRELATION_SET', ESTIMATE_PERCENT=> 10, DEGREE=> 12, CASCADE=> TRUE);
exec DBMS_STATS.LOCK_SCHEMA_STATS('ISADMIN');
quit
###############################
Database | Schema | Table | Index Statistics
###############################
Gather Database Stats:
====================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
degree => 4,
OPTIONS => 'GATHER STALE',
GATHER_SYS => TRUE
STATTAB => PROD_STATS);
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether
to collected or not.
DEGREE => 4 :Degree of parallelism.
OPTIONS =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats
objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT
STATISTICS section -last third in this post-.
Note: All parameters above are valid for all stats kind (schema,table,..) except Gather_SYS.
For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed
values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not
recommended to be gathered on all columns.
Removed "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU
overhead during gathering DB statistics.
This task became an automated task starting from 10g, To check the status of that task:
SQL> select * from dba_autotask_client where client_name = "auto optimizer stats collection" ;
Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'SCOTT',
estimate_percent=>10,
degree=>1,
cascade=>TRUE,
options=>'GATHER STALE');
Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';
SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'EMP',
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2 :Degree of parallelism.
options =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE :(DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO : you can specify one column between "" instead of all
columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for
columns already have histograms.
FOR ALL COLUMNS : collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : collect histo for columns have skewed value should test skewness first>.
FOR ALL INDEXED COLUMNS : collect histograms for columns have indexses only.
Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'SCOTT',
indname => 'EMP_I',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
###################
Fixed OBJECTS Statistics
###################
What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:
-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
-also note that performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representative statistics.
How to gather stats on fixed objects:
------ ------------------------------
Firstly Check the last analyzed date:
------ --------------------------
select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';
Secondly Export the current fixed stats in a table: (in case you need to revert back)
------ - --------------------------------------
EXEC DBMS_STATS.CREATE_STAT_TABLE('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
EXEC dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Thirdly Gather the fixed objects stats:
------ ---------------------------
exec dbms_stats.gather_fixed_objects_stats;
Note In case of reverting back to the old stats:
---- ----------------------------------------
In case you experienced a bad performance on fixed tables after gathering the new statistics:
exec dbms_stats.delete_fixed_objects_stats();
exec DBMS_STATS.import_fixed_objects_stats(stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER');
#################
SYSTEM STATISTICS
#################
What is system statistics:
-------------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.
Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.
There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):
NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD
statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats();
WORKLOAD statistics:
----------------------
This will gather statistics during the current workload -which supposed to be representative of actual system I/O and CPU workload on the DB-.
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);
Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$;
cpuspeedNW: Shows the noworkload CPU speed, in terms of the average number of CPU cycles per
second.
ioseektim: The sum of seek time, latency time, and OS overhead time.
iotfrspeed: I/O transfer speed,tells the optimizer how fast the database can read data in a single read
request.
cpuspeed: Stands for CPU speed during a workload statistics collection.
maxthr: The maximum I/O throughput.
slavethr: Average parallel slave I/O throughput.
sreadtim: The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim: The average time (seconds) for a sequential multiblock read.
mbrc: The average multiblock read count in blocks.
According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.
Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats();
####################
Data Dictionary Statistics
####################
Facts:
-----
>Dictionary tables are the tables owned by SYS and residing in the system tablespace.
>Normally data dictionary statistics in 9i is not required unless performance issues are detected.
>In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.
If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:
SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');
When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.
Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables where owner='SYS' and table_name like '%$' order by 2;
Gather Dictionary Statistics:
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; ->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS'); ->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE); ->Will gather stats on the whole DB+SYS schema.
#############
Extended Statistics "11g onwards"
#############
Extended statistics can be gathered on columns based on functions or column groups.
Gather extended stats on column function:
===================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott';
In order to make optimizer work with function based terms you need to gather extended stats:
1-Create extended stats:
>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;
2-Gather histograms:
>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SCOTT','EMP', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'EMP',
method_opt => 'for all columns size skewonly for columns (lower(ENAME))'
);
end;
To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');
Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement with the column group the optimizer will make use of column group statistics.
1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)') from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SH',
tabname => 'CUSTOMERS',
method_opt => 'for all columns size skewonly for columns (country_id,cust_state_province)'
);
end;
Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');
########
Histograms
########
What are Histograms?
------------------
>Holds data about values within a column in a table for number of occurrences for a specific value/range.
>Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
>Usually being used against columns have data being repeated frequently like country or city column.
>gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
>Two types of Histograms can be gathered:
>Frequency histograms: is when distinct values (buckets) in the column is less than 255 (# countries is always less than 254.
Height balanced histograms: are similar to frequency histograms in their design, but distinct values > 254
See Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms/
- Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
- Mainly being gathered on foreign key columns/columns in WHERE statement.
- Helps in SQL multi-table joins.
- Column histograms like statistics are being stored in data dictionary.
- If application exclusively uses bind variables, Oracle recommends deleting any existing Oracle histograms and disabling Oracle histograms generation.
Caution:
– Do not create them on Columns that are not queried.
– Do not create them on every column of every table.
– Do not create them on PK of a table.
Verify the existence of histograms:
----------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics where owner='SCOTT' and table_name='EMP';
Creating Histograms:
----------------------
e.g.
SQL> Exec dbms_stats.gather_table_stats(
ownname => '',
tabname => '',
estimate_percent => dbms_stats.auto_sample_size,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO ');
SQL> Exec dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
FOR COLUMNS SIZE AUTO => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT => to prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns .
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value .
FOR ALL INDEXES COLUMNS => collect histograms for columns have indexes.
Note: For AUTO & SKEWONLY Oracle will decide whatever to create Histograms or not.
Check existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms where table_name='SMFILECABINET' group by column_name;
Drop Histograms: 11g
-----------------
e.g.
SQL> Exec dbms_stats.delete_column_stats(ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=> HISTOGRAM);
Stop gathering Histograms: 11g
e.g.
SQL> Exec dbms_stats.set_table_prefs
('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for the PROD_ID column.
Drop Histograms: 10g
----------------
e.g.
SQL> exec dbms_stats.delete_column_stats( user, 'T', 'USERNAME' );
##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.
1-Create STATS TABLE:
- ----------------------
SQL> Exec dbms_stats.create_stat_table (ownname => 'SYSTEM', stattab => 'prod_stats', tblspace => 'USERS');
2-Export the statistics to the STATS table:
------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table:
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats (ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats (ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => 'SCOTT',stattab => 'prod_stats',statown => 'SYSTEM');
For Table stats and it's indexes:
SQL> Exec dbms_stats.import_TABLE_stats ( ownname => 'SCOTT', stattab => 'prod_stats', tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');
==============
Restore statistics: -From Dictionary-
==============
Old statistics are saved automatically in SYSAUX for 31 day.
Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1);
Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1);
Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1);
Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1);
Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1);
OR:
SQL> Exec dbms_stats.restore_schema_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');
Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=====
FACTS:
=====
To Check current Stats history retention period (days):
---------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
---------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60);
Purge statistics older than 10 days:
-------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:
Check Stats tables size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
/
Check Stats indexes size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/
Move Stats tables in same tablespace:
>>>>>>
select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
/
Rebuild Stats indexes:
>>>>>>
select 'alter index '||segment_name||' rebuild online;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
/
Check for un-usable indexes:
>>>>>>
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
Delete Statistics:
=============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS ();
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS ();
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS ();
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ();
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT');
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats (ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats ( ownname => 'SCOTT',indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.DELETE_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');
Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.
Pending Statistics: "11g onwards"
=============
Switch on pending statistics:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PENDING','TRUE');
Gather 11g statistics:
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES');
Test your critical SQL statement with the pending stats:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
When proven, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS();
