From oracle-l-bounce@freelists.org Fri Aug 6 10:06:11 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i76F6AF17882 for ; Fri, 6 Aug 2004 10:06:10 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i76F6AI17877 for ; Fri, 6 Aug 2004 10:06:10 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 527D372C573; Fri, 6 Aug 2004 09:57:01 -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 27997-18; Fri, 6 Aug 2004 09:57:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9D1E372C2B6; Fri, 6 Aug 2004 09:57:00 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 06 Aug 2004 09:55:34 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7AC3A72C8B1 for ; Fri, 6 Aug 2004 09:55:34 -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 26664-90 for ; Fri, 6 Aug 2004 09:55:34 -0500 (EST) Received: from mail.alise.lv (dao-telia.alise.lv [194.19.227.253]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 956CB72C8A2 for ; Fri, 6 Aug 2004 09:55:33 -0500 (EST) Received: by mail.alise.lv (Postfix, from userid 333) id 3789D9D8A3; Fri, 6 Aug 2004 18:10:22 +0300 (EEST) Received: from ross.alise.lv (unknown [172.16.0.14]) by mail.alise.lv (Postfix) with ESMTP id DFB549D89D for ; Fri, 6 Aug 2004 18:10:20 +0300 (EEST) In-Reply-To: <3f3bf313f3bcd9.3f3bcd93f3bf31@optonline.net> To: oracle-l@freelists.org Subject: Re: RE: Time to read 6000 (block size 2k) blocks MIME-Version: 1.0 X-Mailer: Lotus Notes Release 6.5.2 June 01, 2004 Message-ID: From: J.Velikanovs@alise.lv Date: Fri, 6 Aug 2004 18:02:02 +0300 X-MIMETrack: Serialize by Router on ROSS/IT ALISE/LV(Release 5.0.11 |July 24, 2002) at 2004.08.06 18:02:03, Serialize complete at 2004.08.06 18:02:03 Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 7070 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: J.Velikanovs@alise.lv Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org jaysingh1@optonline.net, Can you try: SELECT --+ ORDERED USE_NL(P, E) DISTINCT P.PROFILEDUSERID PROFILEDUSERID, SEARCH_LAST_NAME, SEARCH_FIRST_NAME FROM EXTENDEDATTRIBUTES E, PROFILEDUSER P WHERE P.PROFILEDUSERID = E.PROFILEDUSERID AND P.SEARCH_COMPANY_NAME LIKE 'ACME%' ESCAPE '/' AND E.CUSTOMERID = 'ABCDEFGH' AND HSBC_USER_CATEGORY IN ('VAL1','VAL2') AND ROWNUM < 150 ORDER BY SEARCH_LAST_NAME,SEARCH_FIRST_NAME Jurijs +371 9268222 (+2 GMT) ============================================ Thank you for teaching me. http://otn.oracle.com/ocm/jvelikanovs.html jaysingh1@optonline.net Sent by: oracle-l-bounce@freelists.org 06.08.2004 17:52 Please respond to oracle-l To: oracle-l@freelists.org cc: Subject: Re: RE: Time to read 6000 (block size 2k) blocks Cary, Thanks for your response. I ahve enclosed all the details here. Generated extended tarce with level12. SELECT DISTINCT P.PROFILEDUSERID PROFILEDUSERID, SEARCH_LAST_NAME, SEARCH_FIRST_NAME FROM PROFILEDUSER P , EXTENDEDATTRIBUTES E WHERE P.PROFILEDUSERID = E.PROFILEDUSERID AND P.SEARCH_COMPANY_NAME LIKE 'ACME%' ESCAPE '/' AND E.CUSTOMERID = 'ABCDEFGH' AND HSBC_USER_CATEGORY IN ('VAL1','VAL2') AND ROWNUM < 150 ORDER BY SEARCH_LAST_NAME,SEARCH_FIRST_NAME call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 11.39 41.98 6126 18805 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 11.40 41.99 6126 18805 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 180 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT UNIQUE 1 COUNT STOPKEY 1 NESTED LOOPS 4766 TABLE ACCESS BY INDEX ROWID PROFILEDUSER (it has 450,000 rows) 4767 INDEX RANGE SCAN (PROFILEDUSER_IX03) 1 TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES (it has 15,000 rows) 9530 INDEX UNIQUE SCAN (ATTRIBUTES_PK) SQL> select column_name from dba_ind_columns where index_name='PROFILEDUSER_IX03'; COLUMN_NAME -------------------------------------------------------------------------------- SEARCH_COMPANY_NAME SQL> select column_name from dba_ind_columns where index_name='ATTRIBUTES_PK'; COLUMN_NAME -------------------------------------------------------------------------------- PROFILEDUSERID ----- Original Message ----- From: Cary Millsap Date: Friday, August 6, 2004 10:41 am Subject: RE: Time to read 6000 (block size 2k) blocks > Yes, this is actually 0.003568s/read, which is pretty good. > > A better question, though, is, "Does the application really need > to make > 18,805 visits to the database buffer cache to return just one row?" > > Unless your query uses some kind of aggregation function to return the > single row (count, sum, etc.), then you should be able to make > this SQL = > do > its job with 10-20 LIOs instead of 18,805. If you can do that, you = > should be > able to reduce response time from 41.99s to about 0.04s. > > I can't see your SQL here, but because there were 2 fetch calls, > I'll = > bet > that you're not aggregating the result, and that you should be > able to = > get > to the 0.04s response time target. It might be as simple as a > missing = > index, > or SQL that debilitates the use of an index. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > * Nullius in verba * > > Upcoming events: > - Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 = > Charlotte > - SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 = > Hartford > - Hotsos Symposium 2005: March 6-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > From: oracle-l-bounce@freelists.org = > [oracle-l-bounce@freelists.org] > On Behalf Of Khedr, Waleed > Sent: Friday, August 06, 2004 9:19 AM > To: oracle-l@freelists.org > Subject: RE: Time to read 6000 (block size 2k) blocks > > Five millisecond is not bad for single block sequential read. > > Waleed > > -----Original Message----- > From: jaysingh1@optonline.net [jaysingh1@optonline.net]=3D20 > Sent: Friday, August 06, 2004 9:49 AM > To: oracle-l@freelists.org > Subject: Time to read 6000 (block size 2k) blocks > > > Hi All, > > The question may be wispy. > We have 14 CPU sun box,8i 2 node OPS. Not under heavy load. > > In our case it is taking 21.86 sec for 6126 blocks (from disk) > > db file sequential read 6126 0.29 > 21.86 > > Approximately how long it should take to read 6000 blocks? > > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- --------- > - > ---------- > Parse 1 0.01 0.01 0 0 0 > 0 > Execute 1 0.00 0.00 0 0 0 > 0 > Fetch 2 11.39 41.98 6126 18805 0 > 1 > ------- ------ -------- ---------- ---------- ---------- --------- > - > ---------- > total 4 11.40 41.99 6126 18805 0 > 1 > > > > db file sequential read 6126 0.29 > 21.86 > > Thanks > Sami > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------