Re: Strange Oracle behavior between different environments
Date: Fri, 19 Aug 2011 12:19:24 -0700 (PDT)
Message-ID: <157f191d-daac-43d1-aedf-f8f87e1ace5e_at_o9g2000vbo.googlegroups.com>
On Aug 19, 1:24 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Fri, 19 Aug 2011 08:00:25 -0700, Alex L wrote:
> > 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
>
> 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
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 Received on Fri Aug 19 2011 - 14:19:24 CDT