RE: performance issue for a specific column selection

Is there an index on hospital_id column, how big is the table?        

Regards

Amit  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of raja rao Sent: 28 September 2005 11:37
To: oracle list
Subject: performance issue for a specific column selection

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 <http://store.yahoo.com/redcross-donate3/> here to donate to the Hurricane Katrina relief effort.

PLEASE READ: The information contained in this email is confidential and intended for the named recipient(s) only. If you are not an intended recipient of this email please notify the sender immediately and delete your copy from your system. You must not copy, distribute or take any further action in reliance on it. Email is not a secure method of communication and Nomura International plc ('NIplc') will not, to the extent permitted by law, accept responsibility or liability for (a) the accuracy or completeness of, or (b) the presence of any virus, worm or similar malicious or disabling code in, this message or any attachment(s) to it. If verification of this email is sought then please request a hard copy. Unless otherwise stated this email: (1) is not, and should not be treated or relied upon as, investment research; (2) contains views or opinions that are solely those of the author and do not necessarily represent those of NIplc; (3) is intended for informational purposes only and is not a recommendation, solicitation or offer to buy or sell securities or related financial instruments. NIplc does not provide investment services to private customers. Authorised and regulated by the Financial Services Authority. Registered in England no. 1550505 VAT No. 447 2492 35. Registered Office: 1 St Martin's-le-Grand, London, EC1A 4NP. A member of the Nomura group of companies.

--

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