1 2 Previous Next 18 Replies Latest reply on Oct 31, 2019 7:12 PM by Jonathan Lewis

    Index organized table (IOT) - Insert/Update performance 12.2.0.1

    user591200

      Hi,

       

      I have following SQL and it is considerably slower ,

       

      T_IOT              -  This is an Oracle Indexed Organized table (about 8.4 Million rows)

                              -   idx_pk(nr_time,uid,inst_id)

                              -  Two secondary indexes  T_IOT_STATE(st,inst_id, svnm) , idx2(uid,inst_id,svnm)

       

      T_BASE          - Heap Table - (4.2 million rows)

       

       

      SQL_ID  azxc8vqsbng04, child number 0

      -------------------------------------

      INSERT  INTO T_IOT

          SELECT

              apar.UID,

              'UP',

              TO_DATE('2019-10-24','yyyy-mm-dd'),

              TO_DATE('1969-12-31','yyyy-mm-dd'),

              'IDLE',

              NULL,

              'tkt007.jj.bb.com'

          FROM

              t_base apar

          WHERE

              apar.UID NOT IN (

                  SELECT

                      UID

                  FROM

                      T_IOT ob

                  WHERE

                      INST_ID = 'UP'

              )

              AND apar.GP_NM = 'UA'

              AND ROWNUM <= 5000;

       

      Plan hash value: 3554622160

       

      ---------------------------------------------------------------------------------------------------------------

      | Id  | Operation                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      ---------------------------------------------------------------------------------------------------------------

      |   0 | INSERT STATEMENT         |                            |       |       |       | 30926 (100)|          |

      |   1 |  LOAD TABLE CONVENTIONAL | T_IOT                |       |       |       |            |          |

      |*  2 |   COUNT STOPKEY          |                            |       |       |       |            |          |

      |*  3 |    HASH JOIN RIGHT ANTI  |                            |  2697K|   156M|    65M| 30926   (1)| 00:00:02 |

      |*  4 |     INDEX SKIP SCAN      | T_IOT_STATE          |  1683K|    46M|       | 13872   (1)| 00:00:01 |

      |*  5 |     TABLE ACCESS FULL    | t_base   |  4205K|   128M|       |  4995   (1)| 00:00:01 |

      ---------------------------------------------------------------------------------------------------------------

       

      Query Block Name / Object Alias (identified by operation id):

      -------------------------------------------------------------

       

         1 - SEL$5DA710D3

         4 - SEL$5DA710D3 / OB@SEL$2

         5 - SEL$5DA710D3 / APAR@SEL$1

       

      Outline Data

      -------------

       

        /*+

            BEGIN_OUTLINE_DATA

            IGNORE_OPTIM_EMBEDDED_HINTS

            OPTIMIZER_FEATURES_ENABLE('12.2.0.1')

            DB_VERSION('12.2.0.1')

            ALL_ROWS

            OUTLINE_LEAF(@"SEL$5DA710D3")

            UNNEST(@"SEL$2")

            OUTLINE_LEAF(@"INS$1")

            OUTLINE(@"SEL$1")

            OUTLINE(@"SEL$2")

            INDEX_FFS(@"INS$1" "T_IOT"@"INS$1" ("T_IOT"."NR_TIME"

                    "T_IOT"."UID" "T_IOT"."INST_ID"))

            FULL(@"SEL$5DA710D3" "APAR"@"SEL$1")

            INDEX_SS(@"SEL$5DA710D3" "OB"@"SEL$2" ("T_IOT"."ST" "T_IOT"."INST_ID"

                    "T_IOT"."SVR_NM"))

            LEADING(@"SEL$5DA710D3" "APAR"@"SEL$1" "OB"@"SEL$2")

            USE_HASH(@"SEL$5DA710D3" "OB"@"SEL$2")

            SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "OB"@"SEL$2")

            END_OUTLINE_DATA

        */

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         2 - filter(ROWNUM<=5000)

         3 - access("APAR"."UID"="UID")

         4 - access("INST_ID"='UP')

             filter("INST_ID"='UP')

         5 - filter(NVL("APAR"."GP_NM",'UA')='UA')

       

      Column Projection Information (identified by operation id):

      -----------------------------------------------------------

       

         2 - "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200], ROWNUM[8]

         3 - (#keys=1) "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200]

         4 - "OB".ROWID[ROWID,1249], "NR_TIME"[TIMESTAMP,11], "UID"[VARCHAR2,1024],

             "INST_ID"[VARCHAR2,200], "OB".ROWID[ROWID,1249]

         5 - "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200]

       

       

      From Real time SQL Monitor following was observed ,

       

      pl1.jpg

       

      There seems to be a massive 5GB Temp for the hash join,

       

      pl2.jpg

       

       

      Any idea why this is happening ?

       

      currently the above query takes about 3 - 5 minutes.

        • 1. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
          John Thorton

          user591200 wrote:

           

          Hi,

           

          I have following SQL and it is considerably slower ,

           

          T_IOT - This is an Oracle Indexed Organized table (about 8.4 Million rows)

          - idx_pk(rundt,uid,inst_id)

          - Two secondary indexes T_IOT_STATE(st,inst_id, svnm) , idx2(uid,inst_id,svnm)

           

          T_BASE - Heap Table - (4.2 million rows)

           

           

          SQL_ID azxc8vqsbng04, child number 0

          -------------------------------------

          INSERT INTO T_IOT

          SELECT

          apar.UID,

          'UP',

          TO_DATE('2019-10-24','yyyy-mm-dd'),

          TO_DATE('1969-12-31','yyyy-mm-dd'),

          'IDLE',

          NULL,

          'tkt007.jj.bb.com'

          FROM

          t_base apar

          WHERE

          apar.UID NOT IN (

          SELECT

          UID

          FROM

          T_IOT ob

          WHERE

          INST_ID = 'UP'

          )

          AND apar.GP_NM = 'UA'

          AND ROWNUM <= 5000;

           

          Plan hash value: 3554622160

           

          ---------------------------------------------------------------------------------------------------------------

          | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

          ---------------------------------------------------------------------------------------------------------------

          | 0 | INSERT STATEMENT | | | | | 30926 (100)| |

          | 1 | LOAD TABLE CONVENTIONAL | T_IOT | | | | | |

          |* 2 | COUNT STOPKEY | | | | | | |

          |* 3 | HASH JOIN RIGHT ANTI | | 2697K| 156M| 65M| 30926 (1)| 00:00:02 |

          |* 4 | INDEX SKIP SCAN | T_IOT_STATE | 1683K| 46M| | 13872 (1)| 00:00:01 |

          |* 5 | TABLE ACCESS FULL | t_base | 4205K| 128M| | 4995 (1)| 00:00:01 |

          ---------------------------------------------------------------------------------------------------------------

           

          Query Block Name / Object Alias (identified by operation id):

          -------------------------------------------------------------

           

          1 - SEL$5DA710D3

          4 - SEL$5DA710D3 / OB@SEL$2

          5 - SEL$5DA710D3 / APAR@SEL$1

           

          Outline Data

          -------------

           

          /*+

          BEGIN_OUTLINE_DATA

          IGNORE_OPTIM_EMBEDDED_HINTS

          OPTIMIZER_FEATURES_ENABLE('12.2.0.1')

          DB_VERSION('12.2.0.1')

          ALL_ROWS

          OUTLINE_LEAF(@"SEL$5DA710D3")

          UNNEST(@"SEL$2")

          OUTLINE_LEAF(@"INS$1")

          OUTLINE(@"SEL$1")

          OUTLINE(@"SEL$2")

          INDEX_FFS(@"INS$1" "T_IOT"@"INS$1" ("T_IOT"."NR_TIME"

          "T_IOT"."UID" "T_IOT"."INST_ID"))

          FULL(@"SEL$5DA710D3" "APAR"@"SEL$1")

          INDEX_SS(@"SEL$5DA710D3" "OB"@"SEL$2" ("T_IOT"."STATE" "T_IOT"."INST_ID"

          "T_IOT"."SVR_NM"))

          LEADING(@"SEL$5DA710D3" "APAR"@"SEL$1" "OB"@"SEL$2")

          USE_HASH(@"SEL$5DA710D3" "OB"@"SEL$2")

          SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "OB"@"SEL$2")

          END_OUTLINE_DATA

          */

           

          Predicate Information (identified by operation id):

          ---------------------------------------------------

           

          2 - filter(ROWNUM<=5000)

          3 - access("APAR"."UID"="UID")

          4 - access("INST_ID"='UP')

          filter("INST_ID"='UP')

          5 - filter(NVL("APAR"."GP_NM",'UA')='UA')

           

          Column Projection Information (identified by operation id):

          -----------------------------------------------------------

           

          2 - "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200], ROWNUM[8]

          3 - (#keys=1) "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200]

          4 - "OB".ROWID[ROWID,1249], "NR_TIME"[TIMESTAMP,11], "UID"[VARCHAR2,1024],

          "INST_ID"[VARCHAR2,200], "OB".ROWID[ROWID,1249]

          5 - "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200]

           

           

          From Real time SQL Monitor following was observed ,

           

          pl1.jpg

           

          There seems to be a massive 5GB Temp for the hash join,

           

          pl2.jpg

           

           

          Any idea why this is happening ?

           

          currently the above query takes about 3 - 5 minutes.

          I would like to point out that the primary purpose for the IOT is to speed up the SELECT of values from this table.

          The actual table load should be a one time event that does not usually occur during OLTP processing.

          IMO, I would accept the current results & NOT waste any more time on it.

           

          Of course if you from Compulsive Tuning Disorder you can continue to obsess over this irrelevant observation.

           

          If you can successfully reduce IOT load time to 0.1 seconds, how much faster will the overall OLTP application run over the 1st month of deployment?

          • 2. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
            user591200

            Hi John,

             

            Thanks providing your thoughts. Ideally I would like to get it under 1 min , currently there is bunch of App servers timing out on this issue. Any idea why 5 GB of temp is used ? that's where the time is spent.

             

            Thanks,

            Jayanath

            • 3. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
              John Thorton

              user591200 wrote:

               

              Hi John,

               

              Thanks providing your thoughts. Ideally I would like to get it under 1 min , currently there is bunch of App servers timing out on this issue. Any idea why 5 GB of temp is used ? that's where the time is spent.

               

              Thanks,

              Jayanath

              The posted EXPLAIN PLAN reports that SQL should complete in about 1 seconds.

              Was PLAN produced on Production DB where slow load time exists?

               

              If you remove the INSERT part of the SQL, how long does SELECT take? Post EXPLAIN PLAN for just the SELECT.

              Are statistics current & complete for all tables & indexes?

               

              I just may be old & going blind, but I can read content of graphics pasted into post.

               

              Please click on URL below & provide details as stated in #5 - #9 inclusive

               

              How to  improve the performance of my query? / My query is running slow.


              • 4. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                Bhavani Dhulipalla

                Most of the time spent in Hash Join and seems like the oracle is not doing the optimal Hash Join as it is writing into the Temp tablespace -

                 

                The Estimations are also seems correct from your SQL Monitor Report -

                 

                May be you can paralleize the query a bit and that way you can use more PGA than temporary tablespaces -

                 

                Also see if you can hint the query with out the hash join(May be with FILTER in Execution Plan) to see if it finishes Faster -

                 

                 

                Thanks

                Bhavani

                • 5. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                  AndrewSayer

                  user591200 wrote:

                   

                  Hi John,

                   

                  Thanks providing your thoughts. Ideally I would like to get it under 1 min , currently there is bunch of App servers timing out on this issue. Any idea why 5 GB of temp is used ? that's where the time is spent.

                   

                  Thanks,

                  Jayanath

                  This doesnt look like the sort of statement an application session Should be executing. It looks like its trying to synchronise one IOT from one large table, if it was an application statement then you’d expect that the table being used to load the IOT was small and just contained a bit of transactional data, are you missing some filters?

                  Whats with the rownum filter? If this is to chunk up the load then you should know that you are doing more and more work on each execution - you have to read more from t_base each time.

                   

                  If this is some sort of batch load from t_base into your IOT from multiple sessions then you’d be better off chunking up t_base using dbms_parallel_execute and then running the statement for each chunk of rows in t_base.

                   

                  The statement could be a lot more efficient if you had an index on your IOT that lead with inst_id, uid. I trust UID is not null?

                   

                  -Edit

                  Im not sure why the temp usage is so high, it could be incorrectly reporting many executions rather than one. The size Of UID is large (400) but probably not used to that limit, and that shouldn’t add up to the 5GB you’re seeing. I’ll also note the fast full scan in the outline that doesn’t exist in the actual plan, perhaps that is being executed as part of the load and getting reported weirdly.


                  Having a huge PK in an IOT is certainly a bad idea for performance of both querying and DML, but it shouldn’t be causing what youre seeing.

                  • 6. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                    Joerg.Sobottka

                    Have you tried an index on t_base with UID, GP_NM ?

                    You also may can try to rewrite it from a NOT IN to a normal join.

                    • 7. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                      Dom Brooks

                      In my opinion, secondary indexes on IOTs are a reasonable indication that you might be better off using a normal heap table.

                      • 8. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                        Jonathan Lewis

                        I've note examined the effect of a hash join right anti when the build table is an index organized table, but looking at the projection from the index skip scan of the t_iot_state index the data passed up to the hash join is listed as:

                         

                        "OB".ROWID[ROWID,1249], "NR_TIME"[TIMESTAMP,11], "UID"[VARCHAR2,1024],  "INST_ID"[VARCHAR2,200], "OB".ROWID[ROWID,1249]

                         

                        The rowid for an IOT is, in fact, its primary key, which means  in this case (nr_time, uid, inst_id), so I doubt if Oracle is passing up all five listed items, it's more likely passing the rowid once as that carries all the information needed.  -- I assumed that the 1st PK column is nr_time rather than the rundt you said in your description.

                         

                        IF (speculation) the ROWID is padded to fixed sizes then the 4M rows in the SQL Monitor "actual rows" multiplied by the 1.25K of the rowid gives you 5GB, which has to spill to disc.

                         

                        You'll note that the SQL Monitor report says that almost all the time is spent concurrently on the skip scan and hash join, before anyy access is done to the t_base table. That tends to confirm that something big is being written to disc as the rows are scanned, and the "fixed width on rowid" fits the aritmetic.

                         

                        In the Monitored run, did you specifiy rownum <= 1000 - it looks like a bit of a coincidence that scanned the 4.2M rows you told us about in t_base to get exactly 1,000 rows as output if you were asking for 5,000.    If you didn't have the "rownum <=5000" predicate, how many rows would you expect to appear in total ? This may make a big difference to the choices you can make to improve the speed of the insert.

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                          John Thorton

                          user591200 wrote:

                           

                          Hi,

                           

                          I have following SQL and it is considerably slower ,

                           

                          T_IOT - This is an Oracle Indexed Organized table (about 8.4 Million rows)

                          - idx_pk(nr_time,uid,inst_id)

                          - Two secondary indexes T_IOT_STATE(st,inst_id, svnm) , idx2(uid,inst_id,svnm)

                           

                          T_BASE - Heap Table - (4.2 million rows)

                           

                          Why are you physically duplicating same data in two tables?

                          Why does IOT have twice the rows as the BASE table?

                          What business problem are you really trying to solve?

                          • 10. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                            user591200

                            Hi Jonathan,

                             

                            Thanks and I am amazed by your ability to drill through the details , Please forgive me for my inconsistent anonymization efforts. I corrected on the original posting.

                             

                            The pagination was set to 5000 when the monitoring was done. Without pagination it is around 91,424 rows. ( The image is when I was trying with 1000 row filter, to see whether that makes difference , but it made little difference)

                             

                            For defect fixes sometimes they truncate T_IOT and/or T_BASE time to time , so the transaction will fill the T_BASE and the above pagination query will be executed. I drilled little deep and it seem that above 5000 is a rare case. So 90% of time it will be closer to 1000 +/-  rows.

                             

                            I see that if I re-write and apply a hint to avoid the HASH JOIN ANTI , like using a hint /*+ USE_NL(APAR OB) */  , the query gets much faster but cost of the query is pretty high.

                             

                            Any thoughts are helpful.

                            • 11. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                              Joerg.Sobottka

                              user591200 wrote:

                               

                              Hi Jonathan,

                               

                              Thanks and I am amazed by your ability to drill through the details , Please forgive me for my inconsistent anonymization efforts. I corrected on the original posting.

                               

                              The pagination was set to 5000 when the monitoring was done. Without pagination it is around 91,424 rows. ( The image is when I was trying with 1000 row filter, to see whether that makes difference , but it made little difference)

                               

                              For defect fixes sometimes they truncate T_IOT and/or T_BASE time to time , so the transaction will fill the T_BASE and the above pagination query will be executed. I drilled little deep and it seem that above 5000 is a rare case. So 90% of time it will be closer to 1000 +/- rows.

                               

                              I see that if I re-write and apply a hint to avoid the HASH JOIN ANTI , like using a hint /*+ USE_NL(APAR OB) */ , the query gets much faster but cost of the query is pretty high.

                               

                              Any thoughts are helpful.

                              Again, to avoid the Hash Join Anti I suggested:

                              Have you tried an index on t_base with UID, GP_NM ?

                              You also may can try to rewrite it from a NOT IN to a normal join.

                              Can you test that?

                              • 12. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                                Jonathan Lewis

                                Without the plan I can't be certain, but if you're seeing a nested loop anti join then it's not surprising that the cost has gone up while the performance has gone down. The optimizer has various problems with costing that show up in all sorts of ways.  In fact, you should expect the cost to go up when you hint the plan to do something difference because normally Oracle will have found what it thinks is the lowest cost plan and you're telling it to do something different.

                                 

                                We still don't have enough information to suggest the best strategy - for example, if your 1,000 rows are all the rows that have GP_NM = 'AU' then an indexed access path to t_base with a nested loop to test the UID would be a good thing; but if you had 4M rows with gp_nm = 'AU' and did 4M probes that discarded 3,999,000 rows then the nested loop would end up being very CPU intensive and probably a bad idea.

                                 

                                Two other thoughts:

                                 

                                a) in the outline you see the hint  SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "OB"@"SEL$2")

                                If you changed that to NO_swap_join_inputs(.....etc....) that would make the t_base table the "build" table in the hash join and you'd see the join change to HASH JOIN ANTI  (rather than RIGHT ANTI) and that might be a little more efficient for the large scale requirement - it might not dump so much (it might not dump anything) to disc. 

                                 

                                b) if you have a large volume to move and you do use "rownum <= 5000" to transfer them in batches then each batch will be slower than the last because you will be checking all the previous transfers every single batch and finding that they already exist.  So for the 90,000 rows that might exist you probably don't want to use batches; and for small volumes you don't need to use batches; so get rid of the predicate.  Worst case scenario - you might want to hint a nested loop for day to day running, and have code that does a hash join for the occasions when you truncate and reload.

                                 

                                 

                                Regards

                                Jonathan Lewis

                                 

                                 

                                PS - a complete set of hints to get the hash join the right way around would probably be:

                                /*+

                                unnest(@sel$2)

                                leading(apar@sel$1 ob@sel$2)

                                use_hash(ob@sel$2)

                                no_swap_join_inputs(ob)

                                */

                                 

                                Though it might need to be

                                /*+

                                unnest(@sel$2)

                                leading(@sel$5da710d3 apar@sel$1 ob@sel$2)

                                use_hash(@sel$5da710d3 ob@sel$2)

                                no_swap_join_inputs(@sel$5da710d3 ob)

                                */

                                 

                                For the nested loop, the correct set would be:

                                /*+

                                leading(apar@sel$1 ob@sel$2)

                                use_nl(ob@sel$2)

                                */

                                 

                                Your use_nl(apar ob) is a little bit lucky - though it does actually restrict Oracle fairly aggressively. Technically the use_nl(ob) is the bit which says use a nested loop into OB, but you then need the leading(apar ob) to say look only at the join order that starts with apar, so that the use_nl() hint has to be the only join option left for the optimzer.  See: https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/

                                • 13. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                                  Jonathan Lewis

                                  I'm trying to build a model of your situation to check the ROWID projection hypothesis.

                                   

                                  I need to know which columns in the two tables you've declared as NOT NULL (may through a primary)

                                  I also need to know why the predicate on gp_nm turns into nvl(gp_nm,'UA') = 'UA' when the nvl() isn't in the query.

                                   

                                   

                                  In passing, to force the plan I had to include some generated query block names in the hints so you may have to include the long query block name in the hinting for the nested loop join to make it behave consistently.

                                   

                                  Regards

                                  Jonathan Lewis

                                  • 14. Re: Index organized table (IOT) - Insert/Update performance 12.2.0.1
                                    Jonathan Lewis

                                    Modelled it.

                                     

                                    Demonstrated the point. Oracle seems to be using a fixed-width memory allocation for the universal rowid on the join. Change the "swap_join_inputs" to no_swap_join_Inputs and the scale of the space allocation will probably change.

                                     

                                    Regards

                                    Jonathan Lewis

                                     

                                    UPDATE - and I've just realised why Oracle is projecting the (nominally irrelevant "rowid") - it's because the thing you've anonymised to UID is in the rowid but not in the index that Oracle has picked for its skip scan, so rather then visit the "table", it's extracting the uid from the rowid - which means it needs the rowid. So another way to reduce the temp space used is to hint the use of idx2 which includes the columns you need ("uid" and inst_id) so that you don't have to pick up a universal rowid.  change the index_ss hint in the outline to index_ffs() and change the list of column to the list of columns that make up idx2.

                                    1 2 Previous Next