This content has been marked as final. Show 5 replies
let me know how to get back that same execution plan as old DBYou already have same execution plan as your old database. Since execution plan is so simple and same in both versions, you would need to think out of the box.
First go for session level 10046 trace. Enable trace for session, run your query and then disable the trace after 10 minutes ( no need to run for whole 2 hours).
TKPROF and share the output here on the forum.
I suspect that either you are facing some cluster level wait event which is slowing down the query, or, you might be facing "direct path read" wait event (I faced this problem with me, but, in your case, it may not be the issue).
Also take a AWR snapshot before running the query, and another manual snapshot after you finish the trace and see what are top wait events and are there any buffer waits in TABLESPACE IO STATISTICS section of AWR.
*please paste your code/outputs enclosed in
tags to make it readable* Salman
Problem resolved .. by re-gathering sys stats & setting it as old prod usingRegathering and setting are 2 different things. Which one helped you? You shold not be setting it same as your previous server if both hardwares have difference in physical configuraiton and speed/number of CPUs etc. Youa re supposed to collect fresh stats which "should" represent your current hardware.
If you are setting same like your previous server, it may help you in this query, but may create problems for your overall system.
Yes , It has been taken care .
Due to diffrence in CPU speed of old/New DB this problem arised , we have auto sys stats which is also improving query day by day.
Just for this particular query which was sev1 for us (One time requirement), we did old db stat setting, However here onwards totally dependent on new sys stats
Edited by: moreajays on Dec 28, 2012 12:00 PM