Re: STATSPACK in 10g
- From: Yong Huang <yong321@xxxxxxxxx>
- To: tanel@xxxxxxxxxx
- Date: Thu, 11 Feb 2010 09:38:18 -0800 (PST)
Speaking of v$sql_plan in 9i, I have in my own notes the following.
"Don't select * from v$sql_plan in 9i. Selecting certain columns may
cause ORA-600 [504] [row cache objects]. Filter_predicates column
should be avoided (see Bug:3545517). That column could also cause
ORA-7445 (Doc:376923.995) and ORA-3113 (Bug:4035880).
Access_predicates column may also be bad (inferred from Note:340090.1)"
Basically, if you avoid selecting filter_predicates and
access_predicates columns, it should be fine. I have no technical proof.
Just empirical observation based on many times of query of that view
on high concurrent databases.
Yong Huang
----- Original message -----
One was a bug which was fixed in 9.2.0.7 I think. When a nested loop access
was used on indexed access path to an underlying X$ under V$SQL_PLAN then
sometimes that loop went crazy and never returned from FIXED TABLE access -
which meant that the library cache latch held for that access was never
released. Anyone who wanted to use that library cache latch would get hung
and eventually it was almost the whole database.
--
//www.freelists.org/webpage/oracle-l
Other related posts: