| May you clarify me more? [message #293556] |
Mon, 14 January 2008 01:41  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
I am reading again the book Expert One by One, and I am wondering about the section written in chapter 2
| Quote: |
One last comment about the shared pool and the init.ora parameter,
SHARED_POOL_SIZE. There is no relationship between the outcome of the query:
sys@TKYTE816> select sum(bytes) from v$sgastat where pool = ʹshared poolʹ;
SUM(BYTES)
‐‐‐‐‐‐‐‐‐‐
18322028
1 row selected.
and the SHARED_POOL_SIZE init.ora parameter:
sys@TKYTE816> show parameter shared_pool_size
NAME TYPE VALUE
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
shared_pool_size string 15360000
SVRMGR>
other than the fact that the SUM(BYTES) FROM V$SGASTAT will always be larger than the SHARED_POOL_SIZE. The shared pool holds many other structures that are outside the scope of the corresponding init.ora parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor. For example, the init.ora parameter, CONTROL_FILES, contributes 264
bytes per file to the ʹmiscellaneousʹ section of the shared pool. It is unfortunate that the ʹshared poolʹ in V$SGASTAT and the init.ora parameter SHARED_POOL_SIZE are namedas they are, since the init.ora parameter contributes to the size of the shared pool, but it is not the only contributor.
|
The shared pool holds many other structures, of course, it is the most critial pieces of memory in SGA. The shared pool is where Oracle caches many bits of program data, and the shared pool is is ... etc. But, we decide to force a maximum size for share pool by SHARED_POOL_SIZE, imagine it - shared pool - like a box, many datas may be loaded, cached, restrained...etc. However, I think that, no more size than the size was setup to SHARED_POOL_SIZE can be allocated. However, why did Mr Thomas Kyte write:
| Quote: |
There is no relationship between the outcome of the query:
sys@TKYTE816> select sum(bytes) from v$sgastat where pool = ʹshared poolʹ;
SUM(BYTES)
‐‐‐‐‐‐‐‐‐‐
18322028
1 row selected.
and the SHARED_POOL_SIZE init.ora parameter:
sys@TKYTE816> show parameter shared_pool_size
NAME TYPE VALUE
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
shared_pool_size string 15360000
SVRMGR>
|
Maybe the first value is in current session, may be not. If it is, why is it bigger than SHARED_POOL_SIZE parameter's value?
To avoid your's meaning mistake, the section above is for 9i and period.
In 10g, SGA_TARGET - ADMM new features control SHARED_POOL_SIZE, I am not suprised if..
SQL> select sum(bytes/1024/1024) MB from v$sgastat where pool='shared pool';
MB
----------
880.916672
SQL> alter system flush shared_pool;
System altered.
SQL> select sum(bytes/1024/1024) MB from v$sgastat where pool='shared pool';
MB
----------
881.214966
Thank you!
[Updated on: Mon, 14 January 2008 02:07] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: May you clarify me more? [message #293564 is a reply to message #293563] |
Mon, 14 January 2008 02:09  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Michel, I am sorry about wrong word. Instead of "above", I used "below". I am sorry, and I changed it.
The meaning that I wondered is :
Why did Mr Thomas Kyte write:
| Quote: |
One last comment about the shared pool and the init.ora parameter,
SHARED_POOL_SIZE. There is no relationship between the outcome of the query:
|
sys@TKYTE816> select sum(bytes) from v$sgastat where pool = ʹshared poolʹ;
SUM(BYTES)
‐‐‐‐‐‐‐‐‐‐
18322028
1 row selected.
and the SHARED_POOL_SIZE init.ora parameter:
sys@TKYTE816> show parameter shared_pool_size
NAME TYPE VALUE
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
shared_pool_size string 15360000
I think
| Quote: |
However, I think that, no more size than the size was setup (set value) to SHARED_POOL_SIZE can be allocated
|
In this case, that is the size 18322028was more the size of SHARED_POOL_SIZE 15360000. And, all of them are used for 9i and period. Period of 10g, the value of SHARED_POOL_SIZE is a statis value, we have to change it by alter system set or changing it in initSID.ora.
Thank you for your time!
[Updated on: Mon, 14 January 2008 02:19] Report message to a moderator |
|
|
|
|
|
| Re: May you clarify me more? [message #293577 is a reply to message #293574] |
Mon, 14 January 2008 02:56 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
No, Mr Michel!
I just wondered about the difference between of value 'shared pool' and SHARED_POOL_SIZE, why the 'shared pool' in pool name could be larger than SHARED_POOL_SIZE?
In 9i, I am sorry, at time now, I have not any 9i to test, but I did it in my DB at house. I realized that Mr Thomas Kyte wrote right, 'shared pool' in pool name some time is bigger than the value of SHARED_POOL_SIZE which I set in Database. Tonight (in HaNoi), I will test again and send it here.
May be my question not clean.
|
|
|
|
| Re: May you clarify me more? [message #293578 is a reply to message #293574] |
Mon, 14 January 2008 03:11 |
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
|
Member |
|
|
Thomas Kyte is correct but point he wanted to highlight could not be made clear due to language(sentences) he used.
With kind permission of you all over there at ORAFAQ let me put it in a layman language. Parameter shared_pool_size is your target for shared pool which you gives to oracle and sum(bytes) from v$sgastat ["select sum(bytes) from v$sgastat where pool = 'shared pool'"] is your actual current shared pool size in SGA. The rule is simple Oracle does not follow your sizing parameters strictly but uses them as reference targets only. The actual size may be lower / higher or equal to sizing parameter specified by you depending upon requirement by instance. This is a bit of wired intelligence built into the Oracle kernel. This is default behavior you can find it your self by testing for other sizing parameters.
Regards
Manjit Kumar [mkbhati]
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
