9i to 10g query performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: 9i to 10g query performance

  1. #1
    Join Date
    Oct 2008
    Posts
    69

    9i to 10g query performance

    Hi Folks, the following query runs in 25 mins on a 9i database. (9.2.0.1.0)
    On 10g it takes 19 hours.

    When you run tuning advisor on 10g it says
    'ORA-00979 not a GROUP BY expression'
    It doesn't fall over though, it keeps running.

    The query was created by someone ages ago through Business Objects.
    Can anyone see anything glaringly obvious that 10g wouldn't like about the following but 9i is perfectly happy with?

    Code:
    SELECT
      WO_BACKLOG_TREND.WOPRIORITY,
      WO_BACKLOG_TREND.DATADATE,
      SUM(WO_BACKLOG_TREND.REMHRS),
      WO_BACKLOG_TREND.LOCATION,
      NVL(WO_BACKLOG_TREND.CREWID,'BLAH'),
      NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH'),
      NVL(WO_BACKLOG_TREND.LEADCRAFT,'BLAH'),
      NVL(WO_BACKLOG_TREND.SUPERVISOR,'BLAH'),
      SUM(WO_BACKLOG_TREND.PLANHRS),
      SUM(WO_BACKLOG_TREND.ACTHRS),
      MTR_LOCN_ANC.LH2||' : '||MTR_LOCN_ANC.LD2,
      MTR_LOCN_ANC.LH3,
      MTR_LOCN_ANC.LH3||' : '||MTR_LOCN_ANC.LD3,
      MTR_LOCN_ANC.LH4||' : '||MTR_LOCN_ANC.LD4,
      MTR_LOCN_ANC.LH1,
      MTR_LOCN_ANC.LH2,
      MTR_LOCN_ANC.LH4,
      MTR_LOCN_ANC.LH5,
      MTR_LOCN_ANC.LH6
    FROM
      V_MTR_WO_BACKLOG_TREND  WO_BACKLOG_TREND,
      V_MTR_LOCN_ANC  MTR_LOCN_ANC,
      LOCATIONS  LOCATIONS3
    WHERE
      ( WO_BACKLOG_TREND.LOCATION=MTR_LOCN_ANC.LOCATION and WO_BACKLOG_TREND.SITEID=MTR_LOCN_ANC.SITEID  )
      AND  ( LOCATIONS3.LOCATION=MTR_LOCN_ANC.LOCATION and LOCATIONS3.SITEID=MTR_LOCN_ANC.SITEID  )
      AND  (
      (WO_BACKLOG_TREND.WOPRIORITY  IS NULL  
      OR   WO_BACKLOG_TREND.WOPRIORITY  IN  (1,2,3,4))
      AND  (NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH')  IS NULL  
      OR   NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH')  IN  ('PM','CM','PMA'))
      AND  MTR_LOCN_ANC.LH1  =  ('VENTURE')
      AND  MTR_LOCN_ANC.LH2  LIKE  ('KW%')
      AND  LOCATIONS3.LO2  IS NOT NULL  
      )
    GROUP BY
      WO_BACKLOG_TREND.WOPRIORITY, 
      WO_BACKLOG_TREND.DATADATE, 
      WO_BACKLOG_TREND.LOCATION, 
      NVL(WO_BACKLOG_TREND.CREWID,'BLAH'), 
      NVL(WO_BACKLOG_TREND.WORKTYPE,'BLAH'), 
      NVL(WO_BACKLOG_TREND.LEADCRAFT,'BLAH'), 
      NVL(WO_BACKLOG_TREND.SUPERVISOR,'BLAH'), 
      MTR_LOCN_ANC.LH2||' : '||MTR_LOCN_ANC.LD2, 
      MTR_LOCN_ANC.LH3, 
      MTR_LOCN_ANC.LH3||' : '||MTR_LOCN_ANC.LD3, 
      MTR_LOCN_ANC.LH4||' : '||MTR_LOCN_ANC.LD4, 
      MTR_LOCN_ANC.LH1, 
      MTR_LOCN_ANC.LH2, 
      MTR_LOCN_ANC.LH4, 
      MTR_LOCN_ANC.LH5, 
      MTR_LOCN_ANC.LH6
    /

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    What is the execution plan on 9i and on 10g? How do they differ? You have gathered statistics in 10g?

  3. #3
    Join Date
    Oct 2008
    Posts
    69
    yeah, statistics have been gathered.
    I'll run execution plan on 9i. (i joined company just a couple months ago when they were in the middle of the project)
    The 10g plan is full of full table scans on the biggest table.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool

    Quote Originally Posted by philthee View Post
    yeah, statistics have been gathered.
    ... Etc...
    The 10g plan is full of full table scans on the biggest table.
    Seems you are missing indexes and/or statistics (on indexes also)....
    Check if the indexes on 9i are the same as on 10g in number and columns indexed.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Oct 2008
    Posts
    69
    Quote Originally Posted by LKBrwn_DBA View Post
    Seems you are missing indexes and/or statistics (on indexes also)....
    Check if the indexes on 9i are the same as on 10g in number and columns indexed.
    The number of indexes is the same (for this schema) on 9i and 10g.
    The indexes all have statistics (as do the tables).

    While the above would explain slower performance (even though this is not the case) what would explain the sudden complaint about GROUP BY clause. Note that it is not enough for it to fall over but it is grumbling about it while executing the query. With the Enterprise manager console thick client thingy, if you look at the details of the session it is still processing but if you click on the SQL tab it says a database error has occured
    'ORA-00979 not a GROUP BY expression'.

    Annoyingly this is one report our users didn't test and of course this is the one that is not working.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Can you please send the execution plan on 9i and on 10g.

    Are you sure the number of records in the source tables in 9i and 10 are same?
    Same type index?
    Same partition strategy?
    Create table is same in both 9i and 10?
    Try to gather stats again on 10g.
    Cheers!
    OraKid.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You may have bumped into bug #6530141 or some variation of it.

    Open an SR with Oracle - there are work arounds but since they involve either patching or the use of hidden init.ora parameters better for you to work with Oracle face-to-face.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Oct 2008
    Posts
    69
    Well, having just come back from holiday i decided to try out something.
    In our test system i set optimizer_features_enable=9.2.0 and for the one query that i have been given it is now flying in at 20 minutes rather than 20 hours.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by philthee View Post
    Well, having just come back from holiday i decided to try out something.
    In our test system i set optimizer_features_enable=9.2.0 and for the one query that i have been given it is now flying in at 20 minutes rather than 20 hours.
    Are you aware you are turning off Ora10g optimizer features?

    Why don't hint the offending query to follow the proven execution plan and let the system to the default optimizer setting.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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