This is the first article about my experiences with Exadata machine during the 3 month period.
I’ll try to avoid all commercial hypes and try to present the most valuable database machine as much realistic as I can.
I’ve took 3 – 4 books that covers material related to Exadata in scope of preparations to test features not available in regular Oracle database.
In scope of testing, I’ve created several different table types:
– tdh_nopartitions_normal
– tdh_nopartitions_flash
– tdh_nopartitions_columnar
– tdh_nopartitions_columnar_flsh
– tdh_nopartitions_in_memory
All tables are build on top of ex_tran_data_history table, which has several billions of rows, and all of them have the same number of rows (500 millions). Tables are not partitioned.
The DDL statements are:
1.
create table tdh_nopartitions_normal
tablespace exacloud_tbs2
nologging
as
select * from EX_TRAN_DATA_HISTORY where rownum <=500000000;
alter table tdh_nopartitions_normal move nologging storage (cell_flash_cache none);
2.
create table tdh_nopartitions_flash
tablespace exacloud_tbs2
nologging
as
select * from EX_TRAN_DATA_HISTORY where rownum <=500000000;
alter table tdh_nopartitions_flash move nologging storage (cell_flash_cache keep);
3.
create table tdh_nopartitions_columnar
tablespace exacloud_tbs2
nologging
compress for query high
as
select * from EX_TRAN_DATA_HISTORY where rownum <=500000000;
alter table tdh_nopartitions_columnar move nologging compress for query high storage (cell_flash_cache none);
4.
create table tdh_nopartitions_columnar_flsh
tablespace exacloud_tbs2
nologging
compress for query high
storage (cell_flash_cache keep)
as
select * from EX_TRAN_DATA_HISTORY where rownum <=500000000;
5.
create table tdh_nopartitions_in_memory
inmemory memcompress for query
nologging
as
select * from EX_TRAN_DATA_HISTORY where rownum <=500000000;
ALTER TABLE tdh_nopartitions_in_memory INMEMORY PRIORITY CRITICAL;
1. tdh_nopartitions_normal
This is table created on regular SAS disks.
2. tdh_nopartitions_flash
This one is created on Flash disks.
3. tdh_nopartitions_columnar
As the name suggests, this is table which uses columnar compression (compress for query high) feature of Exadata.
It is using SAS storage (not Flash Cache).
4. tdh_nopartitions_columnar_flsh
This is the the same table as previous one (meaning columnar compression is used), but the table is on Flash Cache storage, not on SAS.
5. tdh_nopartitions_in_memory
This is the In-Memory version of the same table.
This article will cover serial read which is the simplest possible case.
1.
select count(*) from tdh_nopartitions_normal;
11,33 sec
2.
select count(*) from tdh_nopartitions_flash;
7,6 sec
3.
select count(*) from tdh_nopartitions_columnar;
4,08 sec
4.
select count(*) from tdh_nopartitions_columnar_flsh;
3,84 sec
5.
select count(*) from tdh_nopartitions_in_memory;
7,97 sec
Besides In-Memory serial read, all results are expected.
If I move the table from SAS to Flash Cache disks (tdh_nopartitions_normal —> tdh_nopartitions_flash), I get better response time (11,33 sec —> 7,6 sec).
When I use columnar compression (tdh_nopartitions_columnar), even on SAS disks, I get even better results comparing with SAS or Flash Cache not compressed tables (7,6 sec —-> 4,08 sec).
When I move columnar compressed table to Flash Cache (tdh_nopartitions_columnar —> tdh_nopartitions_columnar_flsh), I can squeeze a performance a little bit more (4,08 sec —-> 3,84).
With In-Memory option (available on regular Oracle EE 12c editions also), I was at first disappointed.
I checked
select * from v$inmemory_area;
and can clearly see that POPULATE_STATUS column is changing it’s status from POPULATING —> DONE
Additionally, the following SQL confirms that table has been populated:
select alloc_bytes, used_bytes from v$inmemory_area;
51486130176 8508145664
At the same time, the following query tell me that no table has been populated:
select table_name,cache, buffer_pool,
inmemory,
inmemory_priority,
inmemory_compression,
inmemory_distribute,
inmemory_duplicate
from user_tables
where INMEMORY<>'DISABLED';
Only after adding DUPLICATE ALL at the end of DDL:
alter table tdh_nopartitions_in_memory inmemory priority critical
memcompress for query high
duplicate all;
I get the expected results:
select count(*) from tdh_nopartitions_in_memory;
Elapsed: 00:00:03.40
As you can see, the result is even better than columnar compressed table on Flash Cache (tdh_nopartitions_columnar_flsh —-> tdh_nopartitions_in_memory), as the elapsed time dropped from the 3,84 sec —> 3,40 sec which is the best result.
To conclude the first part where I’ve tested serial read.
With Exadata you have more features to play with, comparing with Oracle 12 EE database running on non Exadata hardware.
Flash Cache is great for tables (materialized views, temporary tables) that needs very fast storage, and will reduce performances by 40% and more.
On the other hand, columnar compression not only use far less storage space (that will be covered in another article), but is great for improving read speed, as using that feature improves speed by another 50% (300% comparing with the first case – tdh_nopartitions_normal).
Here you have to be aware of limitations of columnar compression, that will be also covered in future articles.
What is really great, In-Memory option (winner in this test), which is available with Oracle 12c (12.1.0.2+, 12.2…) provides the best performances, and this option is available no matter if you are using Exadata or not. The only downside of In-Memory option is additional licenses costs if you are going to use it.
As you can guess, performances are always highly dependent on data used.
The results provides here are representative for one company. You will probably have different results.
The goal here is not to provide exact results that you can use in all cases.
Instead you should only get a feeling of what performance impact you can get by using different options.
Comments