Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Location
    Finland
    Posts
    9

    Unanswered: Slow query in oracle 9.2.0

    Hi,

    We are testing oracle 9.2.0 with baan (erp) software. On our new server everything is much (10x) faster than before, except one query. That is 10 times slower than in old machine with oracle 8.0.5.


    I uploaded a zip file to this thread. It's got four files:
    next_record_current_server.txt - tkprof from oracle 8.0.5
    next_record_new_server.txt - tkprof from oracle 9.2.0.5
    sp_4_5_new_server.lst - staspack report from new server
    sp_4_5_2747255744_new_server.lst - statspack report of the sql query

    The column 'query' is very high in tkprof raport of the new server.
    Also buffer gets per execute is very high in statspack report of the new server.
    But what might be the cause? Is optimizer going wild ? But only in this one query?

    I'd appreciate any help on this matter. (We should go live next weekend ...)


    / Vesa Muhonen
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    Can you post a 10053 trace of the optimizer? To collect this you need two log in to the server first log in as the executing user (make sure they have a plan table) the second log in is as sys then find the sid and serial# from the other session. (Select sid, serial#, username from v$session where username = <>) Then as the sys user execure dbms_system.set_ev(sid, serail#,10053,1,''); the last is two single quotes. Then in the first session take the SQL in question and do an 'EXPLAIN PLAN FOR ...' Then turn off tracing dbms_system.set_ev(sid, serail#,10053,1,'') or kill the first window. Do not TKPROF this trace file it is already formated. For safty sake do this on a non production server but one that does give you the same explain plans.

  3. #3
    Join Date
    Feb 2003
    Location
    Finland
    Posts
    9
    Hi,

    I opened a case to oracle metalink, and got a solution for this case.

    We've hit <BUG:3455017> - Bad First_Rows Plan In 920.
    It is considered as _not_ a bug, but a new feature in oracle 920.

    Optimizer will avoid the ORDER BY clause.
    With this parameter this can be changed:
    alter session set "_sort_elimination_cost_ratio"

    Setting this low enough will still choose ORDER BY and query will be faster.
    In our case.
    alter session set "_sort_elimination_cost_ratio"=5
    is ok.




    / Vesa

Posting Permissions

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