From oracle-l-bounce@freelists.org Mon Apr 5 12:05:16 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i35H5Er24342 for ; Mon, 5 Apr 2004 12:05:14 -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 i35H5Eo24336 for ; Mon, 5 Apr 2004 12:05:14 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8184E6344DB; Mon, 5 Apr 2004 12:00:56 -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 11796-93; Mon, 5 Apr 2004 12:00:56 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BE88A6346B7; Mon, 5 Apr 2004 12:00:55 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 05 Apr 2004 11:59:50 -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 A7A736344E6 for ; Mon, 5 Apr 2004 11:59:49 -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 11796-74 for ; Mon, 5 Apr 2004 11:59:49 -0500 (EST) Received: from ddbcinc.com (unknown [216.222.6.50]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 10B926343DA for ; Mon, 5 Apr 2004 11:59:49 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: QUERY Tune Date: Mon, 5 Apr 2004 11:12:19 -0600 Message-ID: <87E9F113CEF1D211A4C30090273018741BBAB0@ddbcinc.ddbc.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: QUERY Tune thread-index: AcQbDA0vJsWoKBV0QL2MWKdBtbdcMQAJCYag From: "Justin Cave (DDBC)" To: X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2545 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jcave@ddbcinc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Actually, CONTAINS is an Oracle Text (formerly interMedia Text) function. It is a lot more sophisticated, and should be a lot faster than, LIKE with the appropriate wildcards. =20 Justin Cave Distributed Database Consulting, Inc. http://www.ddbcinc.com/askDDBC -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Kevin Toepke Sent: Monday, April 05, 2004 6:44 AM To: 'oracle-l@freelists.org' Subject: RE: QUERY Tune Wow! I agree with Mladen, shoot the developer. If the developer is not available to be shot, try these ideas. 1) Replace the CONTAINS function (not an oracle function, must be a PL/SQL call) with LIKE or INSTR 2) Figure out what the SCORE function is and tune it. Better yet, if it is deterministic, just code the return value in the query. 3) Stop summing zeros. The sum of any number of zeros is zero. content_score and meta_score are always zero. 4) Given 3, kill the middle select in the in-line view -- all it contributes is time and a bunch of zeros to the sum. 5) Given 3, kill the DECODE in the name_count sum -- all it contributes is CPU cycles. You get the idea. Kevin -----Original Message----- From: Seema Singh [mailto:oracledbam@hotmail.com] Sent: Friday, April 02, 2004 4:54 PM To: oracle-l@freelists.org Subject: QUERY Tune Hi, select sp.page_id as page_id,one_line_item_desc, sp.page_title as=20 page_title, sp.url_stub as Url_stub, sum(category_score) as cat_count, sum(decode(sp.meta_keywords,null,name_score,(name_score+me ta_score))) as name_count, sum(content_score) as cont ent_count from static_pages sp, ( select page_id , 0 as category_score, score(20) as name_score, 0 as conte nt_score, 0 as meta_score from static_pages where page_t itle is not null and live_p =3D 't' and contains(page _title, 'GEAR,GUIDE',20) > 0 UNION ALL select page_id, 0 as category_score, 0 as name_score, 0 as content_score, 0 a s meta_score from static_pages where page_title is not n ull and live_p =3D 't' and contains(meta_keywords, 'G EAR,GUIDE',20) > 0 UNION ALL select to_number(on_what_id) as page_id, weight as category_score, 0 as name_score, 0 as content_score, 0 as meta_score from site_wide_category_map where category_id in (1386,866,1068 ,1069,1286,3321,3508,8015,8289,8292) and on_which_table=3D'st atic_pages' and approved_p=3D't' ) dyn where dyn.page_id =3D sp.page_id and (template_key !=3D 'travel_photo _gallery' and template_key !=3D 'tpg_red_0703' and template_key ! =3D 'yde_redesign_may2003' and template_key !=3D 'yde' and templat e_key !=3D 'new_yde' and template_key !=3D 'photo_gallery_template' ) group by sp.page_id, one_line_item_desc, sp.page_title, sp .url_stub order by cat_count desc, name_count desc, content_ count desc This query is very slow,some times its hanges.Please suggest if anything you find? thx -Seema _________________________________________________________________ Free up your inbox with MSN Hotmail Extra Storage! Multiple plans available. http://join.msn.com/?pgmarket=3Den-us&page=3Dhotmail/es2&ST=3D1/go/onm002= 00362 ave/ direct/01/ ---------------------------------------------------------------- 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 -----------------------------------------------------------------