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

"Billy" <vslabs_at_onwe.co.za> wrote in message news:<1116934199.537614.72540_at_z14g2000cwz.googlegroups.com>...
> Talk2Vineet_at_gmail.com wrote:
>
> > sorry to include os and ora version in my previous mail. My OS is
> > Windows 2000 pro. and oracle version is 8.1.7.We have checked both
> the
> > users doing all the DMBS_STATS, but couldn't get it run as per our
> > expectations.
>
> Well then it would seem that the tables and indexes are not as similar
> as you're hoping that they would be... or else the CBO is getting it
> horribly wrong. Not that the latter is impossible, but I would first
> discard all other causes before blaming the CBO or an Oracle bug.
>
> The table structure may for example be the same for USER1.TABLEA and
> USER2.TABLEA. The amount of data in the tables may be the same within a
> few rows.. but the nature of the data could be very different resulting
> in the CBO making the wrong decision like dealing with skewed indexes
> values without realising it.
>
> One table may be subject to FGAC and other other not. USER1's session
> may be altered by a logon trigger that changes CBO related settings.
> TABLEB in could be an IOT in USER2's schema and not in USER1's.
>
> Etc.
>
> There are a myriad possibilities why two tables or two views from
> different Oracle Users/Schemas may look logically the same, but are
> physically very different.
>
> The assumption that USER1's execution plan is wrong (because it is
> slow) and USER2's execution plan correct (because it is fast) for the
> same SQL on similar looking tables could also be wrong.
>
> To confirm that your assumption is indeed correct, what happens when
> you force the USER1's SQL to use the same execution path as USER2's
> SQL? If by forcing the execution plan it is faster, then you at least
> know that the CBO made the wrong decision and can then troubleshoot it
> from that aspect. It could be that due to the physical differences, the
> original "slow" SQL from USER1 is actually the correct and fastest
> method given the available stat and design and data of USER1's tables
> and indexes.

Hi Billy,
if 2 users fire the same SQL against the same database, and 1 user is 10 times
slower than the other one, I would always test this scenario on a test machine, make a logon trigger on both users, and incorporate a 10046 trace
within the logon trigger. 10046 will tell you right away what your problem is.

If the cause is different access paths, I afterwards run a 10053 in the same way. 10046 let you know, without guessing what is going on.

//Bjarke. Received on Tue May 24 2005 - 20:58:33 CDT