關於實時統計 oracle 19c 僅適用於特定表
Reg 實時統計數據，它擴展了線上統計數據收集 (12c) 以包括傳統的 DML 語句。實時統計資訊有助於優化器生成更優化的計劃。批量載入操作收集所有必要的統計資訊，而實時統計資訊增加而不是取代傳統統計資訊。
Oracle 引入了新參數
_optimizer_gather_stats_on_conventional_dml並且_optimizer_use_stats_on_conventional_dml預設情況下為 true 且_optimizer_stats_on_conventional_dml_sample_rate為 100%實時統計如何工作？
預設情況下
_optimizer_gather_stats_on_conventional_dml為 true，因此它會自動啟動當 DML 操作目前正在修改表時（正常），如果上述參數打開，Oracle 數據庫會動態計算最重要的統計資訊的值。
考慮一個有很多插入並且行在增加的表的例子。在插入行時，實時統計資訊會跟踪不斷增加的行數。如果優化器對新查詢執行硬解析，則優化器可以使用實時統計資訊來獲得更準確的成本估算。
USER_TAB_COL_STATISTICS並USER_TAB_STATISITICS有列 NOTES 告訴實時統計資訊已被使用。“STATS_ON_CONVENTIONAL_DML”。執行計劃顯示
|Id| Operation | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop| --------------------------------------------------------------------------------------- | 0| INSERT STATEMENT | | | |910 (100)| | | | | 1| LOAD TABLE CONVENTIONAL |SALES| | | | | | | | 2| OPTIMIZER STATISTICS GATHERING | |918K| 25M|910 (2)|00:00:01| | | | 3| PARTITION RANGE ALL | |918K| 25M|910 (2)|00:00:01| 1 | 28 | | 4| TABLE ACCESS FULL |SALES|918K| 25M|910 (2)|00:00:01| 1 | 28 | Also the explain plan in the query used will tell in note section Note ----- - dynamic statistics used: stats for conventional DML
LOAD TABLE CONVENTIONAL並且OPTIMIZER STATISTICS GATHERING是已添加到此新功能的解釋計劃中的新參數。
can we apply real-time statistics only for a particular set of table . if yes, how can we achieve them ? , as we know getting real time statistics for any dml operation thats occuring for more that 100k rows is very costly operation我試圖找出路，但沒有成功
首先，您應該只在支持人員的指示下更改下劃線參數。但更重要的是，對於這些參數，實時統計資訊僅適用於特定的數據庫產品。有關詳細資訊，請參閱許可證指南。
更改這些參數可能會違反您的許可協議！
LOAD TABLE CONVENTIONAL並且OPTIMIZER STATISTICS GATHERING是已添加到此新功能的解釋計劃中的新參數。這些操作並非特定於實時統計資訊。
OPTIMIZER STATISTICS GATHERING適用於在執行期間擷取統計資訊的任何操作。create table as select是一個已經這樣做了一段時間的例子。
LOAD TABLE CONVENTIONAL表示數據庫執行正常（非直接路徑）插入。您可以禁用實時統計：
- 帶有
no_gather_optimizer_statistics提示的特定語句- 通過鎖定該表的統計資訊的表（儘管這也會停止後台作業收集統計資訊！）
例如，只有這三個插入中的第一個具有
OPTIMIZER STATISTICS GATHERING操作：set long 10000 set serveroutput off cl scr create table t as select 1 c1 from dual connect by level <= 100; insert /*+ monitor */into t with rws as ( select level x from dual connect by level <= 10 ) select * from rws; select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan') from dual; SQL Plan Monitoring Details (Plan Hash Value=2403765415) ====================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ====================================================================================================================================================== | 0 | INSERT STATEMENT | | | | 1 | +0 | 1 | 0 | | | | | | 1 | LOAD TABLE CONVENTIONAL | T | | | 1 | +0 | 1 | 0 | 4 | 32768 | | | | 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 2 | 1 | +0 | 1 | 10 | | | | | | 3 | VIEW | | 1 | 2 | 1 | +0 | 1 | 10 | | | | | | 4 | CONNECT BY WITHOUT FILTERING | | | | 1 | +0 | 1 | 10 | | | | | | 5 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 | 1 | | | | | ====================================================================================================================================================== insert /*+ monitor no_gather_optimizer_statistics */ into t with rws as ( select level x from dual connect by level <= 10 ) select * from rws; select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan') from dual; SQL Plan Monitoring Details (Plan Hash Value=2403765415) ====================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ====================================================================================================================================== | 0 | INSERT STATEMENT | | | | 1 | +0 | 1 | 0 | | | | 1 | LOAD TABLE CONVENTIONAL | T | | | 1 | +0 | 1 | 0 | | | | 2 | VIEW | | 1 | 2 | 1 | +0 | 1 | 10 | | | | 3 | CONNECT BY WITHOUT FILTERING | | | | 1 | +0 | 1 | 10 | | | | 4 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 | 1 | | | ====================================================================================================================================== exec dbms_stats.lock_table_stats ( user, 'T' ); insert /*+ monitor */into t with rws as ( select level x from dual connect by level <= 10 ) select * from rws; select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan') from dual; SQL Plan Monitoring Details (Plan Hash Value=2403765415) ====================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ====================================================================================================================================== | 0 | INSERT STATEMENT | | | | 1 | +0 | 1 | 0 | | | | 1 | LOAD TABLE CONVENTIONAL | T | | | 1 | +0 | 1 | 0 | | | | 2 | VIEW | | 1 | 2 | 1 | +0 | 1 | 10 | | | | 3 | CONNECT BY WITHOUT FILTERING | | | | 1 | +0 | 1 | 10 | | | | 4 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 | 1 | | | ======================================================================================================================================正如我們所知，為超過 100k 行發生的任何 dml 操作獲取實時統計數據是非常昂貴的操作
你怎麼會知道這事？