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

user591200 · Oct 29, 2019 8:27PM

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

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

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

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

|* 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 ,

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

Any idea why this is happening ?

currently the above query takes about 3 - 5 minutes.

Jonathan Lewis · Oct 30, 2019 3:03PM

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.

John Thorton · Oct 29, 2019 8:54PM

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 ,

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

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 · 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 · 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 · 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 · Oct 30, 2019 3:18AM

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 · 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 · Oct 30, 2019 7:54AM

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

Jonathan Lewis · 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

John Thorton · 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 · 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.

Forum Stats

Quick Links

Categories

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

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Best Answer

Answers