Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help improving the performance of a query - please

Re: Help improving the performance of a query - please

From: <michael_bialik_at_my-deja.com>
Date: Tue, 30 Nov 1999 21:33:03 GMT
Message-ID: <821fqg$34b$1@nnrp1.deja.com>


Hi.

 It's a wild guess without enough info ( too many possibilities ).  Is it possible for you to post:

  1. TKPROF output of PROD ( to see where from all these rows are fetched ).
  2. Output of SELECT table_name, index_name, column_name FROM user_ind_columns WHERE table_name IN ('RECEIVED_RECORDS','FILE_TYPES', 'RECEIVED_FILES' ) ORDER BY 1,2,column_position;
  3. Mark UNIQUE indexes for each table.

  Anyway, I'm going to make a guess:
  If RECEIVED_FILE_ID field is a primary key ( or unique identifier )   of 'RECEIVED_FILES table then you may eliminate multiple joins to   RECEIVED_RECORDS table by using:

  SELECT RF.RECEIVED_FILE_ID
  FROM

  	FILE_TYPES FT,
 	RECEIVED_FILES RF
  WHERE
        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 EXISTS ( SELECT 1 FROM RECEIVED_RECORDS RR
                     WHERE
                        RF.RECEIVED_FILE_ID = RR.RECEIVED_FILE_ID
                        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    Check index 'RF_FS_FK' selectivity is good.

   HTH. Michael.

In article <3843fc6b.26819086_at_NEWS>,
  JOC (JOC) wrote:
> 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.
>
> *************************************************
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 30 1999 - 15:33:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US