From oracle-l-bounce@freelists.org Wed Sep 28 10:03:00 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8SF2x5o010276 for ; Wed, 28 Sep 2005 10:02:59 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j8SF2v6H010262 for ; Wed, 28 Sep 2005 10:02:57 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6B85E1EFC3E; Wed, 28 Sep 2005 10:02:51 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21792-01; Wed, 28 Sep 2005 10:02:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CCA291EF3A6; Wed, 28 Sep 2005 10:02:50 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5C41A.89225E58" Subject: RE: performance issue for a specific column selection Date: Wed, 28 Sep 2005 13:51:09 +0300 Message-ID: <083667B535F3464CA0DD0D1DAFA4E37607479F65@camexc1.kfs.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: performance issue for a specific column selection Thread-Index: AcXEGPdFtyQpRdMhQsiUxClLPKe8hQAAVrJg From: "Yasin Baskan" To: , "oracle list" X-archive-position: 26024 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: yasbs@kocbank.com.tr Precedence: normal Reply-To: yasbs@kocbank.com.tr X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.3 required=5.0 tests=AWL,BAYES_00,CLICK_BELOW, DATE_IN_PAST_03_06,HTML_50_60,HTML_FONTCOLOR_UNKNOWN, HTML_LINK_CLICK_HERE,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C5C41A.89225E58 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Can you send the create script for HSTAB, is it a table, view, materialized view, etc...? =0D ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of raja rao Sent: Wednesday, September 28, 2005 1:37 PM To: oracle list Subject: performance issue for a specific column selection =0D Hi All, =0D Can someone help me why a simple select is behaving strage: =0D select hospital_id,acct# from HSTAB where rownum=3D1; =0D 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. =0D select acct#,mr# ,admit_dt from ... =0D This is taking less than a second. The only problem is with the hospital_id column. =0D When I checked the wait events, the below are the waits: =0D SID EVENT MODULE ----- ------------------------------ ----------------------------------- USERNAME P1 P2 P3 ---------- ---------- ---------- ---------- 73 direct path write SQL*Plus OPS$ORACLE 201 6365 7 =0D my db_files param is 200. So this file is temp file the wait is happening on. =0D I already have DISK_ASYNCH_IO=3DTRUE . =0D What else i have to do to fix this issue. =0D Thanks in advance, Raj ________________________________ Yahoo! for Good Click here to donate to the Hurricane Katrina relief effort.=0D Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel= ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu= mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz= ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina= iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini= kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji= gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde= ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi= Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi= kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi= zararlardan Bankamiz sorumlu tutulamaz. This message and the files attached to it are under the privacy liability= in accordance with the Banking Law and confidential to the use of the= individual or entity to whom they are addressed. This message cannot be= copied, disclosed or sold monetary consideration for any purpose. If you= are not the intended recipient of this message, you should not copy,= distribute, disclose or forward the information that exists in the content= and in the attachments of this message; please notify the sender= immediately and delete all copies of this message. Our Bank does not= warrant the accuracy, integrity and currency of the information= transmitted with this message. This message has been detected for all= known computer viruses thence our Bank is not liable for the occurrence of= any system corruption caused by this message ------_=_NextPart_001_01C5C41A.89225E58 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Can you send the create script for= HSTAB, is it a table, view, materialized view,= etc...?

 


From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]= On Behalf Of raja rao
Sent: Wednesday, September= 28, 2005 1:37 PM
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= =3D1;

 

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           &nbs= p;            &= nbsp; MODULE
----- ------------------------------= -----------------------------------
USERNAME           P1         P2         P3
---------- ---------- ---------- ----------
   73 direct path write           &nbs= p;  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=3DTRUE .

 

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.



Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel= ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu= mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz= ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina= iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini= kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji= gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde= ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi= Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi= kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi= zararlardan Bankamiz sorumlu tutulamaz.
This message and the= files attached to it are under the privacy liability in accordance with= the Banking Law and confidential to the use of the individual or entity to= whom they are addressed. This message cannot be copied, disclosed or sold= monetary consideration for any purpose. If you are not the intended= recipient of this message, you should not copy, distribute, disclose or= forward the information that exists in the content and in the attachments= of this message; please notify the sender immediately and delete all= copies of this message. Our Bank does not warrant the accuracy, integrity= and currency of the information transmitted with this message. This= message has been detected for all known computer viruses thence our Bank= is not liable for the occurrence of any system corruption caused by this= message
------_=_NextPart_001_01C5C41A.89225E58-- -- http://www.freelists.org/webpage/oracle-l