Real-Time Statistics in Oracle 19c

By -

 Until Oracle version 12c, executing DML statements on a table (either using the conventional method or direct-path) would not update the table’s statistics, and online statistics gathering was only possible when creating an index.

In version 12c, a minor improvement was introduced: during a bulk load operation on a table, its statistics could be updated online. However, for DML statements executed in conventional mode, the table’s statistics still remained unchanged.

One of the new features in Oracle 19c is Real-Time Statistics, which allows certain important statistics to be updated online during the execution of DML statements.

This new feature only improves the process of statistics gathering — the underlying mechanism for collecting statistics remains the same as before in this Oracle version.

In the example below, we will explore this new Oracle feature in more detail.

Example:
We create the table mytbl with the following command

SQL> CREATE TABLE usef.mytbl (id NUMBER, name VARCHAR2(100));

Table created

As expected, no statistics are recorded for this table:

SQL> select table_name, num_rows,last_analyzed from dba_tab_statistics where table_name=’MYTBL’;
Press enter or click to view image in full size
SQL> select table_name, column_name, low_value, high_value,notes,LAST_ANALYZED from dba_tab_col_statistics where table_name=’MYTBL’;

no rows selected

We manually update the statistics for this table:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(‘USEF’, ‘MYTBL’, METHOD_OPT=>’FOR ALL COLUMNS SIZE 5′);

PL/SQL procedure successfully completed
SQL> select table_name, num_rows,last_analyzed from dba_tab_statistics where table_name=’MYTBL’;
Press enter or click to view image in full size

Now, let’s insert 1,000 records into this table:

SQL> BEGIN

  2     for i in 1..1000 loop

  3        insert into usef.MYTBL values(i,’DBA’);

  4     end loop;

  5  END;

  6  /

PL/SQL procedure successfully completed

We check the statistics in the dba_tab_statistics and dba_tab_col_statistics views:

select owner,table_name, num_rows,last_analyzed,notes from dba_tab_statistics where table_name=’MYTBL’;
Press enter or click to view image in full size
select column_name, low_value, high_value,notes,LAST_ANALYZED from dba_tab_col_statistics s where notes = ‘STATS_ON_CONVENTIONAL_DML’ and table_name=’MYTBL’;
Press enter or click to view image in full size

By checking the NOTES column in these views, we can see that the statistics were updated as STATS_ON_CONVENTIONAL_DML. However, these gathered statistics are written to disk periodically, and the following command can be used to flush them from memory to disk when needed:

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed

A FULL TABLE SCAN on mytbl now has a cost of 3:

SQL> select count(*) from mytbl;
Press enter or click to view image in full size

If we insert another 1 million rows into this table, we will see that the execution plan changes automatically:

BEGIN

   for i in 1..1000000 loop

      insert into usef.MYTBL values(i,’DBA’);

   end loop;

END;

/

In the updated plan, the cost of a FULL TABLE SCAN for this table changes to 550:

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed
SQL> select * from mytbl;
Press enter or click to view image in full size

When we execute the following query, we can confirm that the statistics gathered during the DML operations are indeed used in the execution plan:

SQL> select count(*) from usef.mytbl;

  COUNT(*)

———-

   1000000
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>’TYPICAL‘));

Plan hash value: 404528059
----------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |   550 (100)|      |
|   1 |  SORT AGGREGATE    |       |     1 |            |      |
|   2 |   TABLE ACCESS FULL| MYTBL | 1001K |   550   (1)|00:00:01|
----------------------------------------------------------------

Note

   – dynamic statistics used: statistics for conventional DML

The Real-Time Statistics feature can be disabled using the hidden parameter optimizer_gather_stats_on_conventional_dml_ at the system or session level. It can also be disabled for a specific statement using the NO_GATHER_OPTIMIZER_STATISTICS hint.

Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Boolean Data Type in Oracle ِDatabase 23ai

By -
Image
  Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation. SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN ); Table created You can also use the keyword  BOOL  instead of  BOOLEAN : SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL > CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE ); Table created To populate the  is_accept  column, you can use the three keywords:  TRUE ,  FALSE , and  NULL : SQL > insert into table1 values ( 1 , true ); 1 row inserted SQL > insert into table1 values ( 2 , null ); 1 row inserted SQL > insert into table1 values ( 3 , false ); 1 row inserted SQL > select * from table1; ID IS_ACCEPT ---------- ---------- ...
Read more