Re: Different user, different indexes used. Why?

From: Mladen Gogala <mgogala.SPAM_ME.NOT_at_verizon.net>
Date: Sat, 17 Feb 2007 22:44:42 GMT
Message-ID: <uzLBh.1832$lG6.1551@trndny08>

On Wed, 14 Feb 2007 10:45:26 -0800, samuelrollins wrote:

> When I connect using SQL*Plus to Oracle 8i DB using cost based
> optimization and run _identical_ queries (syntax and literals) as two
> different users, as one of the users the query takes less than a
> second and as the other user it takes over a minute. I did an explain
> plan on both and found that for the fast user unique indexes are used
> on all three tables in the query and for the slow user only one unique
> index is used while the other two indexes are ignored.
>
> Can someone explain how this could happen?
>
> Thanks,
> Sam

Sam, did you run the SQL in question with the event 10053, level 1 enabled? The trace file will show you the decisions made by the CBO. This will likely answer your question.

-- 
http://www.mladen-gogala.com

Received on Sat Feb 17 2007 - 16:44:42 CST