Real Time Statistics
From Robs_Wiki
Revision as of 21:32, 26 October 2020 by Qadmin wiki (talk | contribs) (Created page with "<pre> Introduction ======= Oracle Database 19c introduces real-time statistics, which extends online statistics gathering to also include conventional DML statements. Statist...")
Introduction ======= 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. Oracle Real Time statistics rely on fixed statistics, so it is important that you keep gathering fixed statistics as usual. In Oracle19c real time statistics are applied by default, but they can be manipulated using the following hidden parameters: _optimizer_gather_stats_on_conventional_config 0 settings for optimizer online stats gathering on conventional DML _optimizer_gather_stats_on_conventional_dml TRUE optimizer online stats gathering for conventional DML _optimizer_stats_on_conventional_dml_sample_rate 100 sampling rate for online stats gathering on conventional DML _optimizer_use_stats_on_conventional_config 0 settings for optimizer usage of online stats on conventional DML _optimizer_use_stats_on_conventional_dml TRUE use optimizer statistics gathered for conventional DML How does real time statistics work? ===================== • When a DML operation is currently modifying a table, Oracle Database dynamically computes values for the most essential statistics. There is no call to DBMS_STATS visible in trace. But you can see an 'OPITMIZER STATISTICS GATHERING' operation in the execution plan. • The changes to the table are tracked in memory and are flushed to the data dictionary later on along with other monitoring data, as part of the table monitoring feature. (Table monitoring was introduced in 10g and is used to determine if statistics are stale. All table dml and truncates are tracked in memory and flushed to disk every 15 minutes by SMON.) This feature is only available on Engineered Systems such as Exadata and Exadata Cloud Service. You can simulate this in a test environment as follows: SQL> alter system set "_exadata_feature_on" = true scope = spfile; Example using real time statistics ===================== SQL> alter session set container=pdb1; SQL> alter session set current_schema=pdbadmin; SQL> CREATE TABLE TEST(id NUMBER, name VARCHAR2(20)); SQL> begin for a in 1..500000 loop insert into TEST values (a,'test'||to_char(a)); end loop; commit; end; / --Generate statistics: begin DBMS_STATS.GATHER_TABLE_STATS('PDBADMIN', 'TEST', METHOD_OPT=>'FOR ALL COLUMNS SIZE auto'); end; / --Check the statistics: SET PAGES 5000 LINES 200 COL TABLE_NAME for a15 COL COLUMN_NAME FORMAT a13 COL NOTES FORMAT a35 select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,LAST_ANALYZED,NOTES from dba_tab_col_statistics where table_name = 'TEST'; TABLE_NAME COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED NOTES --------------- ------------- ------------ ----------- -------------------- --------------- TEST ID 500000 500000 26-OCT-2020 09:02:34 TEST NAME 500000 500000 26-OCT-2020 09:02:34 SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES from DBA_TAB_STATISTICS where table_name='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED NOTES --------------- ---------- ---------- ----------- -------------------- ---------------------- TEST 500000 1432 16 26-OCT-2020 09:02:34 --Now, let’s add another 100000 records: begin for a in 500001..600000 loop insert into TEST values (a,'test'||to_char(a)); end loop; commit; end; / PL/SQL procedure successfully completed. Now check the statistics again: SQL> select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,LAST_ANALYZED,NOTES from dba_tab_col_statistics where table_name = 'TEST'; TABLE_NAME COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED NOTES --------------- ------------- ------------ ----------- -------------------- ----------------- TEST ID 500000 500000 26-OCT-2020 09:02:34 TEST NAME 500000 500000 26-OCT-2020 09:02:34 TEST NAME 1049 26-OCT-2020 09:25:40 STATS_ON_CONVENTIONAL_DML TEST ID 970 26-OCT-2020 09:25:40 STATS_ON_CONVENTIONAL_DML SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES from DBA_TAB_STATISTICS where table_name='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED NOTES --------------- ---------- ---------- ----------- -------------------- ------------------------ TEST 500000 1432 16 26-OCT-2020 09:02:34 TEST 600000 1718 26-OCT-2020 09:25:39 STATS_ON_CONVENTIONAL_DML Let’s see what happens when we insert and select data from this table: SQL> explain plan for insert into test values(1000001,'test1000001'); SQL> select * From table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | TEST | | | | | --------------------------------------------------------------------------------- Note ----- - dynamic statistics used: statistics for conventional DML enabled SQL> set autotrace traceonly SQL> SELECT COUNT(*) FROM TEST WHERE ID > 590000; Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 471 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| TEST | 9895 | 125K| 471 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID">590000) Note ----- - dynamic statistics used: statistics for conventional DML