Re: QUERY Tune

From: Dan Tow <dantow_at_singingsql.com>
Date: Fri, 2 Apr 2004 16:36:56 -0800
Message-ID: <1080952616.406e0728ad59b@www.singingsql.com>

Trying again to cope with the <50% quote rule:

Here's a good, simple minimum for the information needed to tune a query reasonably efficiently:

-The current execution plan.
-The list of indexes on the tables, with their columns, in order, and which of these are unique.
-The selectivity (as a percentage of rows in the affected table) of single-table
conditions mentioned in the WHERE clause, such as "live_p = 't'" -The complete view-defining query text of any views mentioned in the SQL, as well as any views mentioned in those views,... -The number of rows the query returns.
-The number of rows and name of the biggest table (sanity check, combined with the previous datapoint to determine whether the usual assumptions about filter independence and rowcounts before and after joins hold). -The current runtime of the query.
-The target runtime of the query (helps determine how radical the solution might
need to be).

Yours,

Dan Tow
dantow_at_singingsql.com
650-858-1557
Author, SQL Tuning, O'Reilly

Quoting Seema Singh <oracledbam_at_hotmail.com>:

> 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,
> ...
> .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

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 Fri Apr 02 2004 - 18:33:25 CST