DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: tunning

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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 ]

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    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
  •  


Click Here to Expand Forum to Full Width