Oraclue
Oracle internals, debugging and undocumented features
Wide Table Select ( Row Shipping )
July 13, 2009
Posted by on 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
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.
Can you, please, explain the feature in some more details? What is the effect and what type of applications can benefit from enabling it?
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 ).
Pingback: Blogroll Report 10/07/2009 – 17/07/2009 « Coskan’s Approach to Oracle
Pingback: Log Buffer #154: a Carnival of the Vanities for DBAs | Pythian Group Blog
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?
Hi Frits,
Please see last post Direct path reads and serial table scans in 11g.
Thanks for reading my blog.
Miladin
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.
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
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