Re: Oracle CBO / Performance Issue !!!

On Aug 13, 6:46 am, Johne_uk <edg..._at_tiscali.co.uk> wrote:
> Hi,
>
> We have just introduced a new application (java based) which accesses
> an Oracle 9i db. The app is quite complex and we have experienced some
> oracle back end performance issues.
>
> Explain plans indicate that certain indexes were not being used on the
> instance (9208 on Solaris) whereas when we tested the same app on a
> Linux server (9207) it used indexes where practical.
>
> One particular sql ran in 57s on the 9208 and 4s on the 9207.
>
> Strangely, enought when I deleted statiscs on the 9208 instance it
> executed in about 4 secs also.
>
> Both instances have CBO set as CHOOSE and I can't see any notable
> differences in the init.ora to suggest what the problem is. I just
> find it strange how having stats for a schema will make things run
> slower.
>
> The app schemas are also identical (used schema comparison tool) -
> they were dervied from the same export dump. All indexes are valid on
> 9208 schema.
>
> We also tried using optimizer hints on the 9208 sql to try and make it
> use indexes but it still opted for full tables scans.
>
> Just wondering where to start looking at this problem from.
>
> Any guidance would be appreciated
>
> Thanks in advance
> John

Hi John,

        The problem is the "un-uniform histograms" on the column which is involved in the specific statement you found. Picking sub-optimal execution plan is a problem of CBO, not only in 9i, but in 10g. Fortunately, it occurs occasionally.

        Please refer to my suggestion as following.

        go check the histograms of columns (with 75 buckets) that are involved in the statement.

         If you found skewed or highly-skewed histograms, do analyze again only on table level.

         For instance, analyze target table or tables by using command "Analyze table im_blacklist compute statistics;" .

         The table level analysis will overwrite histograms information and eliminate histograms information on columns. That means, no histograms is created on columns and CBO will automatically select index path access, which is also only option for CBO at that circumstance.

         If it works, you are lucky. If not, go check if bind variables are used in your statement.

         I have a article to talk about the exactly same case I ever experienced. Hope it helps.

        Follow this link for that article: http://oraclepoint.com/oralife/?p=80

        Good luck.

R.Wang Received on Thu Aug 16 2007 - 23:51:10 CDT