Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: explain plan

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: explain plan

    Hi,

    I'm checking a query which is running very slow. Though there are indexes on the table, its not using the indexes. I couldn't understand why its not using it.
    Any suggestions on this.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    First of all have the tables been analyzed recently?
    Then are the indexes appropriate for the criteria used in the queries?
    Are the criteria selective, i.e. if you are checking for value x but value x occurs in say 50% of the rows Oracle will prefer a full table scan.
    If all of the above are fine then check if the stats in user_tab_columns are accurate, if not try increasing the sample size.

    There could be lots of other reasons why its not using the indexes but this would be a good initial set of checks.

    Alan

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    The tables are analyzed. The table is now making an index scan but there are 3 nested loops. What changes can I make to it so that the query makes a merge or hash join?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We (TINW) don't have crystal ball & we are NOT looking over your shoulder.
    You do NOT show us (TINU) the table description.
    You do NOT show us the actual query.
    You expect solutions without providing any clues or other useful information.
    Your On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It depends. What oracle version is this? are you using manual or automatic pga memory setting ? Is the parameter hash_join_enabled set to TRUE ?

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    I know its difficult to help me without providing the query, but its confidential and I couldn't change anything to the code so that I can post it here.

    I'm using Oracle 10g. How do I check for these conditions? I saw in init.ora but it just shows

    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    add to initSID.ora the following line:
    _make_SQL_faster=TRUE
    It is a new undocument parameter in 10g.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by anacedent
    add to initSID.ora the following line:
    _make_SQL_faster=TRUE
    It is a new undocument parameter in 10g.
    LOL.

    Seriously nandinir, check for workarea_size_policy. If you are on AUTO, make sure you have a proper value of pga_aggregate_target (which dictates a target for the *_area_size parameters). If you are not, make sure you have set sort_area_size/hash_area_size properly. Also, hash_join_enabled must be TRUE. You can query those values from v$parameter.

    But.. before you change anything there.. please, answer me this.

    Why do you think the NESTED LOOP is the bottleneck here?
    Have you tkprof this?

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    My manager thinks that Nested loops are bad here, and would be a better choice to have a merge or Hash join. Each of the nested loop has a cost of 2500 approx.
    I didnt do a TKPROF

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Unless you try different joins you dont really know. Try putting in hints and switching on tracing to help identify the optimal join operation.

    Once you identify the best plan you can try different dbms_stats options or init.ora parameters to reach the desired plan without hints. If you still cant get the ideal plan without hints then you may have to resort to hints, especially if you know the data distribution isnt going to change or fix the plan using outlines.

    Alan

  11. #11
    Join Date
    Jul 2005
    Posts
    276
    What about HASH_JOIN_ENABLED in Oracle 10g? Is it Obsolete?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Are you unwilling or incapable of reading the fine manuals found at http://tahiti.oracle.com; specifically the Oracle Reference manual which answers your question?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I would like to see a TKPROF of this. NESTED LOOPS are not "always" bad, especially if the access is made by INDEX (it is a serial operation anyways; you read the index leaf block; go to table; read index; go to table, etc). HASH joins makes sensse when there is some amount of data that can be merge together in memory.

    Do the TKPROF and hide the SQL if you want. Just show us the statistics.

  14. #14
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by nandinir
    Hi,

    I'm checking a query which is running very slow. Though there are indexes on the table, its not using the indexes. I couldn't understand why its not using it.
    Any suggestions on this.
    Quote Originally Posted by nandinir
    The table is now making an index scan but there are 3 nested loops. What changes can I make to it so that the query makes a merge or hash join?
    The original problem was that you wanted the query to use an index. Now the problem is that you don't?

    Just checking

    PS HASH_JOIN_ENABLED became obsolete in 10.1. Prior to that the default and recommended value was TRUE. I don't know why anyone would set it to FALSE but I guess you never know.
    Last edited by WilliamR; 10-29-06 at 08:30.

  15. #15
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by JMartinez
    check for workarea_size_policy. If you are on AUTO, make sure you have a proper value of pga_aggregate_target (which dictates a target for the *_area_size parameters).
    Strictly speaking it bypasses the *_area_size settings entirely. They are only used if workarea_size_policy = MANUAL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •