Search

Top 60 Oracle Blogs

Recent comments

Oracle

OOW10 Bloggers Meetup Agenda — T-shirts are Back and More…

Almost time for the Annual Bloggers Meetup @ OOWCounting down. The details are finally organized — this year, we have not one, but TWO great prizes at the Oracle OpenWorld Bloggers Meetup.

1) T-shirt art contest on stylish Pythian designer t-shirts — one lucky blogger will receive an HP X310 Data Vault, generously sponsored again this year by HP.

2) For the best, most creative blog post about the meetup itself, Pythian is giving away an Apple TV. But, there are a few small rules:

  1. the blog post must use as many names of people in attendance as possible.
  2. the blog post must be readable. It needs to make sense to someone who wasn’t there. It must be a story and not a list.
  3. the blog post must contain one other small tidbit of information about each individual that you’ll uncover during your “networking” as you weave your fascinating stories of the evening (like their blog name, a contact detail, favorite color, title, hobby, cat’s name, certification, what they were drinking, how much they had to drink…).
  4. the entry must be posted by midnight on Sunday the 26th of September. Please reference this post in your blog and it should be automatically picked up by the blog engine and posted in the comment as a track-back (if it doesn’t appear in the comments for some reason — please do post the link in your comment!

Can you tell we’re trying to encourage a little more mingling? :)

And last but not least — all this couldn’t be happening without Vanessa Simmons who’s been orchestrating all of the fun this year. Thanks Vanessa!

HP X300 Data Vault appletv

Oracle DB 11.2.0.2 patch set is here … Again…

The incredible disappearing patch set has reappeared on MOS. Download it quick before it gets pulled again. :)

Cheers

Tim…

PS. Remember, read the patch note before you download it. You don’t need all 4.8G. Also, check out the new recommended patching instructions.

The disappearing 11.2.0.2 patch set…

Last week there was a little episode where the 11.2.0.2 patchset was released, as noted here:

Many of us started downloading it. Some (like me) got a full download, while others got cut off part way through when the patch was pulled from MOS. Someone on twitter mentioned the patch is scheduled for an October release (can’t find the tweet now), so I guess it was put live by accident. Who knows what changes, if any, will happen between now and then, so I would suggest that anyone who managed to get hold of the patch redownload it when it is eventually released, just in case they miss something important. Having said that, the OUI now checks for updates, so it is probably safe.

Even though the patch set is not on general release at the moment, there is plenty of information available to get yourself ready for it.

Cheers

Tim…

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).

 
...
 
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.

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.

 
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?

OOW 2010 – my schedule



I was able to finalize my schedule a week before the OOW 2010 and most of the sessions that I’ll be attending are related to Performance & Exadata.

So that would be:

Hotsos Symposium 2010 Presentations

I got an email a few days ago asking if I would provide the scripts from my Hotsos Symposium 2010 presentations. I didn’t even realize the presentations had been posted anywhere but I managed to find them on my company’s website. So anyway, I decided to go ahead and post a link to the PDF’s and the scripts here as well. So click on the pretty pictures to get the PDFs and the cleverly titled text links to get the accompanying zip files with the scripts.

Controlling Execution Plans Zip File

My Favorite Scripts 2010 Zip File

Oh and Bob Sneed as “Disco Duck” (Thanks Marco)

Oracle Database 11g Release 2 Patchset 1 (11.2.0.2) Is Now Available, But This Is Not Just An Announcement Blog Entry.

BLOG UPDATE: I should have peeked at my blog aggregator before posting this. I just found that my friend Greg Rahn posted about the same content on his blog earlier today. Hmmm.…plagerism! Oracle Database 11g Release 2 Patchset 1 (11.2.0.2 Part Number E15732-03) is available as of today for x86 and x86_64 Linux as per [...]

11.2.0.2 Patch Set For Oracle Database Server

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'}

The patching nightmares are over (11.2.0.2.0)…

One of the things that continually annoys me is that to get the latest version of the database you have to install the base release and then instantly patch it to the latest patch set. Not any more.

“Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).”

You don’t understand how happy this makes me. In addition, the installer also downloads and applies madatory patches, so even when you’re mid-way through the lifecycle of a patchset, your new installations are still up to date. :)

There is a bunch of new functionality already listed in the new features manual:

Happy downloading and upgrading.

Notes.

  • Read the patch notes before you start downloading. You probably don’t need all the zip files (4.8G). :)
  • Out-of-place patching (new ORACLE_HOME) is the recommended method now, so there is no real difference between patch sets and upgrades. Grid infrastructure *must* be patches out-of-place.
  • I guess OFA directories should now include the first 4 digits of the version (11.2.0 -> 11.2.0.2) as those directories will only ever contain that patch set.

Cheers

Tim…

Is this really a patch set?

The title of the post is a question taken from a MOS Doc ID 1189783.1 named “Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2″. Yes, the first patch set for 11gR2 is out for Linux x86 and x86-64 (patch number is 10098816). It’s 5G in size, BTW. There’s no patch set release notes, [...]