Query tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query tuning

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    don't use outer join.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Analyze PK_TAM_MTR_GRP_LIST
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Oct 2003
    Posts
    312
    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???

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Oct 2003
    Posts
    312
    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,

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    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
  •  



Click Here to Expand Forum to Full Width