re: puzzling performance of query

[hreddy007]

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

[davey23uk]

no version, no explain plans, no table structures, no data sizes = no help

[gandolf989]

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.

[tamilselvan]

========
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

[davey23uk]

similar != same

he didnt say they were the same

[khussain]

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.