SQL Performance (MOSC)

MOSC Banner

How to identify root cause of logical IO and reduce it?

edited May 4, 2017 10:15PM in SQL Performance (MOSC) 24 commentsAnswered

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")

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center