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) *
