Re: Performance difference issue between two users of the same database

Talk2Vineet_at_gmail.com wrote:

> I have a problem, would appriciate you if your help makes me resolve
> this issue. I have created two users in the same database and when i
> run a query in these two users, one user executes it in a minute,
while
> other takes more than 10 minutes. Query to be run has mixture of
views
> and tables while views in turn has many tables joined with each
other.
> I have checked all the indexes and both the users have similiar
> indexes, when checked default tablespaces, the fast user's is system
> while other's is users. According to Expain plan of the query, the
user
> which is fast using an index on a table while other is not. I got
stuck
> in it for the last 2 weeks but could not make it work, even i created
> many users and even in different schemas also but it didn't work.
> Please help me out.

Oracle version? o/s platform? DMBS_STATS done?

The execution plan costs each step of execution - including using indexes (or not using indexes). The CBO deems that in one case the use of an index is simply more expensive than not using it.

If that is not true then it means that either the CBO had insufficient or incorrect (statistical) data to make a correct decision, or the CBO premises (see optimizer pfile/spfile parameters) are incorrect.

--
Billy