Oracle BLOG: February 2017

Friday, February 24, 2017

Some DBMS_STATS.GATHER_FIXED_OBJECTS_STATS tips

Starting in Oracle 10g we see a new package gather_fixed_stats for analyzing the dictionary fixed structures (the X$ tables). We now have three types of stats to analyze for the SQL optimizer:

Table/object/schema stats: Via dbms_stats.gather_table_stats. Or gather_schema_stats. Traditional metadata from data tables.
System stats: Via dbms_stats.gather_system_stats: OS statistics (disk, CPU timings).
Dictionary objects: Used to make dictionary queries more efficient. The gather_fixed_objects_stats collects the same metadata as gather_table_stats, excepts for the number of blocks. This is because the x$ structures and the v$ views only exists in the RAM of the SGA.

The docs note "Analyze fixed objects only once, unless the workload footprint changes. You must be connected a SYS (or a user with SYSDBA) to invoke dbms_stats.gather_fixed_objects_stats.

Just like the workload statistics, Oracle recommends that you analyze the x$ tables only once, and during a typical database workload.

exec dbms_stats.gather_schema_stats('sYS?,gather_fixed=>TRUE)

exec dbms_stats.gather_fixed_objects_stats(?ALL?);

Oracle notes that the data dictionary now contains several classes of x$ structures and v$ views: See here for all important v$ views.

Structural fixed data - for example, v$datafile, v$datafile_header, &c

v$archive_dest
v$archive_dest_status
v$archive_gap
v$archive_processes
v$archived_log
v$bh
v$buffer_pool
v$buffer_pool_statistics
v$controlfile
v$controlfile_record_section
v$database
v$datafile
v$datafile_header
v$dataguard_status

Session based fixed data - Such as v$session, v$access, &c.

v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat

Workload fixed data - Such as v$sql, v$sql_plan
SQL data:

v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines

We also see the new procedures dbms_stats.export_fixed_objects_stats anddbms_stats.import_fixed_ objects_stats for migrating production workload statistics into test and development instances.

Re-Analyzing fix object statistics

Oracle recommends a single analyze of data dictionary and x$ fixed structures for the cost-based optimizer, but it is not clear when it is necessary to re-analyze the v$ views and x$ structures.

If it ain't broke, don't fix it. However, Oracle recommends that major parameter changes (db_cache_size, shared_pool_size. sga_target, &c ) may be followed-up with a re-analyze using dbms_stats.gather_fixed_stats.

gather_fixed_objects_stats usage tips

You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
Also note Bug 3982803 - OERI[kcbshcb_1] with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
Andrew Holdsworth of Oracle Corporation notes that dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters:

'the payback from good statistics management and execution plans will exceed any benefit of init.ora tuning by orders of magnitude?

Fixed Table Statistics

The data dictionary has many fixed tables, such as X$ tables. Oracle suggests that you also collect statistics for these objects, however, less frequently than the other normal objects.

There is a new parameter gather_fixed available in the procedure gather_database_stats which when set to TRUE, collects the statistics for data dictionary fixed tables. gather_fixed is set to FALSE by default, and causes statistics not to be gathered for fixed tables. It may not be necessary to collect statistics very often for data dictionary fixed tables.

Another procedure, gather_fixed_objects_stats, is primarily aimed at collecting statistics of fixed objects. This procedure takes the following arguments:

STATTAB: The user statistics table identifier describing where to save the current statistics. Default value is NULL for dictionary collection.
STATID: The optional identifier to associate with these statistics within STATTAB. Default value is also NULL
STATOWN: The schema containing STATAB. Default value is NULL.
NO_INVALIDATE: Do not invalidate the dependent cursors if it is set to TRUE. Default value is FALSE.

It is also possible to delete statistics on all fixed tables by using the new procedure delete_fixed_objects_stats. You can also perform export or import statistics on fixed tables by using the export_fixed_objects_stats and import_fixed_objects_stats procedures respectively.

The following example shows different formats:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', -gather_fixed=>TRUE) ;

PL/SQL procedure successfully completed.

You can also use the gather_fixed_objects_stats procedure to collect statistics.

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (?ALL?);

In addition to what has been shown above, it is also possible to collect statistics for individual fixed tables. The procedures in the dbms_stats package that accept a table name as an argument are enhanced to accept a fixed table name as an argument. Since the fixed tables do not have I/O cost, as the rows reside in memory, CBO takes into account the CPU cost of reading rows.

Oracle suggests the following best practices for collecting statistics.

Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.
Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.
For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically.

In the next section, we will examine the changes introduced for the dbms_stats package.

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those parameters are as follows:

Granularity
Degree

Granularity

This parameter is used in subprograms such as gather_table_stats and gather_schema_stats. This parameter indicates the granularity of the statistics that you want to collect, particularly for partitioned tables. As an example, you can gather the global statistics on a partitioned table, or you can gather global and partition-level statistics. It has two options. They are: AUTO and GLOBAL AND PARTITION.

When the AUTO option is specified, the procedure determines the granularity based on the partitioning type. Oracle collects global, partition-level, and sub-partition level statistics if sub-partition method is LIST. For other partitioned tables, only the global and partition level statistics are generated.

When the GLOBAL AND PARTITION option is specified, Oracle gathers the global and partition level statistics. No sub-partition level statistics are gathered even it is composite partitioned object.

Degree

With this parameter, you are able to specify the degree of parallelism. In general, the ?degree? parameter allows you to parallelize the statistics gathering process. The degree parameter can take the value of auto_degree .

When you specify the auto_degree, Oracle will determine the degree of parallelism automatically. It will be either 1 (serial execution) or default_degree (the system default value based on number of CPUs and initialization parameters), according to the size of the object. Take care if Hyper Threading is used, as you will have less computational power than Oracle assumes.

How to Create OCM Response File for Oracle Grid Infrastructure Patching

First you need to export your variables:

export ORACLE_BASE=/u01/app/oracle
 export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

Then you can run the below to create the response file:

$ORACLE_HOME/OPatch/ocm/bin/emocmrsp

Distributing RMAN backups across multiple disks

Question: I need an RMAN backup strategy to distribute by RMAN backup across five disks, each on a separate mount point. We have 5 mount points (NFS mounted, on a T1 interconnect). How can I evenly distribute the RMAN backup files across the five disk spindles?
Answer: Oracle RMAN has the ability to automatically distribute files if you are using ASM, or you can the "allocate channel" RMAN command, to pipe the output backup files to multiple mount points on separate disks.

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/u01/xxx';
ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT '/u02/xxx';
BACKUP DATABASE PLUS ARCHIVELOG;
}

Or perhaps consider the "configure channel" RMAN syntax to distribute the backup files across multiple disks:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/%U', '/u02/%U';
In these cases where an RMAN job is reading and writing from multiple disks at the same time, consider using the RMAN parallel options to speed-up elapsed time for the backup job.

Tuesday, February 21, 2017

Why do some FIXED_TABLES not have statistics

Lets start with some advice from Metalink note 272479.1: Gathering Statistics For All fixed Objects In The Data Dictionary.

With Oracle Database 10G it is now recommended that you analyze the data dictionary.
The data dictionary has many fixed tables, such as X$ tables and collecting statistics
for these objects is suggested by Oracle. The GATHER_FIXED_OBJECTS_STATS
procedure gathers statistics for all fixed objects (dynamic performance tables) in the
data dictionary.

So we know we should do it – but when?

Advice from the Oracle documentation this time. Statistics on fixed objects, such as the dynamic performance tables, need to be

manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record current database activity; statistics gathering should be done when database has representative activity.

Why do some fixed tables not have statistics even after running: DBMS_STATS.GATHER_FIXED_OBJECTS_STATS?

Some fixed tables are left without stats because the development team has flagged the code to avoid statistics gathering for some fixed tables. This is because they know they will perform better without stats or because it is not supported to gather the statistics upon them.

This is expected behavior which was confirmed in : Bug 12844116 fixed tables statistics.

If you encounter performance issues specifically because statistics are missing on certain of these fixed tables, please file a SR with Database Support, quoting this article as reference.

Some fixed tables(X$) have no CBO statistics against them and use defaults; it may betoo expensive to gather stats or the tables are too volatile such that any statistics are immediately outdated.

The sys.x$ktfbue table is one such object. These statistical inaccuracies can cause suboptimal plans in some cases.

An execution plan regression occurs if there are a large number of records in the X$KTFBUE table. Gathering of dictionary or fixed object stats does not improve performance as this table is intentionally omitted from those packages.

CAUSE

The Cost Based Optimizer uses a cardinality estimate of 100,000 rows for this table, and the 11.2.0.3 execution plan is doing a full table scan. If you have a large number of extents, this query can take more than 1 hour to complete. There are a number of unpublished bugs open on slow perfomance for queries on DBA_EXTENTS.
For example, following bugs:
Bug:13542477 ON 11.2.0.3 QUERY DBA_EXTENTS IS SO SLOW
Bug:14221159 JOIN CARDINALITY INCORRECT CALCULATED AFTER FIX 11814428

SOLUTION

Following are possible workarounds to improve the performance
Gathering stats on the X$KTFBUE table using the following command:

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘X$KTFBUE’);
OR
alter session set “_optimizer_cartesian_enabled” = false;
OR
alter session set “_smm_auto_cost_enabled” = false;
OR
Disable fix for unpublished Bug Bug 11814428: POOR CARDINALITY ESTIMATE FROM JOIN WITH A UNIONALL VIEW:
alter session set “_FIX_CONTROL” = “11814428:0″;

Additional note:

1. So what exactly X$KTFBUE table is used for? X$KTFBUE is a dynamically generated map of used extents in locally managed tablespace.

The followings are the definitions for the columns in x$ktfbue.

ADDR : N/A
INDX : N/A
INST_ID : N/A
KTFBUESEGTSN : ts# containing this segment
KTFBUESEGFNO : Relative number of the file containing the segment header
KTFBUESEGBNO : segment header block number
KTFBUEEXTNO : Extent number
KTFBUEFNO : Relative number of the file containing the extent
KTFBUEBNO : Starting block number of the extent
KTFBUEBLKS : Size of the extent in ORACLE blocks
KTFBUECTM : commit_jtime,Commit Time of the undo in the extent expressed as Julian date
KTFBUESTT :commit_wtime,Commit Time of the undo in the extent expressed as wall clock time
KTFBUESTA : Transaction Status of the undo in the extent;1, ‘ACTIVE’, 2, ‘EXPIRED’, 3, ‘UNEXPIRED’, 0 for non‐undo

2. Another interesting part is that the total number of rows in X$KTFBUE table was about 99 million rows while the total number of extents in the database was also around 99 million rows. So reducing the total number of extent in the database seem a good way to reduce this stats issue. Pay attention to the INITIAL and NEXT in the storage clause. Setting too small will have a large number of extents and create headache in querying fixed tables.

Fixed Objects Statistics and why they are important

Fixed objects are the x$ tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a suboptimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.

Prior to Oracle Database 12c fixed object statistics are not created or maintained by the automatic statistics gathering job. You can collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics asDBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. Blocks is always set to 0 since the x$ tables are in memory structures only and are not stored on disk. You must have the ANALYZE ANY DICTIONARY or SYSDBAprivilege or the DBA role to update fixed object statistics.

Because of the transient nature of the x$ tables it is important that you gather fixed object statistics when there is a representative workload on the system. This may not always be feasible on large system due to additional resource need to gather the statistics. If you can’t do it during peak load you should do it after the system has warmed up and the three key types of fixed object tables have been populated:

Structural data - for example, views covering datafiles, controlfile contents, etc

Session based data - for example, v$session, v$access, etc.
Workload data - for example, v$sql, v$sql_plan,etc

It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module, or make changes to the database configuration. For example if you increase the SGA size then all of the x$ tables that contain information about the buffer cache and shared pool may change significantly, such as x$ tables used in v$buffer_pool or v$shared_pool_advice.

From Oracle Database 12c the automatic statistics gathering job will gather statistics for fixed tables that have missing stats. For this to happen, there will need to be some time available inside the batch window after statistics for the other tables in the system have been gathered. Even with this new functionality, it is still good practice to gather fixed table stats with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS when there's a representative workload running, especially after major changes have been made to the system.

Some Tips : DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

Starting in Oracle 10g we see a new package gather_fixed_stats for analyzing the dictionary fixed structures (the X$ tables). We now have three types of stats to analyze for the SQL optimizer:

Table/object/schema stats: Via dbms_stats.gather_table_stats. Orgather_schema_stats. Traditional metadata from data tables.
System stats: Via dbms_stats.gather_system_stats: OS statistics (disk, CPU timings).
Dictionary objects: Used to make dictionary queries more efficient. Thegather_fixed_objects_stats collects the same metadata as gather_table_stats, excepts for the number of blocks. This is because the x$ structures and the v$ views only exists in the RAM of the SGA.

The docs note "Analyze fixed objects only once, unless the workload footprint changes. You must be connected a SYS (or a user with SYSDBA) to invokedbms_stats.gather_fixed_objects_stats.

Just like the workload statistics, Oracle recommends that you analyze the x$ tables only once, and during a typical database workload.
exec dbms_stats.gather_schema_stats('sYS?,gather_fixed=>TRUE)

exec dbms_stats.gather_fixed_objects_stats(?ALL?);

Oracle notes that the data dictionary now contains several classes of x$ structures and v$ views: See here for all important v$ views.

Structural fixed data - for example, v$datafile, v$datafile_header, &c

Session based fixed data - Such as v$session, v$access, &c.

v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat

Workload fixed data - Such as v$sql, v$sql_plan
SQL data:

v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines

We also see the new procedures dbms_stats.export_fixed_objects_stats anddbms_stats.import_fixed_ objects_stats for migrating production workload statistics into test and development instances.

Re-Analyzing fix object statistics

Oracle recommends a single analyze of data dictionary and x$ fixed structures for the cost-based optimizer, but it is not clear when it is necessary to re-analyze the v$ views and x$ structures.

gather_fixed_objects_stats usage tips

You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
Also note Bug 3982803 - OERI[kcbshcb_1] with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
Andrew Holdsworth of Oracle Corporation notes that dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters:

'the payback from good statistics management and execution plans will exceed any benefit of init.ora tuning by orders of magnitude?

Fixed Table Statistics

The data dictionary has many fixed tables, such as X$ tables. Oracle suggests that you also collect statistics for these objects, however, less frequently than the other normal objects.

Another procedure, gather_fixed_objects_stats, is primarily aimed at collecting statistics of fixed objects. This procedure takes the following arguments:

STATTAB: The user statistics table identifier describing where to save the current statistics. Default value is NULL for dictionary collection.
STATID: The optional identifier to associate with these statistics within STATTAB. Default value is also NULL
STATOWN: The schema containing STATAB. Default value is NULL.
NO_INVALIDATE: Do not invalidate the dependent cursors if it is set to TRUE. Default value is FALSE.

The following example shows different formats:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', -gather_fixed=>TRUE) ;

PL/SQL procedure successfully completed.

You can also use the gather_fixed_objects_stats procedure to collect statistics.

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (?ALL?);

Oracle suggests the following best practices for collecting statistics.

Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.
Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.
For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically.

In the next section, we will examine the changes introduced for the dbms_stats package.

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those parameters are as follows:

Granularity
Degree

Granularity

When the GLOBAL AND PARTITION option is specified, Oracle gathers the global and partition level statistics. No sub-partition level statistics are gathered even it is composite partitioned object.

Degree

Monday, February 20, 2017

All About Statistics In Oracle

In this post I'll try to summarize all sorts of statistics in Oracle, I strongly recommend reading the full article, as it contains information you may find it valuable in understanding Oracle statistics.

#####################################
Database | Schema | Table | Index Statistics
#####################################

Gather Database Statistics:
=======================
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 decide whether to collect index statistics 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 above parameters are valid for all kind of statistics (schema,table,..) except Gather_SYS.
Note: Skew data means the data inside a column is not uniform, there is a particular one or more value are being repeated much than other values in the same column, for example the gender column in employee table with two values (male/female), in a construction or security service company, where most of employees are male workforce,the gender column in employee table is likely to be skewed but in an entity like a hospital where the number of males almost equal the number of female workforce, the gender column is likely to be not skewed.

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 the "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU overhead during gathering DB statistics.

Starting from Oracle 10g, Oracle introduced an automated task gathers statistics on all objects in the database that having [stale ormissing] statistics, To check the status of that task:
SQL> select status from dba_autotask_client where client_name = 'auto optimizer stats collection';

To Enable Automatic Optimizer Statistics task:
SQL> BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
   client_name => 'auto optimizer stats collection',
   operation => NULL,
   window_name => NULL);
   END;
   /

In case you want to Disable Automatic Optimizer Statistics task:
SQL> BEGIN
   DBMS_AUTO_TASK_ADMIN.DISABLE(
   client_name => 'auto optimizer stats collection',
   operation => NULL,
   window_name => NULL);
   END;
   /

To check the tables having stale statistics:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

[update on 03-Sep-2014]
Note: In order to get an accurate information from DBA_TAB_STATISTICS or (*_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS) views, you should manually run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to refresh it's parent table mon_mods_all$ from SGA recent data, or you have wait for an Oracle internal that refresh that table once a day in 10g onwards [except for 10gR2] or every 15 minutes in 10gR2 or every 3 hours in 9i backwards. or when you run manually run one of GATHER_*_STATS procedures.
[Reference: Oracle Support and MOS ID 1476052.1]

Gather SCHEMA Statistics:
======================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'SCOTT',
estimate_percent=>10,
degree=>1,
cascade=>TRUE,
options=>'GATHER STALE');

Gather TABLE Statistics:
====================
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.
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 histograms for columns have skewed value should test skewness first>.
FOR ALL INDEXED COLUMNS : Collect histograms for columns have indexes only.

Note: Truncating a table will not update table statistics, it will only reset the High Water Mark, you've to re-gather statistics on that table.

Inside "DBA BUNDLE", there is a script called "gather_stats.sh", it will help you easily & safely gather statistics on specific schema or table plus providing advanced features such as backing up/ restore new statistics in case of fallback.
To learn more about "DBA BUNDLE" please visit this post:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

Gather Index Statistics:
===================
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:
---------------------------------------------

First Check the last analyzed date:
------ -----------------------------------
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED
     from dba_tab_statistics where table_name='X$KGLDP';
Second Export the current fixed stats in a table: (in case you need to revert back)
------- -----------------------------------
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE
     ('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
SQL> EXEC dbms_stats.export_fixed_objects_stats
     (stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Third Gather the fixed objects stats:
------- ------------------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats;

Note:
In case you experienced a bad performance on fixed tables after gathering the new statistics:

SQL> exec dbms_stats.delete_fixed_objects_stats(); SQL> 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, (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 optimizer how fast the DB 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.

Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-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 Existence 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 (e.g. the number of countries is always less than 254).
-Height balanced histograms: are similar to frequency histograms in their design, but distinct values > 254
See an 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.
> Help 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 histograms and disabling Oracle histograms generation.

Cautions:
– Do not create them on Columns that are not being queried.
– Do not create them on every column of every table.
– Do not create them on the primary key column 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_schema_stats
(ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
   method_opt => 'for all columns size auto',
   degree => 7);

method_opt:
FOR COLUMNS SIZE AUTO => Fastest. you can specify one column 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 histograms for columns have skewed value .
FOR ALL INDEXES COLUMNS   => collect histograms for columns have indexes.

Note: AUTO & SKEWONLY will let Oracle decide whether to create the Histograms or not.

Check the existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms
   where OWNER='SCOTT' table_name='EMP' 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 gather Histograms: 11g
------------------------------
[This will change the default table options]
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 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 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 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);

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"
===============
What is Pending Statistics:
Pending statistics is a feature let you test the new gathered statistics without letting the CBO (Cost Based Optimizer) use them "system wide" unless you publish them.

How to use Pending Statistics:
Switch on pending statistics mode:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','FALSE');
Note: Any new statistics will be gathered on the database will be marked PENDING unless you change back the previous parameter to true:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');

Gather statistics: "as you used to do"
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES');
Enable using pending statistics on your session only:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
Then any SQL statement you will run will use the new pending statistics...

When proven OK, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS();

Once you finish don't forget to return the Global PUBLISH parameter to TRUE:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');
>If you didn't do so, all new gathered statistics on the database will be marked as PENDING, the thing may confuse you or any DBA working on this DB in case he is not aware of that parameter change.

Lock Statistics:
=============
Gathering new statistics is not always a good approach, this may change your applications queries'/reports' execution plans to the worst, it's not guaranteed that gathering new statistics will lead to better execution plans ! .I've learned this lesson before in the hard way! but having a backup of the old statistics before gathering new ones has saved my day!.
This is why you want to avoid having a such scenario, where one of the DBA's in your team has accidentally gathered new statistics on the whole DB ـــscrambling most of execution plans of application queries, in the hope of generating better execution plans. In this case you need to lock the statistics of one or more schema or on key tables in order to prevent their statistics from being refreshed by such unattended maintenance activities.

To lock the statistics on all tables under a specific schema:
SQL> exec dbms_stats.lock_schema_stats('SCHEMA_NAME');
e.g. exec dbms_stats.lock_schema_stats('SCOTT');

To lock the statistics on a specific table:

SQL> exec dbms_stats.lock_table_stats('OWNER','TABLE_NAME'');
e.g. exec dbms_stats.lock_table_stats('SCOTT','EMP');

When you have a need to gather new statistics on those tables that having their statistics locked, you need first to unlock the statistics then gather a new statistics as usual.

To check all tables that have their statistics locked:
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED, STATTYPE_LOCKED from DBA_TAB_STATISTICS
where STATTYPE_LOCKED is not null
and OWNER not in ('SYS','SYSTEM','SQLTXPLAIN','WMSYS')
order by OWNER, TABLE_NAME;

To unlock all tables under a specific schema:
SQL> exec dbms_stats.unlock_schema_stats('SCHEMA_NAME');
e.g. exec dbms_stats.unlock_schema_stats('SCOTT');

To unlock a specific table:
SQL> exec dbms_stats.unlock_table_stats('OWNER','TABLE_NAME'');
e.g. exec dbms_stats.unlock_table_stats('SCOTT','EMP');

=========
Advanced:
=========

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   /