Home » Infrastructure » Unix » Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? (Solasris 8 / 10 Oracle 9.2.0.7)
Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? [message #311460] Fri, 04 April 2008 09:57 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi Folks,

I have a queation but I do not know in which board it will be the best option to post.

We have migrated an OWB Database from an older SUN Systemboard with 4*1,34 Ghz Processors and 32 GB Ram under Solaris 8 to a newer one with 12 CPUs with 1,8 GHZ and 96 GB Ram. Both were located on a Sun Fire 25k.
Formerly on the old borad we have had runtimes for Some Marts at around 25 Mins. On the new one it runs 5hrs plus (job was killed sfter 5 hrs).
Is there any way to analyze if the new hardware needs other parameter to set for oracle ?
We saw many "nested loops" and a huge work on TEMPSPACE. This seems to be one of the major factors for the runtimes, but why does it uses TEMP instaed of the Memory or fork over Processors ?

I do have the sql-scripts, but first I want to know if we could do something within the Hardware or Oracle configuration.

kind regards
Uwe
Re: Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? [message #311496 is a reply to message #311460] Fri, 04 April 2008 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Are DB statistics current in the new database?

[Updated on: Fri, 04 April 2008 11:34] by Moderator

Report message to a moderator

Re: Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? [message #312142 is a reply to message #311460] Tue, 08 April 2008 01:42 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
I just checked - the system statistics seems to be outdated. I generated new ones. But it doesn't help.
I have actually one job which works via sqlplus in 1,5 Mins but if we start it via Batch it needs over 25 Mins.

I am checking the working useres by now, but it seemst that everything loks similiar to the other user.

Batch is connecting from a second Working-Server, but this should not affect, I guess.

Any hints hardly welcome

regards
Uwe
Re: Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? [message #312228 is a reply to message #312142] Tue, 08 April 2008 04:38 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
I have actually one job which works via sqlplus in 1,5 Mins but if we start it via Batch it needs over 25 Mins.


Then I would trace both sessions, and look for differences with tkprof .

Re: Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? [message #312302 is a reply to message #311460] Tue, 08 April 2008 08:33 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
We just filtered the problems with our job. Now I found a constallation which ran over 25 Mins also.

The Problem is that we're using VPD inside the DB. The Batch Job has a procedure which was filled by a variable to filter the data on a mandant.
If I implemented this function the statement needs 25 Mins, if I run the statement withou filtering it works in 2 Mins.
My colleagues told me that we're using VPD in this DWH, so this captured the problems.

Any hints on fixing Problems with VPD on a bigger machine as before ?

regards
Uwe
Re: Performance Issue on Sun Sloaris - Maybe Unix or Hardware Option ? [message #312387 is a reply to message #312302] Tue, 08 April 2008 13:47 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, there seem to be some instances where VPD seems to have dramatically reduced the performance because it changed the execution plan.

But the "look at the trace with TKPROF" I suggested should still give you more information on where exactly the problem is.

[Updated on: Tue, 08 April 2008 13:47]

Report message to a moderator

Previous Topic: oracle_home error
Next Topic: oracle version
Goto Forum:
  


Current Time: Thu Mar 28 12:52:50 CDT 2024