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

Home -> Community -> Usenet -> c.d.o.server -> Re: any ideas for better performance of this query ?

Re: any ideas for better performance of this query ?

From: Ron Reidy <ron_at_indra.com>
Date: Wed, 10 Oct 2001 10:25:18 -0600
Message-ID: <3BC4766E.5FA1B5E3@indra.com>


Parvinder Singh Arora wrote:
>
> Hi all,
>
> I have the following query which is current taking 2 + mins
>
> select m.member_id, m.primary_email, m.second_name,
> m.first_name, s.status, s.download_complete
> from member m,subscriptiondetails b,subtransaction s
> where
> s.from_state = 3201
> and m.member_id = b.sub_id
> and b.subscription_id = s.subscription_id
>
> 10000 rows selected.
>
> Elapsed: 00:02:145.49
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 NESTED LOOPS
> 2 1 NESTED LOOPS
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUBTRANSACTION'
> 4 3 INDEX (RANGE SCAN) OF 'SUBTRANS_FROMSTATE' (NON-UNIQ
> UE)
>
> 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIPTIONDETAILS'
> 6 5 INDEX (UNIQUE SCAN) OF 'SYS_C007122' (UNIQUE)
> 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER'
> 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C007068' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 51453 consistent gets
> 23 physical reads
> 0 redo size
> 451148 bytes sent via SQL*Net to client
> 44937 bytes received via SQL*Net from client
> 668 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 10000 rows processed
>
> The table subtransaction contains around 15,00,000 records followed by
> subscriptiondetails containing 1000 records and member table
> containing 100 records .
>
> I have created index on all the fields of the where clause but still
> there is not much difference in the performance
>
> can any one give me some ideas as to in what direction should i focus
> for better timing of the query ?
>
> Thanks & REgards
> ~Parvinder

Clock time is an extreme;y poor measure of query response time.

Do you have the correct driving table? Are you using indexes correctly? Have you looked at tkprof output?

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Received on Wed Oct 10 2001 - 11:25:18 CDT

Original text of this message

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