There is an odd contradiction that we all encounter for most databases, especially if they are predominantly used during the business day. Here is how that contradiction comes to be – it is in the way that we obtain and use optimizer statistics on those databases. The contradiction runs like this:
- To minimize service disruption, we gather statistics at a quiet time, for example, in the middle of the night
- We then use those statistics during the business day whilst user activity is at its highest.
- Highest user activity will typically mean the highest frequency of data changes.
- Hence the statistics are at their peak accuracy when no-one is using them to optimize queries, and they are at their least accurate when everyone is using them to optimize queries!
We can demonstrate this easily with the following script run in 18c.
SQL> select banner from v$version where rownum = 1 ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
1 row selected.
SQL> create table t (
2 id number(10),
3 num number(10),
4 constraint t_pk primary key ( id )
5 );
Table created.
SQL> insert into t select rownum,rownum from dual connect by rownum<=10000;
10000 rows created.
SQL> exec dbms_stats.gather_table_stats(user,'t')
PL/SQL procedure successfully completed.
SQL> select stale_stats from user_tab_statistics
2 where table_name = 'T';
STALE_S
-------
NO
So far I’ve mimicked a table that has been populated with (say) a days worth of data (10,000) rows, and I’ve gathered statistics at the end of the business day so that my statistics reflect the current data in the table.
SQL> select table_name, num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T';
TABLE_NAME NUM_ROWS BLOCKS
---------- -------- ----------
T 10000 20
1 row selected.
SQL> select table_name, column_name, low_value, high_value, num_distinct
2 from user_tab_col_statistics
3 where table_name = 'T';
TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_DISTINCT
---------- -------------------- --------------- --------------- ------------
T ID C102 C302 10000
T NUM C102 C302 10000
2 rows selected.
Hence, if I was to optimize and run a query now, I can expect an excellent estimate from the optimizer. The query below returns a COUNT of 1000 rows, and we can see from the PLAN_TABLE output, that the estimated rows was also 1000. So a perfect estimate by the optimizer!
SQL> select max(num),count(*)
2 from t where id > 9000;
MAX(NUM) COUNT(*)
---------- ----------
10000 1000
1 row selected.
SQL> select *
2 from table(dbms_xplan.display_cursor(format=>'typical'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1000 | 8000 |
|* 3 | INDEX RANGE SCAN | T_PK | 1000 | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">9000)
20 rows selected.
But of course, the next business day rolls on, and by mid-morning we may have added 1000 more rows to our table, bringing the total number of rows to 11000.
SQL> insert into t select rownum+10000,rownum+10000 from dual connect by rownum<=1000;
1000 rows created.
SQL> commit;
Commit complete.
Even with an explicit flush of the database monitoring information, we not yet reached the threshold where this tables statistics would be considered stale, so even a GATHER_STALE operation would have no effect if we ran one. The statistics still reflect the data from last night.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> select stale_stats from user_tab_statistics
2 where table_name = 'T';
STALE_S
-------
NO
SQL> select table_name, num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T';
TABLE_NAME NUM_ROWS BLOCKS
---------- -------- ----------
T 10000 20
1 row selected.
SQL> select table_name, column_name, low_value, high_value
2 from user_tab_col_statistics
3 where table_name = 'T';
TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE
---------- -------------------- --------------- ---------------
T ID C102 C302
T NUM C102 C302
2 rows selected.
When I run my same query, and don’t forget, this would now be during the business day, when it is critical that I get a good optimizer plan, you can see that the result is now 2000 rows, but the plan estimate is left languishing at its original 1000 rows because the statistics no long reflect the current state of the table.
SQL> select max(num),count(*)
2 from t where id > 9000;
MAX(NUM) COUNT(*)
---------- ----------
11000 2000
1 row selected.
SQL> select *
2 from table(dbms_xplan.display_cursor(format=>'typical'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID 8juuu5warw2z6, child number 0
-------------------------------------
select max(num),count(*) from t where id > 9000
Plan hash value: 2053823973
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1000 | 8000 |
|* 3 | INDEX RANGE SCAN | T_PK | 1000 | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">9000)
20 rows selected.
Having great statistics that are not used, and poor statistics that are used all the time seems a nonsensical way to do things, but there really hasn’t been any alternative unless you were prepared to consume precious server resources to collect statistics whilst your users are performing their transactions. I have known customers to do this in the past, but of course, it takes careful monitoring and management to ensure that the slicing up of the resource “pie” is done appropriately to keep the right balance between business needs and background database tasks.
19c brings a nifty solution to this contradiction by having the database automatically maintain details about how data is changing in a table via a feature called Real Time Statistics. DML activities can be tracked, and that information can then be looped back as input into future query parsing. Here’s the same example from above, this time performed in 19c.
The same table is seeded with the same initial 10000 rows as before. I have included a new column in my data dictionary queries called NOTES. You will see the significance of that shortly.
SQL> select banner from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
1 row selected.
SQL>
SQL> create table t (
2 id number(10),
3 num number(10),
4 constraint t_pk primary key ( id )
5 );
Table created.
SQL> insert into t select rownum,rownum from dual connect by rownum<=10000;
10000 rows created.
SQL> exec dbms_stats.gather_table_stats(user,'t')
PL/SQL procedure successfully completed.
SQL> select stale_stats from user_tab_statistics
2 where table_name = 'T';
STALE_S
-------
NO
1 row selected.
SQL> select notes, table_name, num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T';
NOTES TABLE_NAME NUM_ROWS BLOCKS
-------------------------------- ---------- -------- ----------
T 10000 20
1 row selected.
SQL> select notes, table_name, column_name, low_value, high_value, num_distinct
2 from user_tab_col_statistics
3 where table_name = 'T';
NOTES TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_DISTINCT
-------------------------------- ---------- -------------------- --------------- --------------- ------------
T ID C102 C302 10000
T NUM C102 C302 10000
And since this is a simple query, the optimizer performs exactly as per 18c for its estimate. Once again, at this point, where the data and statistics are in alignment, the estimate is perfect.
SQL> select max(num),count(*)
2 from t where id > 9000;
MAX(NUM) COUNT(*)
---------- ----------
10000 1000
1 row selected.
SQL> select *
2 from table(dbms_xplan.display_cursor(format=>'typical'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)|
| 1 | SORT AGGREGATE | | 1 | 8 | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1000 | 8000 | 5 (0)|
|* 3 | INDEX RANGE SCAN | T_PK | 1000 | | 3 (0)|
----------------------------------------------------------------------------------
The business day commences, and a fresh 1000 rows are added to the database.
SQL> insert into t select rownum+10000,rownum+10000 from dual connect by rownum<=1000;
1000 rows created.
SQL> commit;
Commit complete.
And just like before, this is not enough for the statistics on the table to be considered stale by the database
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL>
SQL> select stale_stats from user_tab_statistics
2 where table_name = 'T';
STALE_S
-------
NO
But, here is where the 19c enhancements come into play. When we query the data dictionary, we now have TWO rows of statistics for this single table.
SQL> select notes, table_name, num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T';
NOTES TABLE_NAME NUM_ROWS BLOCKS
-------------------------------- ---------- -------- ----------
T 10000 20
STATS_ON_CONVENTIONAL_DML T 11000 20
2 rows selected.
The first row is the original statistics we gathered. The second row is new statistics we have derived from the normal DML activities that have occurred on the database. This is not a full GATHER operation, it is simply taking advantage of simple mathematics, eg if I have 10000 rows and then insert 50 and deleted 20, then I would have 10030 rows etc. The real time statistics can also keep track of simple column level statistics, like whether the low and high water mark values have changed.
SQL> select notes, table_name, column_name, low_value, high_value
2 from user_tab_col_statistics
3 where table_name = 'T';
NOTES TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE
-------------------------------- ---------- -------------------- --------------- ----------
T ID C102 C302
T NUM C102 C302
STATS_ON_CONVENTIONAL_DML T ID C102 C302094A
STATS_ON_CONVENTIONAL_DML T NUM C102 C302094A
The real test is whether these statistics can be beneficial to our sample query. I’ll run that through again.
SQL> select max(num),count(*)
2 from t where id > 9000;
MAX(NUM) COUNT(*)
---------- ----------
11000 2000
1 row selected.
SQL> select *
2 from table(dbms_xplan.display_cursor(format=>'typical'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| T | 1895 | 15160 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID">9000)
Note
-----
- dynamic statistics used: statistics for conventional DML
Notice the estimate of 1895 is much closer now to the true value of 2000. You can expect a little variation here from the true value, because as I mentioned before, real time statistics is not about performing a full gather operation on the table – that would be far too resource hungry for every single DML. So some elements of the statistics (for example, the number of distinct values, or histogram distribution) are not feasible to keep up to date every time a DML is performed. But even having the improved row counts and column extrema have yielded benefit on the execution plan. When the normal nightly gather job comes along, these DML statistics are no longer relevant and will be expunged
SQL> exec dbms_stats.gather_table_stats(user,'t')
PL/SQL procedure successfully completed.
SQL> select notes, table_name, num_rows, blocks
2 from user_tab_statistics
3 where table_name = 'T';
NOTES TABLE_NAME NUM_ROWS BLOCKS
-------------------------------- ---------- -------- ----------
T 11000 20
1 row selected.
SQL> select notes, table_name, column_name, low_value, high_value
2 from user_tab_col_statistics
3 where table_name = 'T';
NOTES TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE
-------------------------------- ---------- -------------------- --------------- ----------
T ID C102 C3020B
T NUM C102 C3020B
2 rows selected.
There are other enhancements in 19c to keep the statistics in the dictionary closer to the true data in database tables which you can read about here.
No comments:
Post a Comment