Home » RDBMS Server » Performance Tuning » Regarding Query Tuning
Regarding Query Tuning [message #65190] Thu, 03 June 2004 20:32 Go to next message
raghukalyan
Messages: 44
Registered: May 2004
Member
hi Guys ...

I have a question and it is  ...

How can we say tht a Query is Perfectly Tuned ?

Is it by Cost ..??? If by Cost then wht are better ways of reducing the cost of a Query .I am having a Query which has a inline view and the cost of the query is 1134.So, is there any way to reduce the cost .For this query there are no full table scans .But the cost is high Are there any tools for Query tuning other than SQL Analyzer

The explain Plan for the query is as follows :

lapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=15 Bytes=2
          850)

   1    0   SORT (GROUP BY) (Cost=1134 Card=15 Bytes=2850)
   2    1     VIEW (Cost=1132 Card=15 Bytes=2850)
   3    2       NESTED LOOPS (Cost=1132 Card=15 Bytes=1590)
   4    3         NESTED LOOPS (Cost=568 Card=282 Bytes=26790)
   5    4           NESTED LOOPS (Cost=4 Card=282 Bytes=21150)
   6    5             NESTED LOOPS (Cost=3 Card=1 Bytes=51)
   7    6               NESTED LOOPS (Cost=2 Card=1 Bytes=36)
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION'
          (Cost=1 Card=1 Bytes=26)

   9    8                   INDEX (UNIQUE SCAN) OF 'PK_LOCATION' (UNIQ
          UE)

  10    7                 TABLE ACCESS (BY INDEX ROWID) OF 'DIVISION'
          (Cost=1 Card=1 Bytes=10)

  11   10                   INDEX (UNIQUE SCAN) OF 'PK_DIVISION' (UNIQ
          UE)

  12    6               TABLE ACCESS (BY INDEX ROWID) OF 'REGION' (Cos
          t=1 Card=1 Bytes=15)

  13   12                 INDEX (UNIQUE SCAN) OF 'PK_REGION' (UNIQUE)
  14    5             INDEX (RANGE SCAN) OF 'IDX_RLP_LNBRLONBRDDTTAD'
          (NON-UNIQUE) (Cost=1 Card=1 Bytes=24)

  15    4           TABLE ACCESS (BY INDEX ROWID) OF 'DEALSUMMARY' (Co
          st=2 Card=11547 Bytes=230940)

  16   15             INDEX (UNIQUE SCAN) OF 'PK_DEALSUMMARY' (UNIQUE)
           (Cost=1 Card=283)

  17    3         INLIST ITERATOR
  18   17           INDEX (RANGE SCAN) OF 'IDX_DT_DTRNCD' (NON-UNIQUE)
           (Cost=2 Card=787871 Bytes=8666581)

  19   18             FILTER
  20   19               INLIST ITERATOR
  21   20                 TABLE ACCESS (BY INDEX ROWID) OF 'DEALTRANSA
          CTION' (Cost=4 Card=1 Bytes=16)

  22   21                   INDEX (RANGE SCAN) OF 'IDX_DT_DTRNCD' (NON
          -UNIQUE) (Cost=3 Card=1)

Statistics
----------------------------------------------------------
         56  recursive calls
          0  db block gets
        757  consistent gets
        226  physical reads
          0  redo size
       1287  bytes sent via SQL*Net to client
       3688  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed       

By seeing the Explain can any one of u give me the tips for reducing the cost of the Query. I will be very much thankful to you people.I have created indexes to reduce the cost but gone in vain. So ...plz plz help me  in reducing the cost.

 

Thanks and Regs

Raghukalyan.G

 

 
Re: Regarding Query Tuning [message #65191 is a reply to message #65190] Fri, 04 June 2004 04:51 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
"Perfectly tuned" should not be your goal. You could spend eternity "perfectly tuning" a query. You can always make it run 1% faster, but attaining those subsequent gains will take increasing effort over time.

You need to set hard goals (e.g., "this query needs to return its result set within 3 seconds at least 95% of the time), and benchmark to those.

Don't tune a query by looking at cost.

SeeA.
Re: Regarding Query Tuning [message #65208 is a reply to message #65190] Mon, 14 June 2004 06:31 Go to previous message
Kallol Chakraborty
Messages: 1
Registered: June 2004
Junior Member
Hi Raghu,

Can u provide me the table size details
i.e number of rows and view details.

I will be able to sort out your problem.

Regards
-Kallol
Previous Topic: SQL Taking ForEver
Next Topic: urgent- How to find invalid objects in oracle
Goto Forum:
  


Current Time: Thu Mar 28 07:58:21 CDT 2024