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