My Oracle Support Banner

AWR SQL Reports show Different Execution Statistics for Identical Plan (Doc ID 1664439.1)

Last updated on OCTOBER 30, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

AWR SQL Reports ($ORACLE_HOME/rdbms/admin/awrsqrpt.sql) show the same query using the same plan but having different statistics per execution as shown below:

Report 1

#Plan Hash ValueTotal Elapsed Time(ms)Executions1st Capture Snap IDLast Capture Snap ID
1 3430396252 483,169 1 96748 96748

 

Plan Statistics

Stat NameStatement TotalPer Execution% Snap Total
Elapsed Time (ms) 483,169 483,169.36 51.56
CPU Time (ms) 334,911 334,911.09 63.23
Executions 1    
Buffer Gets 28,645,047 28,645,047.00 68.85
Disk Reads 1,611,566 1,611,566.00 38.75
Parse Calls 1 1.00 0.06
Rows 5,167,700 5,167,700.00  
User I/O Wait Time (ms) 149,601    

 

Report 2

#Plan Hash ValueTotal Elapsed Time(ms)Executions1st Capture Snap IDLast Capture Snap ID
1 3430396252 162,353 1 96842 96842

 

Plan Statistics

Stat NameStatement TotalPer Execution% Snap Total
Elapsed Time (ms) 162,353 162,352.71 14.82
CPU Time (ms) 92,680 92,679.91 16.54
Executions 1    
Buffer Gets 7,319,506 7,319,506.00 15.64
Disk Reads 454,968 454,968.00 26.65
Parse Calls 0 0.00 0.00
Rows 5,169,163 5,169,163.00  
User I/O Wait Time (ms) 70,223    

 

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!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.