Oracle 19c New Feature - Real-Time Statistics explored with an Demo

September 17, 2020

Hi DBA Friends,

Oracle 19c New Feature - Real-Time Statistices explored with an Demo:

Oracle Database 19c introduces real-time statistics, which extends online statistics gathering to also include conventional DML statements.

Statistics are normally gathered by automatic statistics gather job which runs inside the database maintenance window – but that is just once a day.

But for volatile tables statistics can go stale between DBMS_STATS job executions, so the new Oracle 19c feature of real-time statistics can help the optimizer generate more optimal plans for such volatile tables.

Bulk load operations will gather all necessary statistics (pre Oracle 19c behavior)- however real-time statistics augment rather than replace traditional statistics.

We have a table called MYOBJECTS which currently has 72383 rows.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL>

SQL> create table myobjects as select * from dba_objects;

Table created.

SQL> set lines 300 pages 20000

SQL>

SQL> select distinct object_type from myobjects where owner ='SYS';

SQL> select * from table (dbms_xplan.display_cursor);

We now insert some additional rows into the table – basically doubling the number of rows in the table.

In earlier versions there is now a possibility of sub-optimal plans being chosen by the optimizer as it is ‘not aware’ of the fact that some DML activity has happened on the table and now the number of rows in the table have increased two-fold.

But now in Oracle 19c, we can see that as part of the INSERT statement, an OPTIMIZER STATISTICS GATHERING operation was also performed.

insert into myobjects select * from myobjects;

commit;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

When a hard parse of the SQL statement occurs, we can see that that the optimizer has detected that additional rows have been added to the table,

This is also indicated in the Note section: dynamic statistics used: statistics for conventional DML

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

alter system flush shared_pool;

select distinct object_type from myobjects where owner='SYS';

select * from table (dbms_xplan.display_cursor);

Real-time statistics are indicated by STATS_ON_CONVENTIONAL_DML in the NOTES column in the *_TAB_STATISTICS and *_TAB_COL_STATISTICS views.

SELECT NUM_ROWS, BLOCKS, NOTES FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'MYOBJECTS';

Prerequisites:

--------------

This feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata and Exadata Cloud Service, as described here. There is a workaround for testing by enabling the "_exadata_feature_on" initialisation parameter. Don't use this on a real instance or you will be breaking your license agreement.

alter system set "_exadata_feature_on"=true scope=spfile;

shutdown immediate;

startup;

--DROP USER testuser1 CASCADE;

CREATE USER testuser1 IDENTIFIED BY testuser1

QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE TO testuser1;

We also need a few extra grants, just for our testing.

-- For resetting the test data.

GRANT CREATE PROCEDURE TO testuser1;

-- So we can use the DBMS_XPLAN.DISPLAY_CURSOR table function.

GRANT SELECT_CATALOG_ROLE TO testuser1;

-- So we can use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure.

GRANT ANALYZE ANY TO testuser1;

Connect to test user and create a test table.

CONN c##testuser1/testuser1@//192.168.58.101:1521/SRLABPLUG

CREATE TABLE tab1 (

id NUMBER,

description VARCHAR2(50)

);

Create a procedure to truncate the test table, populate it with some data and gather all table statistics.

CREATE OR REPLACE PROCEDURE reset_tab1 AS

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE tab1';

INSERT /*+ APPEND */ INTO tab1 (id, description)

SELECT level, 'Description of ' || level

FROM dual

CONNECT BY level <= 1000;

COMMIT;

DBMS_STATS.gather_table_stats(NULL, 'tab1');

END;

Format of Each Test

Before we run a test we will reset the test table using the following procedure call.

EXEC reset_tab1;

It can take some time for monitoring information to be written to the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views, so after each test we will use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately flush the information to the dictionary.

EXEC DBMS_STATS.flush_database_monitoring_info;

We will then run the following queries to show some of the statistics information for the table and columns. These are not the only statistics that are affected, but they are useful to give us an indication of what is happening. The output below represents the starting point after the reset.

-- SQL*Plus and SQLcl formatting.

SET LINESIZE 100 PAGESIZE 1000

COLUMN table_name FORMAT A10

COLUMN partition_name FORMAT A15

COLUMN column_name FORMAT A11

COLUMN notes FORMAT A25

COLUMN high_value FORMAT 9999999999

COLUMN low_value FORMAT 9999999999

-- NUM_ROWS from USER_TABLES.

SELECT table_name,

num_rows

FROM user_tables

WHERE table_name = 'TAB1';

TABLE_NAME NUM_ROWS

---------- ----------

TAB1 1000

SQL>

-- NUM_ROWS from USER_TAB_STATISTICS.

SELECT table_name,

num_rows,

notes

FROM user_tab_statistics

WHERE table_name = 'TAB1';

TABLE_NAME NUM_ROWS NOTES

---------- ---------- -------------------------

TAB1 1000

SQL>

-- LOW_VALUE and HIGH_VALUE of ID column from USER_TAB_COL_STATISTICS.

SELECT table_name,

column_name,

UTL_RAW.cast_to_number(low_value) AS low_value,

UTL_RAW.cast_to_number(high_value) AS high_value,

sample_size,

notes

FROM user_tab_col_statistics

WHERE table_name = 'TAB1'

AND column_name = 'ID';

TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE SAMPLE_SIZE NOTES

---------- ----------- ----------- ----------- ----------- -------------------------

TAB1 ID 1 1000 1000

SQL>

Conventional Path INSERT ... SELECT:

We reset the test data and run a conventional path INSERT ... SELECT to create 1000 rows.

-- Reset the test data.

EXEC reset_tab1;

-- Conventional path insert. (rows 1001-2000)

INSERT INTO tab1

SELECT level+1000 AS id,

'Description of ' || level AS description

FROM dual

CONNECT BY level <= 1000;

COMMIT;

We check the execution plan of the statement using the DBMS_XPLAN.DISPLAY_CURSOR table function and we can see an "OPTIMIZER STATISTICS GATHERING" operation in the plan.

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

We flush the monitoring information to the dictionary.

EXEC DBMS_STATS.flush_database_monitoring_info;

We check some basic statistics for the TAB1 table.

-- NUM_ROWS from USER_TABLES.

SELECT table_name,

num_rows

FROM user_tables

WHERE table_name = 'TAB1';

-- NUM_ROWS from USER_TAB_STATISTICS.

SELECT table_name,

num_rows,

notes

FROM user_tab_statistics

WHERE table_name = 'TAB1';

-- LOW_VALUE and HIGH_VALUE of ID column from USER_TAB_COL_STATISTICS.

SELECT table_name,

column_name,

UTL_RAW.cast_to_number(low_value) AS low_value,

UTL_RAW.cast_to_number(high_value) AS high_value,

sample_size,

notes

FROM user_tab_col_statistics

WHERE table_name = 'TAB1'

AND column_name = 'ID';

Activity Logs:

--------------

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1795161192 bytes

Fixed Size 9137256 bytes

Variable Size 520093696 bytes

Database Buffers 1258291200 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL> show parameter _exadata_feature_on

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

_exadata_feature_on boolean TRUE

SQL> delete table myobjects;

delete table myobjects

ERROR at line 1:

ORA-00903: invalid table name

SQL> delete myobjectsl

SQL>

SQL> clear screen

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SRLABPLUG READ WRITE NO

SQL> show parameter _exadata

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

_exadata_feature_on boolean TRUE

SQL> delete myobjects;

579064 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

COUNT(*)

----------

SQL> insert into myobjects select * from myobjects;

0 rows created.

SQL> insert into myobjects select * from dba_objects;

72394 rows created.

SQL> commit;

Commit complete.

SQL> select distinct object_type from myobjects where owner ='SYS';

OBJECT_TYPE

-----------------------

INDEX

JOB

TABLE PARTITION

SYNONYM

JAVA DATA

RULE SET

SCHEDULE

CLUSTER

PACKAGE BODY

JAVA CLASS

TRIGGER

TYPE BODY

RULE

EDITION

PROCEDURE

CONTEXT

EVALUATION CONTEXT

PACKAGE

Sachin Ramesh Oracle DBA Blog

Stat Counter

Blog Visit Count

AdSense

Popular Posts

Labels

Social Widget

Report Abuse

Total Pageviews

Blog Archive

Tags

Social Plugin

Subscribe Us

Popular Posts

Menu Footer Widget

Thursday, September 17, 2020

Oracle 19c New Feature - Real-Time Statistics explored with an Demo

Post a Comment: