Both the Oracle Autonomous Databases Serverless (ATP-S) as well as Dedicated (ATP-D) are built on Oracle Exadata back-end infrastructure.
These features shown below are unique to Oracle Exadata and our ATP databases in the Oracle Cloud are able to provide the high performance because of the underlying Oracle Exadata platform on which ATP databases are hosted.
Smart Scans: Query Offload capability where SQL processing is performed significantly at the Exadata Storage Cell layer and only the blocks needed to satisfy the query are sent back to the database server or compute node over the interconnect.
Storage Indexes: Indexes created automatically in the memory of the Exadata Storage Cells whose purpose is to reduce I/O by eliminating the scan of 1 MB Storage Regions not required to satisfy the query.
Hybrid Columnar Compression: Data is organized and compressed in units called Compression Units which provide the highest level of compression without significantly compromising at the same time on performance . This type of compression is exclusive to Exadata and a few members of Oracle’s family like ZFS storage appliances and Pillar Axiom SAN storage.
Let us connect to our Oracle Autonomous Transaction Processing database (ATP-S) and see for ourselves these unique Exadata features in operation.
Smart Scans
Connect to the ATP database as the TEST_MIG user.
While the 10 million row table is being created we can see from the Performance Hub that the highest resource usage is by the consumer group HIGH.
SQL> CONN test_mig/mypasswd@gsatp_high Connected. create table mysales nologging as select rownum as id, rownum + 1 as flag, 'Samsung Galaxy S7' as product, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id , 5000 as amount_sold, trunc(sysdate - 10000 + mod(rownum,10000)) as order_date, trunc(sysdate - 9999 + mod(rownum,10000)) as ship_date from dual connect by level<=1e7 ; Table created. SQL> SELECT COUNT(*) FROM MYSALES; COUNT(*) 10000000
Check the Smart Scan related statistics before issuing the query
SQL> SELECT a.NAME, b.VALUE/1048576
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME IN
('physical read total bytes', 'physical write total bytes',
'cell IO uncompressed bytes')
OR a.NAME LIKE 'cell physical%');
NAME B.VALUE/1048576
physical read total bytes .0078125
physical write total bytes 0
cell physical IO interconnect bytes .0078125
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes eligible for smart IOs 0
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 0
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 0
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
We now issue the query which will perform a FULL TABLE SCAN against the 10 million row MYSALES table.
Note the time for the query execution.
SQL> select product,channel_id,sum(amount_sold) from mysales where order_date > sysdate -360 group by product,channel_id; PRODUCT CHANNEL_ID SUM(AMOUNT_SOLD) Samsung Galaxy S7 1 60000000 Samsung Galaxy S7 3 60000000 Samsung Galaxy S7 4 60000000 Samsung Galaxy S7 2 60000000 Samsung Galaxy S7 0 55000000 Elapsed: 00:00:00.66
From the execution plan statistics we can see that a SMART SCAN has occurred via the operation shown TABLE ACCESS STORAGE FULL.
Execute the same query we had issued earlier to return Smart Scan related statistics.
Note the difference in the values for “physical read total bytes” which is around 670 MB versus the value for “cell physical IO interconnect bytes returned by smart scan” which is just over 1 MB.
This shows clearly that a Smart Scan has been effective in reducing the amount of I/O performed by the compute node or database tier by performing majority of the I/O workload and processing at the Exadata Storage Cell layer.
SQL> SELECT a.NAME, b.VALUE/1048576
FROM v$sysstat a, v$mystat b
WHERE a.statistic# = b.statistic#
AND ( a.NAME IN
('physical read total bytes', 'physical write total bytes',
'cell IO uncompressed bytes')
OR a.NAME LIKE 'cell physical%');
NAME B.VALUE/1048576
physical read total bytes 670.007813
physical write total bytes 0
cell physical IO interconnect bytes 1.03898621
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 670
cell physical IO bytes eligible for smart IOs 211.046875
cell physical IO bytes saved by columnar cache 84
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 126.125
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 1.03117371
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 95.1148415
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
Storage Indexes
Note the value for the statistic “cell physical IO bytes saved by storage index” before running some random queries against the MYSALES table which is performing equality predicate filters.
After the queries have been executed we can see that a significant portion of the potential I/O has been saved by using the Exadata Storage Indexes.
Note the value for the statistic ” cell physical IO bytes saved by storage index” – it shows that over 1300 MB of I/O has been saved by using the storage indexes.
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name in
('cell physical IO interconnect bytes returned by smart scan',
'cell physical IO bytes saved by storage index');
NAME MB
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 1.03117371
SQL> select * from mysales where id=4711;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD ORDER_DAT SHIP_DATE
4711 4712 Samsung Galaxy S7 1 711 5000 04-MAR-06 05-MAR-06
Elapsed: 00:00:01.26
SQL> select * from mysales where id=4712;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD ORDER_DAT SHIP_DATE
4712 4713 Samsung Galaxy S7 2 712 5000 05-MAR-06 06-MAR-06
Elapsed: 00:00:00.90
SQL> select * from mysales where id=4714;
ID FLAG PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD ORDER_DAT SHIP_DATE
4714 4715 Samsung Galaxy S7 4 714 5000 07-MAR-06 08-MAR-06
Elapsed: 00:00:00.57
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name in
('cell physical IO interconnect bytes returned by smart scan',
'cell physical IO bytes saved by storage index');
NAME MB
cell physical IO bytes saved by storage index 1338.15625
cell physical IO interconnect bytes returned by smart scan 1.22946167
Hybrid Columnar Compression
We create a copy of the existing MYSALES table using the highest degree of Hybrid Columnar Compression which is COMPRESS FOR ARCHIVE HIGH.
Note the size of the MYSALES_COMPRESS_HIGH table is now only 3 MB compared with 680 MB of the uncompressed MYSALES table.
SQL> create table mysales_compress_high
compress for archive high
nologging parallel 4
as select * from mysales;
Table created.
SQL> select sum(bytes)/1048576 from user_segments where segment_name='MYSALES_COMPRESS_HIGH';
SUM(BYTES)/1048576
3
SQL> select sum(bytes)/1048576 from user_segments where segment_name='MYSALES';
SUM(BYTES)/1048576
680
