I was just reading out an amazing Oracle Exadata book to enhance my knowledge about Oracle Exadata, it is a must read book for every Oracle DBAs. I am posting here one little snip from this book, how authors have explained the Exadata concepts and practical way to find the Exadata Offload eligible bytes for a given SQL statement i.e. how to check if your SQL statement was offloaded to Exadata storage cell or Exadata Smart Scan was used.
There are lots of things that is explained wonderfully in this book. If you want to get a copy of this and bring your career to a new level - get a copy of this book
Click Here
https://www.amazon.in/Expert-Oracle-Exadata-Martin-Bach/dp/1430262419/ref=sr_1_fkmr1_3?ie=UTF8&qid=1536641983&sr=8-3-fkmr1&keywords=expert+oracle+exadata+second+edition
Offload Eligible Bytes
There is another clue to whether a statement used a Smart Scan or not. As you saw in previous sections, the
V$SQL family of views contain 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 whether a statement used a Smart Scan. It appears that this column is set to a value greater than 0 only when a Smart Scan is used. You can make use of this observation to write a little script (
fsx.sql) that returns a value of
YES or
NO, depending on whether that column in
V$SQL has a value greater than 0. The output of the script is a little too wide to fit in a book format, which is why there are a couple of cut-down versions in the examples. And, of course, all of the versions will be available in the online code repository. You have already seen the script in action in several of the previous sections. The script is shown here for your convenience, along with an example of its use:
> !cat fsx.sql
----------------------------------------------------------------------------------------
--
-- File name: fsx.sql
--
-- Purpose: Find SQL and report whether it was Offloaded and % of I/O saved.
--
-- Usage: This scripts prompts for two values.
--
-- sql_text: a piece of a SQL statement like %select col1, col2 from skew%
--
-- sql_id: the sql_id of the statement if you know it (leave blank to ignore)
--
-- Description:
--
-- This script can be used to locate statements in the shared pool and
-- determine whether they have been executed via Smart Scans.
--
-- It is based on the observation that the IO_CELL_OFFLOAD_ELIGIBLE_BYTES
-- column in V$SQL is only greater than 0 when a statement is executed
-- using a Smart Scan. The IO_SAVED_% column attempts to show the ratio of
-- of data received from the storage cells to the actual amount of data
-- that would have had to be retrieved on non-Exadata storage. Note that
-- as of 11.2.0.2, there are issues calculating this value with some queries.
--
-- Note that the AVG_ETIME will not be acurate for parallel queries. The
-- ELAPSED_TIME column contains the sum of all parallel slaves. So the
-- script divides the value by the number of PX slaves used which gives an
-- approximation.
--
-- Note also that if parallel slaves are spread across multiple nodes on
-- a RAC database the PX_SERVERS_EXECUTIONS column will not be set.
--
---------------------------------------------------------------------------------------
set pagesize 999
set lines 190
col sql_text format a70 trunc
col child format 99999
col execs format 9,999
col avg_etime format 99,999.99
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offload for a7
select sql_id, child_number child, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)/
decode(px_servers_executions,0,1,px_servers_executions/
decode(nvl(executions,0),0,1,executions)) avg_etime,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,
100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES))
"IO_SAVED_%", sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
In the fsx script, you can see that the OFFLOAD column is just a DECODE that checks to see if the IO_CELL_OFFLOAD_ELIGIBLE_BYTES column is equal to 0 or not. The IO_SAVED_% column is calculated using the IO_INTERCONNECT_BYTES field, and it attempts to show how much data was returned to the database servers.
The script can be used for many useful purposes. The author primarily uses it to find the SQL_ID and child cursor number of SQL statements in the shared pool. In this example, it is used to determine if a statement has been offloaded or not:
SQL> select /*+ gather_plan_statistics fsx-example-002 */
2 avg(id) from bigtab where id between 1000 and 50000;
AVG(ID)
----------
25500
Elapsed: 00:00:00.64
SQL> alter session set cell_offload_processing=false;
Session altered.
Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics fsx-example-002 */
2 avg(id) from bigtab where id between 1000 and 50000;
AVG(ID)
----------
25500
Elapsed: 00:00:53.88
SQL> @fsx4
Enter value for sql_text: %fsx-example-002%
Enter value for sql_id:
SQL_ID CHILD OFFLOAD IO_SAVED_% AVG_ETIME SQL_TEXT
------------- ------ ------- ---------- ---------- ----------------------------------------
cj0p52wha5wb8 0 Yes 99.97 .63 select /*+ gather_plan_statistics fsx-ex
cj0p52wha5wb8 1 No .00 53.88 select /*+ gather_plan_statistics fsx-ex
2 rows selected.
The elapsed times are a bit of a giveaway as to whether the statement was offloaded or not, but if you are called in after the fact, the output of the fsx script clearly shows that the child_number 1 has not been offloaded. The fact that a new child cursor has been created is very important in this example. When setting CELL_OFFLOAD_PROCESSING to FALSE, the optimizer created a new child cursor due to a mismatch. Reasons why child cursors are created can be found in v$sql_shared_cursor. This view contains a long list of flags that allow you to identify differences between child cursors but is very hard to read in SQL*Plus. Oracle added a CLOB containing XML data in 11.2.0.2 that makes it easier to spot the difference. Using the SQL ID from the previous example, this is put to use. Note that I cast the CLOB to XML for better readability:
SQL> select xmltype(reason) from v$sql_shared_cursor
2 where sql_id = 'cj0p52wha5wb8' and child_number = 0;
XMLTYPE(REASON)
---------------------------------------------------------------------------
<ChildNode>
<ChildNumber>0</ChildNumber>
<ID>3</ID>
<reason>Optimizer mismatch(12)</reason>
<size>2x356</size>
<cell_offload_processing> true false </cell_offload_processing>
</ChildNode>
Translating the XML output into plain English, you can see that there was an optimizer mismatch: The parameter cell_offload_processing has changed from TRUE to FALSE.
It is not always the case for child cursors to be created after changing parameters. Certain underscore parameters such as _SERIAL_DIRECT_READ will not cause a new child cursor to be created. Some executions of the same cursor might be offloaded, others not. This can be quite confusing, although this should be a very rare occurrence! Here is an example to demonstrate the effect:
SQL> select /*+ gather_plan_statistics fsx-example-004 */ avg(id)
2 from bigtab where id between 1000 and 50002;
AVG(ID)
----------
25501
Elapsed: 00:00:00.68
SQL> alter session set "_serial_direct_read" = never;
Session altered.
Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics fsx-example-004 */ avg(id)
2 from bigtab where id between 1000 and 50002;
AVG(ID)
----------
25501
Elapsed: 00:04:50.32
SQL> SQL> alter session set "_serial_direct_read" = auto;
Session altered.
Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics fsx-example-004 */ avg(id)
2 from bigtab where id between 1000 and 50002;
AVG(ID)
----------
25501
Elapsed: 00:00:00.63
SQL> @fsx4
Enter value for sql_text: %fsx-example-004%
Enter value for sql_id:
SQL_ID CHILD OFFLOAD IO_SAVED_% AVG_ETIME SQL_TEXT
------------- ------ ------- ---------- ---------- ----------------------------------------
6xh6qwv302p13 0 Yes 55.17 97.21 select /*+ gather_plan_statistics fsx-ex
As you can see, there are three executions using the same child cursor (no new child cursor has been created). The statistics about I/O saved and execution time now have little value: Two executions completed in less than a second, and one took almost five minutes. This is the well-known problem with averages: They obfuscate detail.
