RE: QUERY Tune

From: Kevin Toepke <ktoepke_at_rlcarriers.com>
Date: Mon, 5 Apr 2004 08:43:32 -0400
Message-ID: <524A8924345BD411B65600508B9A8B6505F17B9E@wilmail.wilnet>

Wow! I agree with Mladen, shoot the developer. If the developer is not available to be shot, try these ideas.

Replace the CONTAINS function (not an oracle function, must be a PL/SQL call) with LIKE or INSTR
Figure out what the SCORE function is and tune it. Better yet, if it is deterministic, just code the return value in the query.
Stop summing zeros. The sum of any number of zeros is zero. content_score and meta_score are always zero.
Given 3, kill the middle select in the in-line view -- all it contributes is time and a bunch of zeros to the sum.
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_at_hotmail.com] Sent: Friday, April 02, 2004 4:54 PM
To: oracle-l_at_freelists.org
Subject: QUERY Tune

Hi,

select sp.page_id as page_id,one_line_item_desc, sp.page_title as 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 = '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 = '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='st
	atic_pages'		 and approved_p='t'	 ) dyn	   where
	 dyn.page_id = sp.page_id     and (template_key != 'travel_photo


	_gallery' and template_key != 'tpg_red_0703'  and template_key !

	= 'yde_redesign_may2003'  and template_key != 'yde'  and templat
	e_key != 'new_yde'  and template_key != '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=en-us&page=hotmail/es2&ST=1/go/onm00200362ave/ direct/01/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_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_at_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
-----------------------------------------------------------------

Received on Mon Apr 05 2004 - 07:38:04 CDT