| Regarding Query Tuning [message #65190] |
Thu, 03 June 2004 20:32  |
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  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
