Home » RDBMS Server » Server Administration » May you clarify me more?
May you clarify me more? [message #293556] Mon, 14 January 2008 01:41 Go to next message
trantuananh24hg
Messages: 668
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 #293559 is a reply to message #293556] Mon, 14 January 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the end, what is the question and for which Oracle version?

Regards
Michel
Re: May you clarify me more? [message #293562 is a reply to message #293559] Mon, 14 January 2008 02:04 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you Michel for your responding.

Wrote I that..
Quote:


To avoid your's meaning mistake, the section above is for 9i and period



Because I am using 10g, however, I assume I am in 9i, 9.2.0.

Thank you!

[Updated on: Mon, 14 January 2008 02:06]

Report message to a moderator

Re: May you clarify me more? [message #293563 is a reply to message #293562] Mon, 14 January 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry still don't understand what is the question.
I think you want an answer for 9.2 but I'm not sure.

Regards
Michel


Re: May you clarify me more? [message #293564 is a reply to message #293563] Mon, 14 January 2008 02:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #293574 is a reply to message #293564] Mon, 14 January 2008 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't compare the result from different versions.
The result of "mismatch" depends on the version.
First, they do not report the same thing even if it is the same name.
Then, in current versions, Oracle round up to the next granule.

Regards
Michel
Re: May you clarify me more? [message #293577 is a reply to message #293574] Mon, 14 January 2008 02:56 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 Go to previous messageGo to next message
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]
Re: May you clarify me more? [message #293581 is a reply to message #293578] Mon, 14 January 2008 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"target" is not correct, "minimum" is more appropriate.

Regards
Michel
Re: May you clarify me more? [message #294923 is a reply to message #293556] Sun, 20 January 2008 21:58 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
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?


SHARED_POOL_SIZE is the minimum allocated value for the shared pool. And in v$sgastat 'shared pool' indicates current memory allocation in shared pool. If sga_target is set then it may allocate more than minimum value of SGA if it need.
Previous Topic: example tablespace
Next Topic: virtual memory problem in window oracle database
Goto Forum:
  


Current Time: Mon Dec 05 19:27:52 CST 2016

Total time taken to generate the page: 0.12675 seconds