-
tunning
could someone please give me some input to tune the below query???
SELECT DISTINCT ARRG_KEY, GAS.st_code ST_CODE, GAC.cnty_code CNTY_CODE,
LPAD(LOC1, 3, '0') TWP, LPAD(LOC2, 3, '0') RNG, LPAD(LOC3, 2, '0') SEC, LOC4,
SUBSTR(LOC4, 1, INSTR(LOC4, '/') - 1) QTR1,
SUBSTR(LOC4, INSTR(LOC4, '/') + 2, INSTR(LOC4, '/', 1, 2) - INSTR(LOC4, '/') - 2) QTR2,
SUBSTR(LOC4, INSTR(LOC4, '/', 1, 2) + 2, INSTR(LOC4, '/', 1, 3) - INSTR(LOC4, '/', 1, 2) - 2) QTR3,
SUBSTR(LOC4, INSTR(LOC4, '/', 1, 3) + 2, INSTR(LOC4, '/', 1, 4) - INSTR(LOC4, '/', 1, 3) - 2) QTR4
FROM I_ALL_AGREEMENTS AA, DOE.TRACT_MASTER_1 TM, DOE.LAND_CONTRACT_MASTER_7 XRF, (SELECT ID, MIN(COUNTY_REF) COUNTY_REF FROM DOE.LAND_CONTRACT_MASTER_9 GROUP BY ID) LCM, AREA_COUNTIES GAC, AREA_STATES GAS, DOE.COUNTYMASTER CM
WHERE AA.agmt_num = XRF.id AND AA.subs_num = '000' AND TM.id = XRF.tract_ref AND AA.AGMT_NUM = LCM.id AND SUBSTR(LCM.county_ref, 1, INSTR(LCM.county_ref, '*') - 1) = GAS.st_pstl_abbr --(+)
AND GAS.st_code = GAC.st_code AND LCM.county_ref = CM.id AND DESCRIPTION = GAC.cnty_name --(+)
AND TM.id IN (SELECT ID FROM DOE.TRACT_MASTER_0 WHERE MAP_TYPE_CODE = 'TR')
ORDER BY ARRG_KEY, ST_CODE, CNTY_CODE, TWP, RNG, SEC, QTR1, QTR2, QTR3;
------------------------------------------------------------
Statement Id=4203110 Type=
Cost=2.64039087471493E-308 TimeStamp=02-06-04::09::34:49
(1) SELECT STATEMENT CHOOSE
Est. Rows: 16,673 Cost: 306,700
(21) SORT UNIQUE
Est. Rows: 16,673 Cost: 204,897
(20) FILTER
(19) SORT GROUP BY
Est. Rows: 16,673 Cost: 204,897
(18) HASH JOIN
Est. Rows: 1,283,767 Cost: 1,292
(2) TABLE ACCESS FULL DOE.COUNTYMASTER [Analyzed]
(2) Blocks: 5 Est. Rows: 3,187 of 3,187 Cost: 2
Tablespace: USR_DATA_01
(17) HASH JOIN
Est. Rows: 867,127 Cost: 687
(3) TABLE ACCESS FULL POPE.AREA_COUNTIES [Analyzed]
(3) Blocks: 15 Est. Rows: 3,602 of 3,602 Cost: 4
Tablespace: data
(16) MERGE JOIN CARTESIAN
Est. Rows: 18,777 Cost: 666
(13) HASH JOIN
Est. Rows: 244 Cost: 178
(4) TABLE ACCESS FULL DOE.TRACT_MASTER_0 [Analyzed]
(4) Blocks: 15 Est. Rows: 1,967 of 11,803 Cost: 4
Tablespace: USR_DATA_01
(12) HASH JOIN
Est. Rows: 1,431 Cost: 173
(10) HASH JOIN
Est. Rows: 551 Cost: 156
(8) HASH JOIN
Est. Rows: 425 Cost: 153
(5) TABLE ACCESS FULL DOE.LAND_CONTRACT_MASTER_9 [Analyzed]
(5) Blocks: 1 Est. Rows: 417 of 417 Cost: 2
Tablespace: USR_DATA_01
(7) TABLE ACCESS BY INDEX ROWID INCOMING.I_ALL_AGREEMENTS [Analyzed]
(7) Blocks: 1,048 Est. Rows: 1,001 of 66,060 Cost: 150
Tablespace: mydata
(6) NON-UNIQUE INDEX RANGE SCAN INCOMING.AA_SUBS_NUM [Analyzed]
Est. Rows: 4 Cost: 2
(9) TABLE ACCESS FULL DOE.LAND_CONTRACT_MASTER_7 [Analyzed]
(9) Blocks: 3 Est. Rows: 1,272 of 1,272 Cost: 2
Tablespace: USR_DATA_01
(11) TABLE ACCESS FULL DOE.TRACT_MASTER_1 [Analyzed]
(11) Blocks: 73 Est. Rows: 29,986 of 29,986 Cost: 13
Tablespace: USR_DATA_01
(15) BUFFER SORT
Est. Rows: 77 Cost: 662
(14) TABLE ACCESS FULL POPE.AREA_STATES [Analyzed]
(14) Blocks: 1 Est. Rows: 77 of 77 Cost: 2
Tablespace: data_ts
Last edited by learning_bee; 06-02-2004 at 10:42 AM.
-
1. Its spelt tuning, not tunning
2. Format your code and plan - its really hard to read like that
wrap it in [ code ] and [ / code ]
-
Code:
SELECT DISTINCT ARRG_KEY, GAS.st_code ST_CODE, GAC.cnty_code CNTY_CODE,
LPAD(LOC1, 3, '0') TWP, LPAD(LOC2, 3, '0') RNG,
LPAD(LOC3, 2, '0') SEC, LOC4,
SUBSTR(LOC4, 1, INSTR(LOC4, '/') - 1) QTR1,
SUBSTR(LOC4, INSTR(LOC4, '/') + 2,
INSTR(LOC4, '/', 1, 2) - INSTR(LOC4, '/') - 2) QTR2,
SUBSTR(LOC4, INSTR(LOC4, '/', 1, 2) + 2,
INSTR(LOC4, '/', 1, 3) - INSTR(LOC4, '/', 1, 2) - 2) QTR3,
SUBSTR(LOC4, INSTR(LOC4, '/', 1, 3) + 2,
INSTR(LOC4, '/', 1, 4) - INSTR(LOC4, '/', 1, 3) - 2) QTR4
FROM I_ALL_AGREEMENTS AA,
DOE.TRACT_MASTER_1 TM,
DOE.LAND_CONTRACT_MASTER_7 XRF,
(SELECT ID, MIN(COUNTY_REF) COUNTY_REF
FROM DOE.LAND_CONTRACT_MASTER_9
GROUP BY ID) LCM,
AREA_COUNTIES GAC,
AREA_STATES GAS,
DOE.COUNTYMASTER CM
WHERE AA.agmt_num = XRF.id
AND AA.subs_num = '000'
AND TM.id = XRF.tract_ref
AND AA.AGMT_NUM = LCM.id
AND SUBSTR(LCM.county_ref, 1,
INSTR(LCM.county_ref, '*') - 1) = GAS.st_pstl_abbr --(+)
AND GAS.st_code = GAC.st_code
AND LCM.county_ref = CM.id AND DESCRIPTION = GAC.cnty_name --(+)
AND TM.id IN
(SELECT ID
FROM DOE.TRACT_MASTER_0
WHERE MAP_TYPE_CODE = 'TR')
ORDER BY ARRG_KEY, ST_CODE, CNTY_CODE,
TWP, RNG, SEC, QTR1, QTR2, QTR3;
(16) MERGE JOIN CARTESIAN - Find why the plan goes for MERGE JOIN CARTESIAN.
Tamil
Last edited by tamilselvan; 06-02-2004 at 11:57 AM.
-
Originally posted by davey23uk
1. Its spelt tuning, not tunning
2. Format your code and plan - its really hard to read like that
wrap it in [ code ] and [ / code ]
Watt hee said.
I remember when this place was cool.
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
|