Re: STATSPACK in 10g

From: Yong Huang <>
Date: Thu, 11 Feb 2010 09:38:18 -0800 (PST)
Message-ID: <>

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

Received on Thu Feb 11 2010 - 11:38:18 CST

Original text of this message