re: puzzling performance of query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: re: puzzling performance of query

Hybrid View

  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Question re: puzzling performance of query

    hi! ,

    I have a query that is taking 14-15 seconds to complete in prod onsite..

    Its taking for ever to run about 45 minutes in a similar environment
    simulated at our site ..

    When I look at the explain plans they look exactly the same
    except for the order of the indexes ...!!!

    Could that be the problem for the poor performance at our site ?

    Any ideas , hints , help would be appreciated ...!!!

    i did the following on our site
    execute dbms_stats.gather_schema_stats('schema_name) ..

    thanks,
    harish
    thanks,
    harish

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    no version, no explain plans, no table structures, no data sizes = no help

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Originally posted by davey23uk
    no version, no explain plans, no table structures, no data sizes = no help
    Agreed. But if you know the order of the indexs is different, then change the order at the site running slower. Because, sometimes the order matters.
    this space intentionally left blank

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ========
    Its taking for ever to run about 45 minutes in a similar environment
    simulated at our site ..
    =========

    No 2 environments are same. You can find 100s of differences if you dig into deep.

    Tamil

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    similar != same

    he didnt say they were the same

  6. #6
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    what version are you on ?

    Have you looked to confirm that the stats were generated when you ran a "dbms_stats.gather_schema_stats('schema_name" ??


    The reason I ask is because I ran the gather schema stats (on our 9204 database), and it did not gather any stats (I did not spend too much time investigating) ... I just ran gather table stats (since there were only 5 table).

    Make sure the stats are gathered at the same level (tables, indexes, etc) on both the instances.

    Good luck.

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