tunning

[learning_bee]

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

[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 ]

[tamilselvan]

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

[Mr.Hanky]

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.