RE: Mutating (SQL) execution plan!?...is that possible...stranges t thing I have every seen
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'CMarquez@xxxxxxxx'" <CMarquez@xxxxxxxx>
- Date: Thu, 10 Mar 2005 10:01:23 -0500
Wolfgang is the best!!! Talk about remote diagnostics!!!!
-----Original Message-----
From: Marquez, Chris [mailto:CMarquez@xxxxxxxx]
Sent: Thursday, March 10, 2005 9:43 AM
To: Wolfgang Breitling
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Mutating (SQL) execution plan!?...is that possible...strangest
thing I have every seen
>>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@xxxxxxxxxxxxx]=20
Sent: Thursday, March 10, 2005 9:34 AM
To: Marquez, Chris
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx
>=20
> Chris Marquez
> Oracle DBA
> HEYMONitor(tm) - heymonitor.com
> "Oracle Monitoring & Alerting Solution"
> --
> //www.freelists.org/webpage/oracle-l
>=20
--=20
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
