FreeLists / oracle-l / RE: Very Strange Query Access Plan

["Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>]

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.