John WatsonMessages: 5359Registered: January 2010 Location: Global Village

Senior Member

Some observations, possibly of little value:
The CBO expects 2 rows back, but is getting 6292. So there is a problem with cardinality estimates. The only mistake I can see is at id 14, where the CBO expects 8766 rows but it should be 7060. I wouldn't have thought it would make much difference, but even so, I would gather stats on userlist.
Are ids 11 and 12 bit odd? The cardinality estimate is perfect, but why do it that way? I can't see the point of scanning the entire index to retrieve two rowids at 12 and then retrieving both rows and filtering one away at 11. The CBO must have a reason for doing this, but I would hint a full scan of roleinfo (or perhaps make roleinfo_pk invisible) and see what happens.

This kind of query is generally quite easy to tune. We will today use a variation of the technique called QUERY DECOMPOSITION AND RECONSTRUCTION.

Given your Query Plan and Predicate Information, it is easy to see the first queries you need to run in order to validate the plan. Since cardinality is the number one thing in a query plan and you seem to understand this given your other statements, decompose the main query into smaller queries to see how well the optimizer did in getting the right cardinalities. You should be able to make the association between these four sql statements and your predicate info and query plan. Run them and then post your results.