Real time with statistics
Date: Tue, 27 Apr 2021 21:33:57 -0400
Message-ID: <f5b32045-0a7c-6089-fe6d-3c8d47b0ec8c_at_gmail.com>
Oracle 19c has made available real time statistics with the January release update (19.10):
The idea is to update statistics as DML is being executed:
OPTIMIZER_REAL_TIME_STATISTICS
OPTIMIZER_REAL_TIME_STATISTICS
controls whether the database automatically gathers real-time
statistics during conventional DML operations.
| Property | Description |
|---|---|
|
Parameter type |
Boolean |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
Basic |
No |
|
Oracle RAC |
Different values can be set on different instances. |
When this parameter is set to true,
the database automatically gathers real-time statistics
during conventional DML operations. The default setting is false, which means real-time
statistics are disabled.
This parameter is not a new new parameter, it was available on Exadata since earlier versions but now it was released into the wild. Tim Hall had an article about it:
https://oracle-base.com/articles/19c/real-time-statistics-19c
In that article, you can find the following command: "alter system set "_exadata_feature_on"=true scope=spfile;"
That was cheating, trying to tell your instance that it is running on Exadata hardware when it is not. Nigel Bayliss mentioned that the real time statistics parameter is available for general use in his blog:
https://blogs.oracle.com/optimizer/whats-new-in-the-oracle-optimizer-for-oracle-database-19c
Question:
Did anybody try it with high transaction volume? I expect a lot of latching, just like it happened with OPTIMIZER_DYNAMIC_SAMPLING=11. However, I have no OLTP database with the version high enough to try it out. Did anyone here try that, even if only on Exadata? If yes, were there any problems? What was the quality of plans? Was there a significant IO/CPU increase? Please let me know, if anyone has tried it.
I am having problems with the idea that different RAC
instances can have different values. Statistics are written
in data dictionary tables. Does that mean that one instance
will have up to date stats while the other will not? How
will that work?
Regards
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com-- http://www.freelists.org/webpage/oracle-l Received on Wed Apr 28 2021 - 03:33:57 CEST