Mutating (SQL) execution plan!?...is that possible...strangest thing I have every seen

-Environment:

Oracle 9205 RAC / RHEL3
cursor_sharing =3D FORCE
Table MONITORING ON and STATISTICS updated every night (for tables that need it).
Perl / Web based application.

This is strangest thing I have every seen. This query uses an index on "id" and return in 2 seconds...I tested it and normally our end user experience confirms that. The explain plan confirms this. When forcing a full table scan on this 2+ mill table it take < 2 min.

SELECT COUNT(*) AS COUNT
FROM MEMBERS
WHERE UPPER (active) =3D :"SYS_B_0"=20
AND id =3D :"SYS_B_1"

He is the strange part. We are starting notice that after some (up) time (couple of days?) the query start degrading...slows down. Instead of taking seconds like when the db is first started-up, it starts taking 2 minutes ....the same time as a full table scan!? When I watch the session, I see its wait event is for "db file scattered read"...Full Table Scan, right?

This is where things get really strange. If developer changes something trivial, like the case of the word and, like; "AND" to "and", in he Perl code and runs it again and it takes only seconds!!!
Now I realize that maybe Oracle sees this as technically new SQL, but it is the same query and they should use the same plan!? Finally as soon as we flush the shared pool the original query (in the program code) starts performing within seconds again!?

What do you think?

Thanks...please reply directly to me as well as to the list; cmarquez_at_aarp.org.

Chris Marquez
Oracle DBA
HEYMONitor(tm) - heymonitor.com
"Oracle Monitoring & Alerting Solution"
--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 10 2005 - 09:11:40 CST