Re: Help improving the performance of a query - please

The execution path is already optimal, as the biggest table is retrieved last.
The only thing you could do is try to optimise the order by: check out whether this is a disk sort or not, increase sort_area_size and the like.

Hth,

--
Sybrand Bakker, Oracle DBA
<JOC (JOC)> wrote in message news: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 Tue Nov 30 1999 - 13:08:44 CST