Oracle 12c gather statistics oracle deep dive

I referred some reference from my favorite Tim Hall blog (ORACLE_BASE)

A.Basic of optimizer statistics: –

The optimizer cost model relies on statistics collected about the objects involved in a query, and the database and host where the query runs. Statistics are critical to the optimizer’s ability to pick the best execution plan for a SQL statement.

Object	What influences statistics	Views
Table Statistics	1.No of rows. 2.No of blocks. 3.Avg row length.	1.DBA_TABLES 2.DBA_TAB_STATISTICS
Column Statistics	1.No of distinct values. 2.No of nulls 3.Data distribution(Histogram) 4.Extended statistics	1.DBA_TAB_COLS 2.DBA_TAB_COL_STATISTICS 3.USER_PART_COL_STATISTICS 4.USER_SUBPART_COL_STATISTICS
Index Statistics	No of distinct values. No of leaf blocks. Clustering g factor	1.DBA_INDEXES 2.DBA_IND_STATISTICS
Histograms	Data distribution in columns	DBA_TAB_HISTOGRAMS
Extended Stats	Relation between data stored in different columns of the same table	DBA_TAB_COL_STATISTICS
System statistics	I/O performance and utilization. CPU performance and utilization	sys.aux_stats$

 

B.Configuring Automatic Optimizer Statistics Collection Using Cloud Control FOR gather statistics oracle 

Please go to “Administration”->”Oracle Schedure”->”Automated Maintenance Tasks”

 

You can see 3 default automatic jobs scheduled. You must choose “Optimizer Statistics Gathering”

Now click on “Optimizer Statistics Gathering”

You may click on “Global Statistics Gathering Options” for viewing configured parameters.

 

You must choose “Configure” button to enable or disable /Change Window.

You may check by sqlplus also to check status of  Automatics Optimizer Stats Collection

D.Some standard statistics gather  FOR gather statistics oracle 

Gather statistics:-

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’); EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15, cascade => TRUE);

 EXEC DBMS_STATS.gather_table_stats(ownname=>’C##TEST’,tabname=>’TT1′,method_opt=>’FOR COLUMNS C1′);

Delete statistics:-

EXEC DBMS_STATS.delete_database_stats;

EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);

EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMP’);

EXEC DBMS_STATS.delete_column_stats(‘SCOTT’, ‘EMP’, ‘EMPNO’);

EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMP_PK’);

E.Determining When Optimizer Statistics Are Stale  FOR gather statistics oracle 

A.First create a demo table and load some data.

SQL> set linesize 300;

SQL> create table tab1(c1 number(10),c2 char(100));

Table created.

SQL> insert into tab1 select rownum,’A’ from dual connect by rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

B.Now statistics are not populated.

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS     BLOCKS STA

———- ———- —

C.Let me gather statistics now

 

SQL>  exec dbms_stats.gather_table_stats(‘TEST’,’TAB1′);

PL/SQL procedure successfully completed.

 

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS     BLOCKS STA

———- ———- —

9999        244 NO

D.Now again let me load more data

SQL> insert into tab1 select rownum,’A’ from dual connect by rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

E.Still DBA_TAB_STATISTICS shows STALE statistics NO.

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS     BLOCKS STA

———- ———- —

9999        244 NO

 

F.Now let me flush/write the database monitoring information from memory to disk

 

G.Now the statistics will show STALE

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS     BLOCKS STA

———- ———- —

9999        244 YES

F.Gathering Statistics for Fixed Objects  FOR gather statistics oracle 

Fixed objects are dynamic performance tables and their indexes. These objects record current database activity. Unlike other database tables, the database does not automatically use dynamic statistics for SQL statement referencing X$ tables when optimizer statistics are missing. Instead, the optimizer uses predefined default values. These defaults may not be representative and could potentially lead to a suboptimal execution plan. Thus, it is important to keep fixed object statistics current.

To gather schema statistics using GATHER_FIXED_OBJECTS_STATS:

1. Start SQL*Plus, and connect to the database with the appropriate privileges for the procedure that you intend to run.

2. Run the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure, specifying the desired parameters. Chapter 12 Gathering Optimizer Statistics Manually

G.Gathering Statistics for Volatile Tables Using Dynamic Statistics  FOR gather statistics oracle 

A.Let us think my table TAB2 is extremely volatile. So We want dynamic statistics to be collected on the fly to avoid optimizer gather wrong statistics.

First let me create the demo table.

SQL> create table tab2(c1 number(10),c2 char(100));

Table created.

Let me gather statistics now.

SQL> exec dbms_stats.gather_table_stats(‘TEST’,’TAB2′);

PL/SQL procedure successfully completed.

 

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB2′;

NUM_ROWS     BLOCKS STA

———- ———- —

0          0 NO

Now I need to load data again

SQL> insert into tab2 select rownum,’A’ from dual connect by rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

Now check explain plan does not show proper cardinality

SQL> set autotrace traceonly;

SQL> select * from tab2;

9999 rows selected.

Execution Plan

———————————————————-

Plan hash value: 2156729920

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |     1 |   115 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TAB2 |     1 |   115 |     2   (0)| 00:00:01 |

Now We need to delete the statistics and lock the statistics. So optimizer will not gather statistics if there is any change in table.

sqlplus test/test

SQL> exec dbms_stats.delete_table_stats(‘TEST’,’TAB2′);

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.lock_table_stats(‘TEST’,’TAB2′);

PL/SQL procedure successfully completed.

Now as there is no statistics, Oracle optimizer uses dynamic sampling.

The following table describes the levels for dynamic statistics. Note the following:

• If dynamic statistics are not disabled, then the database may choose to use dynamic statistics when a SQL statement uses parallel execution.

• If the OPTIMIZER_ADAPTIVE_FEATURES initialization parameter is true, then the optimizer uses dynamic statistics when relevant SQL plan directives exist. The database maintains the resulting statistics in the server result cache, making them available to other queries.

SQL> set autotrace traceonly;

SQL>  select * from tab2;

9999 rows selected.

Execution Plan

———————————————————-

Plan hash value: 2156729920

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |  8435 |   947K|    68   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TAB2 |  8435 |   947K|    68   (0)| 00:00:01 |

————————————————————————–

Note

—–

   – dynamic statistics used: dynamic sampling (level=2)

H.Enabling Concurrent Statistics Gathering  FOR gather statistics oracle 

Oracle Database 11g Release 2 (11.2.0.2) introduces a new statistics gathering mode, ‘concurrent statistics gathering’. The goal of this new mode is to enable a user to gather statistics on multiple tables in a schema (or database), and multiple (sub)partitions within a table concurrently. Gathering statistics on multiple tables and (sub)partitions concurrently can reduce the overall time it takes to gather statistics by allowing Oracle to fully utilize a multi-processor environment.

To enable concurrent statistics gathering, use the DBMS_STATS.SET_GLOBAL_PREFS procedure to set the CONCURRENT preference. Possible values are as follows:

• MANUAL Concurrency is enabled only for manual statistics gathering.

• AUTOMATIC Concurrency is enabled only for automatic statistics gathering.

• ALL Concurrency is enabled for both manual and automatic statistics gathering.

• OFF Concurrency is disabled for both manual and automatic statistics gathering. This is the default value.

This tutorial in this section explains how to enable concurrent statistics gathering.

Create a partitioned table.

SQL> create table t1 (id number, sometext varchar2(50),my_date date) tablespace data partition by hash (id) partitions 16;

Please note that concurrency is OFF.

Check if you have sufficient job process

SQL> show parameter job;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     1000

Now you change concurrency to ‘ALL’

PL/SQL procedure successfully completed.

Create a procedure to parallel insert data (30 sessions will insert data parallely)

 

Procedure created.

SQL> exec manysessions;

PL/SQL procedure successfully completed.

Now gather schema in another session.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘TEST’);

PL/SQL procedure successfully completed.

Please check status now.

SQL> /

I.Incremental Statistics  FOR gather statistics oracle 

:-

In typical data warehousing environment existence of huge partitioned tables is very common, gathering statistics on such tables is challenging tasks. For partitioned tables there are two types of statistics Global and Partition level statistics. Gathering global statistics is very expensive and resource consuming operation as it scans whole table. Hence most of the time people use to reduce estimate_percent down to less than 1 percent. This does helps in reducing time taken to gather stats but may not be sufficient to represent the data distribution. Gathering partition level statistics is not so expensive as it gathers only for the partitions where data has been changed.

Traditionally statistics are gathered in two phase

1. Scan complete table to gather Global statistics

2. Scan only the partitions where data has been changed

Obviously global stats can be derived by using partition level stats like say for example number of rows at table level = just sum number of rows from all the partitions. But global stats like NDV(Number of Distinct Values) which is very important in calculating cardinality can’t be derived so easily. The only way to derive them is by scanning the whole table.

These synopsis data are stored in WRI$_OPTSTAT_SYNOPSIS$ and WRI$_OPTSTAT_SYNOPSIS_HEAD$ tables residing in SYSAUX tablespace. Table WRI$_OPTSTAT_SYNOPSIS$ will grow enormously as there will be individual synopsis created for each hash proportional to distinct value existing at table,partition and column level. Table WRI$_OPTSTAT_SYNOPSIS_HEAD$ will have each record for every table, partition, and column. In 11.1 release gathering incremental statistics would take longer time if you have wide tables with many partitions due to delete statement working on WRI$_OPTSTAT_SYNOPSIS$ table. In 11.2 this issue has been resolved by Range-Hash partitioning the WRI$_OPTSTAT_SYNOPSIS$ table.

Create a range based partition

Insert data in the table

Let me gather the statistics now:-

Please check the analyze date

Please check initial setting of statistics related parameters

Now let me set TRUE to INCREMENTAL statistics

Please check synopsis table now

Now I will again load data only to one partition

Let me gather stats again

Now We will notice that the partition,where we load data is only analyzed avoiding costly full scan of all partitions

We can see the preference for the INCREMENTAL still TRUE

Now let me turn INCREMENTAL statistics to FALSE again

Let me gather statistics again

Now please note statistics updated all the partitions of the table again which will go for  costly full table scan for all partitions

Let me create test seup first

sqlplus test/test
SQL> DROP TABLE tab1 PURGE;
Table dropped.

SQL> CREATE TABLE tab1 (
id               NUMBER,
gender           VARCHAR2(1),
has_y_chromosome VARCHAR2(1),
CONSTRAINT tab1_pk PRIMARY KEY (id),
CONSTRAINT tab1_gender_chk CHECK (gender IN (‘M’, ‘F’)),
CONSTRAINT tab1_has_y_chromosome_chk CHECK (has_y_chromosome IN (‘Y’, ‘N’))
);

Table created.

Now please check there is no histogram collected

Please note that we observe cardinality miscalculation in explain plan,which is evident here.Cardinality mismatch always leads to improper explain plan hence poor performance.

We will also observe that Oracle found that this sql query can be optimized further

At this point, the optimizer has written SQL plan directives into the SGA, but they are not visible to us as they have not been persisted to the SYSAUX tablespace.

We can wait for the database to persist the SQL plan directives, or manually persist them using the DBMS_SPD package.

If we repeat the previous query, the SQL plan directives will be visible.

Now let me rerun the query and query explain plan will show proper cardinality with help of sql plan directive

K.Extended Statistics  FOR gather statistics oracle 

In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, consider a customers table where the values in a cust_state_province column are influenced by the values in a country_id column, because the state of California is only going to be found in the United States. If the Oracle Optimizer is not aware of these real-world relationships, it could potentially miscalculate the cardinality estimate if multiple columns from the same table are used in the where clause of a statement. With extended statistics you have an opportunity to tell the optimizer about these real-world relationships between the columns.

By creating extended statistics on a group of columns, the optimizer can determine a more accurate cardinality estimate when the columns are used together in a where clause of a SQL statement.You can use DBMS_STATS.CREATE_EXTENDED_STATS to define the column group you want to have statistics gathered on as a whole. Once the group has been established Oracle will automatically maintain the statistics on that column group when statistics are gathered on the table.

If we continue with the initial example of the customers table, When the value of cust_state_province is ‘CA’ we know the value of country_id will be 52790 or the USA. There is also a skew in the data in these two columns; because the company used in the SH is based in San Francisco so the majority of rows in the table have the values ‘CA’ and 52790. Both the relationship between the columns and the skew in the data can make it difficult for the optimizer to calculate the cardinality of these columns correctly when they are used together in a query.

Let us connect to SH schema:-

It is evident from explain plain that cardinality estimate incorrect.

Prior to Oracle Database 11g the only option open to us would be to make the optimizer aware of the data skew in both the country_id column (most rows have 52790 as the value) and the cust_state_province column (most rows have ‘CA’ as the value). We can do this by gathering histograms on the skewed columns.

Let me create extended statistics  on the two column used in where clause

We can now see it goes for dynamic sampling and adaptive optimizer feature

Optionally we can see report of the column

Also I need to turn off optimizer dynamic sampling and optimizer adaptive feature to simulate extended statistics.

I will recreate statistics again

Let me gather statistics again

Still it will show bad cardinality estimation

Now let me again gather statistics with skewonly option of histogram.It will show correct estimate.

L.How to enable old statistics from history  FOR gather statistics oracle 

Let me check what are old statistics collected in previous test case

Let me enable older statistics now

Now if we check explain plan,it went back to previous explain plan cost

M.Online Statistics FOR GATHER STATISTICS ORACLE

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations. Gathering statistics as part of the data loading operation means no additional full data scan is required to have statistics available immediately after the data is loaded.

Online statistics gathering does not gather histograms or index statistics, as these types of statistics require additional data scans, which could have a large impact on the performance of the data load. To gather the necessary histogram and index statistics without re-gathering the base column statistics use the DBMS_STATS.GATHER_TABLE_STATS procedure with the new options parameter set to GATHER AUTO. Note that for performance reasons, GATHER AUTO builds histogram using a sample of rows rather than all rows in the table.