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 18.104.22.168
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 ...)
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.