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.
No comments:
Post a Comment