Exadata Smart Scan - Part V

Smart scan do not occur for every query run on Exadata. There are three basic requirements that must be met for smart scans to occur.

 

·         There must be a full scan for an object

·         The scan must be a Direct path read operation.

·         The object must be on Exadata storage.

 

However, even when those conditions are met, there are cases that prevent smart scans. Here are the few situations where smart scans

               

·         Cannot be used on Clustered tables

·         Cannot be used on Index organized tables (IOT’s)

·         Cannot be used on tables created with ROWDEPENDENCIES enabled.

 

Here is an Index Organized table, having a huge data set.

 

demo@PDB1> select table_name,iot_type,num_rows,avg_row_len

  2  from user_tables

  3  where table_name ='T';

 

TABLE_NAME IOT_TYPE       NUM_ROWS AVG_ROW_LEN

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

T          IOT           100000000         135

 

demo@PDB1>

 

when we run a simple query against a non-indexed column, it took more than a min.

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

demo@PDB1> set serveroutput off timing on

demo@PDB1> select count(*) from t where owner ='SYS';

 

  COUNT(*)

----------

  69329593

 

Elapsed: 00:01:00.94

demo@PDB1> select * from table( dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9cjaxv5d7rg8m, child number 0

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

select count(*) from t where owner ='SYS'

 

Plan hash value: 454320086

 

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

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

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

|   0 | SELECT STATEMENT      |      |       |       |   529K(100)|          |

|   1 |  SORT AGGREGATE       |      |     1 |     5 |            |          |

|*  2 |   INDEX FAST FULL SCAN| T_PK |  3225K|    15M|   529K  (1)| 00:00:21 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OWNER"='SYS')

 

The absence of “Storage” filter in the above plan, confirms that smart scan was not used in this execution.

 

Also a quick check on V$SQL shows this.

 

demo@PDB1> col io_cell_offload_eligible_bytes format 999 heading "OFFLOAD_ELIG"

demo@PDB1> col io_interconnect_bytes format 999999999999 heading "BYTES_RETURNED"

demo@PDB1> select sql_id, sql_text,child_number,

  2     io_cell_offload_eligible_bytes ,

  3     io_interconnect_bytes

  4  from v$sql

  5  where sql_id ='9cjaxv5d7rg8m';

 

SQL_ID        SQL_TEXT               CHILD_NUMBER OFFLOAD_ELIG BYTES_RETURNED

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

9cjaxv5d7rg8m select count(*) from t            0            0    16039387136

               where owner ='SYS'

 

 

The V$SQL view contains a column called IO_CELL_OFFLOAD_ELIGIBLE_BYTES, which shows the number of bytes that are eligible for offloading. This column can be used as an indicator of weather a statement used a smart scan. It appears that this column is set to a value greater than zero only when smart scan is used. Since it zero now, smart scan was not used in this case.

 

The same goes for the tables created with ROWDEPENDENCIES enabled.

 

demo@PDB1> select table_name,dependencies,num_rows,avg_row_len

  2  from user_tables

  3  where table_name ='T';

 

TABLE_NAME DEPENDEN   NUM_ROWS AVG_ROW_LEN

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

T          ENABLED   100000000         135

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

demo@PDB1> set serveroutput off timing on

demo@PDB1> select count(*) from t where owner ='SYS';

 

  COUNT(*)

----------

  69329593

 

Elapsed: 00:00:22.73

demo@PDB1> select * from table( dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9cjaxv5d7rg8m, child number 0

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

select count(*) from t where owner ='SYS'

 

Plan hash value: 2966233522

 

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

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

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

|   0 | SELECT STATEMENT           |      |       |       |   551K(100)|          |

|   1 |  SORT AGGREGATE            |      |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS STORAGE FULL| T    |  3225K|    15M|   551K  (1)| 00:00:22 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - storage("OWNER"='SYS')

       filter("OWNER"='SYS')

 

 

20 rows selected.

 

demo@PDB1> col sql_text format a22

demo@PDB1> col io_cell_offload_eligible_bytes format 999 heading "OFFLOAD_ELIG"

demo@PDB1> col io_interconnect_bytes format 999999999999 heading "BYTES_RETURNED"

demo@PDB1> select sql_id, sql_text,child_number,

  2     io_cell_offload_eligible_bytes ,

  3     io_interconnect_bytes

  4  from v$sql

  5  where sql_id ='9cjaxv5d7rg8m';

 

SQL_ID        SQL_TEXT               CHILD_NUMBER OFFLOAD_ELIG BYTES_RETURNED

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

9cjaxv5d7rg8m select count(*) from t            0            0    16603504640

               where owner ='SYS'

 

 

With Clustered tables, it goes like this:

 

demo@PDB1> select table_name, cluster_name,num_rows,blocks

  2  from user_tables

  3  where cluster_name is not null;

 

TABLE_NAME CLUSTER_NAME           NUM_ROWS     BLOCKS

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

DEPT       EMP_DEPTNO_CLUST          10000    1063434

EMP        EMP_DEPTNO_CLUST      100000000    1063434

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

demo@PDB1> set serveroutput off timing on

demo@PDB1> select count(*) from emp where job='TABLE';

 

  COUNT(*)

----------

   3377882

 

Elapsed: 00:00:14.85

demo@PDB1> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  ay9rgugytpy1k, child number 0

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

select count(*) from emp where job='TABLE'

 

Plan hash value: 2083865914

 

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

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

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

|   0 | SELECT STATEMENT           |      |       |       |   289K(100)|          |

|   1 |  SORT AGGREGATE            |      |     1 |    10 |            |          |

|*  2 |   TABLE ACCESS STORAGE FULL| EMP  |  2941K|    28M|   289K  (1)| 00:00:12 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("JOB"='TABLE')

 

 

19 rows selected.

 

demo@PDB1> col sql_text format a22

demo@PDB1> col io_cell_offload_eligible_bytes format 999 heading "OFFLOAD_ELIG"

demo@PDB1> col io_interconnect_bytes format 999999999999 heading "BYTES_RETURNED"

demo@PDB1> select sql_id, sql_text,child_number,

  2     io_cell_offload_eligible_bytes ,

  3     io_interconnect_bytes

  4  from v$sql

  5  where sql_id ='ay9rgugytpy1k';

 

SQL_ID        SQL_TEXT               CHILD_NUMBER OFFLOAD_ELIG BYTES_RETURNED

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

ay9rgugytpy1k select count(*) from e            0            0     8699068416

              mp where job='TABLE'

 

demo@PDB1>

 

 

In all the above three cases, we see IO_CELL_OFFLOAD_ELIGIBLE_BYTES is zero, which confirms that smart scan was not possible for those workloads targeted on Index organized and clustered tables and for those tables created with ROWDEPDENCIES enabled.

Exadata Smart Scan - Part IV

One of the most important thing to learn about exadata is how to verify whether a query has been able to take advantage of smart scans. Unfortunately, the normal execution plan output from DBMS_XPLAN package doesn’t show us whether a smart scan was used or not. However there are several techniques that we can use to work around the issue.

 

Option #1 – 10046 Trace.

 

One of the most straightforward ways to determine whether a Smart Scan was used is to enable a 10046 trace on the statement in question. If Smart Scan was used, there will be CELL SMART TABLE SCAN or CELL SMART INDEX SCAN events in the trace file.

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

demo@PDB1> @tkfilename

/u02/app/oracle/diag/rdbms/c2qa/C2QA2/trace/C2QA2_ora_20623.trc

 

PL/SQL procedure successfully completed.

 

demo@PDB1> exec dbms_monitor.session_trace_enable;

 

PL/SQL procedure successfully completed.

 

demo@PDB1> set timing on

demo@PDB1> select count(*) from big_table where owner ='SCOTT';

 

  COUNT(*)

----------

         0

 

Elapsed: 00:00:00.37

demo@PDB1> exit

Disconnected from Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

 

Here is an excerpt from the trace file collected for the previous statement.

 

WAIT #140673634483024: nam='cell smart table scan' ela= 300 cellhash#=3115627269 p2=0 p3=0 obj#=75304 tim=1675694589751

WAIT #140673634483024: nam='cell smart table scan' ela= 231 cellhash#=3810989880 p2=0 p3=0 obj#=75304 tim=1675694590166

WAIT #140673634483024: nam='cell smart table scan' ela= 278 cellhash#=3875978273 p2=0 p3=0 obj#=75304 tim=1675694590630

WAIT #140673634483024: nam='cell smart table scan' ela= 344 cellhash#=922794634 p2=0 p3=0 obj#=75304 tim=1675694591168

WAIT #140673634483024: nam='cell smart table scan' ela= 222 cellhash#=1184623385 p2=0 p3=0 obj#=75304 tim=1675694591555

WAIT #140673634483024: nam='cell smart table scan' ela= 244 cellhash#=1518238859 p2=0 p3=0 obj#=75304 tim=1675694591952

WAIT #140673634483024: nam='cell smart table scan' ela= 3 cellhash#=3115627269 p2=0 p3=0 obj#=75304 tim=1675694592797

WAIT #140673634483024: nam='cell smart table scan' ela= 0 cellhash#=3810989880 p2=0 p3=0 obj#=75304 tim=1675694592821

WAIT #140673634483024: nam='cell smart table scan' ela= 1 cellhash#=3875978273 p2=0 p3=0 obj#=75304 tim=1675694592835

WAIT #140673634483024: nam='cell smart table scan' ela= 0 cellhash#=922794634 p2=0 p3=0 obj#=75304 tim=1675694592847

WAIT #140673634483024: nam='cell smart table scan' ela= 91 cellhash#=1184623385 p2=0 p3=0 obj#=75304 tim=1675694592949

WAIT #140673634483024: nam='cell smart table scan' ela= 0 cellhash#=1518238859 p2=0 p3=0 obj#=75304 tim=1675694592962

 

 

demo@PDB1> alter session set cell_offload_processing = false;

 

Session altered.

 

demo@PDB1> @tkfilename

/u02/app/oracle/diag/rdbms/c2qa/C2QA2/trace/C2QA2_ora_26004.trc

 

PL/SQL procedure successfully completed.

 

demo@PDB1> exec dbms_monitor.session_trace_enable;

 

PL/SQL procedure successfully completed.

 

demo@PDB1> set timing on

demo@PDB1> select count(*) from big_table where owner ='SCOTT';

 

  COUNT(*)

----------

         0

 

Elapsed: 00:00:17.71

demo@PDB1> exit

Disconnected from Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

 

Here is an excerpt from the trace file collected for the previous statement.

 

WAIT #139992250651064: nam='direct path read' ela= 940 file number=24 first dba=898 block cnt=126 obj#=75304 tim=1675728463173

WAIT #139992250651064: nam='direct path read' ela= 914 file number=24 first dba=1026 block cnt=126 obj#=75304 tim=1675728467054

WAIT #139992250651064: nam='direct path read' ela= 718 file number=24 first dba=262530 block cnt=126 obj#=75304 tim=1675728469359

WAIT #139992250651064: nam='direct path read' ela= 884 file number=24 first dba=262658 block cnt=126 obj#=75304 tim=1675728473479

WAIT #139992250651064: nam='direct path read' ela= 787 file number=24 first dba=262786 block cnt=126 obj#=75304 tim=1675728475785

WAIT #139992250651064: nam='direct path read' ela= 1111 file number=24 first dba=1541 block cnt=123 obj#=75304 tim=1675728478315

WAIT #139992250651064: nam='direct path read' ela= 870 file number=24 first dba=263170 block cnt=126 obj#=75304 tim=1675728483321

WAIT #139992250651064: nam='direct path read' ela= 881 file number=24 first dba=263298 block cnt=126 obj#=75304 tim=1675728485735

WAIT #139992250651064: nam='direct path read' ela= 882 file number=24 first dba=263426 block cnt=126 obj#=75304 tim=1675728488460

WAIT #139992250651064: nam='direct path read' ela= 639 file number=24 first dba=263554 block cnt=126 obj#=75304 tim=1675728490717

WAIT #139992250651064: nam='direct path read' ela= 401 file number=24 first dba=2178 block cnt=126 obj#=75304 tim=1675728491858

WAIT #139992250651064: nam='direct path read' ela= 665 file number=24 first dba=263682 block cnt=126 obj#=75304 tim=1675728494490

 

As we can see this trace file contains several CELL SMART TABLE SCAN wait events, while it was offloaded.

 

Option #2 – Performance statistics

 

We can also look at some of the performance views such as V$SESSTAT and V$MYSTAT and V$SYSSTAT, Here is an example using V$MYSTATS, which is a simple version of V$SESSTAT that limits the data to current session. For this example we will look at the statistics “cell scans” which is incremented when a smart table scan occurs.

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

demo@PDB1> select t1.value

  2  from v$mystat t1 ,

  3      v$sysstat t2

  4  where t1.statistic# = t2.statistic#

  5  and t2.name ='cell scans';

 

     VALUE

----------

         0

 

demo@PDB1> select count(*) from big_table where owner ='SCOTT';

 

  COUNT(*)

----------

         0

 

demo@PDB1> select t1.value

  2  from v$mystat t1 ,

  3      v$sysstat t2

  4  where t1.statistic# = t2.statistic#

  5  and t2.name ='cell scans';

 

     VALUE

----------

         1

 

demo@PDB1> alter session set cell_offload_processing=false;

 

Session altered.

 

demo@PDB1> select t1.value

  2  from v$mystat t1 ,

  3      v$sysstat t2

  4  where t1.statistic# = t2.statistic#

  5  and t2.name ='cell scans';

 

     VALUE

----------

         1

 

demo@PDB1> select count(*) from big_table where owner ='SCOTT';

 

  COUNT(*)

----------

         0

 

demo@PDB1> select t1.value

  2  from v$mystat t1 ,

  3      v$sysstat t2

  4  where t1.statistic# = t2.statistic#

  5  and t2.name ='cell scans';

 

     VALUE

----------

         1

 

demo@PDB1>

 

In the first execution, that statement got executed with Smart scan and the “cell scan” statistics got incremented, when turned off smart scan and ran the statement again this time the statistics was not incremented.

 

Option #3 – Offload Eligible bytes

 

There is another clue to determine whether a statement used a smart scan or not. The V$SQL family of view contains a column called IO_CELL_OFFLOAD_ELIGIBLE_BYTES, which shows the number of bytes eligible for offloading. This column can be used as an indicator of whether a statement used a smart scan or not. A value greater than zero indicates that particular sql used Smart scan.

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

SQL_ID: 7cfz5wy9caaf4

demo@PDB1> set feedback 6 sql_id

demo@PDB1> select count(*) from big_table where owner ='SCOTT';

 

  COUNT(*)

----------

         0

 

SQL_ID: 6c4xcpt8jm2hz

demo@PDB1> alter session set cell_offload_processing = false;

 

Session altered.

 

SQL_ID: 8nc18qc64surr

demo@PDB1> select count(*) from big_table where owner ='SCOTT';

 

  COUNT(*)

----------

         0

 

SQL_ID: 6c4xcpt8jm2hz

demo@PDB1> set feedback 6

demo@PDB1> col sql_text format a20

demo@PDB1> select sql_id, sql_text,child_number,

  2      decode( io_cell_offload_eligible_bytes,0,'NO','YES') offload

  3  from v$sql

  4  where sql_id ='6c4xcpt8jm2hz' ;

 

SQL_ID        SQL_TEXT             CHILD_NUMBER OFF

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

6c4xcpt8jm2hz select count(*) from            0 YES

               big_table where own

              er ='SCOTT'

 

6c4xcpt8jm2hz select count(*) from            1 NO

               big_table where own

              er ='SCOTT'

 

 

demo@PDB1>

 

 

Option #4 – SQL Monitoring

 

There is one another tool that is very useful for determining whether a SQL statement was offloaded or not. The new Real time sql monitoring functionality that was added in Oracle 11g, it is built into DBMS_SQLTUNE package and provides a great deal of information, not only whether a statement was offloaded, but also which step in the plan were offloaded. Here is an example.

 

demo@PDB1> show parameter cell_offload_processing

 

NAME                                 TYPE        VALUE

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

cell_offload_processing              boolean     TRUE

demo@PDB1> set linesize 1000 feedback 6 sql_id

demo@PDB1> select /*+ monitor */ count(*) from big_table where owner is not null;

 

  COUNT(*)

----------

 100000000

 

SQL_ID: ck892h3q96mgf

 

demo@PDB1> set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000

demo@PDB1> select dbms_sqltune.report_sql_monitor('ck892h3q96mgf',type=>'TEXT') from dual;

SQL Monitoring Report

 

SQL Text

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

select /*+ monitor */ count(*) from big_table where owner is not null

 

Global Information

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

 Status              :  DONE (ALL ROWS)

 Instance ID         :  2

 Session             :  C##RAJESH (2583:11690)

 SQL ID              :  ck892h3q96mgf

 SQL Execution ID    :  33554432

 Execution Started   :  07/16/2019 04:31:21

 First Refresh Time  :  07/16/2019 04:31:21

 Last Refresh Time   :  07/16/2019 04:31:21

 Duration            :  .350881s

 Module/Action       :  SQL*Plus/-

 Service             :  qcpr1.us1.ocm.s7187175.oraclecloudatcustomer.com

 Program             :  sqlplus.exe

 Fetch Calls         :  1

 

Global Stats

=======================================================

| Elapsed |   Cpu   |..... |    Offload     |  Cell   |

| Time(s) | Time(s) |..... | Returned Bytes | Offload |

=====================.....=============================

|    0.35 |    0.18 |..... |           62MB |   6.11% |

=======================================================

 

SQL Plan Monitoring Details (Plan Hash Value=599409829)

=================================================...===================

| Id |          Operation           |   Name    |...| Activity Detail |

|    |                              |           |...|   (# samples)   |

=================================================...===================

|  0 | SELECT STATEMENT             |           |...|                 |

|  1 |   SORT AGGREGATE             |           |...|                 |

|  2 |    TABLE ACCESS STORAGE FULL | BIG_TABLE |...| Cpu (1)         |

=================================================...===================

 

 

The presence of “Offload Returned Bytes” and “Cell Offload” column in “global stats” section confirms that this SQL got offloaded. Whereas with offloading disabled, we don’t see such information available in the “global stats” section.

 

demo@PDB1> alter session set cell_offload_processing = false;

 

Session altered.

 

SQL_ID: 8nc18qc64surr

demo@PDB1> select /*+ monitor */ count(*) from big_table where owner is not null;

 100000000

 

SQL_ID: ck892h3q96mgf

demo@PDB1> select dbms_sqltune.report_sql_monitor('ck892h3q96mgf',type=>'TEXT') from dual;

SQL Monitoring Report

 

SQL Text

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

select /*+ monitor */ count(*) from big_table where owner is not null

 

Global Information

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

 Status              :  DONE (ALL ROWS)

 Instance ID         :  2

 Session             :  C##RAJESH (2583:11690)

 SQL ID              :  ck892h3q96mgf

 SQL Execution ID    :  33554433

 Execution Started   :  07/16/2019 04:34:26

 First Refresh Time  :  07/16/2019 04:34:26

 Last Refresh Time   :  07/16/2019 04:34:42

 Duration            :  16s

 Module/Action       :  SQL*Plus/-

 Service             :  qcpr1.us1.ocm.s7187175.oraclecloudatcustomer.com

 Program             :  sqlplus.exe

 Fetch Calls         :  1

 

Global Stats

=================================================================

| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read  | Read  |

| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs  | Bytes |

=================================================================

|      17 |      14 |     3.03 |     1 |     2M | 15860 |  15GB |

=================================================================

 

SQL Plan Monitoring Details (Plan Hash Value=599409829)

=================================================....===============================

| Id |          Operation           |   Name    |....| Activity |   Activity Detail|

|    |                              |           |....|   (%)    |     (# samples)  |

=================================================....===============================

|  0 | SELECT STATEMENT             |           |....|          |                  |

|  1 |   SORT AGGREGATE             |           |....|          |                  |

|  2 |    TABLE ACCESS STORAGE FULL | BIG_TABLE |....|   100.00 | Cpu (14)         |

|    |                              |           |....   | direct path read (3) |  

=================================================....===============================

 

 

Note that we cut out a number of columns from the report because it is very wide and doesn’t fit nicely in this page.