Loading....

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
Please follow and like us:

Last Update: August 27, 2020  

August 27, 2020 863 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?