Exadata and Virtual Private Database: will it offload my query?
Posted by Martin Bach on October 14, 2014
During one of the classes I taught about Exadata optimisations I had an interesting question:
If I am using VPD, will Exadata still offload the query?
Background is that we discussed function offloading, and the meta-view v$sqlfn_metadata. It turned out that SYS_CONTEXT() is not offloadable in 11.2.0.4.
SQL> select name,datatype,analytic,aggregate,offloadable,descr 2 from v$sqlfn_metadata where name = 'SYS_CONTEXT'; NAME DATATYPE ANA AGG OFF DESCR -------------------------------------------------- -------- --- --- --- ------------------------------ SYS_CONTEXT UNKNOWN NO NO NO SYS_CONTEXT
Since I’m a great fan of the Tom Kyte method (don’t say it is so, show it!) I needed a quick example. Of all the Oracle books I read “Effective Oracle by Design” was among the most inspiring.
Where to check?
My first idea was to check v$sqlfn_metadata to see if the ever present SYS_CONTEXT() was offloadable:
SQL> select name,offloadable 2 from v$sqlfn_metadata 3 where name = 'SYS_CONTEXT'; NAME OFF ----------------------- --- SYS_CONTEXT NO SYS:dbm011> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
OK so it does not appear to be offloadable. Does it matter? As you will see, it does not although it could startle you at first.
Reproduce a test case: 11.2.0.4/11.2.3.3.1.140529.1
After finishing the example from the Oracle documentation I decided that I needed a few more rows in the table to get to a more realistic data distribution for an Exadata system. You wouldn’t see a smart scan on a row with < 10 rows. This can be done quite easily, and the end result was:
SYS> select count(*) from scott.orders_tab; COUNT(*) ----------- 12000003 1 row selected.
I had to ensure that the query is offloaded first-simplez! Kerry Osborne has a script for this:
SYS> select /*+ gather_plan_statistics rowcount_sys_stats */ 2 count(*) from scott.orders_tab; COUNT(*) ----------- 12000003 1 row selected. Elapsed: 00:00:00.81 SYS:OGGSRC> @scripts/fsx Enter value for sql_text: %rowcount_sys_stats% Enter value for sql_id: SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT ------------- ------ ----------- ------ ---------- ------ ------- ---------- ---------------------------------------------------------------------- 8bv5b04mjku08 0 1093340548 1 .81 0 Yes 38.47 select /*+ gather_plan_statistics rowcount_sys_stats */ count(*) from 1 row selected. SYS:OGGSRC> @scripts/dplan Enter value for sql_id: 8bv5b04mjku08 Enter value for child_no: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 8bv5b04mjku08, child number 0 ------------------------------------- select /*+ gather_plan_statistics rowcount_sys_stats */ count(*) from scott.orders_tab Plan hash value: 1093340548 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 7521 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS STORAGE FULL| ORDERS_TAB | 12M| 7521 (1)| 00:01:31 | --------------------------------------------------------------------------------- 15 rows selected.
So I guess it is.
Enter VPD
With all the VPD bells and whistles turned on I connected as on the the users for which there is an explicit context set and ran my query:
TBROOKE> select /*+ gather_plan_statistics tbrooke_query_001 */
2 count(*) from scott.orders_tab;
COUNT(*)
-----------
4
1 row selected.
Elapsed: 00:00:00.15
1 row selected.
Which shows that the VPD policy works. What do my tools indicate?
SYS> @scripts/fsx
Enter value for sql_text: %tbrooke_query_001%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ----------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
bs9gq0bdqazzu 0 1093340548 1 .15 0 Yes 99.98 select /*+ gather_plan_statistics tbrooke_query_001 */ count(*) from s
1 row selected.
SYS> @scripts/dplan
Enter value for sql_id: bs9gq0bdqazzu
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bs9gq0bdqazzu, child number 0
-------------------------------------
select /*+ gather_plan_statistics tbrooke_query_001 */ count(*) from
scott.orders_tab
Plan hash value: 1093340548
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7651 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS STORAGE FULL| ORDERS_TAB | 1 | 6 | 7651 (3)| 00:01:32 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("CUST_NO"=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))
filter("CUST_NO"=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))
21 rows selected.
So using the script it is easily visible that a smart scan has happened and in fact it saved 99.98% of IO. Which is not surprising giving that only 4 rows out of the whole result set have been returned. The Real Time SQL Monitor Report confirmed the finding by the way. I had to sneak in another hint (+monitor) otherwise the statement wouldn’t be captured in SQL Monitor (only “long running” statements are captured by default)
SQL Monitoring Report SQL Text ------------------------------ select /*+ gather_plan_statistics monitor tbrooke_query_002 */ count(*) from scott.orders_tab Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : TBROOKE (33:239) SQL ID : 8ydqam3fuwt2z SQL Execution ID : 16777216 Execution Started : 10/14/2014 04:28:24 First Refresh Time : 10/14/2014 04:28:24 Last Refresh Time : 10/14/2014 04:28:24 Duration : .100139s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@enkdb01.enkitec.com (TNS V1-V3) Fetch Calls : 1 Global Stats ==================================================================================== | Elapsed | Cpu | IO | PL/SQL | Fetch | Buffer | Read | Read | Cell | | Time(s) | Time(s) | Waits(s) | Time(s) | Calls | Gets | Reqs | Bytes | Offload | ==================================================================================== | 0.10 | 0.01 | 0.09 | 0.00 | 1 | 27352 | 228 | 214MB | 99.98% | ==================================================================================== SQL Plan Monitoring Details (Plan Hash Value=1093340548) ========================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | ========================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | | | | | 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | | | | | 2 | TABLE ACCESS STORAGE FULL | ORDERS_TAB | 1 | 7651 | 1 | +0 | 1 | 4 | 228 | 214MB | 99.98% | 2M | | | =========================================================================================================================================================================
This confirms that a full table scan happened, and it must have been executed as a direct path read. A DPR on Exadata most oftent transforms into a smart scan. As you can see I didn’t specify any other predicate, and yet the VPD was offloaded.
Reproduce a test case: 12.1.0.2.0/12.1.1.1.1.140712
Interestingly in 12.1.0.2.0 the function SYS_CONTEXT is offloadable:
SQL> select name, offloadable 2 from v$sqlfn_metadata where name = 'SYS_CONTEXT'; NAME OFF ------------------------------ --- SYS_CONTEXT YES 1 row selected.
For completeness sake I have repeated my test here. I copied the table via network link and created the same users and VPD. Not too much of a difference. Information is shown here without comments:
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
TBROOKE> select /*+ gather_plan_statistics tbrooke_query_013 */ count(*) from scott.orders_tab;
COUNT(*)
----------
4
Elapsed: 00:00:00.02
SQL> @scripts/fsx
Enter value for sql_text: %tbrooke_query_013%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
bf0s5hzr7x9r5 0 1093340548 1 .02 0 Yes 99.97 select /*+ gather_plan_statistics tbrooke_query_013 */ count(*) from s
1 row selected.
SQL> @scripts/dplan
Enter value for sql_id: bf0s5hzr7x9r5
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bf0s5hzr7x9r5, child number 0
-------------------------------------
select /*+ gather_plan_statistics tbrooke_query_013 */ count(*) from
scott.orders_tab
Plan hash value: 1093340548
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS STORAGE FULL| ORDERS_TAB | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("CUST_NO"=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))
filter("CUST_NO"=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))
21 rows selected.
SQL Monitoring Report
SQL Text
------------------------------
select /*+ gather_plan_statistics monitor tbrooke_query_014 */ count(*) from scott.orders_tab
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : TBROOKE (1042:32766)
SQL ID : 55yy67scgw2sf
SQL Execution ID : 16777216
Execution Started : 10/14/2014 05:13:30
First Refresh Time : 10/14/2014 05:13:30
Last Refresh Time : 10/14/2014 05:13:30
Duration : .016025s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@enkdb03.enkitec.com (TNS V1-V3)
Fetch Calls : 1
Global Stats
==================================================================================================
| Elapsed | Cpu | IO | Application | PL/SQL | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Calls | Gets | Reqs | Bytes | Offload |
==================================================================================================
| 0.02 | 0.01 | 0.01 | 0.00 | 0.00 | 1 | 27441 | 223 | 214MB | 99.99% |
==================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1093340548)
=========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
=========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | | | |
| 2 | TABLE ACCESS STORAGE FULL | ORDERS_TAB | 1 | 2 | 1 | +0 | 1 | 4 | 223 | 214MB | 99.99% | 3M | | |
=========================================================================================================================================================================
In the last example flash cache and storage indexes attributed greatly to the quick execution time. Using Adrian Billington’s mystats I can see more detail. I have removed what’s not needed from the report.
SQL> @scripts/mystats stop t=1 ========================================================================================== MyStats report : 14-OCT-2014 05:27:44 ========================================================================================== ------------------------------------------------------------------------------------------ 1. Summary Timings ------------------------------------------------------------------------------------------ Type Statistic Name Value ------ ---------------------------------------------------------------- ---------------- TIMER snapshot interval (seconds) 13.59 TIMER CPU time used (seconds) 0.01 ------------------------------------------------------------------------------------------ 2. Statistics Report ------------------------------------------------------------------------------------------ Type Statistic Name Value ------ ---------------------------------------------------------------- ---------------- STAT CPU used by this session 7 STAT CPU used when call started 7 STAT DB time 9 ... STAT cell IO uncompressed bytes 3,031,040 STAT cell blocks helped by minscn optimization 370 STAT cell blocks processed by cache layer 370 STAT cell blocks processed by data layer 370 STAT cell blocks processed by txn layer 370 STAT cell flash cache read hits 10 STAT cell num smartio automem buffer allocation attempts 1 STAT cell physical IO bytes eligible for predicate offload 224,763,904 STAT cell physical IO bytes saved by storage index 221,732,864 STAT cell physical IO interconnect bytes 5,360 STAT cell physical IO interconnect bytes returned by smart scan 5,360 STAT cell scans 1 ... STAT consistent gets 27,816 STAT consistent gets direct 27,437 STAT consistent gets examination 3 STAT consistent gets examination (fastpath) 3 STAT consistent gets from cache 379 STAT consistent gets pin 376 STAT consistent gets pin (fastpath) 376 STAT db block changes 787 STAT db block gets 2,879 STAT db block gets from cache 2,879 ... STAT logical read bytes from cache 26,689,536 ... STAT physical read bytes 224,763,904 STAT physical read requests optimized 223 STAT physical read total IO requests 223 STAT physical read total bytes 224,763,904 STAT physical read total bytes optimized 224,763,904 STAT physical read total multi block requests 215 STAT physical reads 27,437 STAT physical reads direct 27,437 ... STAT table fetch by rowid 1 STAT table scan blocks gotten 3 STAT table scan disk non-IMC rows gotten 1,685 STAT table scan rows gotten 1,685 STAT table scans (direct read) 1 STAT table scans (long tables) 1 ... ------------------------------------------------------------------------------------------ 3. About ------------------------------------------------------------------------------------------ - MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net) - Based on the SNAP_MY_STATS utility by Jonathan Lewis ========================================================================================== End of report ==========================================================================================
In the case of VPD the fact that SYS_CONTEXT() is offloadable did not play a major role.
Leave a Reply