How to identify root cause of logical IO and reduce it?
Query:
INSERT INTO TABLE a1
(SELECT Inst_SEQ.NEXTVAL,
a1.*
FROM a1
WHERE col1 IN
(SELECT col
FROM b1
WHERE col2 = :B1
AND col3 = :B2
AND col4 = :B3
AND col5 = 'T'
);
Execution Plan:
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 33917 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | SEQUENCE | Inst_SEQ | | | | |
| 3 | NESTED LOOPS | | 135K| 77M| 33917 (1)| 00:07:55 |
| 4 | NESTED LOOPS | | 138K| 77M| 33917 (1)| 00:07:55 |
|* 5 | INDEX RANGE SCAN | IDX_B1_1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_A1_1 | 138K| | 489 (1)| 00:00:07 |
| 7 | TABLE ACCESS BY INDEX ROWID| a1 | 138K| 75M| 33915 (1)| 00:07:55 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("col2" = ":B1" and "col3" = ":B2" and "col4" = ":B3" and "col5" = 'T')
6 - access("a1"."col1"="col")