statspack引起latch free,时间服务器~

Yong Huang

If I were you, I would just patch it to 9.2.0.8. I always try to patch the database to the highest patch level within the major release number, 9.2.0.8 for 9i, 10.2.0.4 for 10g, etc.

V$sql is based on x$kglcursor. But that fixed view is further based on x$kglob, according to x$kqfdt. I never had problems querying v$sql. But if you do, you may be hitting the bug you mentioned. How large is your shared pool and how many cursors do you have now?

V$sql_plan in 9i has lots of performance or hang problems. But most can be avoided if you do not select filter_predicates and access_predicates. Here's my own note:

"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)"

When you have your problem, you can do two things. See if there's a trace file in udump (sometimes in bdump). It may contain a call stack. If not, run pstack command or AIX equivalent against the server process to get the call stack. Search on Metalink for the function near the top of the stack excluding error dumping functions (those kge* ones). If you don't know which one to focus on, post the call stack and I'll tell you.

Yong Huang

棉花糖ONE

有一个可能是对v$视图的访问执行计划较差,导致扫描了大部分的library cache,而导致library cache latch持有的时间太长,可以看看执行计划

secooler

将运行正常的服务器与这台服务器上的v$sql视图中的数据量比较一下，进而比较一下具体SQL的执行计划，看看会不会有新的收获。

tiantianchifan

谢谢各位~ 我回头试一下

cc59

这个bug我也遇到过，整得连节点都给hang住了。

recraete sp也可以解决。 可以看一下是否有synonmy是invalid的。