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

How do you gather statistics? Do you per chance gather histograms ( "for all indexed columns size skewonly" or somethink like it?). I have the feeling you are setting yourself up for the bind variable peeking trap (you also seem to run with cursor_sharing=force) and appear to be falling into it.

BTW. if you change something in the sql, like the case of the word "AND" to "and" then it IS a different sql and will get parsed anew.

Marquez, Chris wrote:
> -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
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l