RE: Facing poor performance on Oracle 10.2.0.2 running on RHEL 4
Date: Wed, 20 Feb 2008 15:52:55 -0800
Message-ID: <C388F0160F8F6A4DA25614D019619C8F1703DA@PA-EXCH21.vmware.com>
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
Received on Wed Feb 20 2008 - 17:52:55 CST