Oracle Statistics Gathering

This topic contains the following sections:

Oracle Statistics Refresh Frequency
Oracle System Statistics
Optimizing Statistics Gathering Duration in Siebel Databases

Oracle statistics gathering has significantly been improved since 12c and particularly in 19c Database and future releases. Hence, Oracle recommends that Siebel customers to take advantage of these improvements and start using DBMS statistics gathering rather than COE script.

Auto Sampling is almost 100% accurate and the results are superior compared to defining a percentage (like what COE script is doing).
Oracle automatically determines what histograms are needed and at the same time can be customized to the requirements (histograms on indexed columns, etc.).
It is recommended to start with basic statistics gathering and enhance it moving forward:
```
begin
dbms_stats.gather_schema_stats(ownname=>'Siebel_Schema');
end;
/
```
This method gathers statistics on tables including all the columns that it finds them important like indexed columns and columns with skewed data shape.
Or, gathering statistics on tables and indexed columns only that we have been recommending and might be sufficient in most cases:
```
begin
dbms_stats.gather_schema_stats(ownname=>'Siebel_Schema',cascade=>true);
end;
/
```
Important: If COE script has been previously used to gather Optimizer statistics, some tables with few rows are locked. To unlock all Siebel tables for statistics gathering, the following command can be used:
```
exec dbms_stats.unlock_schema_stats('Siebel_Schema');
```
Degree of parallelism and concurrency speeds the statistics gathering dramatically.
Stale statistics are automatically detected (see below) which reduces the time to keep statistics accurate.
The DBMS package provides extreme flexibility, allowing all the adjustment needed. Statistics for table partitioning is particularly important for many Siebel customers.
Specific statistics can be gathered as PENDING and tested before they are put to use.

Oracle Statistics Refresh Frequency

By default, Oracle verifies CBO statistics on every Maintenance Window (once a day between plus weekends), and it selects for gathering only those application tables that have had more than 10% of changes (inserts, updates and deletes). The problem with this method is that tables that retain several months of data, only get to reach the 10% threshold after months. There are two options:

Manually (or schedule) a GATHER_SCHEMA_STATS periodically. Once a month seems adequate period in most cases but needs to be adjusted depending on the rate of changes.
Automatically, which also gives you the possibility to change the staleness threshold from 10% to something more reasonable (5% or lower). For example:
```
DBMS_STATS.SET_SCHEMA_PREFS('SIEBEL','STALE_PERCENT','5');
```

Note: The High-Frequency and ongoing Auto-Statistics gathering are not recommended for Siebel deployments.

Refer to the following technical briefs for full details:

Understanding Optimizer Statistics with Oracle Database 19c
The Optimizer in Oracle Database 19c
Best Practices for Gathering Optimizer Statistics with Oracle Database 19c

Oracle System Statistics

Oracle does not recommend gathering system statistics for Siebel database. If for any specific reason system statistics are gathered it is mandatory to make sure that DB_FILE_MULTIBLOCK_READ_COUNT is not set. This applies to Siebel database on Exadata. For more information, see Exadata Recommended Database Parameters.

Optimizing Statistics Gathering Duration in Siebel Databases

Efficient statistics gathering is essential for maintaining optimal query performance in Siebel environments. The following recommendations can significantly reduce the duration of statistics collection:

1. Schema-Level Statistics Gathering with High Parallelism

The most effective approach is to gather statistics at the Siebel schema level with a high degree of parallelism.

The maximum parallelism degree depends on available Oracle Database CPU resources.

Note: Statistics gathering can run during production hours; however, parallel job execution should be adjusted to prevent excessive CPU consumption.

2. INCREMENTAL Option for Large Tables

The INCREMENTAL option can significantly reduce processing time, especially for large Siebel tables.

This approach requires partitioning, ideally using the "creation date" column, so only a few partitions require updates.

Considerations:

Some tables may already be partitioned to manage concurrency issues in data blocks or B-tree index nodes.

In such cases, a multi-level partitioning strategy is required, using "range partitioning on the creation date" as the first level and hash partitioning on ROW_ID, USER_KEY, or another suitable column as the second level.

3. GATHER STALE Option

The GATHER STALE option reduces duration by collecting statistics only for tables where staleness exceeds the threshold (default: 10%).

Execution example:

sql 
CopyEdit 
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SIEBEL', DEGREE=>10, OPTIONS=>'GATHER STALE';

4. Exporting and Importing Statistics

If a frequently refreshed test environment with an identical Siebel database is available, statistics can be exported and imported instead of being gathered from scratch.

This method is useful when other optimizations are insufficient or when gathering statistics exceeds acceptable time limits.

By implementing these strategies, organizations can achieve significant reductions in statistics gathering duration while ensuring optimal database performance.