Home > Internals > Wide Table Select ( Row Shipping )

Wide Table Select ( Row Shipping )

In 10g Oracle  introduces a special performance feature called “row shipping” or “wide table select”.Row shipping is feature which allows row data from the datablock to be shipped directly to the client.

Aperently , this feature had some issues in earlier version of 10g  and fix was to disable the “row shipping” feature by default.Oracle introduced “fix” in version 10.2 ( that’s the lowest version I have to test anyway ) .

The fix introduces the hidden parameter “_enable_row_shipping” which now defaults to FALSE ( I am still talking about Oracle version 10g ) . The parameter can be set to TRUE nstance wide to re-enable the row shipping feature.

So default for 10.2.0.3:

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi
PL/SQL Release 10.2.0.3.0 – Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production

SQL> col name head NAME for a25
SQL> col value head VALUE for a20
SQL> col description head DESCRIPTION for a40
SQL> set linesize 167
SQL> Select  nam.ksppinm                           name,
2          val.ksppstvl                          value,
3          val.ksppstdf                          is_default,
4          decode(bitand(nam.ksppiflg/256,1),
5                 1,’True’,
6                   ‘False’
7          )                                     is_session_modifiable,
8          decode(bitand(nam.ksppiflg/65536,3),
9                 1,’Immediate’,
10                 2,’Deferred’ ,
11                 3,’Immediate’,
12                   ‘False’
13          )                                     is_system_modifiable,
14          decode(bitand(val.ksppstvf,7),
15                 1,’Modified’,
16                 4,’System Modified’,
17                   ‘False’
18          )                                     is_modified,
19          decode(bitand(val.ksppstvf,2),
20                 2,’True’,
21                   ‘False’
22          )                                     is_adjusted,
23          nam.ksppdesc                          description
24  from
25          x$ksppi        nam,
26          x$ksppsv       val
27  where
28          nam.indx = val.indx
and    nam.ksppinm like lower(‘%&1%’);
29  Enter value for 1: shipp
old  29: and    nam.ksppinm like lower(‘%&1%’)
new  29: and    nam.ksppinm like lower(‘%shipp%’)

NAME                      VALUE                IS_DEFAUL IS_SE IS_SYSTEM IS_MODIFIED     IS_AD DESCRIPTION
————————- ——————– ——— —– ——— ————— —– —————————————-
_enable_row_shipping      FALSE                TRUE      True  Immediate False           False use the row shipping optimization for wide table selects

Some of the workarround were special event and patch:

1. One off patches for this bug use the event 10040 to enable
/ disable row shipping instead of the parameter.
The default with this fix is NOT to use row shipping.
If 10040 is set then row shipping will be enabled again
but this should only be set instance wide and not in a session.
This fix introduces event 10040 which disables the
“row shipping” feature when set to any level > 0.

e.g.

disable wide table select (a.k.a row shipping)

alter system set events ’10040 trace name context forever, level 1′;

2. Bug 4690401 had patches available for 10.1 whereby setting
event 10040 would DISABLE “row shipping”. The event usage
in that fix is OPPOSITE to the event usage in this fix.
Both fixes provide a means to disable the feature.

and this is what Oracle document says:

From 10.2.0.2 onwards you can use the hidden parameter _enable_row_shipping=FALSE. And the event will not be valid from 10.2.0.2 onwards.

For Oracle 11g:

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
PL/SQL Release 11.1.0.6.0 – Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

SQL> Select  nam.ksppinm                           name,
2          val.ksppstvl                          value,
3          val.ksppstdf                          is_default,
4          decode(bitand(nam.ksppiflg/256,1),
5                 1,’True’,
6                   ‘False’
7          )                                     is_session_modifiable,
8          decode(bitand(nam.ksppiflg/65536,3),
9                 1,’Immediate’,
10                 2,’Deferred’ ,
11                 3,’Immediate’,
12                   ‘False’
13          )                                     is_system_modifiable,
14          decode(bitand(val.ksppstvf,7),
15                 1,’Modified’,
16                 4,’System Modified’,
17                   ‘False’
18          )                                     is_modified,
19          decode(bitand(val.ksppstvf,2),
20                 2,’True’,
21                   ‘False’
22          )                                     is_adjusted,
23          nam.ksppdesc                          description
24  from
25          x$ksppi        nam,
26          x$ksppsv       val
27  where
28          nam.indx = val.indx
29  and    nam.ksppinm like lower(‘%&1%’);
Enter value for 1: shipp
old  29: and    nam.ksppinm like lower(‘%&1%’)
new  29: and    nam.ksppinm like lower(‘%enable_row_shipp%’)

NAME                      VALUE                IS_DEFAUL IS_SE IS_SYSTEM IS_MODIFIED     IS_AD DESCRIPTION
————————- ——————– ——— —– ——— ————— —– —————————————-
_enable_row_shipping      TRUE                 TRUE      True  Immediate False           False use the row shipping optimization for wide table selects

It default to TRUE.

Row shipping can be used for SELECT statements which select over 80% of a tables column width and
have no expressions, subqueries etc.. which have to be applied to
column data.

eg: Basic selects which select most columns directly from a table
with no column value filtering.

This treshold of 80% ( default) can be changed using _row_shipping_threshold  parameter.

SQL> @p
Dfl Parameter                                   DESCP                                                                                           Value
— ——————————— —————————————————————- ———-
_row_shipping_threshold           row shipping column selection threshold                          80

e.g.

SQL> alter session set “_row_shipping_threshold”=70;

Session altered.

SQL> @nd
SID NAME                                                           VALUE                ISD
———- —————————————- ————————- —
239 _row_shipping_threshold                  70                        NO

There is one more hidden parameter related to row shipping :

_row_shipping_explain which enables row shipping explain plan support .Default is FALSE.

It can be changed at session level:

SQL> alter session set “_row_shipping_explain”=true;

Session altered.

SQL> @nd
SID NAME                                     VALUE                     ISD
———- —————————————- ————————- —
239 _row_shipping_threshold                  70                        NO
239 _row_shipping_explain                    true                       NO

About these ads
Categories: Internals
  1. Timur Akhmadeev
    July 14, 2009 at 6:39 am

    Miladin, thanks for the info.
    Do you know some more details about the feature? I.e. what exactly “directly to the client” means? I think this might be skipping a step of copiyng a buffer from buffer cache to PGA, but I doubt if this is true and may result in significant performance gains.

  2. July 14, 2009 at 11:16 am

    Can you, please, explain the feature in some more details? What is the effect and what type of applications can benefit from enabling it?

  3. July 14, 2009 at 4:38 pm

    Hi Timur/Mladen,

    I discovered this feature few days ago by checking some Oracle bugs.Oracle does not provide any details at all.
    Only one reference:Transparent Data Encryption with OCI

    Row shipping cannot be used, because the key to make the row usable is not available at the receipt-point.

    I think this might be skipping a step of copiyng a buffer from buffer cache to PGA, but I doubt if this is true and may result in significant performance gains.

    I do not know details yet ( still testing ) but it sounds like that.

    As I said in Oracle version 11g this feature is enabled by default.You have to specifically set _row_shipping_enable=false to turn it off .What is the effect and what type of applications can benefit from enabling it?

    Faster access by skiping some steps used in regular query processing and I might be wrong but as name said this feature benefits basic select statement with most columns without filtering ( saw bug where Oracle pointing issues when selecting from table with large column list ).

  4. July 17, 2009 at 5:33 pm

    Doug burns observed full table scans which resulted in ‘direct path reads’ instead of the ‘db file scattered reads’ you would expect in Oracle 11. Could this be related?

  5. July 17, 2009 at 6:03 pm

    Hi Frits,

    Please see last post Direct path reads and serial table scans in 11g.

    Thanks for reading my blog.

    Miladin

  6. Tim Hopkins
    October 5, 2009 at 12:53 pm

    Thanks Miladin, this helped to give us a bit more insight into a problem we were having with virtual columns on 11.1.0.6.

    We have a scenario where virtual columns were being returned as NULL if we used SELECT * and the table contained above a certain number of columns.
    After stumbling across your post, I now realise it’s because virtual columns don’t seem to be compatible with this row shipping ‘enhancement’.

    I’ll try posting some sample test case code in another comment. Fingers crossed WordPress doesn’t screw up the formatting.

  7. Tim Hopkins
    October 5, 2009 at 1:27 pm

    I failed trying to post the sample code here so I’ve posted it to my nascent blog instead.

    Please see the url below for a demonstration of the problem with virtual columns and row shipping in 11.1.0.6
    http://timothyhopkins.net/?p=18

    Cheers,
    Tim

  8. October 13, 2009 at 1:52 pm

    Hi Tim,

    Thanks for reading my post.
    I saw your post.Very good example.As you know Oracle is not documenting all features and sometime is hard to find out what is really going on.

    Thanks,

    Miladin

  1. July 17, 2009 at 3:09 pm
  2. July 17, 2009 at 5:02 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 43 other followers

%d bloggers like this: