12 Replies Latest reply: Apr 8, 2013 6:50 PM by jgarry RSS

    Row Chaining Questions

    12663
      I am trying understand the mechanics of row chaining and how table design affects it.

      Oracle Database ver 11.2.0.3
      block_size = 32K for all tablespaces
      I have made a copy of a large table from a datawarehouse and I am trying to understand the reason for row chaining in this copy. I created this copy by using

      CREATE TABLE COPIED_TABLE AS SELECT * FROM BASE TABLE.

      PCT_FREE = 10
      I then ran the following command immediately after the table was created

      ANALYZE TABLE COPIED_TABLE LIST CHAINED ROWS;

      The CHAINED_ROWS table has approximately 20,000 entries for this table.

      So I picked one specific row and tried to look at its data to see why the row chain. I couldn't really figure out from the data what the issue was.
      So I used vsize to figure out an approximate length of that specific row. The row was 1029 Bytes as per vsize. I understand that vsize doesn't consider the column and row overhead.

      As per gather stats, the average row length is 850 Bytes. So one block could have (32768 - (0.1 * 32768))/850 = 34.7 rows. 10% of the space in the block would be saved for updates since PCT_FREE is 10.

      So my first question is -
      The row that chained, why did it chain? Did it chain because Oracle put can only fit 34 complete rows and for the 35th row it put 0.7 of the row in one block and then 0.3 of that row in another block?

      Also, this table I am working with has 279 columns. If a table has more than 255 columns there is intra-block chaining. (http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:358341515662).
      I do see opportunity to remove some columns in this case and move it to a different table. However, before I take that step I am looking to figure what difference will it make - will the logical IO be less or is difference not significant.
      So my second question is - Can someone point me to a blog or content on this forum that can give some direction on how I can go about figuring out the difference.

      My third question is - If I can help it, will it be better to keep all table to 255 columns or less?

      Thank you
        • 1. Re: Row Chaining Questions
          JustinCave
          In general, it would be rare for a well-designed table to have more than 255 columns. So, yes, in that sense, it's probably a good idea to reconsider the design if you find yourself wanting to add more columns. But that has nothing to do with row chaining and everything to do with proper database design.

          If you have more than 255 columns in the table (including hidden columns), every row will be chained (either inter- or intra-block). I haven't looked at it in a while but my recollection is that listing the chained rows only shows you cases where there are row pieces in multiple blocks. You ended up with row pieces in multiple blocks because there was enough space in a block for the first row piece but not enough space for the second row piece when you did your CTAS.

          What fraction of the rows have row pieces in multiple blocks? 20,000 rows in a 40,000 row table would be very different than 20,000 rows in a 2 million row table (or a 200 million row table). Are the columns in the second row piece commonly needed? Or would a large fraction of your queries be able to avoid reading the second row piece? The more often you need to read the second row piece and the more frequently the second row piece is in a second block, the more logical I/O the chained rows will create.

          Justin
          • 2. Re: Row Chaining Questions
            12663
            Hello Justin,
            Thank you for your advice. There are 25K rows which have chained in a table of 5 million rows. I have been given a requirement which calls for adding more columns to this table. So I just want to be sure that that would not be a problem. Looking at some of the postings on this forums and some other well know blogs, it looked to me that there could be an issue with performance and so I set out to see if I could quantify the problem and make a case to split some columns out into a separate table.
            Knowing the data, I know that some of the columns in the middle or close to the end of row structure are accessed more often compared to some of the columns at the beginning of the row.
            How do I find out whether these columns fall in the first other second row-piece?
            Thank you very much for your inputs.
            • 3. Re: Row Chaining Questions
              sb92075
                1* select 25000/5000000 from dual
              SQL> /
              
              25000/5000000
              -------------
                       .005
              while 25,000 might be a "big" number; is not much when considering the whole table.

              If you have to go looking for a performance problem, you have too much time on your hands.
              It appears that you suffer from Compulsive Tuning Disorder.
              • 4. Re: Row Chaining Questions
                JustinCave
                The first 255 columns (including hidden columns) will be in the first row piece. Everything else will be in the second row piece. If all your columns are basic data types (NUMBER, VARCHAR2, etc.) you can just get the first 255 columns.

                Be aware that the amount of row chaining in your copy of the table may be radically different than the amount of chaining in the actual table. The CTAS operation will tend to minimize the amount of row chaining that takes place unless you only ever insert into the real table.

                Justin
                • 5. Re: Row Chaining Questions
                  Jonathan Lewis
                  qqq wrote:
                  Thank you for your advice. There are 25K rows which have chained in a table of 5 million rows. I have been given a requirement which calls for adding more columns to this table. So I just want to be sure that that would not be a problem. Looking at some of the postings on this forums and some other well know blogs, it looked to me that there could be an issue with performance and so I set out to see if I could quantify the problem and make a case to split some columns out into a separate table.
                  It is interesting to see that there are only a few chained rows when you have 279 columns, this suggests that most of your rows have nulls in the columns past 255 - which means that if you're adding columns that are likely to be populated you could suffer an immediate performance problem because those columns may cause many rows to acquire an extra row piece, with at least 24 nulls before you get to a value in the new columns.

                  It's not nice to have more than 255 columns - but if you do then you need to think very carefully about column order to minimise (a) the number of rows which actually store a second row piece, and (b) the number of columns the most important and/or frequent queries have to traverse to get to the data they need.
                  Knowing the data, I know that some of the columns in the middle or close to the end of row structure are accessed more often compared to some of the columns at the beginning of the row.
                  How do I find out whether these columns fall in the first other second row-piece?
                  Count their position - be aware, though that you need to look at the "internal column" number rather than column position - if you have any LONG or LONG RAW columns they are automatically repositioned (internally) to the end of the row.

                  Regards
                  Jonathan Lewis
                  • 6. Re: Row Chaining Questions
                    rp0428
                    >
                    Count their position - be aware, though that you need to look at the "internal column" number rather than column position - if you have any LONG or LONG RAW columns they are automatically repositioned (internally) to the end of the row.
                    >
                    And doesn't that mean that any of those columns that are not null will cause row chaining when there are more than 255 columns?
                    • 7. Re: Row Chaining Questions
                      12663
                      Thank You Justin and Jonathan for your advice.
                      I do see some possibility of eliminating some columns and moving them to another table without affecting the functionality of the reporting application.

                      So I just created another table with lesser columns in the development environment. After running the analyze command, i could see that the new table has no row chaining.

                      Now to verify if this change really helps, I took a sample query and ran it against both the existing table with chained rows and the new table with lesser columns which has no chained rows at all. Here the run details from autotrace

                      Scenario 1: Query against Original table:
                      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                                  |                    |  2145M|  2123G|       |   612K  (1)| 03:03:49 |       |       |        |      |            |
                      |   1 |  PX COORDINATOR                                   |                    |       |       |       |            |          |       |       |        |      |            |
                      |   2 |   PX SEND QC (RANDOM)                             | :TQ10012           |  2145M|  2123G|       |   612K  (1)| 03:03:49 |       |       |  Q1,12 | P->S | QC (RAND)  |
                      |*  3 |    VIEW                                           |                    |  2145M|  2123G|       |   612K  (1)| 03:03:49 |       |       |  Q1,12 | PCWP |            |
                      |*  4 |     WINDOW NOSORT                                 |                    |  2145M|   421G|       |   612K  (1)| 03:03:49 |       |       |  Q1,12 | PCWP |            |
                      |   5 |      SORT GROUP BY                                |                    |  2145M|   421G|   448G|   612K  (1)| 03:03:49 |       |       |  Q1,12 | PCWP |            |
                      |   6 |       PX RECEIVE                                  |                    |  2145M|   421G|       |  1842  (10)| 00:00:34 |       |       |  Q1,12 | PCWP |            |
                      |   7 |        PX SEND HASH                               | :TQ10011           |  2145M|   421G|       |  1842  (10)| 00:00:34 |       |       |  Q1,11 | P->P | HASH       |
                      |*  8 |         HASH JOIN BUFFERED                        |                    |  2145M|   421G|       |  1842  (10)| 00:00:34 |       |       |  Q1,11 | PCWP |            |
                      |   9 |          PX RECEIVE                               |                    |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,11 | PCWP |            |
                      |  10 |           PX SEND HASH                            | :TQ10009           |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,09 | P->P | HASH       |
                      |  11 |            PX BLOCK ITERATOR                      |                    |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,09 | PCWC |            |
                      |  12 |             TABLE ACCESS FULL                     | W_ORG_D            |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,09 | PCWP |            |
                      |  13 |          PX RECEIVE                               |                    |   345K|    59M|       |  1593   (2)| 00:00:29 |       |       |  Q1,11 | PCWP |            |
                      |  14 |           PX SEND HASH                            | :TQ10010           |   345K|    59M|       |  1593   (2)| 00:00:29 |       |       |  Q1,10 | P->P | HASH       |
                      |* 15 |            HASH JOIN BUFFERED                     |                    |   345K|    59M|       |  1593   (2)| 00:00:29 |       |       |  Q1,10 | PCWP |            |
                      |  16 |             PX RECEIVE                            |                    |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,10 | PCWP |            |
                      |  17 |              PX SEND BROADCAST                    | :TQ10006           |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,06 | P->P | BROADCAST  |
                      |  18 |               PX BLOCK ITERATOR                   |                    |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,06 | PCWC |            |
                      |  19 |                TABLE ACCESS FULL                  | W_ZEBRA_REGION_D   |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
                      |* 20 |             HASH JOIN                             |                    |   345K|    52M|       |  1590   (2)| 00:00:29 |       |       |  Q1,10 | PCWP |            |
                      |  21 |              JOIN FILTER CREATE                   | :BF0000            |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,10 | PCWP |            |
                      |  22 |               PX RECEIVE                          |                    |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,10 | PCWP |            |
                      |  23 |                PX SEND HASH                       | :TQ10007           |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,07 | P->P | HASH       |
                      |  24 |                 PX BLOCK ITERATOR                 |                    |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWC |            |
                      |  25 |                  TABLE ACCESS FULL                | W_INT_ORG_D        |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWP |            |
                      |  26 |              PX RECEIVE                           |                    |   344K|    47M|       |  1587   (2)| 00:00:29 |       |       |  Q1,10 | PCWP |            |
                      |  27 |               PX SEND HASH                        | :TQ10008           |   344K|    47M|       |  1587   (2)| 00:00:29 |       |       |  Q1,08 | P->P | HASH       |
                      |  28 |                JOIN FILTER USE                    | :BF0000            |   344K|    47M|       |  1587   (2)| 00:00:29 |       |       |  Q1,08 | PCWP |            |
                      |* 29 |                 HASH JOIN BUFFERED                |                    |   344K|    47M|       |  1587   (2)| 00:00:29 |       |       |  Q1,08 | PCWP |            |
                      |  30 |                  JOIN FILTER CREATE               | :BF0001            | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,08 | PCWP |            |
                      |  31 |                   PX RECEIVE                      |                    | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,08 | PCWP |            |
                      |  32 |                    PX SEND HASH                   | :TQ10004           | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,04 | P->P | HASH       |
                      |  33 |                     PX BLOCK ITERATOR             |                    | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,04 | PCWC |            |
                      |* 34 |                      TABLE ACCESS FULL            | W_ORG_D            | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,04 | PCWP |            |
                      |  35 |                  PX RECEIVE                       |                    |   344K|    38M|       |  1494   (2)| 00:00:27 |       |       |  Q1,08 | PCWP |            |
                      |  36 |                   PX SEND HASH                    | :TQ10005           |   344K|    38M|       |  1494   (2)| 00:00:27 |       |       |  Q1,05 | P->P | HASH       |
                      |  37 |                    JOIN FILTER USE                | :BF0001            |   344K|    38M|       |  1494   (2)| 00:00:27 |       |       |  Q1,05 | PCWP |            |
                      |* 38 |                     HASH JOIN BUFFERED            |                    |   344K|    38M|       |  1494   (2)| 00:00:27 |       |       |  Q1,05 | PCWP |            |
                      |  39 |                      PX RECEIVE                   |                    | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                      |  40 |                       PX SEND HASH                | :TQ10001           | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
                      |  41 |                        PX BLOCK ITERATOR          |                    | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |
                      |* 42 |                         TABLE ACCESS FULL         | WC_PRODUCT_HIER_DH | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
                      |* 43 |                      HASH JOIN                    |                    |   894K|    57M|       |  1486   (2)| 00:00:27 |       |       |  Q1,05 | PCWP |            |
                      |  44 |                       JOIN FILTER CREATE          | :BF0002            |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                      |  45 |                        PX RECEIVE                 |                    |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                      |  46 |                         PX SEND HASH              | :TQ10002           |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
                      |  47 |                          PX BLOCK ITERATOR        |                    |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,02 | PCWC |            |
                      |* 48 |                           TABLE ACCESS FULL       | W_PRODUCT_D        |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                      |  49 |                       PX RECEIVE                  |                    |   894K|    50M|       |  1438   (2)| 00:00:26 |       |       |  Q1,05 | PCWP |            |
                      |  50 |                        PX SEND HASH               | :TQ10003           |   894K|    50M|       |  1438   (2)| 00:00:26 |       |       |  Q1,03 | P->P | HASH       |
                      |  51 |                         JOIN FILTER USE           | :BF0002            |   894K|    50M|       |  1438   (2)| 00:00:26 |       |       |  Q1,03 | PCWP |            |
                      |* 52 |                          HASH JOIN                |                    |   894K|    50M|       |  1438   (2)| 00:00:26 |       |       |  Q1,03 | PCWP |            |
                      |  53 |                           PX RECEIVE              |                    |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |
                      |  54 |                            PX SEND BROADCAST LOCAL| :TQ10000           |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,00 | P->P | BCST LOCAL |
                      |  55 |                             PX BLOCK ITERATOR     |                    |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,00 | PCWC |            |
                      |* 56 |                              TABLE ACCESS FULL    | W_DAY_D            |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,00 | PCWP |            |
                      |  57 |                           PX BLOCK ITERATOR       |                    |  4802K|   215M|       |  1300   (2)| 00:00:24 |     1 |     4 |  Q1,03 | PCWC |            |
                      |* 58 |                            TABLE ACCESS FULL      | W_ORDERITEM_F      |  4802K|   215M|       |  1300   (2)| 00:00:24 |     1 |    44 |  Q1,03 | PCWP |            |
                      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                       
                      Note
                      -----
                         - dynamic sampling used for this statement (level=5)
                       
                       
                       
                      Run Time: 00:00:56.77
                       
                       
                      Statistics
                      ----------------------------------------------------------
                               94  recursive calls
                              358  db block gets
                          3785496  consistent gets
                           104580  physical reads
                                0  redo size
                           568127  bytes sent via SQL*Net to client
                            11757  bytes received via SQL*Net from client
                              541  SQL*Net roundtrips to/from client
                               18  sorts (memory)
                                0  sorts (disk)
                             8090  rows processed
                      Scenarion 1: Query against new table with lesser columns:

                      ********************************************************
                       
                      Elapsed: 00:00:39.52       
                             
                      Statistics
                      ----------------------------------------------------------
                             7158  recursive calls
                             2064  db block gets
                           692228  consistent gets
                            64466  physical reads
                                0  redo size
                           655170  bytes sent via SQL*Net to client
                            11761  bytes received via SQL*Net from client
                              541  SQL*Net roundtrips to/from client
                              434  sorts (memory)
                                0  sorts (disk)
                             8090  rows processed
                      My question is, if you see statistics, the consistent gets are a lot lower on the new table and there is also some improvement in run time. Do these statistics i.e. lower consistent gets and lower run time prove sufficiently that this is a better way to go or in opinion of the guru's these are too small differences and I am suffering with some kind of performance tuning syndrome :-).

                      Also, I did one more test, I set star_transformation_enabled to true at session level and I ran the same query against original and new table. I did this because there is a plan to turn on this optimizer feature in the near future.

                      Scenario 2: Query against Original Table:
                      Elapsed: 00:00:40.39
                       
                      Statistics
                      ----------------------------------------------------------
                             1283  recursive calls
                             8102  db block gets
                          1689756  consistent gets
                            54237  physical reads
                          2246000  redo size
                           655436  bytes sent via SQL*Net to client
                            11757  bytes received via SQL*Net from client
                              541  SQL*Net roundtrips to/from client
                               78  sorts (memory)
                                0  sorts (disk)
                             8090  rows processed
                       
                      ################################
                      Scenario 2: Query against new table with lesser columns:
                       
                      Elapsed: 00:01:08.59 
                       
                      Statistics
                      ----------------------------------------------------------
                             2996  recursive calls
                             8257  db block gets
                          1375566  consistent gets
                            39723  physical reads
                          2283024  redo size
                           655506  bytes sent via SQL*Net to client
                            11761  bytes received via SQL*Net from client
                              541  SQL*Net roundtrips to/from client
                              163  sorts (memory)
                                0  sorts (disk)
                             8090  rows processed
                      In Scenario 2 as well, the consistent gets were lower for the new table but the run time was longer for the new table. As I understand it, the lower we can get the consistent gets, we are doing less work to get the same data. Hence the query is more efficient. But why then does the run time not reflect it in the case of star transformation? Can you please advise?

                      Edited by: qqq on Apr 7, 2013 2:33 PM
                      • 8. Re: Row Chaining Questions
                        rp0428
                        >
                        So I just created another table with lesser columns in the development environment. After running the analyze command, i could see that the new table has no row chaining.
                        >
                        Please see the FAQ for how to post a tuning request and the information that you need to provide.

                        Part of that information is:

                        1. DDL for the table and indexes
                        2. The query being used
                        3. row counts for the table and for the predicates used in the query
                        4. info about stats. You did update the table and index stats didn't you?
                        5. The 'actual' execution plans.

                        An explain plan just shows what Oracle 'thinks' it is going to do. The actual plans show what Oracle actually 'did' do. Just because Oracle expected to save doesn't mean the savings were actually achieved.

                        When you post the plans use \
                         on the line before and on the line after to preserve formatting.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                        • 9. Re: Row Chaining Questions
                          12663
                          Seems like there is a limit to how much I can add in one post. I am adding Explain plan for

                          Scenarion 1: Query against new table with lesser columns:
                          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          | Id  | Operation                                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                                  |                    |  2145M|  2123G|       |   612K  (1)| 03:03:47 |       |       |        |      |            |
                          |   1 |  PX COORDINATOR                                   |                    |       |       |       |            |          |       |       |        |      |            |
                          |   2 |   PX SEND QC (RANDOM)                             | :TQ10012           |  2145M|  2123G|       |   612K  (1)| 03:03:47 |       |       |  Q1,12 | P->S | QC (RAND)  |
                          |*  3 |    VIEW                                           |                    |  2145M|  2123G|       |   612K  (1)| 03:03:47 |       |       |  Q1,12 | PCWP |            |
                          |*  4 |     WINDOW NOSORT                                 |                    |  2145M|   421G|       |   612K  (1)| 03:03:47 |       |       |  Q1,12 | PCWP |            |
                          |   5 |      SORT GROUP BY                                |                    |  2145M|   421G|   448G|   612K  (1)| 03:03:47 |       |       |  Q1,12 | PCWP |            |
                          |   6 |       PX RECEIVE                                  |                    |  2145M|   421G|       |  1740  (11)| 00:00:32 |       |       |  Q1,12 | PCWP |            |
                          |   7 |        PX SEND HASH                               | :TQ10011           |  2145M|   421G|       |  1740  (11)| 00:00:32 |       |       |  Q1,11 | P->P | HASH       |
                          |*  8 |         HASH JOIN BUFFERED                        |                    |  2145M|   421G|       |  1740  (11)| 00:00:32 |       |       |  Q1,11 | PCWP |            |
                          |   9 |          PX RECEIVE                               |                    |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,11 | PCWP |            |
                          |  10 |           PX SEND HASH                            | :TQ10009           |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,09 | P->P | HASH       |
                          |  11 |            PX BLOCK ITERATOR                      |                    |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,09 | PCWC |            |
                          |  12 |             TABLE ACCESS FULL                     | W_ORG_D            |   268K|  7864K|       |    93   (2)| 00:00:02 |       |       |  Q1,09 | PCWP |            |
                          |  13 |          PX RECEIVE                               |                    |   345K|    59M|       |  1491   (2)| 00:00:27 |       |       |  Q1,11 | PCWP |            |
                          |  14 |           PX SEND HASH                            | :TQ10010           |   345K|    59M|       |  1491   (2)| 00:00:27 |       |       |  Q1,10 | P->P | HASH       |
                          |* 15 |            HASH JOIN BUFFERED                     |                    |   345K|    59M|       |  1491   (2)| 00:00:27 |       |       |  Q1,10 | PCWP |            |
                          |  16 |             PX RECEIVE                            |                    |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,10 | PCWP |            |
                          |  17 |              PX SEND BROADCAST                    | :TQ10006           |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,06 | P->P | BROADCAST  |
                          |  18 |               PX BLOCK ITERATOR                   |                    |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,06 | PCWC |            |
                          |  19 |                TABLE ACCESS FULL                  | W_ZEBRA_REGION_D   |  1321 | 30383 |       |     2   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
                          |* 20 |             HASH JOIN                             |                    |   345K|    52M|       |  1488   (2)| 00:00:27 |       |       |  Q1,10 | PCWP |            |
                          |  21 |              JOIN FILTER CREATE                   | :BF0000            |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,10 | PCWP |            |
                          |  22 |               PX RECEIVE                          |                    |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,10 | PCWP |            |
                          |  23 |                PX SEND HASH                       | :TQ10007           |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,07 | P->P | HASH       |
                          |  24 |                 PX BLOCK ITERATOR                 |                    |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWC |            |
                          |  25 |                  TABLE ACCESS FULL                | W_INT_ORG_D        |  9740 |   114K|       |     2   (0)| 00:00:01 |       |       |  Q1,07 | PCWP |            |
                          |  26 |              PX RECEIVE                           |                    |   344K|    47M|       |  1486   (2)| 00:00:27 |       |       |  Q1,10 | PCWP |            |
                          |  27 |               PX SEND HASH                        | :TQ10008           |   344K|    47M|       |  1486   (2)| 00:00:27 |       |       |  Q1,08 | P->P | HASH       |
                          |  28 |                JOIN FILTER USE                    | :BF0000            |   344K|    47M|       |  1486   (2)| 00:00:27 |       |       |  Q1,08 | PCWP |            |
                          |* 29 |                 HASH JOIN BUFFERED                |                    |   344K|    47M|       |  1486   (2)| 00:00:27 |       |       |  Q1,08 | PCWP |            |
                          |  30 |                  JOIN FILTER CREATE               | :BF0001            | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,08 | PCWP |            |
                          |  31 |                   PX RECEIVE                      |                    | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,08 | PCWP |            |
                          |  32 |                    PX SEND HASH                   | :TQ10004           | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,04 | P->P | HASH       |
                          |  33 |                     PX BLOCK ITERATOR             |                    | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,04 | PCWC |            |
                          |* 34 |                      TABLE ACCESS FULL            | W_ORG_D            | 35290 |   964K|       |    93   (2)| 00:00:02 |       |       |  Q1,04 | PCWP |            |
                          |  35 |                  PX RECEIVE                       |                    |   344K|    38M|       |  1392   (2)| 00:00:26 |       |       |  Q1,08 | PCWP |            |
                          |  36 |                   PX SEND HASH                    | :TQ10005           |   344K|    38M|       |  1392   (2)| 00:00:26 |       |       |  Q1,05 | P->P | HASH       |
                          |  37 |                    JOIN FILTER USE                | :BF0001            |   344K|    38M|       |  1392   (2)| 00:00:26 |       |       |  Q1,05 | PCWP |            |
                          |* 38 |                     HASH JOIN BUFFERED            |                    |   344K|    38M|       |  1392   (2)| 00:00:26 |       |       |  Q1,05 | PCWP |            |
                          |  39 |                      PX RECEIVE                   |                    | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                          |  40 |                       PX SEND HASH                | :TQ10001           | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
                          |  41 |                        PX BLOCK ITERATOR          |                    | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |
                          |* 42 |                         TABLE ACCESS FULL         | WC_PRODUCT_HIER_DH | 93791 |  4671K|       |     7   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
                          |* 43 |                      HASH JOIN                    |                    |   894K|    57M|       |  1384   (2)| 00:00:25 |       |       |  Q1,05 | PCWP |            |
                          |  44 |                       JOIN FILTER CREATE          | :BF0002            |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                          |  45 |                        PX RECEIVE                 |                    |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                          |  46 |                         PX SEND HASH              | :TQ10002           |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
                          |  47 |                          PX BLOCK ITERATOR        |                    |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,02 | PCWC |            |
                          |* 48 |                           TABLE ACCESS FULL       | W_PRODUCT_D        |   243K|  1904K|       |    48   (3)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                          |  49 |                       PX RECEIVE                  |                    |   894K|    50M|       |  1336   (2)| 00:00:25 |       |       |  Q1,05 | PCWP |            |
                          |  50 |                        PX SEND HASH               | :TQ10003           |   894K|    50M|       |  1336   (2)| 00:00:25 |       |       |  Q1,03 | P->P | HASH       |
                          |  51 |                         JOIN FILTER USE           | :BF0002            |   894K|    50M|       |  1336   (2)| 00:00:25 |       |       |  Q1,03 | PCWP |            |
                          |* 52 |                          HASH JOIN                |                    |   894K|    50M|       |  1336   (2)| 00:00:25 |       |       |  Q1,03 | PCWP |            |
                          |  53 |                           PX RECEIVE              |                    |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,03 | PCWP |            |
                          |  54 |                            PX SEND BROADCAST LOCAL| :TQ10000           |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,00 | P->P | BCST LOCAL |
                          |  55 |                             PX BLOCK ITERATOR     |                    |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,00 | PCWC |            |
                          |* 56 |                              TABLE ACCESS FULL    | W_DAY_D            |   292 |  3504 |       |   136   (0)| 00:00:03 |       |       |  Q1,00 | PCWP |            |
                          |  57 |                           PX BLOCK ITERATOR       |                    |  4801K|   215M|       |  1199   (2)| 00:00:22 |     1 |    11 |  Q1,03 | PCWC |            |
                          |* 58 |                            TABLE ACCESS FULL      | W_ORDERITEM_TMP_F  |  4801K|   215M|       |  1199   (2)| 00:00:22 |     1 |    44 |  Q1,03 | PCWP |            |
                          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          
                          Note
                          -----
                             - dynamic sampling used for this statement (level=5)
                          Edited by: qqq on Apr 7, 2013 2:33 PM
                          • 10. Re: Row Chaining Questions
                            Jonathan Lewis
                            qqq wrote:

                            My question is, if see statistics, the consistent gets are a lot lower on the new table and there is also some improvement in run time. Do these statistics i.e. lower consistent gets and lower run time prove sufficiently that this is a better way to go or in opinion of the guru's these are too small differences and I am suffering with some kind of performance tuning syndrome :-).

                            Also, I did one more test, I set star_transformation_enabled to true at session level and I ran the same query against original and new table. I did this because there is a plan to turn on this optimizer feature in the near future.

                            In Scenario 2 as well, the consistent gets were lower for the new table but the run time was longer for the new table. As I understand it, the lower we can get the consistent gets, we are doing less work to get the same data. Hence the query is more efficient. But why then does the run time not reflect it in the case of star transformation? Can you please advise?
                            In scenario 1 the predicted plan didn't change, but the number of consistent and physical reads dropped significantly - the former might be a side effect of getting rid of the intra-block row chaining, the later might be a side effect of building a smaller table, but it might also be a side effect of having run the previous query and caching blocks from the other tables. When I get an indication that the saving might be significant I run the test capturing the actual execution plan, and the changes in v$mystat and v$session_event for my session - possibly by enabling sql tracing, possibly by querying the dynamic performance views.

                            In scenario 2, it is interesting to note that the query generates some redo (that doesn't change as you switch tables). I assume this is probably a side effect of delayed block cleanout. The dramatic change in elapsed time despite the similarity of the statistics suggests that either something else was competing for resources, or that some change in execution plan resulted in much more CPU being used to do whatever Oracle did. Again I would look at the actual execution plans and session stats and waits before try to come to any firm conclusion.

                            Regards
                            Jonathan Lewis
                            • 11. Re: Row Chaining Questions
                              Jan-Marten Spit
                              "If you have to go looking for a performance problem, you have too much time on your hands."

                              why? even if no user is complaining, it's still good te reduce resource consumption and heat production, if you can.

                              let's counter this 'rule' with: if you only have performance problems when users are complaining, you failed.

                              "It appears that you suffer from Compulsive Tuning Disorder. "

                              i like people who can understand that A is a myth, and than solve it by adopting another.
                              • 12. Re: Row Chaining Questions
                                jgarry
                                I agree, it is not only legitimate to ask "what will happen if I make this change?" but an expected good practice to test it.

                                CTD is certainly a worry, but doesn't apply here. I think the number of columns alone is enough to stand back and go "say what? Is this a red flag?" It may come out that there are legitimate reasons including or beyond design for it, but they would still need to be explicit.

                                As far as proactive performance tuning, if you are so busy fighting fires you can't even think about it, that's probably a problem too. Since most systems should be growing, it's a Good Thing™ if you can catch problems before they kick you in the [url http://carymillsap.blogspot.com/2010/02/thinking-clearly-about-performance.html]knee.