Re: IO_CELL_OFFLOAD_RETURNED_BYTES stats
- From: "David Fitzjarrell" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "oratune@xxxxxxxxx" for DMARC)
- To: "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>
- Date: Mon, 28 Apr 2014 09:20:24 -0700 (PDT)
My book covers this and provides scripts that calculate the actual bytes
processed by a Smart Scan/Offloading. An example from the book is shown below:
select sql_id,
io_cell_offload_eligible_bytes qualifying,
io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes actual,
round(((io_cell_offload_eligible_bytes -
io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2)
io_saved_pct,
sql_text
from v$sql
where io_cell_offload_returned_bytes > 0
and instr(sql_text, '&tname') > 0
and parsing_schema_name = upper('&user');
The above query will return data for a given username querying a somewhat
specific table (the instr() function will return all matches, partial and full,
for the value of &tname). Since column projection also contributes to the byte
savings the following two queries can show the column projection generated by a
specific query:
select sql_id,
projection
from v$sql_plan
where sql_id = '&sql_id';
select *
from table(dbms_xplan.display_cursor('&sql_id','&child_no', '+projection'));
The examples used in my book for these scripts use a modified EMP table:
create table emp (empid number not null,
empname varchar2(40),
deptno number)
storage(flash_cache keep);
Thus the column projection returns only those columns for a 'select *' query:
SQL> select *
2 from emp
3 where empid = 7934;
EMPID EMPNAME DEPTNO
---------- ----------------------------------------
----------
7934 Smorthorper7934 15
Elapsed: 00:00:00.16
SQL> select sql_id,
2 projection
3 from v$sql_plan
4 where sql_id = 'gfjb8dpxvpuv6';
SQL_ID PROJECTION
-------------
----------------------------------------------------------------------------
gfjb8dpxvpuv6 "EMPID"[NUMBER,22], "EMP"."EMPNAME"[VARCHAR2,40],
"EMP"."DEPTNO"[NUMBER,22]
SQL>
SQL> select *
2 from table(dbms_xplan.display_cursor('&sql_id','&child_no', '+projection'));
Enter value for sql_id: gfjb8dpxvpuv6
Enter value for child_no:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------SQL_ID
gfjb8dpxvpuv6, child number 0
-------------------------------------
select * from emp where empid = 7934
Plan hash value: 3956160932
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6361 (100)| |
|* 1 | TABLE ACCESS STORAGE FULL| EMP | 1 | 28 | 6361 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("EMPID"=7934)
filter("EMPID"=7934)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPID"[NUMBER,22], "EMP"."EMPNAME"[VARCHAR2,40],
"EMP"."DEPTNO"[NUMBER,22]
29 rows selected.
SQL>
It works the same way for a specific select list:
SQL> select sql_id,
2 projection
3 from v$sql_plan
4 where sql_id = '&sql_id';
Enter value for sql_id: g7990d65xth8s
old 4: where sql_id = '&sql_id'
new 4: where sql_id = 'g7990d65xth8s'
SQL_ID PROJECTION
------------- ------------------------------------------------------------
g7990d65xth8s
g7990d65xth8s "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,40], "DEPTNO"[NUMBER,22]
]
The first query I supplied provides this information:
SQL> select sql_id,
2 io_cell_offload_eligible_bytes qualifying,
3 io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes
actual,
4 round(((io_cell_offload_eligible_bytes -
io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2)
io_saved_pct,
5 sql_text
6 from v$sql
7 where io_cell_offload_returned_bytes > 0
8 and instr(sql_text, 'emp') > 0
9 and parsing_schema_name = 'BING';
SQL_ID QUALIFYING ACTUAL IO_SAVED_PCT SQL_TEXT
------------- ---------- ---------- ------------
---------------------------------------------
g7990d65xth8s 348004352 347953472 99.99 select /*+ cache */ empno,
ename, deptno from
emp where empno = 7934
SQL>
I think that this is what you expected to see from your original query.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Friday, April 25, 2014 4:46 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
I thought IO_CELL_OFFLOAD_RETURNED_BYTES shows the bytes returned by smart scan
processing from the cells, was reading a old documentation, doc bug. Just
looked the newest doc and it is as you have stated, regular path I/O.
Thanks
On Fri, Apr 25, 2014 at 6:11 PM, David Fitzjarrell <oratune@xxxxxxxxx> wrote:
The io_cell_offload_eligible_bytes column reports the bytes of data that
qualify for offload. This is the volume of data that can be offloaded to the
storage cells during query execution. The io_cell_offload_returned_bytes column
reports the number of bytes returned by the regular I/O path. These are the
bytes that were not offloaded to the
>cells.
>
>
>
>
>David Fitzjarrell
>Primary author, "Oracle Exadata Survival Guide"
>
>On Friday, April 25, 2014 9:39 AM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
>
>Hi
>
>
>I am check a query statistics in Exadata and I noticed that
>v$sql.IO_CELL_OFFLOAD_ELIGIBLE_BYTES is cero but
>IO_CELL_OFFLOAD_RETURNED_BYTES has a pretty high value.
>
>
How can this possible?
>
>select
>executions,
>disk_reads*16384/(1024*1024) PHY_MB,
>IO_CELL_OFFLOAD_ELIGIBLE_BYTES/1024/1024 as "OFFLOADED_MB",
>IO_CELL_OFFLOAD_RETURNED_BYTES/1024/1024 as "OFFLOAD_RT_MB",
>IO_INTERCONNECT_BYTES/1024/1024 as "ICONNECT_MB",
>PHYSICAL_READ_REQUESTS ,
>PHYSICAL_READ_BYTES/1024/1024 PHY_R_MB ,
>PHYSICAL_WRITE_REQUESTS ,
>PHYSICAL_WRITE_BYTES/1024/1024 PHY_W_MB
>from v$sql
>where sql_id = '4j92kqhrpcpr6'
>
>EXECUTIONS PHY_MB OFFLOADED_MB OFFLOAD_RT_MB ICONNECT_MB
>PHYSICAL_READ_REQUESTS PHY_R_MB PHYSICAL_WRITE_REQUESTS PHY_W_MB
>---------- ---------- ------------ ------------- -----------
>---------------------- ---------- ----------------------- ----------
> 1 130755.328 0 259544.156 520041.828
>2801860 130598.375 641580 129899.297
>
>
>Thanks
>
>
>
>
>
>
>
>
Other related posts:
