-
Query tuning
does anyone has any advises to optimize this ???
Code:
SQL Statement from editor:
SELECT
MS.MTR_NO,
MS.MTR_STATION_ID,
MS.HV_BASIS_CD,
DT1.DT AS CONNECT_DT,
DT2.DT AS HOOK_UP_DT,
DT3.DT AS LOW_PRESSURE_DT,
QUERY1.MTR_GRP_ID AS ZONE_CD,
QUERY2.MTR_GRP_ID AS PROJECT_CD,
(CASE
WHEN MS.MTR_CONN_STATUS_CD = 'I' THEN 1
ELSE 0 END) AS INACTIVE_IND
FROM
TAM_MTR_BLA MS
LEFT OUTER JOIN TAM_MTR_DT DT1
ON MS.MTR_NO = DT1.MTR_NO
AND 'CON' = DT1.DT_TYPE_CD
LEFT OUTER JOIN TAM_MTR_DT DT2
ON MS.MTR_NO = DT2.MTR_NO
AND 'IFLOW' = DT2.DT_TYPE_CD
LEFT OUTER JOIN TAM_MTR_DT DT3
ON MS.MTR_NO = DT3.MTR_NO
AND 'LP' = DT3.DT_TYPE_CD
LEFT OUTER JOIN (
SELECT
GL1.MTR_GRP_ID,
GL1.MTR_NO,
GL1.EFF_DT_FROM,
GL1.EFF_DT_TO
FROM
TAM_MTR_GRP_LIST GL1
JOIN TAM_MTR_GRP MG1
ON
GL1.MTR_GRP_ID = MG1.MTR_GRP_ID
AND 'ZON' =
MG1.MTR_GRP_TYPE_CD) QUERY1
ON MS.MTR_NO = QUERY1.MTR_NO
AND '25-AUG-2004' BETWEEN
QUERY1.EFF_DT_FROM AND QUERY1.EFF_DT_TO
LEFT OUTER JOIN (
SELECT
GL2.MTR_GRP_ID,
GL2.MTR_NO,
GL2.EFF_DT_FROM,
GL2.EFF_DT_TO
FROM
TAM_MTR_GRP_LIST GL2
JOIN TAM_MTR_GRP MG2
ON
GL2.MTR_GRP_ID = MG2.MTR_GRP_ID
AND 'PRJ' =
MG2.MTR_GRP_TYPE_CD) QUERY2
ON MS.MTR_NO = QUERY2.MTR_NO
AND '25-AUG-2004' BETWEEN
QUERY2.EFF_DT_FROM AND QUERY2.EFF_DT_TO
WHERE
MS.MTR_RECORD_STATUS_CD = 'CUR'
AND MS.MTR_STATION_ID = '2041'
AND MS.EFF_DT_FROM = '25-AUG-2004'
------------------------------------------------------------
Statement Id=4203132 Type=
Cost=2.64039087471493E-308 TimeStamp=23-09-04::14::03:31
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 347
(24) HASH JOIN OUTER
Est. Rows: 1 Cost: 347
(18) HASH JOIN OUTER
Est. Rows: 1 Cost: 176
(12) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 5
(9) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 4
(6) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 3
(3) TABLE ACCESS BY INDEX ROWID ESUITE.TAM_MTR_BLA [Analyzed]
(3) Blocks: 4,942 Est. Rows: 1 of 133,736 Cost: 2
Tablespace: data_ts
(2) NON-UNIQUE INDEX RANGE SCAN ESUITE.TAM_MTR_indx_ts [Analyzed]
Est. Rows: 11 Cost: 1
(5) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_DT [Analyzed]
(5) Blocks: 2 Est. Rows: 1 of 166 Cost: 1
Tablespace: data_ts
(4) UNIQUE INDEX UNIQUE SCAN BLA.PK_TAM_MTR_DT [Analyzed]
Est. Rows: 4
(8) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_DT [Analyzed]
(8) Blocks: 2 Est. Rows: 1 of 166 Cost: 1
Tablespace: data_ts
(7) UNIQUE INDEX UNIQUE SCAN BLA.PK_TAM_MTR_DT [Analyzed]
Est. Rows: 4
(11) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_DT [Analyzed]
(11) Blocks: 2 Est. Rows: 1 of 166 Cost: 1
Tablespace: data_ts
(10) UNIQUE INDEX UNIQUE SCAN BLA.PK_TAM_MTR_DT [Analyzed]
Est. Rows: 4
(17) VIEW (Embedded SQL)
Est. Rows: 40,045 Cost: 151
(16) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_GRP_LIST [Analyzed]
(16) Blocks: 1,831 Est. Rows: 1,578 of 254,856 Cost: 6
Tablespace: data_ts
(15) NESTED LOOPS
Est. Rows: 40,045 Cost: 151
(13) NON-UNIQUE INDEX FULL SCAN BLA.IDX1_TAM_MTR_GRP [Analyzed]
Est. Rows: 25 Cost: 1
(14) UNIQUE INDEX RANGE SCAN BLA.PK_TAM_MTR_GRP_LIST [Not Analyzed]
Est. Rows: 1,583 Cost: 1
(23) VIEW (Embedded SQL)
Est. Rows: 40,045 Cost: 151
(22) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_GRP_LIST [Analyzed]
(22) Blocks: 1,831 Est. Rows: 1,578 of 254,856 Cost: 6
Tablespace: data_ts
(21) NESTED LOOPS
Est. Rows: 40,045 Cost: 151
(19) NON-UNIQUE INDEX FULL SCAN BLA.IDX1_TAM_MTR_GRP [Analyzed]
Est. Rows: 25 Cost: 1
(20) UNIQUE INDEX RANGE SCAN BLA.PK_TAM_MTR_GRP_LIST [Not Analyzed]
Est. Rows: 1,583 Cost: 1
Last edited by learning_bee; 09-23-2004 at 03:18 PM.
-
-
Analyze PK_TAM_MTR_GRP_LIST
-
thanks Jeff and slimdave
the cost is higher and oracle does a FTS on the table after I analyze the table and PK.....
Code:
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 603
(22) HASH JOIN OUTER
Est. Rows: 1 Cost: 603
(17) HASH JOIN OUTER
Est. Rows: 1 Cost: 304
(12) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 5
(9) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 4
(6) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 3
(3) TABLE ACCESS BY INDEX ROWID ESUITE.TAM_MTR_BLA [Analyzed]
(3) Blocks: 4,942 Est. Rows: 1 of 133,736 Cost: 2
Tablespace: data_ts
(2) NON-UNIQUE INDEX RANGE SCAN ESUITE.TAM_MTR_indx_ts [Analyzed]
Est. Rows: 11 Cost: 1
(5) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_DT [Analyzed]
(5) Blocks: 2 Est. Rows: 1 of 166 Cost: 1
Tablespace: data_ts
(4) UNIQUE INDEX UNIQUE SCAN BLA.PK_TAM_MTR_DT [Analyzed]
Est. Rows: 4
(8) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_DT [Analyzed]
(8) Blocks: 2 Est. Rows: 1 of 166 Cost: 1
Tablespace: data_ts
(7) UNIQUE INDEX UNIQUE SCAN BLA.PK_TAM_MTR_DT [Analyzed]
Est. Rows: 4
(11) TABLE ACCESS BY INDEX ROWID BLA.TAM_MTR_DT [Analyzed]
(11) Blocks: 2 Est. Rows: 1 of 166 Cost: 1
Tablespace: data_ts
(10) UNIQUE INDEX UNIQUE SCAN BLA.PK_TAM_MTR_DT [Analyzed]
Est. Rows: 4
(16) VIEW (Embedded SQL)
Est. Rows: 38,313 Cost: 280
(15) NESTED LOOPS
Est. Rows: 38,313 Cost: 280
(13) TABLE ACCESS FULL BLA.TAM_MTR_GRP_LIST [Analyzed]
(13) Blocks: 1,831 Est. Rows: 247,618 of 248,630 Cost: 280
Tablespace: data_ts
(14) NON-UNIQUE INDEX RANGE SCAN BLA.IDX1_TAM_MTR_GRP [Analyzed]
Est. Rows: 1
(21) VIEW (Embedded SQL)
Est. Rows: 38,313 Cost: 280
(20) NESTED LOOPS
Est. Rows: 38,313 Cost: 280
(18) TABLE ACCESS FULL BLA.TAM_MTR_GRP_LIST [Analyzed]
(18) Blocks: 1,831 Est. Rows: 247,618 of 248,630 Cost: 280
Tablespace: data_ts
(19) NON-UNIQUE INDEX RANGE SCAN BLA.IDX1_TAM_MTR_GRP [Analyzed]
Est. Rows: 1
what is your thoughts about this???
-
Cost is meaningless -- how many resources did it use and how long did it take? You might like to make sure all your other statistics are up to date also.
And what version are you on? Have you gathered SYSTEM stats for 9i+, or set optimizer_index_cost_adj if on 8i?
-
Slimdave,
I am on 9i, what do you mean by "Have you gathered SYSTEM stats for 9i" can you light out some hint????
thanks so much,
-
Originally posted by learning_bee
Slimdave,
I am on 9i, what do you mean by "Have you gathered SYSTEM stats for 9i" can you light out some hint????
thanks so much,
Search/read on dbms_stats.gather_system_stats, optimizer_index_caching, optimizer_index_cost_adj
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|