Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance difference issue between two users of the same database

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

From: Billy <vslabs_at_onwe.co.za>
Date: 24 May 2005 05:05:39 -0700
Message-ID: <1116934199.537614.72540@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.

--
Billy
Received on Tue May 24 2005 - 07:05:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US