How to disable the online statistics gathering in Oracle

Leave a reply

select /*+ full(t) parallel(t,8) parallel_index(t,8) causing performance issue in Oracle

On checking the Oracle SQL running queries, I found that the following SQL is repeatedly executing, causing the CPU utilization of Oracle to reach 100%.

Query:

/* SQL Analyze(0) */ select /*+ full(t) parallel(t,8) parallel_index(t,8) db ms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitori ng xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb _pad */to_char(count("FILE_PTR")),substrb(dump(min("FILE_PTR"),16,0,64),1,240), substrb(dump(max("FILE_PTR"),16,0,64),1,240),to_char(count("NOTE_NO")),substrb(d ump(min("NOTE_NO"),16,0,64),1,240),substrb(dump(max("NOTE_NO"),16,0,64),1,240),t o_char(count("BUFFER_TYPE")),substrb(dump(min("BUFFER_TYPE"),16

On the internet, I discovered that it relates to the internal SQL for retrieving table and column statistics in the Oracle database. The command /* SQL Analyze(0) */ works like the internal SQL of the DBMS_STATS package used to collect statistics for objects.

In Oracle 19c, there may be a need for dynamic sampling, and we check if the table or columns have gathered online statistics.

  1. Dynamic Sampling : It affects performance when parsing new SQL by gathering statistics and consuming CPU.

When the Oracle optimizer parameter OPTIMIZER_DYNAMIC_SAMPLING is set to 2 (the default), it enables basic dynamic sampling. This means that if table or index statistics are missing or stale, Oracle will automatically gather minimal statistics at parse time before executing the query. This helps the optimizer make better decisions without requiring full statistics collection. However, it only samples a small portion of the data, so it’s a lightweight and fast operation.

Disable the dynamic Sampling:

ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING = 0;

2. Gather online statistics : It also uses CPU while running SQL, which can increase CPU usage and lead to performance issues during SQL execution.

If online statistics gathering is enabled, the “NOTE” column in the “user_tab_col_statistics” view will display STATS_ON_LOAD to show that statistics gathering has occurred.

col table_name format a20
col column_name format a20
col notes format a20
select table_name,column_name,NOTES from user_tab_col_statistics where table_name in ('TEST1','TEST2');

TABLE_NAME           COLUMN_NAME          NOTES
-------------------- -------------------- --------------------
TEST1                COL1                 STATS_ON_LOAD
TEST2                COL2                 STATS_ON_LOAD

Disabling online statistics gathering

This feature is controlled by the parameter _optimizer_gather_stats_on_load which is true by default.

To disable the feature:

alter system set "_optimizer_gather_stats_on_load"=false;