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.