Configuring Options for Optimizer Statistics Gathering

While collecting statistics for Objects in database , there are few preferences need to be set at DB,TABLE,SCHEM & GLOBAL level.
We will cover setting Preferences for statististics collection as below.
About Optimizer Statistics Preferences
The optimizer statistics preferences set the default values of the parameters used by automatic statistics collection and the DBMS_STATS statistics gathering procedures.
Purpose of Optimizer Statistics Preferences
Preferences enable you to maintain optimizer statistics automatically when some objects require settings that differ from the default.
Preferences give you more granular control over how Oracle Database gathers statistics.
You can set optimizer statistics preferences at the following levels:
=> Table
=> Schema
=> Database (all tables)
=> Global (tables with no preferences and any tables created in the future)
The DBMS_STATS procedures for setting preferences have names of the form SET_*_PREFS.

Procedures for Setting Preferences for Statistics collections.
Below mentioned are available procedure for setting Preferences.

=> SET_TABLE_PREFS -> Specified table only.
=> SET_SCHEMA_PREFS -> All existing tables in the specified schema.
=> SET_DATABASE_PREFS-> All user-defined schemas in the database. You can include system-owned schemas such as SYS and SYSTEM by setting the ADD_SYS parameter to true.
=> SET_GLOBAL_PREFS-> Any table that does not have an existing table preference.

Procedures for Getting Preferences for Statistics .
To query the current preferences, use the DBMS_STATS.GET_PREFS function.

1=> SET_TABLE_PREFS Procedure

This procedure sets the statistics preferences of the specified table in the specified schema.

DBMS_STATS.SET_TABLE_PREFS (
    ownname    IN  VARCHAR2,
    tabname    IN  VARCHAR2,
    pname      IN  VARCHAR2,
    pvalue     IN  VARCHAR2);

Preference Name as => APPROXIMATE_NDV_ALGORITHM,AUTO_STAT_EXTENSIONS,CASCADE,DEGREE,ESTIMATE_PERCENT,GRANULARITY,INCREMENTAL,INCREMENTAL_LEVEL,
INCREMENTAL_STALENESS,METHOD_OPT,NO_INVALIDATE,OPTIONS,PREFERENCE_OVERRIDES_PARAMETER,PUBLISH,STALE_PERCENT,TABLE_CACHED_BLOCKS

Demontrations

Let we test a scenrio as below. In this DEMO we will be chaning CASCADE preferences value as FALSE .
=> Get count of rows for table as below .

SQL> SELECT COUNT(*) FROM EMP_dATA;
  COUNT(*)
----------
      1300

=> Get Index value and it's Last gather time as below .

SQL> SELECT INDEX_NAME,LAST_ANALYZED FROM dba_indexes where table_name='EMP_DATA';

INDEX_NAME   LAST_ANALYZED
--------------------
IDX_EMPD      28-MAY-2022 16:09:23

=> Get Current Value for CASCADE parameter as below.

SELECT DBMS_STATS.GET_PREFS  ('CASCADE', 'ABHI_TEST','EMP_DATA')  AS     "CASCADE" FROM DUAL;

CASCADE
--------------------------------
DBMS_STATS.AUTO_CASCADE

=> Now let we change CASCASE value to FALSE as below.

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('ABHI_TEST','EMP_DATA','CASCADE','false');
PL/SQL procedure successfully completed.

=> Let we DELETE few records and commit as below from TABLE.

SQL> DELETE FROM EMP_dATA WHERE ROWNUM<1000;
999 rows deleted.
SQL> COMMIT;
Commit complete.

=> Check current count as below.

SQL> SELECT COUNT(*) FROM EMP_DATA;

  COUNT(*)
----------
       301

=> Let we do SELECT as below from TABLE.

SQL> SELECT ID FROM EMP_DATA WHERE ID > 1000;

=> Check Execution plan for above query 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	bqa1z6970bs22, child number 0
-------------------------------------
SELECT ID FROM EMP_DATA WHERE ID > 1000
Plan hash value: 1124357846

-----------------------------------------------------------------------------
| Id  | Operation	 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	    |	    |	    |	  2 (100)|	    |
|*  1 |  INDEX RANGE SCAN| IDX_EMPD |	300 |  1200 |	  2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID">1000)

18 rows selected.


=> Now let check statistics as for INDEX from DBA_IND_STATISTICS views.

SQL> SELECT OWNER,INDEX_NAME,TABLE_NAME,NUM_ROWS FROM DBA_IND_STATISTICS WHERE TABLE_NAME='EMP_DATA';

OWNER        INDEX_NAME  TABLE_NAME	  NUM_ROWS
--------------- ----------
ABHI_TEST    IDX_EMPD    EMP_DATA	      1300

As we can see It's still showing 1300 records ,

=> Now let we gather statistics without using CASCADE clause and this will default to "FALSE" as we set above.

SQL> exec dbms_stats.gather_table_stats('ABHI_TEST', 'EMP_DATA');

PL/SQL procedure successfully completed.

=> Now let check statistics as for INDEX from DBA_IND_STATISTICS views.

SQL> SELECT OWNER,INDEX_NAME,TABLE_NAME,NUM_ROWS FROM DBA_IND_STATISTICS WHERE TABLE_NAME='EMP_DATA';

OWNER        INDEX_NAME  TABLE_NAME	  NUM_ROWS
--------------- ----------
ABHI_TEST    IDX_EMPD    EMP_DATA	      1300

It's still showing 1300 rows , hence CASCADE is not working . Let we change CASCADE to it's DEFAULT value as DBMS_STATS.AUTO_CASCADE

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('ABHI_TEST','EMP_DATA','CASCADE','DBMS_STATS.AUTO_CASCADE');

PL/SQL procedure successfully completed.

=> Now let we gather statistics without using CASCADE clause and this will default to "DBMS_STATS.AUTO_CASCADE" as we set above.

SQL> exec dbms_stats.gather_table_stats('ABHI_TEST', 'EMP_DATA');

=> Now let check statistics as for INDEX from DBA_IND_STATISTICS views.

SQL> SELECT OWNER,INDEX_NAME,TABLE_NAME,NUM_ROWS FROM DBA_IND_STATISTICS WHERE TABLE_NAME='EMP_DATA';

OWNER        INDEX_NAME  TABLE_NAME	  NUM_ROWS
--------------- ----------
ABHI_TEST    IDX_EMPD    EMP_DATA	      301


2=> SET_SCHEMA_PREFS Procedure

This procedure sets the statistics preferences of all tables owned by the specified user.

Syntax

DBMS_STATS.SET_SCHEMA_PREFS (
    ownname   IN   VARCHAR2,
    pname     IN   VARCHAR2,
    pvalue    IN   VARCHAR2);

Preference Name are same as above SET_TABLE_PREF procedure.

Demontrations

In below demo we will be changing DEGREE preferences for SCHEMA level

=> Let we get currect Value for DEGREE preferences as below.

SQL> SELECT DBMS_STATS.GET_PREFS  ('DEGREE', 'ABHI_TEST')  AS "STAT_PREFS" FROM DUAL;

STAT_PREFS
--------------------------------------------------------------------------------
NULL

SQL>

=> Let we change currect Value for DEGREE preferences as below.

SQL> EXEC DBMS_STATS.SET_SCHEMA_PREFS('ABHI_TEST','DEGREE','90');

PL/SQL procedure successfully completed.

=> Now we will gather statistics for Schema as below . DEGREE will be dafault to 90 as we set above,

SQL> exec dbms_stats.gather_SCHEMA_stats('ABHI_TEST');

PL/SQL procedure successfully completed.

=> If we check execution plan for stats gather we can see difference as below

SQL>  select * from table(dbms_xplan.display_cursor(sql_id=>'886nq7huuxjfb'));

PLAN_TABLE_OUTPUT
------------------------------------------
SQL_ID	886nq7huuxjfb, child number 0
-------------------------------------
select substrb(dump(val,16,0,64),1,240) ep,  freq, cdn, ndv, (sum(pop)

Plan hash value: 1884882140

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			      | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		      | 	 |	 |	 |    22 (100)| 	 |	  |	 |	      |
|   1 |  WINDOW SORT			      | 	 |  5218 |   239K|    22   (5)| 00:00:01 |	  |	 |	      |
|   2 |   VIEW				      | 	 |  5218 |   239K|    22   (5)| 00:00:01 |	  |	 |	      |
|   3 |    WINDOW BUFFER		      | 	 |  5218 | 52180 |    22   (5)| 00:00:01 |	  |	 |	      |
|   4 |     PX COORDINATOR		      | 	 |	 |	 |	      | 	 |	  |	 |	      |
|   5 |      PX SEND QC (RANDOM)	      | :TQ10001 |  5218 | 52180 |    22   (5)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   6 |       HASH GROUP BY		      | 	 |  5218 | 52180 |    22   (5)| 00:00:01 |  Q1,01 | PCWP |	      |
|   7 |        PX RECEIVE		      | 	 |  5218 | 52180 |    22   (5)| 00:00:01 |  Q1,01 | PCWP |	      |
|   8 | 	PX SEND HASH		      | :TQ10000 |  5218 | 52180 |    22   (5)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 | 	 HASH GROUP BY		      | 	 |  5218 | 52180 |    22   (5)| 00:00:01 |  Q1,00 | PCWP |	      |
|  10 | 	  PX BLOCK ITERATOR	      | 	 |  5500 | 55000 |    21   (0)| 00:00:01 |  Q1,00 | PCWC |	      |
|* 11 | 	   INDEX SAMPLE FAST FULL SCAN| IDX_NULL |  5500 | 55000 |    21   (0)| 00:00:01 |  Q1,00 | PCWP |	      |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - access(:Z>=:Z AND :Z<=:Z) filter("EMP_NO" IS NOT NULL)

Note
-----
   - Degree of Parallelism is 90 because of table property

44 rows selected.

=> Let we reset to DEFAULT as below,

SQL> EXEC DBMS_STATS.SET_SCHEMA_PREFS('ABHI_TEST','DEGREE','DBMS_STATS.AUTO_DEGREE');
PL/SQL procedure successfully completed.

3=> SET_DATABASE_PREFS Procedure

This procedure sets the statistics preferences of all the tables, excluding the tables owned by the database.
These tables can by included by passing TRUE for the add_sys parameter.

Syntax

DBMS_STATS.SET_DATABASE_PREFS (
    pname            IN   VARCHAR2,
    pvalue           IN   VARCHAR2,
    add_sys          IN   BOOLEAN DEFAULT FALSE);

4=> SET_GLOBAL_PREFS Procedure

This procedure sets statistics preferences at the global level.

Syntax

DBMS_STATS.SET_GLOBAL_PREFS (
    pname     IN   VARCHAR2,
    pvalue    IN   VARCHAR2);

We can set several different preferences for DATABASE and GLOBAL level as we did for TABLE and SCHEME level.

Views for getting details.

Using below Query we can get details for Preferences which are defined at any granules.

COL OWNER FORMAT a5
COL TABLE_NAME FORMAT a15
COL PREFERENCE_NAME FORMAT a20
COL PREFERENCE_VALUE FORMAT a30
SELECT * FROM DBA_TAB_STAT_PREFS where OWNER='ABHI_TEST';