Exadata - Smart Scan Testing

Exadata - SMART SCAN testing
--------------------------------------------

Samrt scan works with cell offloading which relate to offloaded the sql processing from the database layer to the cell (storage) layer.

Smart scan is the access mechanism to offload data from storage layer. For example passing only required data blocks after applying predicate filters at the cell layer instead of sending every possible blocks from cell server to database server.
 
                        Figure 1: Traditional Database Storage I/O and SQL Processing Model
 
Process Steps:
-------------------
1.   The client issues a SELECT statement .
2.   The database kernel maps this request to the database files and extents which contain the table data.
3.   The database kernel issues the I/Os to read all the table blocks (ex:5GB).
4.   Then  all the table blocks being queried are read into memory.
5.   Then SQL processing is done against the data blocks searching for the rows that satisfy the predicate (Ex: only 2MB).
6.   The rows are returned to the client.
 

Figure 2: Exadata Smart Scan

 Process Steps:
-------------------
1.   The client issues a SELECT statement.
2.   The database kernel determines that the data is stored on Exadata cells so an iDB (Intelligent Database protocol, simply a unique Oracle data transfer protocol in between storage layer and DB layer  ) command representing the SQL command is constructed and sent to the Exadata cells.
3.   The Exadata Storage Server software scans the data blocks to extract only the relevant rows and columns that satisfy the SQL command.
4.   Exadata cells then return to the database instance iDB messages containing the requested rows and columns of data not block images like traditional database storage (Ex: only 2MB out of 5GB).
5.   The database kernel consolidates the result sets from across all the Exadata cells which is similar as parallel query consolidated operation.
6.   The rows are returned to the client.

Test
------------
Flash current buffer cache
====================
alter system flush buffer_cache;

Create a table for testing
=======================
create table sales_SM_SC as
select
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2007' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=2e4;

Check the table size
=================
SQL> select bytes/1024/1024 as SizeMB from user_segments where segment_name='SALES_SM_SC';

    SIZEMB
----------
     .5625
SQL> alter table sales nologging;

Table altered.

Insert data
============
insert /*+ append */ into sales_SM_SC select
channel_id, cust_id,amount_sold, time_id
from sales;
12399984 rows created.
/

12399984 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 as SizeMB from user_segments where segment_name='SALES_SM_SC';

    SIZEMB
----------
       656

Gather Statistics
==============
SQL> exec dbms_stats.gather_table_stats('SH','SALES_SM_SC')
SQL>
PL/SQL procedure successfully completed.


Check the Query Processing speed without Smart Scan:
---------------------------------------------------------------------
SQL> alter session set cell_offload_processing=false;

Session altered.

---------------------------------------------------------------------------------------------------------------------------------------
NOTE :  Inside the SQL statement optimizer hint to disables Smart Scan for the query

select /*+ OPT_PARAM('cell_offload_processing' 'false') */ count(*) from SALES_SM_SC where channel_id=1

---------------------------------------------------------------------------------------------------------------------------------------
SQL> set timing on
select /* NO_SMART_SCAN */ count(*) from SALES_SM_SC where channel_id=1;

SQL>
  COUNT(*)
----------
      4000

Elapsed: 00:00:16.19
SQL> /

  COUNT(*)
----------
      4000

Elapsed: 00:00:15.68


WIth Smart Scan:
----------------------
SQL> set timing off;
SQL> alter session set cell_offload_processing=true;

Session altered.

SQL> set timing on
SQL> select /* WITH_SMART_SCAN */ count(*) from SALES_SM_SC where channel_id=1;

  COUNT(*)
----------
      4000

Elapsed: 00:00:23.75
SQL> /

  COUNT(*)
----------
      4000

Elapsed: 00:00:00.05

Note: Without cell_offload_processing query took 15:68 seconds where with cell_offload_processing
it took 00:05 seconds.


Query to check Predicate offload and smart scan
----------------------------------------------------------------
SQL> select a.name, b.value/1024/1024 MB
  2  from v$sysstat a, v$mystat b
  3  where a.statistic# = b.statistic# and
  4  (a.name in ('physical read total bytes',
  5              'physical write total bytes',
  6              'cell IO uncompressed bytes')
  7  or a.name like 'cell phy%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        53.0703125
physical write total bytes                                                0
cell physical IO interconnect bytes                              .188957214
cell physical IO bytes sent directly to DB node to balanceCPU u           0
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               53.0625
cell physical IO bytes saved by storage index                             0
cell physical IO interconnect bytes returned by smart scan       .181144714
cell IO uncompressed bytes                                          53.0625

10 rows selected.

So query is getting almost every blocks from predicate offload. Smart scan is required to get only 0.18MB data.

Explain Plan for Without SMART_SCAN
------------------------------------
SQL> set timing off;
SQL> select sql_id from v$sql where sql_text like '%NO_SMART_SCAN%' and sql_text not like '%like%';

SQL_ID
-------------
9fmuwnmmrvapv

SQL> select plan_table_output from table (dbms_xplan.display_cursor('9fmuwnmmrvapv'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9fmuwnmmrvapv, child number 0
-------------------------------------
select /* NO_SMART_SCAN */ count(*) from SALES_SM_SC where channel_id=1

Plan hash value: 1381054164

------------------------------------------------------------------------------------------

| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |             |       |       | 22874 (100)|         |

|   1 |  SORT AGGREGATE            |             |     1 |     3 |            |         |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS STORAGE FULL| SALES_SM_SC |  3545K|    10M| 22874   (2)|00:04:35 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHANNEL_ID"=1)


19 rows selected.


Explain Plan for SMART SCAN
-----------------------------------------

SQL> SQL> select sql_id from v$sql where sql_text like '%WITH_SMART_SCAN%' and sql_text not like '%like%';

SQL_ID
-------------
8rb96afm261ry

SQL> select plan_table_output from table (dbms_xplan.display_cursor('&SQLID'));
Enter value for sqlid: 8rb96afm261ry
old   1: select plan_table_output from table (dbms_xplan.display_cursor('&SQLID'))
new   1: select plan_table_output from table (dbms_xplan.display_cursor('8rb96afm261ry'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8rb96afm261ry, child number 0
-------------------------------------
select /* WITH_SMART_SCAN */ count(*) from SALES_SM_SC where
channel_id=1

Plan hash value: 1381054164

------------------------------------------------------------------------------------------

| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |             |       |       | 22874 (100)|         |

|   1 |  SORT AGGREGATE            |             |     1 |     3 |            |         |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  2 |   TABLE ACCESS STORAGE FULL| SALES_SM_SC |  3545K|    10M| 22874   (2)|00:04:35 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("CHANNEL_ID"=1)
       filter("CHANNEL_ID"=1)


21 rows selected.

NOTE: The Storage Layer did filter on the predicate channel_id=1 before transmitting the result to the Database Layer, which is the reason for the reduced runtime.

----------------------------------------
2nd Example   SMART SCAN
---------------------------------------
SQL> conn sh/sh
Connected.
SQL> set autotrace on
SQL> set timing on
SQL> select count (*) from customers where cust_valid='B';

  COUNT(*)
----------
         0

Elapsed: 00:00:07.08

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     2 | 10245   (1)| 00:02:03 |

|   1 |  SORT AGGREGATE            |           |     1 |     2 |            |       |

|*  2 |   TABLE ACCESS STORAGE FULL| CUSTOMERS | 65588 |   128K| 10245   (1)| 00:02:03 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("CUST_VALID"='B')
       filter("CUST_VALID"='B')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      36884  consistent gets
      36880  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL> select a.name, b.value/1024/1024 MB
    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 phy%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                            288.25
physical write total bytes                                                0
cell physical IO interconnect bytes                              .222740173
cell physical IO bytes sent directly to DB node to balanceCPU u           0
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            288.070313
cell physical IO bytes saved by storage index                             0
cell physical IO interconnect bytes returned by smart scan       .043052673
cell IO uncompressed bytes                                       288.070313

10 rows selected.

Elapsed: 00:00:00.01
SQL>

select distinct event, total_waits, time_waited/100 wait_secs, average_wait/100 avgwait_secs
from v$session_event e, v$mystat s
where event like 'cell%' and e.sid=s.sid;SQL>   2    3 

EVENT                             TOTAL_WAITS    WAIT_SECS AVGWAIT_SECS
---------- ----------------------------------------------------------------
cell single block physical read      52            .59        .0113
cell multiblock physical read         1            .02        .0217
cell smart table scan               191            6.96        .0364

Elapsed: 00:00:00.50

Open another Session, update the query without commit:
+++++++++++++++++++++++++++++++++++++++++++++
SQL> update customers
  2  set cust_credit_limit=1.5*cust_credit_limit
  3  where cust_id > 500000;

Go to first session
--------------------
SQL> select count (*) from customers where cust_valid='B';

  COUNT(*)
----------
         0

Elapsed: 00:01:43.12  ======> Finding1 (more time to get query output)

AME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        607.484375
physical write total bytes                                                0
cell physical IO interconnect bytes                              186.811409 ---->find2: more data to interconnect
cell physical IO bytes sent directly to DB node to balanceCPU u           0
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            576.140625
cell physical IO bytes saved by storage index                             0
cell physical IO interconnect bytes returned by smart scan      155.467659  ---->find3: More data was returned
cell IO uncompressed bytes                                       576.140625

10 rows selected.

Elapsed: 00:00:00.07


SQL> select distinct event, total_waits, time_waited/100 wait_secs, average_wait/100 avgwait_secs
from v$session_event e, v$mystat s
where event like 'cell%' and e.sid=s.sid;  2    3 

EVENT                                  TOTAL_WAITS    WAIT_SECS AVGWAIT_SECS
---------- ----------------------------------------------------------------
cell single block physical read             4009     89.91 ---> find4      .0224             [Wait is more than .59sec]

cell multiblock physical read               1        .02        .0217

cell smart table scan                       375     14.06        .0375


Elapsed: 00:00:00.14

Note: All because for uncommited transaction ---working as traditional buffer cache

Comments

  1. jitender8 July 2014 at 12:41

    Hello Monowar,

    I want to start Preparations for 11g OCM, Please guide me how can I start it.

    Jitender

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle DataGuard – How to resize Redo Log and Standby Redo Log

Oracle DataGuard – How to resize Redo Log and Standby Redo Log set linesize 200 col dest_name for a30 select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status; On Standby server - make file management parameter to manual if not: show parameter standby; alter system set standby_file_management=manual; ================== +     On Primary             + ==================  SQL> select group#,thread#,status from v$log; col member for a60 set linesize 200 select member,group#,status from v$logfile; SQL> select group#,thread#,status from v$log;     GROUP#    THREAD# STATUS ---------- ---------- ----------------          1          1 ACTIVE          2       ...
Read more

Exadata Flash Cache - Management

Total size of Flash CellCLI>  list flashcache attributes effectiveCacheSize          5.82122802734375T Flashcache Mode:  [root@Cellc01-adm ~]# su - celladmin cellcli CellCLI>  list cell attributes flashCacheMode,status          WriteThrough    online > list celldisk where disktype=flashdisk > list physicaldisk FLASH_1_1 detail List PhysicalDisk for FlashCache: CellCLI> list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status      8:0           HardDisk      "HGST    H7240AS60SUN4.0T"                              normal      8:1  ...
Read more