喜欢就来多看看

重温一下：好帖

上一篇 / 下一篇 2007-02-05 00:00:00 / 个人分类：RDBMS

http://www.itpub.net/2182.html

拷贝自Oraclefan.net。
觉得非常好，就转贴到这里来了。
不会侵犯版权吧
初级会员发表于:November 01, 2001 09:04
--------------------------------------------------------------------------------
v$sgastat 中shared pool miscellaneous 值每天都在增长相应shared pool free 的值一直在减少，造成最后没有free 空间，没发连接数据库的情况。
请高手指教。

kerlion
初级会员发表于:November 01, 2001 09:17
--------------------------------------------------------------------------------
become small is OK,
It is bad for become bigger and biger
------------------
===================
WHO AM I？
===================

michael2001
初级会员发表于:November 01, 2001 09:50
--------------------------------------------------------------------------------

引用:
--------------------------------------------------------------------------------
发起人 kerlion:
become small is OK,
It is bad for become bigger and biger

--------------------------------------------------------------------------------

现在是 shared pool free 的值一直减到没法再建立连接的程度。请问你知道 shared pool miscellaneous 是什么含义？？？

yong321@yahoo.com
unregistered 发表于:November 02, 2001 02:11
--------------------------------------------------------------------------------
The most common cause is SQLs not using bind variables. alter system flush shared_pool periodically gives you relief temporarily, or set cursor_sharing to force (for 8i) if their bugs don't bother you. But the ultimate solution is to ask your developers to use bind variables. If the code is from third-party (meaning you can't control), that's out of the question.
Yong Huang

引用:
--------------------------------------------------------------------------------
发起人 michael2001:
v$sgastat 中shared pool miscellaneous 值每天都在增长相应shared pool free 的值一直在减少，造成最后没有free 空间，没发连接数据库的情况。
请高手指教。
--------------------------------------------------------------------------------

michael2001
初级会员发表于:November 02, 2001 12:10
--------------------------------------------------------------------------------

引用:
--------------------------------------------------------------------------------
发起人 yong321@yahoo.com:
The most common cause is SQLs not using bind variables. alter system flush shared_pool periodically gives you relief temporarily, or set cursor_sharing to force (for 8i) if their bugs don't bother you. But the ultimate solution is to ask your developers to use bind variables. If the code is from third-party (meaning you can't control), that's out of the question.
Yong Huang

--------------------------------------------------------------------------------

Yong Huang
我感到已接近问题的关键了,非常感谢!

Best Regards!

michael

ligeng2000
高级会员发表于:November 02, 2001 12:50
--------------------------------------------------------------------------------
兄弟，看了你的分析。我很高兴我在share_poor_size的调整苦恼中，不是孤单的。
我也遇到这个问题。
一个复杂的查询语句执行时，1500M的share_poor_szie 20分钟就消耗完毕，然后提示查询执行失败。
你有没有观察过你的SESSION，看他们都干了些什么？此外，通过select * from v$db_object_cache where sharable_mem > 10000 你可以知道使用内存数据超过10000的SESSION是哪些
看来，share_poor_szie的调整问题，已经有些普遍。
大家可以看看，我的关于：“见过这么长的查询吗？？？？ ”一贴，也是对此问题的困惑。

欢迎大家提出意见，以解我们心头之苦！！！

------------------
头可断头发不能乱，
血可流皮鞋不能不擦油。

michael2001
初级会员发表于:November 02, 2001 15:07
--------------------------------------------------------------------------------

引用:
--------------------------------------------------------------------------------
发起人 ligeng2000:
兄弟，看了你的分析。我很高兴我在share_poor_size的调整苦恼中，不是孤单的。
我也遇到这个问题。
一个复杂的查询语句执行时，1500M的share_poor_szie 20分钟就消耗完毕，然后提示查询执行失败。
你有没有观察过你的SESSION，看他们都干了些什么？此外，通过select * from v$db_object_cache where sharable_mem > 10000 你可以知道使用内存数据超过10000的SESSION是哪些
看来，share_poor_szie的调整问题，已经有些普遍。
大家可以看看，我的关于：“见过这么长的查询吗？？？？ ”一贴，也是对此问题的困惑。

欢迎大家提出意见，以解我们心头之苦！！！

--------------------------------------------------------------------------------

老兄
我用你的方法查了一下果然有很多 sharable_mem>10000 的session ，使用的SQL语句很有问题，正像Yong Huang说的sql语句没用bind variables。你可以查一下oracle On-line Doc,这种情况应该是应用代码的问题，而我们做数据库管理的人还苦苦追寻是不是那个参数设错了！！我摘了段binding 的文档供大家参考。另外ligeng2000给你一个建议把写那个超长sql语句的人干掉！！

GOOD LUCKY!
michael

Binding
Most DML statements, and some queries (such as those with a WHERE clause), require a program to pass data to Oracle as part of a SQL or PL/SQL statement. Such data can be constant or literal data, known when your program is compiled. For example, the following SQL statement, which adds an employee to a database contains several literals, such as 'BESTRY' and 2365:

INSERT INTO emp VALUES
(2365, 'BESTRY', 'PROGRAMMER', 2000, 20)

Hard coding a statement like this into an application would severely limit its usefulness. You would need to change the statement and recompile the program each time you add a new employee to the database. To make the program more flexible, you can write the program so that a user can supply input data at run time.
When you prepare a SQL statement or PL/SQL block that contains input data to be supplied at run time, placeholders in the SQL statement or PL/SQL block mark where data must be supplied. For example, the following SQL statement contains five placeholders, indicated by the leading colons (e.g., :ename), that show where input data must be supplied by the program.

INSERT INTO emp VALUES
(:empno, :ename, :job, :sal, :deptno)

ligeng2000
高级会员发表于:November 02, 2001 15:56
--------------------------------------------------------------------------------
HI，
你可以将1000加大到100000，看还有没有这样的SQL语句。
我的那个查询是应用程序自动生成的，而这个应用程序是ORACLE的数据仓库产品EXPRESS，所以我可以捉到这个SQL。却不能优化它，所以这个SQL花了我1000多M内存，还是不行。
关于这个SQL语句，大爱可以看看我的关于：“见过这么长的查询吗？？？？ ”一贴。
关于SHARD_POOL_SIZE不够用的说明，大家请参见： http://www.itpub.net/showthread.php...d=9552#post9552
中有一个文本。。。

------------------
头可断头发不能乱，
血可流皮鞋不能不擦油。

[被 ligeng2000 编辑过（日期 November 02, 2001）]

michael2001
初级会员发表于:November 02, 2001 17:15
--------------------------------------------------------------------------------
hello ligeng2000
我查v$db_object_cache 中的SQL语句越来越多oracle 是不是应该定期清除一些对象，但它好像并没这么做，是为什么？？

michael

ligeng2000
高级会员发表于:November 02, 2001 20:30
--------------------------------------------------------------------------------

引用:
--------------------------------------------------------------------------------
发起人 michael2001:
hello ligeng2000
我查v$db_object_cache 中的SQL语句越来越多oracle 是不是应该定期清除一些对象，但它好像并没这么做，是为什么？？

michael

--------------------------------------------------------------------------------

经过很长时间在METALINK上的查找，我终于发现问题的关键了。
我遇到的问题本与你大同小异：一个超大SQL语句的执行会快速消耗share_pool_size,然后引发三种错误：
ORA-01037 maximum cursor memory exceeded错误
ORA-01041 internal error. HOSTDEF extension does not exist错误
ORA-04031错误。
然后，SQL执行失败。中间做过很多努力，调整参数如下：
a)open_cursors=50 --> 15000
b)sort_area_size=65536 -->8M
c)hash_area_szie=131072 -->16M
d)star_transformation_enabled = false -->true
e)query_rewrite_enabled=false -->true
f)SHARED_POOL_RESERVED_SIZE=10/ SHARED_POOL_SIZE
但都不能阻止share_poor_size如水般的流逝。

终于我做出了将optimizer_mode改为FIRST_ROWS的决定，使其在执行SQL时，做COST BASE的分析。幸运的事情来了，原有的查询语句消耗内存非常小，十分钟才减少10M左右。换一个更厉害的查询，仍是如此，旅试不爽。

看看ORACLE的朋友们一定要多去METALINK看看.