Re: Help improving the performance of a query - please

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Dec 1999 10:44:38 -0000
Message-ID: <944045189.8415.0.nnrp-12.9e984b29@news.demon.co.uk>

To get a better idea, we need index definitions and some column statistics.

For example:

---

Looking at the plan the first access seems to be on FILE_STATE to give 1,139 rows
(on the small data set, where the large data set has 67,000 rows).

How many rows do you have at value 'PP', and how many rows do you have per

    RF.SENDING_PARTICIPANT_ID Possibly you need an index on

    (sending_participant_id, file_state) to get a high selectivity on the first pass.

etc.....

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

JOC (JOC) wrote in message <3843fc6b.26819086_at_NEWS>...
>My brain is obviousky in off mode today, so I was wondering if any
>bright sparks out there could help - I'd really appreciate it. I need
>to tune the following statement. This is on Oracle 7.3.4 and the
>optimiser is rule-based (well choose, but nothing is analysed), on
>HP-UX.
>Table rr has about 55 million rows in it, rf about 67 thousand (both
>dynamic), and ft is static with about 100 rows. (this query is not
>used for a batch job, and the user is currently sitting at the
>computer for 3 - 4 minutes an update). -
>
>SELECT DISTINCT RF.RECEIVED_FILE_ID
>FROM
> RECEIVED_RECORDS RR,
> FILE_TYPES FT,
> RECEIVED_FILES RF
>WHERE
> RF.RECEIVED_FILE_ID = RR.RECEIVED_FILE_ID
> AND RF.FILE_TYPE_ID = FT.FILE_TYPE_ID
> AND RF.SENDING_PARTICIPANT_ID = :b1
> AND RF.FILE_STATE = 'PP'
> AND FT.PARTICIPANT_ROLE_ID LIKE NVL(:b2,'%')
> AND FT.DATA_FLOW_ID = :b3
> AND RR.REJECTION_REASON_ID = :b4
> AND RR.RECORD_STATE_ID = :b5
> AND RR.REJECTION_DATA = TO_CHAR(:b6)
> ORDER BY RF.RECEIVED_FILE_ID
>
>
>and the explain plan looks something like
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: RULE
> 0 SORT (UNIQUE)
> 0 NESTED LOOPS
> 107 NESTED LOOPS
> 1399 TABLE ACCESS (BY ROWID) OF 'RECEIVED_FILES'
> 1400 INDEX (RANGE SCAN) OF 'RF_FS_FK' (NON-UNIQUE)
> 1139 TABLE ACCESS (BY ROWID) OF 'FILE_TYPES'
> 1139 INDEX (UNIQUE SCAN) OF 'FT_PK' (UNIQUE)
> 6 TABLE ACCESS (BY ROWID) OF 'RECEIVED_RECORDS'
> 113 INDEX (RANGE SCAN) OF 'RL_PK' (UNIQUE)
>
>(this done on our small db, the times on the client are
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 0 0.00 0.00 0 0 0
>0
>Execute 5 0.00 0.00 0 0 0
>0
>Fetch 5 69.96 1098.91 20735 1799134 0
>0
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 10 69.96 1098.91 20735 1799134 0
>0
>
>
>Thanks for any help
>
>Jo
>
>
>*************************************************
>
>The usual - anything I say reflects on me, and not upon my current
employer.
>
>*************************************************
Received on Wed Dec 01 1999 - 04:44:38 CST