Re: QUERY TUNING

Ganeshi Kini,

    There is a great Graphical Oracle Database Browser called SQL*Pro. It allows you to run up to 10 SQL statements at the same time. ( It is a multithreaded application ). Each statement has it's own window. Each of these windows keep statistics, such as start time, time run so far, and end time. In the event you don't want to wait for the SQL statement to complete, you can cancel it with the touch of a button, without affecting any other running statements. When the statement is cancelled, you won't need to talk to your DBA for anything, because it is a clean cancel through Oracle's OCI API.

    It also allows you to run an explain plan on the statement. and display it
in a graphical tree view look. I would suggest that you run explain plans on the
statement until it is using the most selective indexes on each table. From the
graphical explain plan, you can double click on each index used, and see the selectivity of each index.

    There are so many other features that should help you in this, and any other
Orace development activities. You can visit the following Web Site :

    http://www.erols.com/erikbengtson/

    Good Luck

Ganesh Kini wrote in message <35338A64.3B8E_at_epnet.com>...
>Can anyone suggest an approache to tune the following query which takes
>days to complete. Delc has 2.5M records with about 1.9 having not like
>'4%'. Artlc has 10M records, Article has 7M records, ProdSnap has 100K
>records of which 8K meets the condition.
>The query selects the subject headings in a product.
>
>Thanks
>
> Select Delc.Deid from MFS.DELC
> where exists
> (select 1 from MFS.ARTLC, MFS.ARTICLE, MFS.PRODSNAP
> WHERE PRODSNAP.PRODCODE = 'A0C'
> AND PRODSNAP.CHANNELID = 'CDROM'
> AND PRODSNAP.CYCLE = TO_DATE('199701'||'01', 'YYYYMMDD')
> AND PRODSNAP.CONTENTTYPE = 'A'
> AND ARTICLE.MID = PRODSNAP.MID
> AND ARTICLE.DTFORMAT BETWEEN PRODSNAP.DTACTBEG AND
>PRODSNAP.DTACTEND
> AND ARTLC.AN = ARTICLE.AN
> AND (article.UPDATETYPE IS NULL OR article.updatetype != 'D')
> AND ARTLC.DEID = DELC.DEID
> )
> and Delc.TagNo NOT like '4%';
Received on Tue Apr 14 1998 - 18:45:23 CDT