performance issue for a specific column selection

From: raja rao <raja4list_at_yahoo.com>
Date: Wed, 28 Sep 2005 03:37:16 -0700 (PDT)
Message-ID: <20050928103716.57015.qmail@web31415.mail.mud.yahoo.com>

Hi All,  

Can someone help me why a simple select is behaving strage:  

select hospital_id,acct# from HSTAB where rownum=1;  

This is almost taking 20 seconds to get the data. Where as teh same sql without hospital_id column is taking just less than a second.  

select acct#,mr# ,admit_dt from ... This is taking less than a second. The only problem is with the hospital_id column.  

When I checked the wait events, the below are the waits:  

  SID EVENT                          MODULE


----- ------------------------------ -----------------------------------

USERNAME           P1         P2         P3


---------- ---------- ---------- ----------

   73 direct path write              SQL*Plus
OPS$ORACLE        201       6365          7
 

my db_files param is 200. So this file is temp file the wait is happening on.  

I already have DISK_ASYNCH_IO=TRUE .  

What else i have to do to fix this issue.  

Thanks in advance,
Raj                 

---

Yahoo! for Good
 Click here to donate to the Hurricane Katrina relief effort.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 28 2005 - 05:39:24 CDT