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_at_vmware.com>
  > An: oracle-l_at_freelists.org
  > 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_at_freelists.org'
> 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