STATISTICS COLLECTION FOR TABLE USING DBMS STATS ORACLE

Create some sample tables of same structure for our test

kIsH@zxd00<^>create table XSAL1 as select * from XSAL nologging;

Table created.

Insert some rows into table and check the number of modifications

kIsH@zxd00<^>select inserts from dba_tab_modifications where table_name in ('XSAL1');

   INSERTS
----------
   1000000

First check the settings of statistics before planning for table. For partitioned tables, the process is different. Always leave as many parameter like the one marked with *** to auto wherever applicable. This will make oracle to choose the optimum value rather than choosing the manual suboptimal value.

STATS_PARAM
------------------------------------------------
AUTOSTATS_TARGET =====> AUTO ***
CASCADE =====> DBMS_STATS.AUTO_CASCADE ***
CONCURRENT =====> OFF
DEGREE =====> NULL
ESTIMATE_PERCENT =====> DBMS_STATS.AUTO_SAMPLE_SIZE  ***
GRANULARITY =====> AUTO ***
INCREMENTAL =====> FALSE
INCREMENTAL_LEVEL =====> PARTITION
INCREMENTAL_STALENESS =====> ALLOW_MIXED_FORMAT
METHOD_OPT =====> FOR ALL COLUMNS SIZE AUTO ***
OPTIONS =====> GATHER
STALE_PERCENT =====> 10

12 rows selected.

Parallel degree is determined by the formula.

Degree = cpu_count * parallel_threads_per_cpu = ?

If you exceed the above calculated value, there is no use in specification of value higher than ?. PQ or parallel slaves will not be utilized beyond ?

kIsH@zxd00<^>show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     1
kIsH@zxd00<^>show parameter parallel_thread

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu             integer     1

Only way to improve manual statistics speed is to increase the degree value which throw parallel workers to gather statistics

kIsH@zxd00<^>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'KISH',TABNAME => 'XSAL1',DEGREE => 8)

PL/SQL procedure successfully completed.

Leave a Reply