We have a 10g database on Solaris 10 that is having some serious
performance issues. The same database restored onto a Windows machine
performs fine.

I'm not sure if the Solaris/Windows thing makes a difference at all,
but I'm curious if anyone knows of anything.

Some background: We discovered the issue while optimizing a query,
and examining the explain plan cost of the query. Unoptimized, this
query had a cost of 256000 in 10g/Solaris. It had a cost of 6 in 10g/
Windows.

Optimized, the query had a cost of 51 in 10g/Solaris, and a cost of 4
in 10g/Windows. So we're still seeing a pretty huge difference on
this query alone, and the general performance of the Solaris DB is
just very poor, despite the hardware being far superior to the Windows
machine.

The Solaris DB is WE8ISO8859P1 character set. The Windows DB is
WE8MSWIN1252. Any ideas? Other information I can provide?

Thanks,
Alex

On Aug 19, 8:00*am, Alex L <ala... (AT) gmail (DOT) com> wrote:
Show us the plans like the example on http://blogs.oracle.com/optimizer/en...w_do_i_know_if

Randolf's guide to posting is useful:
http://oracle-randolf.blogspot.com/2...rformance.html

Also let us know exactly how you "restored" to the windows machine.
Some operations can reorganize data, changing blevels and such. Exact
patch levels, system statistics, various statistics gathering methods
can influence information available to the optimizer and how it
behaves.

jg
--
@home.com is bogus.
It's not a gang, it's a club.
http://www.signonsandiego.com/news/2...ed-crime-case/

Alex:

# We have a 10g database on Solaris 10 that is having some serious
performance issues.

....

I just have one system left 10.2 on Solaris ( an old machine ) but it
runs just peachy!


# I'm not sure if the Solaris/Windows thing makes a difference at all,
but I'm curious if anyone knows of anything.

....

Well I am sure that in general Solaris should be faster. Does that
help any?

We need more details ... like what Joel suggested.

The most likely shot in the dark guess is statistics related. Like
perhaps statistics regathered on the windows machine but old stats
maybe on the Solaris database.

A 10046 trace is also a good route to start looking at things like
this along with a dbms_xplan type approach.

On Fri, 19 Aug 2011 08:00:25 -0700, Alex L wrote:

Is there any difference in the system statistics? What are the values in
SYS.AUX_STATS$ table? These values govern the behavior of the CBO. Also,
is there any significant discrepancy in the instance parameters?



--
http://mgogala.byethost5.com

On Aug 19, 1:24*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
Joel, I can gather the information you request, but it won't be
today. And to clarify, the issue is not with the query exactly, but
really with an app that is universally slow in the Solaris
environment, but runs perfectly acceptably in Windows and Redhat
(though the Redhat environment uses AL32UTF8, which is why we are
thinking this might be character set related). We have pretty well
narrowed down that the bottleneck appears to the be DB, rather than
other network issues.

John, I agree, our thoughts are that the Solaris environment should be
faster, especially considering it has better hardware than our local
Windows environment. I'll look into the trace you mentioned, thanks.

Mladen, it appears that both have up to date statistics, running
GATHER_STATS_JOB nightly. SYS.AUX_STATS$ are basically the same,
difference CPUSPEED numbers.

Windows:
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-30-2005 15:04
SYSSTATS_INFO DSTOP 08-30-2005 15:04
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 484.974958263773
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

Solaris:
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-21-2006 05:43
SYSSTATS_INFO DSTOP 01-21-2006 05:43
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 452.169401387898
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR

I don't believe there is a significant difference in parameters, but I
didn't set them up, and I discovered this morning that the Solaris
system is 10.1.0.5 and the Windows system is 10.2.0.1. So there could
be more differences than I thought. Is it possible the different
versions could be responsible for the different performance? I don't
know enough about them, but I know my gut is now to try to get both on
the same version for a more direct comparison.

Sorry for the lack of specifics, kinda new to Oracle, and I was mainly
looking for directions to go in for troubleshooting and gathering more
specific data. Thanks everyone.

-Alex

On Aug 19, 12:19*pm, Alex L <ala... (AT) gmail (DOT) com> wrote:

Solaris is really picky about configuration, if everything is slow,
you probably should read DISM notes on MOS. Just put DISM in the
Search Knowledge Base box.

Statspack or AWR may also give more clues.

jg
--
@home.com is bogus.
"SALESMAN!" - PeeWee Herman
http://www.businessweek.com/technolo...-08192011.html
http://www.eweek.com/c/a/IT-Infrastr...425029/?kc=rss

Alex:

First nothing is better than a real problem to start learning. If you
are fairly new to Oracle then a couple of things to point out in terms
of resources and knowledge base.

Along with the Oracle ( pretty good and free documentation ) there are
a couple of essential books to help in expand your knowledge base.

Probably first in the regard to systematic trouble shooting is Cary
Millsap's book "Optimizing Oracle Performance". Along with that book
the most recent one by Tom Kyte in the Architecture series and
Christian Antognini book "Troubleshooting Oracle Performance".
Jonathan Lewis has an excellent book on the Oracle Optimizer also
highly recommended.

# I don't believe there is a significant difference in parameters, but
I didn't set them up, and I discovered this morning that the Solaris
system is 10.1.0.5 and the Windows system is 10.2.0.1. *So there could
be more differences than I thought.

....

Seriously??? 10.1 was just a really buggy early version of 10g. You
don't want to be running 10.1 at all.

10.2.0.1 is the base release of 10.2 not patched up at all.

Both of those versions are seriously behind the curve and I would not
do any serious investigation of problems on either of those.

Is there any reason they are not patched up appropriately?

If so then do that prior to spending any more valuable time chasing
problems.


# Is it possible the different versions could be responsible for the
different performance?

....

Does a bear poop in the woods?


# Sorry for the lack of specifics, kinda new to Oracle, and I was
mainly looking for directions to go in for troubleshooting and
gathering more specific data.

....

Hey no problems we beat people up until they give us the information
we are looking for.

On Fri, 19 Aug 2011 12:19:24 -0700, Alex L wrote:

..
Alex, the absence of the numbers for SREATIM,MREADTIM and MBRC tells me
that you didn't gather system statistics. In that case, Oracle will use
its own, platform and version dependent defaults. Defaults for Windows
and Solaris are likely different. You should gather system stats in the
moments when the loads experienced by the system is typical. Please, use
DBMS_STATS.GATHER_SYSTEM_STATS procedure to collect it. I don't know the
default values for either platform, by my experience tells me that the
defaults are usually very off target and do not make much sense.


--
http://mgogala.byethost5.com

On Fri, 19 Aug 2011 14:47:13 -0700, John Hurley wrote:


And 10.2.0.1 had one really interesting bug. The client would hang all
the processes after some 200 days or so.

--
http://mgogala.byethost5.com

On Aug 19, 10:00*am, Alex L <ala... (AT) gmail (DOT) com> wrote:
I worked for an Oracle development company that made accounting
software for the Agribusiness sector at my suggestion, they did not
buy a $500K HP and started using three $5K Windows/Intel machines for
development. We had a customer with a .5 mil HP that sent us their
entire system which when installed on one of the Windows boxes run
much faster than their HP. They sent sent their people over to sort it
out and we never did figure it out. Money doesn't buy happiness.