SQL 重新load的原因？

YYH2458

Many thanks for yong's help.

   But the DB has only one main user schema for AP. So it used most share pool memory.  And I monitor one SQL, found  that if the sql execution is increasing in v$sqlarea, the sharable_mem is also increasing.

  I think it's abnormal.   But I don't understand what can  lead this symptom ??? Pls give me some advice.


Thanks,
Yu

Yong Huang

原帖由 YYH2458 于 2009-7-30 03:27 发表
   But the DB has only one main user schema for AP. So it used most share pool memory.  And I monitor one SQL, found  that if the sql execution is increasing in v$sqlarea, the sharable_mem is also increasing.

  I think it's abnormal.   But I don't understand what can  lead this symptom ??? Pls give me some advice.

What if you check v$sql? If it's simply because you have more and more child cursors for one parent SQL, check v$sql_shared_cursor to see why the same child cursor can't be shared.

If checking v$sql still shows sharable_mem increase, that's odd. Any other value that changes? Executions and buffer_gets of course. What else?

You said you're running 8.1.7.4? I can't find a bug, but Bug 1519805 is kind of like it, although it's for 8.1.6.2. Can you creat a test case? This is what I did in 10.2.0.4:

select count(*) from t;
query v$sql for the above SQL
select count(*) from t;
query v$sql again, check all values that have changed (not just sharable_mem)
run a DDL on t (grant select on t to dba)
select...
query

I don't see change in sharable_mem.

Yong Huang

[ 本帖最后由 Yong Huang 于 2009-7-30 13:45 编辑 ]