Query Tunning
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Query Tunning

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Question Query Tunning

    Hi All,

    I have to tune this query. I have no clue how\where to start this.

    This query is NOT comin out AT ALL...
    Please give ur valuable outputs

    Attached my the query and plan

    Many Thanks in advance
    Attached Files Attached Files
    Cheers!
    OraKid.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Try using subqueries.

    Code:
    SELECT ...
      FROM ( SELECT ...
               FROM mytable
              WHERE column  = constant AND
                    column2 In ()etc) table_name,
    Also try joining together tables that have an inner join and then join to that the outer join tables.

    Code:
    SELECT ...
      FROM ( SELECT ...
               FROM mytable
              WHERE column  = constant AND
                    column2 In ()etc) table_name
      LEFT OUTER JOIN tablec ON
           join clause
    You can also do a subquery in the outer join. My strategy is to use subqueries to eliminate rows and can be eliminated before doing a join of any kind, and to put the outer joins at the end. Doing so should cut down on the nested loops which tend to be costly, and hopefully replace them with hash joins. IMHO Hash is best.
    this space intentionally left blank

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Query Tunning

    Define

    This query is NOT comin out AT ALL...
    Is this PeopleSoft? NVision perhaps?
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027

    Re: Re: Query Tunning

    Originally posted by stmontgo
    Define

    Is this PeopleSoft? NVision perhaps?
    I should have guessed this was some sort of sappy application.
    this space intentionally left blank

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Re: Re: Query Tunning

    Originally posted by gandolf989
    I should have guessed this was some sort of sappy application.
    Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guess
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027

    Re: Re: Re: Re: Query Tunning

    Originally posted by stmontgo
    Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guess
    I'm not qualified to make that kind of guess with any accuracy. According to www.dba-oracle.com:

    The _optimizer_search_limit and _optimizer_max_permutations parameters work together, and the optimizer will generate possible table joins permutations until the value specified by _optimizer_search_limit or _optimizer_max_permutations is exceeded. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining nine tables together will exceed the optimizer_search_limit but still may spend expensive time attempting to evaluate all 362,880 possible table join orders (nine factorial) until the optimizer_max_permutations parameter has exceeded its default limit of 80,000 table join orders.
    And according to www.orafaq.com:
    NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
    But why does this query suck?
    Last edited by gandolf989; 11-16-2004 at 12:07 AM.
    this space intentionally left blank

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi Guys thanks for ur effort

    My boss gave this query and wanted me to tune. I don't understand any thing...
    Cheers!
    OraKid.

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Its

    optimizer_max_permutations = 2000

    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    PL/SQL Release 9.2.0.4.0 - Production
    CORE 9.2.0.3.0 Production
    TNS for HPUX: Version 9.2.0.4.0 - Production
    NLSRTL Version 9.2.0.4.0 - Production
    Attached Files Attached Files
    Cheers!
    OraKid.

  9. #9
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Re: Re: Re: Re: Query Tunning

    And according to www.orafaq.com:
    oh boo hoo! a little alter session never hurt nobody

    SQL> alter session set optimizer_max_permutations=1999;

    Session altered.

    SQL>


    alas he has not answered my questions on the configuration

    > Is this PeopleSoft

    > What do you mean by not coming out at all? Do you wait 2 minutes and ctrl+c, 20 minutes
    I'm stmontgo and I approve of this message

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Originally posted by balajiyes
    Hi Guys thanks for ur effort

    My boss gave this query and wanted me to tune. I don't understand any thing...
    Then it would benefit you to rewrite it using the advice I gave you above, and then test that query and see if the explain plan changed. By the way you are using manual undo and archivelog is turned off. You might want to do some research and change to automatic undo and turn on archivelog.
    this space intentionally left blank

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