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

From: Marquez, Chris <CMarquez_at_aarp.org>
Date: Thu, 10 Mar 2005 09:42:35 -0500
Message-ID: <7E412C164E6ECB468834A39F31E6E0D4074B5E1A@mbs06dc.na.aarp.int>

>>Do you per chance gather histograms ( "for=20 >>all indexed columns size skewonly" or somethink like it?). Yes, Yes, Yes, we do!
At the recommendation of Oracle *guru*? (web site article).

>>(you also seem to run with cursor_sharing=3Dforce)=20 Yes again.

>>I have the feeling you are setting yourself up for the=20 >>bind variable peeking trap and appear to be falling into it. What the heck is "bind variable peeking trap"?

Thanks...any suggestions?

Chris

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]=20 Sent: Thursday, March 10, 2005 9:34 AM
To: Marquez, Chris
Cc: oracle-l_at_freelists.org
Subject: 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=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"

to "and" then it IS a different sql and will get parsed anew.

Marquez, Chris wrote:
> -Environment:
> Oracle 9205 RAC / RHEL3
> cursor_sharing =3D3D FORCE
> Table MONITORING ON and STATISTICS updated every night (for tables=20
> that need it). Perl / Web based application.

>=20

> 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=20
> confirms this. When forcing a full table scan on this
> 2+ mill table it take < 2 min.

>=20

> SELECT COUNT(*) AS COUNT
> FROM MEMBERS
> WHERE UPPER (active) =3D3D :"SYS_B_0"=3D20
> AND id =3D3D :"SYS_B_1"

>=20

> 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.

>=20

> Chris Marquez
> Oracle DBA
> HEYMONitor(tm) - heymonitor.com
> "Oracle Monitoring & Alerting Solution"
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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

Received on Thu Mar 10 2005 - 09:45:54 CST