Home » RDBMS Server » Performance Tuning » How to understand query plan
How to understand query plan [message #325477] Fri, 06 June 2008 05:38 Go to next message
Manoj.Gupta.91
Messages: 217
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
Re: How to understand query plan [message #325485 is a reply to message #325477] Fri, 06 June 2008 06:03 Go to previous messageGo to next message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member
Hi,

maybe this will help
Re: How to understand query plan [message #325486 is a reply to message #325477] Fri, 06 June 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Performance Tuning Guide
Chapter 19 Using EXPLAIN PLAN

Regards
Michel
Re: How to understand query plan [message #325491 is a reply to message #325486] Fri, 06 June 2008 06:23 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Study DBMS_STATS Package
And CBO (Cost Base Optimizer)

http://www.dbasupport.com/oracle/ora9i/CBO1_4.shtml
Previous Topic: query takes so much time to update
Next Topic: Partitioning decision
Goto Forum:
  


Current Time: Mon Dec 05 11:19:53 CST 2016

Total time taken to generate the page: 0.06945 seconds