Slow AWR Generation After Upgrading To 12.2 waiting on "control file sequential read" wait event
(Doc ID 2537108.1)
Last updated on NOVEMBER 14, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Gen 2 Exadata Cloud at Customer - Version N/A and later
Information in this document applies to any platform.
Symptoms
- After upgrading to 12.2, AWR generation is taking long time
- 10046 tracefile shows following sql taking most time:
SQL Hash Value: 884859204 SQL Id: ccz231hubvsa4 waiting on "control file sequential read" wait event
SQL ID: ccz231hubvsa4 Plan Hash: 2704158448SELECT /*+ ALL_ROWS */ /* ASH_TOP_OBJECTS */ ash.current_obj#,
Elapsed times include waiting on following events:
ash.dim1_percentage, ash.event, ash.dim12_percentage,
dbms_ash_internal.get_obj_name( my_obj.owner,
my_obj.object_name, my_obj.subobject_name,
my_obj.object_type), my_obj.tablespace_name,
ash.pdb_name FROM ( SELECT d12aa_ash.* FROM ( SELECT d12gb_ash.*,
(dim1_count*100/:ash_num_rows) as dim1_percentage, dense_rank()
over (order by dim1_count desc, con_id,
current_obj# ) as dim1_rank FROM ( SELECT con_id, current_obj# ,
event , max(pdb_name) as pdb_name , count(*) as
dim12_count, (count(*) * 100 / :ash_num_rows) as
dim12_percentage, rank() over (partition by con_id,
current_obj# order by count(*) desc,
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 97 0.00 0.00
control file sequential read 40415 0.05 8.69
Changes
Upgrade to 12.2.
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 |
| Changes |
| Cause |
| Solution |
| References |