This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,877 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

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

user591200
user591200 Member Posts: 42 Bronze Badge
edited Nov 28, 2019 12:09PM in General Database Discussions

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 / [email protected]$2

   5 - SEL$5DA710D3 / [email protected]$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.

user591200Martin PreissDom Brooks

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    edited Oct 30, 2019 3:13PM Answer ✓

    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.

    Dom Brooks
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Oct 29, 2019 8:55PM
    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 / [email protected]$2 5 - SEL$5DA710D3 / [email protected]$1Outline 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?

  • user591200
    user591200 Member Posts: 42 Bronze Badge
    edited Oct 29, 2019 9:35PM

    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

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Oct 29, 2019 10:02PM
    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.


  • Bhavani Dhulipalla
    Bhavani Dhulipalla Member Posts: 38
    edited Oct 29, 2019 10:22PM

    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

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Oct 30, 2019 4:43AM
    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.

  • Joerg.Sobottka
    Joerg.Sobottka Senior Berater / Senior Consultant, Oracle ACE Wil (SG), SwitzerlandMember Posts: 598 Bronze Trophy
    edited Oct 30, 2019 3:46AM

    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.

  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Oct 30, 2019 7:55AM

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

    Martin Preiss
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    edited Oct 30, 2019 7:54AM

    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

    user591200
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Oct 30, 2019 9:51AM
    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?

  • user591200
    user591200 Member Posts: 42 Bronze Badge
    edited Oct 30, 2019 10:14AM

    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.