Gathering Incremental Statistics Slow
(Doc ID 2394920.1)
Last updated on MARCH 03, 2022
Applies to:
Oracle Database Exadata Express Cloud Service - Version N/A and laterOracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- Gathering incremental table statistics is slow
- ASH report shows top sql is DELETE FROM SYS.WRI$_OPTSTAT_Synopsis:
Top SQL with Top Events
SQL ID Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text
ct3ch355nvhzm 3506642473 475 34.35 CPU + Wait for CPU 19.42 DELETE 16.81 DELETE FROM SYS.WRI$_OPTSTAT_S... =============>Reason for the stats slowness - Plan shows full tablescan on WRI$_OPTSTAT_SYNOPSIS$:
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 5249 (100)| | | |
| 1 | DELETE | WRI$_OPTSTAT_SYNOPSIS$ | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 4 | 48 | 5249 (1)| 00:01:14 | KEY | KEY |
| 3 | PARTITION HASH SINGLE| | 4 | 48 | 5249 (1)| 00:01:14 | | |
|* 4 | TABLE ACCESS FULL | WRI$_OPTSTAT_SYNOPSIS$ | 4 | 48 | 5249 (1)| 00:01:14 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |