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=20
peeking trap (you also seem to run with cursor_sharing=3Dforce) and =
appear

to be falling into it.

BTW. if you change something in the sql, like the case of the word "AND"

> He is the strange part. We are starting notice that after some (up)=20> time (couple of days?) the query start degrading...slows down. Instead

> of taking seconds like when the db is first started-up, it starts=20> 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=20> read"...Full Table Scan, right?

>=20

> 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=20> only seconds!!! Now I realize that maybe Oracle sees this as=20> technically new SQL, but it is the same query and they should use the=20> same plan!? Finally as soon as we flush the shared pool the original=20> query (in the program code) starts performing within seconds again!?

>=20

> What do you think?

>=20

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