Do Storage Indexes Work with Bind Variables?
I saw a post today where the subject of Exadata Storage Indexes were being discussed. One of the things that caught my eye was a discussion of whether Storage Indexes worked with Bind Variables. One of the posters observed that since smart scan was aimed at data warehouse type queries, bind variables were pretty much irrelevant. Which is true. Still it’s an interesting question. So I thought I’d give it a quick test.
As usual I used a couple of scripts:
fsx.sql – queries v$sql and shows whether a statement has been offloaded or not (slightly modified to remove 2 columns)
mystats.sql – just queries v$mystat
We’ll look at a test with a number column first.
SYS@LABRAT1> -- Do SI's work with bind variables? - Yes
SYS@LABRAT1>
SYS@LABRAT1> -- first here's basic info on my test table (SKEW3)
SYS@LABRAT1>
SYS@LABRAT1> desc kso.skew3
Name Null? Type
-------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
PK_COL NUMBER
COL1 NUMBER
COL2 VARCHAR2(30)
COL3 DATE
COL4 VARCHAR2(1)
SYS@LABRAT1> select count(*) from kso.skew3;
COUNT(*)
----------
384000048
1 row selected.
Elapsed: 00:00:26.53
SYS@LABRAT1> -- 27 seconds to do a full scan with no where clause (there are no indexes)
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 0
SYS@LABRAT1> -- no Storage Index usage by this session yet
SYS@LABRAT1> -- let's try a query using a variable
SYS@LABRAT1> set echo on
SYS@LABRAT1> @test_bv_si
SYS@LABRAT1>
SYS@LABRAT1> variable X NUMBER
SYS@LABRAT1>
SYS@LABRAT1> begin
2
3 :X := -1;
4
5 end;
6
7 /
PL/SQL procedure successfully completed.
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col1 = :x;
COUNT(*)
----------
0
Elapsed: 00:00:00.08
SYS@LABRAT1> set echo off
SYS@LABRAT1> @mystats
Enter value for name: storage
NAME VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index 16025346048
SYS@LABRAT1> -- so it used the storage index
SYS@LABRAT1> @fsx
Enter value for sql_text: select count(*) from kso.skew3 where col1 = :x
Enter value for sql_id:
Enter value for inst_id:
INST SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- --------------------------------------------------
1 1nsxv1zpawmsa 0 2684249835 2 .08 0 Yes 100.00 select count(*) from kso.skew3 where col1 = :x
1 row selected.
SYS@LABRAT1> @dplan
Enter value for sql_id: 1nsxv1zpawmsa
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1nsxv1zpawmsa, child number 0
-------------------------------------
select count(*) from kso.skew3 where col1 = :x
Plan hash value: 2684249835
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 533K(100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SKEW3 | 385 | 1925 | 533K (1)| 01:46:43 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL1"=:X)
filter("COL1"=:X)
20 rows selected.
So the Storage Index was clearly used on this statement using a SQL*Plus number variable. Here’s some 10046 trace data to show that smart scan wait event was used – note also the “enq: KO – fast object checkpoint” wait event which is done before the direct path reads (replaced by the “cell smart table scan” event in Exadata land).
Continue reading ‘Do Storage Indexes Work with Bind Variables?’ »
