Search

OakieTags

more tags

Who's online

There are currently 0 users and 37 guests online.

Recent comments

Affiliations

Home

11gR2

_connect_by_use_union_all

Submitted by Timur Akhmadeev on Tue, 2010-10-05 03:01

This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces [...]

Do Storage Indexes Work with Bind Variables?

Submitted by Kerry Osborne on Mon, 2010-09-13 02:12

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.

#99cc00">?View Code NONE
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).

#99cc00">?View Code NONE
 
...
 
PARSING IN CURSOR #2 len=46 dep=0 uid=0 oct=3 lid=0 tim=1284254192882293 hv=3937292042 ad='76742aa20' sqlid='1nsxv1zpawmsa'
select count(*) from kso.skew3 where col1 = :x
END OF STMT
PARSE #2:c=1000,e=299,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1284254192882292
WAIT #2: nam='ges message buffer allocation' ela= 5 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882398
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882442
WAIT #2: nam='library cache lock' ela= 228 handle address=31804186896 lock address=31727714984 100*mode+namespace=315619966779394 obj#=73486 tim=1284254192882696
WAIT #2: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882741
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192882764
WAIT #2: nam='library cache pin' ela= 176 handle address=31804186896 pin address=31727714728 100*mode+namespace=315619966779394 obj#=73486 tim=1284254192882963
EXEC #2:c=1000,e=1611,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2684249835,tim=1284254192883951
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=73486 tim=1284254192883982
WAIT #2: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192884289
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192884322
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 100 name|mode=1263468550 2=65584 0=1 obj#=73486 tim=1284254192884443
WAIT #2: nam='reliable message' ela= 1287 channel context=31898270672 channel handle=31492015160 broadcast message=31556682800 obj#=73486 tim=1284254192885850
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192885899
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 142 name|mode=1263468550 2=65584 0=1 obj#=73486 tim=1284254192886063
WAIT #2: nam='ges message buffer allocation' ela= 1 pool=0 request=1 allocated=0 obj#=73486 tim=1284254192886118
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 117 name|mode=1263468545 2=65584 0=2 obj#=73486 tim=1284254192886271
WAIT #2: nam='cell smart table scan' ela= 240 cellhash#=379339958 p2=0 p3=0 obj#=73486 tim=1284254192887559
WAIT #2: nam='cell smart table scan' ela= 222 cellhash#=2133459483 p2=0 p3=0 obj#=73486 tim=1284254192888038
WAIT #2: nam='cell smart table scan' ela= 212 cellhash#=3176594409 p2=0 p3=0 obj#=73486 tim=1284254192888531
WAIT #2: nam='cell smart table scan' ela= 1038 cellhash#=379339958 p2=0 p3=0 obj#=73486 tim=1284254192894795
WAIT #2: nam='cell smart table scan' ela= 1061 cellhash#=2133459483 p2=0 p3=0 obj#=73486 tim=1284254192895927
WAIT #2: nam='cell smart table scan' ela= 962 cellhash#=3176594409 p2=0 p3=0 obj#=73486 tim=1284254192896956
WAIT #2: nam='cell smart table scan' ela= 1121 cellhash#=379339958 p2=0 p3=0 obj#=73486 tim=12842541928982088
 
...

So it looks like a definite yes for offloading with bind variables and using Storage Indexes with bind variables. At least with numeric variables. Now let’s check out a varchar2 column and while we’re at it let’s check wild carding using % and the LIKE operator.

#99cc00">?View Code NONE
SYS@LABRAT1> select col2, count(*) from kso.skew3 group by col2;
 
COL2                               COUNT(*)
------------------------------ ------------
                                         12
2342                                     36
asddsadasd                        384000000
 
3 rows selected.
 
Elapsed: 00:00:41.90
SYS@LABRAT1> select count(*) from kso.skew3 where col2 like '2342';
 
    COUNT(*)
------------
          36
 
1 row selected.
 
Elapsed: 00:00:00.10
SYS@LABRAT1> -- you should guess from the elapsed time this one used the Storage Index (it did)
SYS@LABRAT1>
SYS@LABRAT1> select count(*) from kso.skew3 where col2 like '234%';
 
    COUNT(*)
------------
          36
 
1 row selected.
 
Elapsed: 00:00:14.98
SYS@LABRAT1> -- and you should guess from the elapsed time that this one didn't (it didn't)
SYS@LABRAT1> -- so wildcards are not good for Storage Indexes
SYS@LABRAT1> -- let's try varchar2 variables now
SYS@LABRAT1>
SYS@LABRAT1> @test_bv_si
SYS@LABRAT1> 
SYS@LABRAT1> variable W varchar2(10)
SYS@LABRAT1> variable X varchar2(10)
SYS@LABRAT1> variable Y varchar2(10)
SYS@LABRAT1> variable Z varchar2(10)
SYS@LABRAT1> 
SYS@LABRAT1> begin
  2  
  3  :X := '1111';
  4  :Y := '2342';
  5  :Z := '234%';
  6  
  7  end;
  8  
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
SYS@LABRAT1> 
SYS@LABRAT1> select /* 1111 */ count(*) from kso.skew3 where col1 = :x;
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:15.25
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index          0
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- no joy - looks like this one should use Storage Index, why not???
SYS@LABRAT1>
SYS@LABRAT1> select /* 2342 */ count(*) from kso.skew3 where col2 = :y;
 
  COUNT(*)
----------
        36
 
Elapsed: 00:00:00.10
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 1.6000E+10
 
Elapsed: 00:00:00.00
SYS@LABRAT1> --this one worked
SYS@LABRAT1>
SYS@LABRAT1> select /* 2342 */ count(*) from kso.skew3 where col2 like :y;
 
  COUNT(*)
----------
        36
 
Elapsed: 00:00:15.11
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 1.6000E+10
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- this one doesn't work due to the LIKE
SYS@LABRAT1>
SYS@LABRAT1> select /* 234% */ count(*) from kso.skew3 where col2 like :z;
 
  COUNT(*)
----------
        36
 
Elapsed: 00:00:15.19
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 1.6000E+10
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- this one also doesn't work due to the LIKE
SYS@LABRAT1> -- let's try rerunning the same statement but changing the values 
SYS@LABRAT1>
SYS@LABRAT1> exec :Y := '2342';
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
SYS@LABRAT1> select /* various */ count(*) from kso.skew3 where col2 = :y;
 
  COUNT(*)
----------
        36
 
Elapsed: 00:00:00.08
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 3.2000E+10
 
Elapsed: 00:00:00.01
SYS@LABRAT1> -- used the Storage Index as expected
SYS@LABRAT1>
SYS@LABRAT1> exec :Y := '1111';
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
SYS@LABRAT1> select /* various */ count(*) from kso.skew3 where col2 = :y;
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:00.07
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 4.8026E+10
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- This is odd - it used the Storage Index again, even though our original test with '1111' didn't
SYS@LABRAT1> -- is this similar to bind variable peeking in that the statement has a locked in approach?
SYS@LABRAT1> -- I'm not sure.
SYS@LABRAT1>
SYS@LABRAT1> exec :Y := 'asddsadasd';
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
SYS@LABRAT1> select /* various */ count(*) from kso.skew3 where col2 = :y;
 
  COUNT(*)
----------
 384000000
 
Elapsed: 00:00:32.01
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                               VALUE
--------------------------------------------- ----------
cell physical IO bytes saved by storage index 4.8026E+10
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- doesn't appeat that this one got any benefit, 
SYS@LABRAT1> -- but there may not be any blocks that don't contain that value 
SYS@LABRAT1> -- here the stats on the statements in this example
SYS@LABRAT1>
SYS@LABRAT1> set echo off
SYS@LABRAT1>@fsx
Enter value for sql_text: %skew3%
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 14a1chcq10j8q      0 2684249835          1         15.23      0 Yes              99.99 select /* 1111 */ count(*) from kso.skew3 where col1 = :x
    1 367zpt07qh2d6      0 2684249835          1         15.17      0 Yes              99.99 select /* 234% */ count(*) from kso.skew3 where col2 like :z
    1 f834t319m48vw      0 2684249835          3         10.72      0 Yes              86.45 select /* various */ count(*) from kso.skew3 where col2 = :y
    1 ftrtpg2xcdp0t      0 2684249835          1         15.11      0 Yes              99.99 select /* 2342 */ count(*) from kso.skew3 where col2 like :y
    1 gcnvsm28bnu4p      0 2684249835          1           .09      0 Yes             100.00 select /* 2342 */ count(*) from kso.skew3 where col2 = :y
 
5 rows selected.
 
Elapsed: 00:00:00.05

So these results indicate the following:

  1. Storage Indexes Can be Used with Bind Variables on Varchar2 variables
  2. Storage Indexes Don’t Appear to be Used with any Wild Carding (%)
  3. Storage Indexes Aren’t Used with the Like Operator when Bind Variables are used

Now let’s take a quick look at how Storage Indexes work with date fields.

#99cc00">?View Code NONE
 
SYS@LABRAT1> flush_pool
 
System altered.
 
SYS@LABRAT1> select min(col3),max(col3) from kso.skew3;             
 
MIN(COL3) MAX(COL3)
--------- ---------
20-OCT-05 01-JAN-09
 
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                                        0
 
SYS@LABRAT1> set timing on
SYS@LABRAT1> select count(*) from kso.skew3 where col3 = '20-OCT-05';
 
  COUNT(*)
----------
         4
 
Elapsed: 00:00:15.13
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                                        0
 
Elapsed: 00:00:00.01
SYS@LABRAT1> -- so no Storage Index usage???
SYS@LABRAT1> 
SYS@LABRAT1> select count(*) from kso.skew3 where col3 < '19-OCT-05';
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:15.07
SYS@LABRAT1> -- still no Storage Index usage
SYS@LABRAT1> 
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                                        0
 
Elapsed: 00:00:00.00
SYS@LABRAT1> select count(*) from kso.skew3 where col3 > '01-jan-10';
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:15.09
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                                        0
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- still nothing
SYS@LABRAT1> 
SYS@LABRAT1> select count(*) from kso.skew3 where col3 is null;
 
  COUNT(*)
----------
        12
 
Elapsed: 00:00:00.08
SYS@LABRAT1> @mystats
Enter value for name: storage
 
NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                              16012763136
 
Elapsed: 00:00:00.00
SYS@LABRAT1> -- so this time we used the Storage Index 
SYS@LABRAT1> -- why is it different?
SYS@LABRAT1> 
SYS@LABRAT1> @fsx
Enter value for sql_text: %skew3%
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 2sycry6jd7cus      0 2684249835          1         15.06      0 Yes              99.99 select count(*) from kso.skew3 where col3 < '19-OCT-05'
    1 6n5y91cxw4yzu      0 2684249835          1           .08      0 Yes             100.00 select count(*) from kso.skew3 where col3 is null
    1 asfmw4ccsv2u9      0 2684249835          1         28.45      0 Yes              67.15 select min(col3),max(col3) from kso.skew3
    1 fuhmg9hqdbd84      0 2684249835          1         15.12      0 Yes              99.99 select count(*) from kso.skew3 where col3 = '20-OCT-05'
    1 gkbzsmx4w57ym      0 2684249835          1         15.09      0 Yes              99.99 select count(*) from kso.skew3 where col3 > '01-jan-10'
 
5 rows selected.
 
Elapsed: 00:00:00.06
SYS@LABRAT1> @dplan
Enter value for sql_id: fuhmg9hqdbd84
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuhmg9hqdbd84, child number 0
-------------------------------------
select count(*) from kso.skew3 where col3 = '20-OCT-05'
 
Plan hash value: 2684249835
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   537K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |   383 |  3064 |   537K  (2)| 01:47:25 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL3"='20-OCT-05')
       filter("COL3"='20-OCT-05')
 
 
20 rows selected.
 
Elapsed: 00:00:00.09
SYS@LABRAT1> @dplan
Enter value for sql_id: 6n5y91cxw4yzu
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6n5y91cxw4yzu, child number 0
-------------------------------------
select count(*) from kso.skew3 where col3 is null
 
Plan hash value: 2684249835
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   533K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |    12 |    96 |   533K  (1)| 01:46:44 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL3" IS NULL)
       filter("COL3" IS NULL)
 
 
20 rows selected.
 
Elapsed: 00:00:00.02
SYS@LABRAT1> -- I wonder if the date format is disabling the Storage Index
SYS@LABRAT1> 
SYS@LABRAT1> 
SYS@LABRAT1> select count(*) from kso.skew3 where col3 = '20-OCT-2005';
 
  COUNT(*)
----------
         4
 
1 row selected.
 
Elapsed: 00:00:00.08
SYS@LABRAT1> -- ha, that did it!
SYS@LABRAT1> 
SYS@LABRAT1> @fsx
Enter value for sql_text: %skew3%
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 2s58n6d3mzkmn      0 2684249835          1           .07      0 Yes             100.00 select count(*) from kso.skew3 where col3 = '20-OCT-2005'
    1 2sycry6jd7cus      0 2684249835          1         15.06      0 Yes              99.99 select count(*) from kso.skew3 where col3 < '19-OCT-05'
    1 6n5y91cxw4yzu      0 2684249835          1           .08      0 Yes             100.00 select count(*) from kso.skew3 where col3 is null
    1 asfmw4ccsv2u9      0 2684249835          1         28.45      0 Yes              67.15 select min(col3),max(col3) from kso.skew3
    1 fuhmg9hqdbd84      0 2684249835          1         15.12      0 Yes              99.99 select count(*) from kso.skew3 where col3 = '20-OCT-05'
    1 gkbzsmx4w57ym      0 2684249835          1         15.09      0 Yes              99.99 select count(*) from kso.skew3 where col3 > '01-jan-10'
 
6 rows selected.
 
Elapsed: 00:00:00.06
SYS@LABRAT1> @dplan
Enter value for sql_id: 2s58n6d3mzkmn
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2s58n6d3mzkmn, child number 0
-------------------------------------
select count(*) from kso.skew3 where col3 = '20-OCT-2005'
 
Plan hash value: 2684249835
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   533K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |   383 |  3064 |   533K  (1)| 01:46:48 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL3"=TO_DATE(' 2005-10-20 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
       filter("COL3"=TO_DATE(' 2005-10-20 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
 
 
22 rows selected.
 
Elapsed: 00:00:00.03

So like regular B-Tree indexes, implicit conversion, functions applied to columns, etc … can disable Storage Indexes. Not too surprising. It’s also interesting is that the Storage Indexes on dates are a little persnickety. Looks like literals work fine (at least in SQL*Plus) as long as the full 4 digit year is specified. You can see the format that Oracle converts it to is ‘syyyy-mm-dd hh24:mi:ss’. I was unable to get Storage Indexes to work with date columns using SQL*Plus varchar2 variables though. Any one got any ideas?

Parallel Full Table Scans Do Not Always Perform Direct Reads

Submitted by Chris Antognini on Sun, 2010-09-12 11:52

Even though in general parallel full table scans performs direct reads, some exceptions exist. The aim of this post is to show such an exception.
For test purposes I build in my own schema a copy of the SH.SALES table (the one distributed by Oracle with the demo schemas…). On that table I build an [...]

11.2.0.2 Patch Set For Oracle Database Server

Submitted by Greg Rahn on Fri, 2010-09-10 17:51

Update: Mon Sep 13 16:02:36 PDT 2010 The 11.2.0.2 patch is now available for download (for real). If you downloaded it on Friday, there is no need to re-download it. It was mistakenly made public before it could be validated (test MD5 sums, etc.), but has since been validated. Enjoy! Just a quick post that the 11.2.0.2 patch set for Oracle Database Server has been released for x86 and x86-64 platforms. The patchset number is 10098816 and is available for download from My Oracle Support. Be sure to give note 1189783.1 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 a read as several things have changed in the patching process. {lang: 'ar'}

I’m in the Wrong Business

Submitted by Kerry Osborne on Wed, 2010-09-08 14:47

Well I stayed at home today to do some writing on a book project while one of my sons and one of my daughters and my wife went to the Fort Worth Natural Science Museum. So I was feeling a little overworked and like I was missing out because I’ve been slaving away writing “scholarly technical material”. Then I got a text from my son. Just a picture that’s all.

I’m sure these two books will probably sell a few orders of magnitude more copies than any book that I contribute to. Kind of puts it all in perspective. I think next weekend I’ll go to the zoo with them.

Parallel Processing With Standard Edition

Submitted by Chris Antognini on Wed, 2010-09-01 09:55

As clearly stated in the Licensing Information guide, all features related to parallel processing (parallel backup and recovery, parallel query/DML, parallel statistics gathering, parallel index build/scans, parallel Data Pump export/import, in-memory parallel execution, parallel statement queuing and parallel spatial index builds) are only available with the Enterprise Edition. However, as of Oracle Database 11g Release [...]

APPEND_VALUES Hint…

Submitted by oraclebase on Fri, 2010-08-27 13:24

The APPEND_VALUES hint is new to 11gR2 and allows you to use direct-path inserts from “INSERT INTO … VALUES” type statements. Pretty neat if you are doing inserts in a FORALL statement and need the extra punch.

Cheers

Tim…

Fedora 13 and Oracle…

Submitted by oraclebase on Fri, 2010-08-20 19:20

Until a couple of days ago I hadn’t even realized that Fedora 13 was out. I guess that shows how interested I am in Fedora these days. :)

Anyway, I had a play around with it.

Cheers

Tim…

Oracle RAC on VirtualBox…

Submitted by oraclebase on Thu, 2010-08-12 02:08

With the recent news that the latest version of VirtualBox now supports shared disks, I thought I better give it a go and see if I could do a RAC installation on it. The good news is it worked as expected. You can see a quick run through here:

This is pretty good news as that was the last feature that tied me to VMware Server. I’ve now moved pretty much everything I do at home on to VirtualBox and it’s working fine.

It’s worth taking a little time looking at the VBoxManage command line. Some of the operations, like creating the shared disks, have to be done from the command line at the moment. It’s also handy for running VMs in headless mode if you don’t want the GUI screen visible all the time.

Cheers

Tim…

Exadata Storage Server and the Query Optimizer – Part 4

Submitted by Chris Antognini on Mon, 2010-08-09 16:37

When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the next couple of months.
In [...]