Bad Performances/Execution Plan Running OCI7 Against Oracle 10.2.0.4 (Doc ID 1076099.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 7.1.1.0 and later   [Release: 7.1.1 and later ]
Information in this document applies to any platform.

Symptoms


Bad performances/execution plan running OCI against Oracle 10.2.0.4

DIAGNOSTIC ANALYSIS:
--------------------
-the bad plan occurs when running Cursor_Sharing=Force.

Bad Plan Is:

Rows Row Source Operation
------- ---------------------------------------------------
47 HASH JOIN (cr=11969 pr=11921 pw=0 time=2020312 us)
208 TABLE ACCESS FULL TRADE (cr=9604 pr=9573 pw=0 time=29047667 us)
208471 INDEX FAST FULL SCAN NORDEA_TRADE_LEG_BOOK (cr=2365 pr=2348 pw=0
time=71827 us)(object id 46748)


Good Plan Is:

Rows Row Source Operation
------- ---------------------------------------------------
47 CONCATENATION (cr=2041 pr=0 pw=0 time=50193 us)
0 NESTED LOOPS (cr=1867 pr=0 pw=0 time=49987 us)
143 TABLE ACCESS BY INDEX ROWID TRADE (cr=1578 pr=0 pw=0 time=115207
us)
143 INDEX RANGE SCAN NORDEA_TRADE_CONTROL (cr=1491 pr=0 pw=0 time=729
us)(object id 459660)
0 INDEX RANGE SCAN NORDEA_TRADE_LEG_BOOK (cr=289 pr=0 pw=0 time=1096
us)(object id 459738)
47 NESTED LOOPS (cr=171 pr=0 pw=0 time=195 us)
65 TABLE ACCESS BY INDEX ROWID TRADE (cr=39 pr=0 pw=0 time=679 us)
65 INDEX RANGE SCAN NORDEA_TRADE_CONTROL (cr=4 pr=0 pw=0 time=22
us)(object id 459660)
47 INDEX RANGE SCAN NORDEA_TRADE_LEG_BOOK (cr=132 pr=0 pw=0 time=293
us)(object id 459738)
0 NESTED LOOPS (cr=3 pr=0 pw=0 time=23 us)
0 TABLE ACCESS BY INDEX ROWID TRADE (cr=3 pr=0 pw=0 time=21 us)
0 INDEX RANGE SCAN NORDEA_TRADE_CONTROL (cr=3 pr=0 pw=0 time=15
us)(object id 459660)
0 INDEX RANGE SCAN NORDEA_TRADE_LEG_BOOK (cr=0 pr=0 pw=0 time=0
us)(object id 459738)

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms