Tag Archives: degree

Understand Gather Stats job and default behavior in Oracle

Understand Gather Stats job and default behavior in Oracle

Gather Stats job is the default job running in Oracle Database for collecting the most used tables stats automatically. Oracle monitor the most DML tables with help of DBA_TAB_MODIFICATIONS view which stores information about the inserts, deletes, and updates to a table. Gather Stats analyzed stats for those tables having more than 10% rows done DML Operations.

Check the Job Status

SQL> select client_name,status from dba_autotask_client;
------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

Check the job timing with following commands

select operation,target,start_time,end_time from dba_optstat_operations where operation='gather_database_stats(auto)';

Enable and disable the gather stats job

-- Disable
exec dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection');
-- Enable
exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection');

Two ways to change the default preferences for Statistics Gather Job

1. Manually used the Parameter setting while executing the DBMS_STATS.GATHER_*_STATS procedure for gather stats.


exec dbms_stats.gather_table_stats(
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
degree=> null,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

2. Oracle defines default values for preferences, you can change it with procedure DBMS_STATS.SET_*_PREFS.
Following are the procedures to change preferences at different level:

DBMS_STATS.SET_*_PREFS Procedure is used to change the default values for of parameters for control statistics collection.
SET_TABLE_PREFS: Lets you specify default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for a specific table
SET_SCHEMA_PREFS: Lets you change the default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for all objects in a specific schema
SET_DATABASE_PREFS: Lets you change the default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for the entire database, including all user schema’s and system schema’s such as SYS and SYSTEM
SET_GLOBAL_PREFS: Sets global statistics preferences; this procedure lets you change the default statistic collection parameters for any object in the database that doesn’t have an existing preference at the table level. If you don’t set table level preferences or you don’t set any parameter explicitly in the DBMS_STATS.GATHER_*_STATS procedure, the parameters default to their global settings.

Example for Check and Change the Default preferences:

--at the database level by using the SET_DATABASE_PREFS procedure:
SQL> execute dbms_stats.set_database_prefs('ESTIMATE_PERCENT','20');

--check default value set in database with get_prefs procedure:
select dbms_stats.get_prefs ('STALE_PERCENT','SH') stale_percent from dual;

Following are the list of preferences used by gather stats:

Used for database may or may not collect index statistics along with the table statistics. By default,(cascade=true) it collect stats for all of a tables plus indexes.

Means to degree of parallelism the database use for gather the statistics.The default setting is DBMS_STATS.AUTO_DEGREE.

ESTIMATE_PERCENT: specifies the percentage of rows the database must use to estimate for the Statistics. By Default DBMS_STATS.AUTO_SAMPLE_SIZE. It’s value from 0% to 100%. 100% means all rows scanned for statistics.
METHOD_OPT: Two values you can used
1. Columns for which the database will collect statistics.
2. Columns on which the database will create histograms.

Means Database must collect statistics for all columns or only for the indexed columns. If you used the FOR ALL INDEXED COLUMNS option, the database will collect statistics only for those columns that have an index on them.

FOR COLUMNS [size_clause] column [size_clause] [,COLUMN [size_clause]…]
Means one or more columns on which the database must gather statistics.
column stands for column_name or extension name or expression used for gather the stats on the specific column or expression for tables.

size_clause used in both cases which tell database to collect histograms for a column.
One option is to supply an integer value indicating the number of histogram buckets in range 1 through 254.
Value 254 : Create the histograms on all the column mentioned in for or all options above.
Value 1 : then it will not create histograms on the column because of single bucket. Setting value 1 remove the histogram if already present
REPEAT : Histogram is collected on those columns which already have histograms.
AUTO : Database determine for the columns for collect histograms based on column usage plus data distribution.
SKEWONLY : Database determine for which columns collect histograms based on column data distribution.
Default value for the METHOD_OPT parameter is FOR ALL COLUMNS SIZE AUTO.

For Example:

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEES',method_opt=> 'for columns size 254 job_id')
PL/SQL procedure successfully completed.

Default value for the NO_INVALIDATE parameter.TRUE means database doesn’t invalidate the dependent cursors of the table.FALSE means database immediately invalidates the dependent cursors. DBMS_STATS.AUTO_INVALIDATE is auto.

Basically used for statistics gathering for partitioned tables. Having following options to set:
ALL: Gathers subpartition-, partition-, and global-level statistics; this setting provides a very accurate set of table statistics but consuming lot of resources and time.
GLOBAL: Gathers global statistics for a table.
PARTITION: Gathers only partition statistics.
GLOBAL AND PARTITION: Gathers the global and partition statistics, but not the subpartition level statistics.
SUBPARTITION: Gathers only subpartition statistics.
AUTO: Default value for the GRANULARITY parameter and determines the granularity based on the partitioning type.


Enabling Parallelism for a SQL Query

Enabling Parallelism for a SQL Query

You found a query running on large table, you want to enable parallelism to check its speed after normal execution.

For using the parallelism on SQL Query, you need to explicitly defines hints on the SQL Query for forcefully used the parallel operations.

Two way to be used parallel operation on SQL Query as
1. Parallel Hint
2. Parallel_index Hint

Parallel hint works on tables as follows:
In following queries, you are specify the parallel 4 means 4 process is used to fetch data from the employees table.

SELECT /*+ parallel(employees,4) */ emp_id, name FROM hr.employees;

In following queries, you specify nothing, Oracle detect the parallel operation based on database initialization parameters:

SELECT/*+ parallel(emp) */ emp_id, name FROM hr.employees emp;

Parallel_index hints
Parallel_index hint used for parallel access to indexes.

SELECT /*+ parallel_index(emp, emp_i4 ,4) */ emp_id, name
FROM employees WHERE deptno = 10;

Two arguments used in parallel_index hint: Table name and Index name. If you do not specify parallel number then oracle choose automatic on db settings.

Both used if you want to test the query without using parallel execution. Sometime Query is executed better in case of non parallel then parallel and your parallel level is set on table degree or instance level (PARALLEL_DEGREE_POLICY=AUTO) and you want to over come this situation for specific query. Then use No_PARALLEL and NO_PARALLEL_INDEX.

Note: As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated. Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.

Check the parallelism of object or index with following query:

select degree from dba_tables where table_name = 'EMPLOYEES';

select index_name,degree from dba_indexes where table_name = 'EMPLOYEES';

Note: If automatic DOP is enabled (PARALLEL_DEGREE_POLICY=AUTO), then the parallelism that you set on objects is ignored.

Change the degree at object level ( table or index):





Script for change parallel degree for all objects in schema

-- For indexes
select 'alter index '||owner||'."'||index_name||'" parallel (degree 4);' from dba_indexes where owner='IC';

-- For Tables
select 'alter table '||owner||'."'||table_name||'" parallel (degree 4);' from dba_tables where owner='IC';

Enable parallel at session level forcefully:

alter session force parallel query parallel N;
--example: Run SQL Query with parallel with 4 process
alter session force parallel query parallel 4;