Comparing apples to apples on Exadata
Date: Thu, 14 Dec 2017 16:14:42 -0500
Message-ID: <1825993.QCi0mPKIHC_at_wbeldma>
I have two (nearly) identical databases on Exadata.
I have a simple query and can force it to use the same execution plan on both databases.
On database 1, the query finishes consistently around 1 second. The cost analyzer
shows
*Global Stats*
*Elapsed Time(s) *
*Cpu Time(s) *
*IO Waits(s) *
*Other Waits(s) *
*Fetch Calls *
*Buffer Gets *
*Read Reqs *
*Read Bytes *
*Cell Offload *
0.97 0.94 0.02 0.01
1
380K
6026
3GB
95.13%
*SQL Plan Monitoring Details (Plan Hash Value=1373192284) *
*Id*
*Operation*
*Name*
*Rows (Estim) *
*Cost*
*Time Active(s) *
*Start Active *
*Execs*
*Rows (Actual) *
*Read Reqs *
*Read Bytes *
*Cell Offload *
*Mem (Max) *
*Activity (%) *
*Activity Detail (# samples) *
0
SELECT STATEMENT
1
1
3
1
. SORT GROUP BY
3
105K
2
+0
1
3
2048
100.00
Cpu (1)
2
.. TABLE ACCESS STORAGE FULL
##TABLE_NAME##
4M
105K
1
+1
1
4M
6026
3GB
95.13%
3M
On database 2, the query finishes anywhere between 10 seconds to 60 seconds. The cost analyzer shows
*Global Stats*
*Elapsed Time(s) *
*Cpu Time(s) *
*IO Waits(s) *
*Application Waits(s) *
*Cluster Waits(s) *
*Fetch Calls *
*Buffer Gets *
*Read Reqs *
*Read Bytes *
*Cell Offload *
21
10
12
0.00
0.00
1
4M
62926
4GB
81.24%
*SQL Plan Monitoring Details (Plan Hash Value=1373192284) *
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 14 2017 - 22:14:42 CET