| How to understand query plan [message #325477] |
Fri, 06 June 2008 05:38  |
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
Hi All,
I'm new to tune a query, I want to know how to understand a query plan and which statics factor contributes more for faster queries
1 SELECT
2 VC_MAXIMA_CODE,
3 LTRIM(RTRIM(VC_MODEL_NO)) || LTRIM(RTRIM(VC_COLOR)) VC_MODEL_NO,
4 NVL(CH_CRITICAL_FLAG,'R') CRIT,
5 DECODE(SUBSTR(VC_MODEL_NO,9,1),
6 'C','I',
7 SUBSTR(VC_MODEL_NO,9,1)
8 ) ORD1,
9 SUBSTR(VC_MODEL_NO,8,1) ORD2,
10 DT_FIELD1,
11 VC_PRODUCT_CODE,
12 NU_PROD_RATE MRP,
13 VC_CATEGORY_NEW CT,
14 VC_CAT_SALE_PLAN
15 FROM
16 MST_PRODUCT
17 WHERE
18 VC_WATCH_TYPE = 'a002'
19 and NVL(CH_CRITICAL_FLAG,'R') LIKE 'R'
20 AND VC_COMP_CODE = '02'
21 AND VC_MODEL_NO NOT IN
22 ( SELECT VC_MODEL_NO
23 FROM MST_PRODUCT
24 WHERE VC_COMP_CODE = '02'
25 AND VC_WATCH_TYPE = 'a002'
26 AND SUBSTR(VC_MODEL_NO,7,1) = 'P'
27 AND SUBSTR(VC_MODEL_NO,9,1) IN ('Y','C')
28* )
16:02:59 SQL> /
952 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=69 Card=9 Bytes=567)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'MST_PRODUCT' (Cost=69 Card=9 Byt
es=567)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'MST_PRODUCT' (Cost=29
Card=1 Bytes=19)
4 3 INDEX (RANGE SCAN) OF 'INDEX_PRODUCT' (NON-UNIQUE) (Co
st=26 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
37235 consistent gets
265 physical reads
0 redo size
112056 bytes sent via SQL*Net to client
7410 bytes received via SQL*Net from client
65 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
952 rows processed
This is my query plan, Now how to understand Statistics
and what is the presidence of these statistics contributing for faster queries
Thanks & Regards
Manoj
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
