RE: RE: Facing poor performance on Oracle 10.2.0.2 running on RHEL 4

Thanks Milen.
I was also trying to make sure we do not hit some lesser known bugs.

Thanks again.
PB Singh 


-----Original Message-----
From: Milen Kulev [mailto:makulev@xxxxxxx] 
Sent: Thursday, February 21, 2008 2:41 AM
To: Pratap Singh (c); oracle-l@xxxxxxxxxxxxx
Subject: Re: RE: Facing poor performance on Oracle 10.2.0.2 running on RHEL 4

Hello  PB Singh,

cursor_sharing=similar + histograms could be the reason to get mutating
execution plans (not only for the highly skewed data...).
You can get high-version count (many execution plans, not necessarily
diffrent) for the same sql.  Thus could put strain on your shared pool.

Depending on how frequently SQL statements are executed, it could cause high
hard-parse CPU consumption and/or fragmented shared pool.
Normally this can be seen on OLTP databases with (relatively) high execution
rate.

On DWH database your can just increase your shared pool (if already not big
enough), but generally it is on DWH database not a problem.


HTH. Milen

-------- Original-Nachricht --------
> Datum: Wed, 20 Feb 2008 15:52:55 -0800
> Von: "Pratap Singh (c)" <psingh@xxxxxxxxxx>
> An: oracle-l@xxxxxxxxxxxxx
> Betreff: RE: Facing poor performance on Oracle 10.2.0.2 running on 
> RHEL 4

> We were facing poor performance on Oracle 10.2.0.2 database.
> Query were generated by OBI tool. Lots of similar query with hard 
> coded literals.
> 
> We changed cursor_sharing=similar and performance is better by a 
> factor of
> 2+. 
> Query earlier taking 10 sec are finishing in 1 sec.
> 
> Any gotcha for this setting We should be aware of?
> 
> TIA.
> PB Singh
> 
> 
> ------------------------------------------------------------
> PB Singh
> DW Architect and Sr Data Modeler
> VMware
>  
> 
> -----Original Message-----
> From: Pratap Singh (c)
> Sent: Friday, February 15, 2008 11:27 AM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: Facing poor performance on Oracle 10.2.0.2 running on RHEL 4
> 
> We are facing quite poor performance on a DW on 10.2.0.2
> 
> One of the things that came up in performance monitoring was high IOWAIT.
> There are little or no IO taking place from disk, but IOWAIT was going 
> up to 60%. Sometime the number was even 90%.
> 
> Checking the datafiles using filefrag, system admin found that some 
> files had 100's of fragments and temp file has 600,000 fragment.
> 
> Has any seen similar fragment status. Our datafiles are about 10GB 
> size and temp tablespace is 10GB.
> 
> Thanks for any help.
> PB Singh
> 
> 
> ------------------------------------------------------------
> PB Singh
> DW Architect and Sr Data Modeler
> VMware
>  
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 

--
Psst! Geheimtipp: Online Games kostenlos spielen bei den GMX Free Games! 
http://games.entertainment.web.de/de/entertainment/games/free
--
http://www.freelists.org/webpage/oracle-l


Other related posts: