optimizer (9i) solaris

From: Mark <marksgnga_at_yahoo.com>
Date: 2 Dec 2003 20:33:15 -0800
Message-ID: <41d52a1a.0312022033.11eb99c1@posting.google.com>

Summary of situation:

select statement runnnig fast/ok on 8.0.5/Sun 220R
upgrade app to 9i/Sun v480...screams
select statement runnnig fast (for weeks) on v480 server
suddenly slow for no evident reason (no data/application changes)
researched...traced/explain-planned
...added ORDERED hint...fast again
wondered/asked about possible causes...no help from DBA...shelved it/drove on
server crashed/rebooted two weeks later
out of curiosity, tried orig statement without ORDERED...fast as day one.

First, I am not the DBA for this box (I have user level access). I ask the DBA if he could suggest what could cause a sudden shift in performance. He had no (useful) answer. There may be a lot of possibilities here. I realy want to understand what is going on here. I can provide more detail if necessary. I partly suspect the DBA made a change and either doesn't know it or won't fess up. I'm hoping there is some other explaination ... 'learning optimizer'(ha!), etc. Why did the reboot change/reset things?

Thanks in an advance for any guidance,
Mark Received on Tue Dec 02 2003 - 22:33:15 CST