HelloDBA [中文]
Search Internet Search HelloDBA
Home
Tech Documents
Products & Download
Subscribe
  • To Google
  • To Yahoo
  • To zhuxia
  • To xianguo
  • To QQ email
  • To YouDao
About
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

[En] Oracle Row Shipping

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-12-25 01:21:17

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

    To reduce workload in server, some features were introduced to move some jobs to client side. Row shipping is one of such features.
 

    There are steps to process SQL, parsing -> optimzating -> Row Source generating -> result producting. In traditional mode, one job of Row Source gernerator is that choose the columns in SELECT clause from the rows return to client, which named Projection. We could find such information from output DBMS_XPLAN (10g later):
 

SQL代码
  1. HELLODBA.COM>create table WIDE_TBL as select * from sys.gv_$session;   
  2.   
  3. Table created.   
  4.   
  5. HELLODBA.COM>explain plan for select sid,audsid,serial# from WIDE_TBL;   
  6.   
  7. Explained.   
  8.   
  9. HELLODBA.COM>select * from table(dbms_xplan.display(null,null,'ADVANCED'));   
  10.   
  11. PLAN_TABLE_OUTPUT   
  12. ---------------------------------------------------------------------------------------------------------   
  13. Plan hash value: 1188828058   
  14.   
  15. ------------------------------------------------------------------------------   
  16. | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |   
  17. ------------------------------------------------------------------------------   
  18. |   0 | SELECT STATEMENT  |          |    25 |   975 |     3   (0)| 00:00:01 |   
  19. |   1 |  TABLE ACCESS FULL| WIDE_TBL |    25 |   975 |     3   (0)| 00:00:01 |   
  20. ------------------------------------------------------------------------------   
  21.   
  22. ...   
  23.   
  24. Column Projection Information (identified by operation id):   
  25. -----------------------------------------------------------   
  26.   
  27.    1 - "SID"[NUMBER,22], "SERIAL#"[NUMBER,22], "AUDSID"[NUMBER,22]   
  28.   
  29. Note   
  30. -----   
  31.    - dynamic sampling used for this statement   
  32.   
  33. 36 rows selected.   

    Projection will consume CPU, especailly for those "wide tables", who have a lots of columns. When Row Shipping active, it will transfer all of columns to client directly without projection, which will be proceed in client side instead. The parameter _enable_row_shipping decide if Row Shipping be enabled, while _row_shipping_threshold is the project columns threshold that the feature be active or not. Besides, another parameter _row_shipping_explain will control Row Shipping information be stored in v$sql_plan or not. However, this parameter will not affect the feature actually be active or not. Let look into its changes in SQL execution.
 

SQL代码
  1. HELLODBA.COM>analyze table WIDE_TBL compute statistics for table for all columns;   
  2.   
  3. Table analyzed.   
  4.   
  5. HELLODBA.COM>select num_rows*avg_row_len from dba_tables where table_name='WIDE_TBL';   
  6.   
  7. NUM_ROWS*AVG_ROW_LEN   
  8. --------------------   
  9.                 8492   
  10.   
  11. HELLODBA.COM>set autot trace stat   
  12. HELLODBA.COM>alter session set "_enable_row_shipping" = FALSE;   
  13.   
  14. Session altered.   
  15.   
  16. HELLODBA.COM>alter session set events '10079 trace name context forever, level 2';   
  17.   
  18. Session altered.   
  19.   
  20. HELLODBA.COM>select sid,audsid from WIDE_TBL;   
  21.   
  22. 22 rows selected.   
  23.   
  24. Statistics  
  25. ----------------------------------------------------------   
  26.           1  recursive calls   
  27.           0  db block gets   
  28.           6  consistent gets   
  29.           0  physical reads   
  30.           0  redo size  
  31.         846  bytes sent via SQL*Net to client   
  32.         396  bytes received via SQL*Net from client   
  33.           3  SQL*Net roundtrips to/from client   
  34.           0  sorts (memory)   
  35.           0  sorts (disk)   
  36.          22  rows processed   
  37.   
  38. HELLODBA.COM>alter session set events '10079 trace name context off';   
  39.   
  40. Session altered.   
  41.   
  42. HELLODBA.COM>conn demo/demo@erdb   
  43. Connected.   
  44. HELLODBA.COM>set autot trace   
  45. HELLODBA.COM>alter session set "_enable_row_shipping" = TRUE;   
  46.   
  47. Session altered.   
  48.   
  49. HELLODBA.COM>alter session set "_row_shipping_explain" = TRUE;   
  50.   
  51. Session altered.   
  52.   
  53. HELLODBA.COM>alter session set "_row_shipping_threshold" = 2;   
  54.   
  55. Session altered.   
  56.   
  57. HELLODBA.COM>alter session set events '10079 trace name context forever, level 2';   
  58.   
  59. Session altered.   
  60.   
  61. HELLODBA.COM>select sid,audsid from WIDE_TBL;   
  62.   
  63. 22 rows selected.   
  64.   
  65. Execution Plan   
  66. ----------------------------------------------------------   
  67. Plan hash value: 1188828058   
  68.   
  69. ------------------------------------------------------------------------------   
  70. | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |   
  71. ------------------------------------------------------------------------------   
  72. |   0 | SELECT STATEMENT  |          |    22 |   132 |     3   (0)| 00:00:01 |   
  73. |   1 |  TABLE ACCESS FULL| WIDE_TBL |    22 |   132 |     3   (0)| 00:00:01 |   
  74. ------------------------------------------------------------------------------   
  75.   
  76. Note   
  77. -----   
  78.    - row shipping is enabled for this statement   
  79.   
  80. Statistics  
  81. ----------------------------------------------------------   
  82.         358  recursive calls   
  83.           0  db block gets   
  84.          38  consistent gets   
  85.           0  physical reads   
  86.           0  redo size  
  87.        9223  bytes sent via SQL*Net to client   
  88.         396  bytes received via SQL*Net from client   
  89.           3  SQL*Net roundtrips to/from client   
  90.           6  sorts (memory)   
  91.           0  sorts (disk)   
  92.          22  rows processed   
  93.   
  94. HELLODBA.COM>alter session set events '10079 trace name context off';   
  95.   
  96. Session altered.   

    Compare the output, we can find the logical IO was not affected, because this featrue is to modify Row Source Generating operations. We can find the changes in the bytes transfered to client. Row Shipping transfered all of columns to client, with the columns meta infomation, it sent 9223 bytes to client. While in traditional mode, since it just transfered the projected columns, there was just 846 bytes be sent.
 

    We could also look into the content transfered via SQLNet by 10079 event, saw Row Shipping transfered all columns.
 

SQL代码
  1. ...   
  2. A798440 53038001 80015359 3016C506 FF2D2531  [...SYS.....01%-.]   
  3. A798450 4E4908FF 49544341 44094556 43494445  [..INACTIVE.DEDIC]   
  4. A798460 44455441 53038001 68075359 676E6175  [ATED...SYS.huang]   
  5. A798470 32096465 3A363932 34343737 434F4F0E  [ed.2296:7744.OOC]   
  6. A798480 5C4D444C 4E415548 07444547 4E415548  [LDM\HUANGED.HUAN]   
  7. A798490 0C444547 71736C70 7665646C 6578652E  [GED.plsqldev.exe]   
  8. A7984A0 45535504 01000152 04FFFF80 5432B92C  [.USER.......,.2T]   
  9. A7984B0 0423C506 0D146339 327A6E33 35763036  [..#.9c..3nz260v5]   
  10. A7984C0 6E717764 FF800176 10FFFFFF 532F4C50  [dwqnv.......PL/S]   
  11. A7984D0 44204C51 6C657665 7265706F 5B0CC506  [QL Developer...[]   
  12. A7984E0 0C40430E 6E69614D 73657320 6E6F6973  [.C@.Main session]   
  13. A7984F0 4A12C506 FF5B5020 480CC304 02C2031E  [...J P[....H....]   
  14. ...   

    Besides, in traditional mode, oracle will compress the package first when transfer data via SQLNet, while row shipping will not.
 

SQL代码
  1. HELLODBA.COM>create table WIDE_TBL2 as select a.* from WIDE_TBL a, WIDE_TBL b, WIDE_TBL c;   
  2.   
  3. Table created.   
  4.   
  5. HELLODBA.COM>analyze table WIDE_TBL2 compute statistics for table for all columns;   
  6.   
  7. Table analyzed.   
  8.   
  9. HELLODBA.COM>select num_rows*avg_row_len from dba_tables where table_name='WIDE_TBL2';   
  10.   
  11. NUM_ROWS*AVG_ROW_LEN   
  12. --------------------   
  13.              4110128   
  14.   
  15. HELLODBA.COM>conn demo/demo@erdb   
  16. Connected.   
  17. HELLODBA.COM>set autot trace stat   
  18. HELLODBA.COM>alter session set "_enable_row_shipping" = TRUE;   
  19.   
  20. Session altered.   
  21.   
  22. HELLODBA.COM>select * from WIDE_TBL2;   
  23.   
  24. 10648 rows selected.   
  25.   
  26. Statistics  
  27. ----------------------------------------------------------   
  28.           0  recursive calls   
  29.           0  db block gets   
  30.        1268  consistent gets   
  31.           0  physical reads   
  32.           0  redo size  
  33.     4260321  bytes sent via SQL*Net to client   
  34.        8184  bytes received via SQL*Net from client   
  35.         711  SQL*Net roundtrips to/from client   
  36.           0  sorts (memory)   
  37.           0  sorts (disk)   
  38.       10648  rows processed   
  39.   
  40. HELLODBA.COM>alter session set "_enable_row_shipping" = FALSE;   
  41.   
  42. Session altered.   
  43.   
  44. HELLODBA.COM>select * from WIDE_TBL2;   
  45.   
  46. Statistics  
  47. ----------------------------------------------------------   
  48.           1  recursive calls   
  49.           0  db block gets   
  50.        1268  consistent gets   
  51.           0  physical reads   
  52.           0  redo size  
  53.      258356  bytes sent via SQL*Net to client   
  54.        8184  bytes received via SQL*Net from client   
  55.         711  SQL*Net roundtrips to/from client   
  56.           0  sorts (memory)   
  57.           0  sorts (disk)   
  58.       10648  rows processed   

    You can see, although all of data be returned, data size transfered by row shipping is actual table size plus meta data size, while data size compressed in tranditional mode is much less.
 

    The outstanding benifit of Row Shipping is to reduce the Row Source Generating CPU workload, we may find such changes by compare the Row Source statistics data. However, if _rowsource_execution_statistics or SQL Trace (10046 event) be enabled, Row shipping will be turned of automaticly.
 

    --- Fuyuncat Mark ---
Previous： Next：
Return To this Category

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat