RE: RE: Facing poor performance on Oracle 10.2.0.2 running on RHEL 4
Date: Thu, 21 Feb 2008 12:00:22 -0800
Message-ID: <C388F0160F8F6A4DA25614D019619C8F170487@PA-EXCH21.vmware.com>
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_at_gmx.net]
Sent: Thursday, February 21, 2008 2:41 AM
To: Pratap Singh (c); oracle-l_at_freelists.org
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_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-lReceived on Thu Feb 21 2008 - 14:00:22 CST