Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Location
    Palmer, Alaska USA
    Posts
    13

    Question Unanswered: DISTINCT query takes very long time in 10g

    Oracle 10.1.0.3.0
    AIX 5L 64Bit 8gb ram 4processors
    I have a select DISTINCT query that takes forever and a day to run in Oracle 10g, but I can take the exact same query into Oracle 8i and it runs in just a few seconds. The tables are exactly the same, with exactly the same number of rows, and the exact same indexes. Here is the query:

    select distinct
    meridian,township,range,note
    from recnet_notes, recnet_note_list
    where note between
    lower('ak'||from_pg||'.tif') and lower('ak'||to_pg||'.tif')

    It is not a complicated query at all. I might mention that in every other way the 10g database is lightspeeds ahead of our 8i database, so to me it doesn't make any sense why this query is any different. It seems that maybe an initialization parameter in 10g needs to be increased or something, but I am not sure, as most everything is as it comes straight out of the box. In 8i I would be tempted to adjust the sort_area_size parameter, but 10g says its retained for backward compatibility only.
    Anyway, any suggestions or ideas would be greatly appreciated!
    TIA
    - Marty
    Don't take my word for it ... trial and error is my methodology (mostly error)...and then I forget and go thru it all again!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    it's a common question on here... "my query runs great on a [version|box|instance] but runs slow on another [version|box|instance].

    I don't have a 10g here to play with (my customers prefer that you guys find all the problems before they put it into production ) but it is most likely down to statistics. Have you run the same statistics gathering stuff on the 10g instance as on the 8i?

    My syntax is probably deprecated on 10, like it never worked well enough on 8i, but anyway....

    analyse table <tablename> compute statistics for all indexes;

    [for all indexed colums]

    etc, etc - there are many variations on the theme.

    DBMS_STATS.GATHER_<various>_STATS is the (currently) preferred way but could be deprecated by the morning, so it's worth checking your docs.

    See how you get on and do let us know.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please post the EXPLAIN_PLAN for each of the two queries.
    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.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    never seen a BETWEEN clause against two strings.
    I thought it was supposed to be used against numbers or dates ...
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2003
    Location
    Palmer, Alaska USA
    Posts
    13
    Here is the explain plan from Oracle 8i:
    Plan Table
    --------------------------------------------------------------------------------

    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |

    --------------------------------------------------------------------------------

    | SELECT STATEMENT | | | | | | |

    | SORT UNIQUE | | | | | | |

    | NESTED LOOPS | | | | | | |

    | TABLE ACCESS FULL |RECNET_NO | | | | | |

    | INDEX RANGE SCAN |RECNET_NO | | | | | |

    --------------------------------------------------------------------------------


    8 rows selected.


    Here is the explain plan from Oracle 10g:
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3497287575

    --------------------------------------------------------------------------------
    ------------------

    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
    (%CPU)| Time |

    --------------------------------------------------------------------------------
    ------------------


    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 4593K| 236M| | 62321
    (2)| 00:12:28 |

    | 1 | SORT UNIQUE | | 4593K| 236M| 598M| 62321
    (2)| 00:12:28 |

    | 2 | MERGE JOIN | | 4593K| 236M| | 414
    (3)| 00:00:05 |

    | 3 | SORT JOIN | | 95971 | 1593K| | 382
    (2)| 00:00:05 |

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    | 4 | INDEX FULL SCAN | RECNET_NOTES_NDX | 95971 | 1593K| | 382
    (2)| 00:00:05 |

    |* 5 | FILTER | | | | |
    | |

    |* 6 | SORT JOIN | | 19145 | 691K| | 32
    (13)| 00:00:01 |

    | 7 | TABLE ACCESS FULL| RECNET_NOTE_LIST | 19145 | 691K| | 29

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    (4)| 00:00:01 |

    --------------------------------------------------------------------------------
    ------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    5 - filter("NOTE"<=LOWER('ak'||"TO_PG"||'.tif'))
    6 - access("NOTE">=LOWER('ak'||"FROM_PG"||'.tif'))

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    filter("NOTE">=LOWER('ak'||"FROM_PG"||'.tif'))

    21 rows selected.

    Not much info from the 8i explain plan (not sure why). The query returns in about 30sec in 8i, over 20min in 10g. The recnet_notes table has around 96000 rows and the recnet_note_list has just over 19000 rows. I am looking into the analyze table options now.
    Don't take my word for it ... trial and error is my methodology (mostly error)...and then I forget and go thru it all again!

  6. #6
    Join Date
    Mar 2003
    Location
    Palmer, Alaska USA
    Posts
    13
    It was clear from the trace file that the query was going for a bad plan in 10g that made the query very slow. Using the Use Hash hint to the optimizer made the query extremely fast:

    select /*+ USE_HASH(recnet_note_list recnet_notes) */
    distinct meridian,township,range,note
    from recnet_note_list, recnet_notes
    where note between lower('ak'||from_pg||'.tif') and lower('ak'||to_pg||'.tif')

    - Marty
    Don't take my word for it ... trial and error is my methodology (mostly error)...and then I forget and go thru it all again!

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Also dont forget that the optimiser is affected by your init.ora parameters i.e. db_file_multiblock_read_count, optimizer* etc) and also your hardware configuration (i.e. no.of CPUs, memory etc).

    Alan

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    There doesn't seem to be any cost info in the 8i plan (enclosing preformatted text in [CODE] tags would make it easier to read) so maybe it was using RBO, which favours indexes and nested loops. The RBO is desupported in 10g so maybe it has switched to CBO with incomplete stats. Also from 9i onwards the optimizer takes CPU into account, which could change some plans.

Posting Permissions

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