This article is third in series about my experiences with Exadata database machine.
Results in previous two articles are really great for Exadata.
Here I’ll prove that there could be cases where Exadata is actually slower than Oracle database running on commodity hardware.
Test case is serial reading from large partitioned (and sub partitioned) table (1300 list partitions where each partitions has 5 range sub partitions ).
1. --Exadata serial read select count(*) from exacloud.ex_tran_data_history tdh; Elapsed: 00:30:34.96 2. --Exadata serial direct path read select count(*) from exacloud.ex_tran_data_history_columnar tdh; Elapsed: 00:01:12.45 3. --Commodity (Non Exadata) hardware serial read select count(*) from rms.tran_data_history tdh; Elapsed: 00:03:37.51
As you can see, Exadata regular serial read in my case was almost 10 times slower than serial read from non Exadata database.
I still don’t have reasonable explanation from the Oracle Support, but they suspect the large number of segments might be an issue.
Anyway, if you force serial direct path read by executing the following command:
alter session set "_serial_direct_read"=true;
you can get performance you expect (case 3 which is 3 times faster).
Here are results from the reading the same table in parallel.
1. --Exadata parallel read select /*+ parallel (tdh) */ count(*) from exacloud.ex_tran_data_history tdh; Elapsed: 00:00:06.09 2. --Commodity (Non Exadata) hardware parallel read select /*+ parallel (tdh 16) */ count(*) from rms.tran_data_history tdh; Elapsed: 00:02:10.80
As you can see (case 2 parallel read), when executing on non Exadata HW, the performance are better (3 minutes 37 sec –> 2 min 10 sec).
But the real performance boost is when comparing results with Exadata parallel read (case 1 parallel read), where I get 6 sec instead of 2 minutes and 10 seconds.
This is also 12 times better than Exadata serial (direct path) read (case 2 serial read).