RE: Very Strange Query Access Plan

Sounds like the same problem I see almost every week with bind variable peeking in my Baan & Oracle 9i+ systems - maybe someone ran the query(ies) with a bind variable value that indicated a large amount of rows would be returned (e.g. WHERE ssn > :b1; and they set b1 := 0), so Oracle chose a full table scan as it should, but now that explain is loaded in the shared pool and used for all subsequent executions, regardless of what they plug in for their bind variables. If that's the case, a quick fix is to grant permissions on the table (just grant select to some harmless user, or grant and then revoke) so all queries on that table will be invalidated from the shared pool and then the next time it gets executed it will be hard parsed and bind variables will be peeked again. In the long term, you have to modify the query so Oracle can identify between the high and low selectivity versions, or just use a stored outline. See Metalink #387394.1 for more info.  

Regards,
Brandon  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F (LABOR)   Running on 9.2.0.7  

Yesterday, queries against this table used the index. Today, they do not.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l