DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Real-Time Statistics in Oracle 19c

#oracle #webdev #programming #ai

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
Enter fullscreen mode Exit fullscreen mode

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’;
Enter fullscreen mode Exit fullscreen mode 


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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
SQL> select table_name, num_rows,last_analyzed from dba_tab_statistics where table_name=’MYTBL’;
Enter fullscreen mode Exit fullscreen mode


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
Enter fullscreen mode Exit fullscreen mode

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’;
Enter fullscreen mode Exit fullscreen mode 


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’;
Enter fullscreen mode Exit fullscreen mode


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
Enter fullscreen mode Exit fullscreen mode

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

SQL> select count(*) from mytbl;
Enter fullscreen mode Exit fullscreen mode


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;

/
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
SQL> select * from mytbl;
Enter fullscreen mode Exit fullscreen mode


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
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)