SQL Performance (MOSC)

MOSC Banner

Specific query performance in 11i worse than in 9i

edited May 3, 2016 3:45PM in SQL Performance (MOSC) 18 comments

We just upgraded from Oracle 9i to 11g.  The following query (used in a view) worked great in 9i.  But in 11g, performance is an issue.  The query is complicated and so it was divided up like it is

for readability and maintainability.  Each individual subquery is very fast.  And like I said, in 9i the whole thing was very fast.

The explain plan in 11g is crazy.  Look at the row counts near the top:

18E

18E

18E

11P

80T

567G

So this is obviously a problem.  The query only returns 134 rows.  The driver table (bnd_policy_claim) has 134 rows.

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