Oracle CBO / Performance Issue !!!
Johne_uk
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
DA Morgan
Seems to me you already found the issue. Your statistics are leading
the 9i (now obsolete and in desupport) optimizer to make a bad choice.
Don't collect statistics on the table(s) in question or use a histogram
or hints.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Johne_uk
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Hi,
Yes the statistics seem to be the main issue. Out of curiousity -
could the CBO be working differently on the Linux/9207 instance (which
runs fast using stats) compared to the Solaris 9208 instance because
of the diff patch level / OS.
thanks
DA Morgan
Yes. It could also be the data is different or that the clustering
factor is different or init parameters are different, or any number
of other factors. But definitely ... something IS different.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Frank van Bortel
Hash: SHA1
Of course, you could test by exporting statistics
from your 9207, and import them into 9208.
If the problem emerges - it's not the stats.
Then again - you should not test in production, and
tests should have been conducted on same platform,
at similar or same conditions.
- --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD4DBQFGwKWNLw8L4IAs830RAp+gAJ4xDpl6cYYWO9mrzp09CS/M67SzNQCWOETm
JjPjewiHxa1i901gwIymKg==
=YlY5
-----END PGP SIGNATURE-----
Jon Fife
wrote:
>
> >> - Show quoted text -
>
> > Hi,
>
> > Yes the statistics seem to be the main issue. Out of curiousity -
> > could the CBO be working differently on the Linux/9207 instance (which
> > runs fast using stats) compared to the Solaris 9208 instance because
> > of the diff patch level / OS.
>
> > thanks
>
> Of course, you could test by exporting statistics
> from your 9207, and import them into 9208.
> If the problem emerges - it's not the stats.
>
> Then again - you should not test in production, and
> tests should have been conducted on same platform,
> at similar or same conditions.
>
> - --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
>
> iD4DBQFGwKWNLw8L4IAs830RAp+gAJ4xDpl6cYYWO9mrzp09CS/M67SzNQCWOETm
> JjPjewiHxa1i901gwIymKg==
> =YlY5
> -----END PGP SIGNATURE-----
Do you have explain plans for the queries in the two instances (when
they both have statistics)? Is the data the same in both instances?
It seems odd that a query with optimizer hints would still do a FTS.
What hints did you give it?
Jon
R.Wang
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
zigz...@yahoo.com
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
9i may be desupported, but extended support will be available for many
many years. If everyone was as fortunate as Daniel, then we will be
using 11G, But in industry environment it takes years (forever) to
convince your business managers to move to a new release,
sybr...@hccnet.nl
>9i may be desupported, but extended support will be available for many
>many years.
Until 2010, to be exact.
Also you need to have an extended support *contract*, or you move to
sustaining support rightaway.
Please do your homework in order to avoid posting incorrect drivel.
--
Sybrand Bakker
Senior Oracle DBA
Johne_uk
>
> - Show quoted text -
Thanks for your help guys. The performance of the 9208 instance
increased significantly after we ran statistics again after the
application had been used for 24hrs. There are still one or two issues
but some of that is linked to poor quality sql.
Best regards
John