Comparing apples to apples on Exadata

From: Will Beldman <wbeldma_at_uwo.ca>
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
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-l
Received on Thu Dec 14 2017 - 22:14:42 CET

Original text of this message