OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » SGA used (Oracle 12.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SGA used [message #682278]

Thu, 15 October 2020 05:52

deepakdot
Messages: 91
Registered: July 2015

Member

I have allocated 400 GB SGA and 90 GB PGA to Oracle. But how do I know out of 400 GB Memory how much actually Oracle is using ?

in AWR report, it shows the allocations, not the actual used during the Test. Please suggest

Regards
Deepak

Report message to a moderator

Re: SGA used [message #682279 is a reply to message #682278]

Thu, 15 October 2020 05:54

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

v$sgainfo

Report message to a moderator

Re: SGA used [message #682280 is a reply to message #682279]

Thu, 15 October 2020 06:01

deepakdot
Messages: 91
Registered: July 2015

Member

Thanks Watson.

I see this output. So does this means Oracle is using all 380 GB memory . Or any other value i need to look into

Maximum SGA Size 429496729600 (400 GB )
Buffer Cache Size 407485022208 (380 GB )
Free SGA Memory Available 0

~ Deepak

Report message to a moderator

Re: SGA used [message #682281 is a reply to message #682280]

Thu, 15 October 2020 06:05

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

I have no idea, because you are not showing what you are seeing. For example, I see this,

orclz> select * from v$sgainfo;

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size                      9036312 No           0
Redo Buffers                        7737344 No           0
Buffer Cache Size                 536870912 Yes          0
In-Memory Area Size                       0 No           0
Shared Pool Size                  444596224 Yes          0
Large Pool Size                    25165824 Yes          0
Java Pool Size                     16777216 Yes          0
Streams Pool Size                   8388608 Yes          0
Shared IO Pool Size                50331648 Yes          0
Data Transfer Cache Size                  0 Yes          0
Granule Size                        4194304 No           0
Maximum SGA Size                 1048572440 No           0
Startup overhead in Shared Pool   272245032 No           0
Free SGA Memory Available                 0              0

14 rows selected.

orclz>

Report message to a moderator

Re: SGA used [message #682282 is a reply to message #682281]

Thu, 15 October 2020 06:08

deepakdot
Messages: 91
Registered: July 2015

Member

this is my output

Fixed SGA Size 7677400 No 0
Redo Buffers 529190912 No 0
Buffer Cache Size 407485022208 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 11274289152 Yes 0
Large Pool Size 5368709120 Yes 0
Java Pool Size 3758096384 Yes 0
Streams Pool Size 1073741824 Yes 0
Shared IO Pool Size 536870912 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 536870912 No 0
Maximum SGA Size 429496729600 No 0
Startup overhead in Shared Pool 4066217880 No 0
Free SGA Memory Available 0 0

Report message to a moderator

Re: SGA used [message #682283 is a reply to message #682282]

Thu, 15 October 2020 06:12

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Please read and apply How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: SGA used [message #682284 is a reply to message #682283]

Thu, 15 October 2020 06:17

deepakdot
Messages: 91
Registered: July 2015

Member

Sorry. My Mistake. Hope this helps

NAME BYTES RESIZEABLE CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size 7677400 No 0
Redo Buffers 529190912 No 0
Buffer Cache Size 407485022208 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 11274289152 Yes 0
Large Pool Size 5368709120 Yes 0
Java Pool Size 3758096384 Yes 0
Streams Pool Size 1073741824 Yes 0
Shared IO Pool Size 536870912 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 536870912 No 0
Maximum SGA Size 429496729600 No 0
Startup overhead in Shared Pool 4066217880 No 0
Free SGA Memory Available 0 0

Report message to a moderator

Re: SGA used [message #682285 is a reply to message #682284]

Thu, 15 October 2020 06:20

deepakdot
Messages: 91
Registered: July 2015

Member

NAME BYTES RESIZEABLE CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size 7677400 No 0
Redo Buffers 529190912 No 0
Buffer Cache Size 407485022208 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 11274289152 Yes 0
Large Pool Size 5368709120 Yes 0
Java Pool Size 3758096384 Yes 0
Streams Pool Size 1073741824 Yes 0
Shared IO Pool Size 536870912 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 536870912 No 0
Maximum SGA Size 429496729600 No 0
Startup overhead in Shared Pool 4066217880 No 0
Free SGA Memory Available 0 0

Report message to a moderator

Re: SGA used [message #682286 is a reply to message #682285]

Thu, 15 October 2020 06:24

deepakdot
Messages: 91
Registered: July 2015

Member

I am really Sorry. Something code formatting not working properly as you guided. Trying again


NAME							BYTES		 RESIZEABLE CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size					7677400			No		0
Redo Buffers					529190912		No		0
Buffer Cache Size				407485022208	        Yes		0
In-Memory Area Size				0			No		0
Shared Pool Size				11274289152		Yes		0
Large Pool Size					5368709120		Yes		0
Java Pool Size					3758096384		Yes		0
Streams Pool Size				1073741824		Yes		0
Shared IO Pool Size				536870912		Yes		0
Data Transfer Cache Size		        0			Yes		0
Granule Size					536870912		No		0
Maximum SGA Size				429496729600	        No		0
Startup overhead in Shared Pool	                4066217880		No		0
Free SGA Memory Available		        0					0

Report message to a moderator

Re: SGA used [message #682287 is a reply to message #682286]

Thu, 15 October 2020 06:29

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Sussed! That should tell you everything you need to know.

Report message to a moderator

Re: SGA used [message #682288 is a reply to message #682286]

Thu, 15 October 2020 06:30

deepakdot
Messages: 91
Registered: July 2015

Member

Thanks Watson, I was able to format the way you guided. Please suggest how do I calculated how much memory is used by Oracle.

~ Deepak

Report message to a moderator

Re: SGA used [message #682289 is a reply to message #682288]

Thu, 15 October 2020 07:00

deepakdot
Messages: 91
Registered: July 2015

Member

Watson,

During the performance test and after completing the test, the value from v$sgainfo remain same. So then this is telling how much it is assigned to what process?
I want to find during my application performance test, out of 400 GB allocation, how much actually Oracle using / used .

~ Deepak

Report message to a moderator

Re: SGA used [message #682290 is a reply to message #682289]

Thu, 15 October 2020 07:05

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

You have to think, you know. What is your interpretation of the figures shown in that query? What do you think they might mean?

And you must be precise in the words you use. When you ask "So then this is telling how much it is assigned to what process?" what "process" atre you talking about? The whole point of the SGA is that it is shared memory, used by all processes.

Report message to a moderator

Re: SGA used [message #682295 is a reply to message #682290]

Thu, 15 October 2020 07:50

deepakdot
Messages: 91
Registered: July 2015

Member

Here Process, I meant for v$sgainfo.NAME. from this table I know the memory allocations .

I compare the out of v$sgainfo table during the performance test vs after completion of test and both output are same . So I have no clue how to calculate how much memory was used during the Test. My goal is to find out, during the test how much memory was used. So that I can project, for this volume of the database, for such transaction per second, this much memory is required / used . Hope I am clear now.

~ Deepak

Report message to a moderator

Re: SGA used [message #682297 is a reply to message #682295]

Thu, 15 October 2020 08:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

deepakdot wrote on Thu, 15 October 2020 05:50

Here Process, I meant for v$sgainfo.NAME. from this table I know the memory allocations .

I compare the out of v$sgainfo table during the performance test vs after completion of test and both output are same . So I have no clue how to calculate how much memory was used during the Test. My goal is to find out, during the test how much memory was used. So that I can project, for this volume of the database, for such transaction per second, this much memory is required / used . Hope I am clear now.

~ Deepak

What if you initial assumption that memory is a critical resource is/was incorrect?
If you can't measure any difference, then perhaps there is no difference to be measured.

Report message to a moderator

Re: SGA used [message #682299 is a reply to message #682295]

Thu, 15 October 2020 08:40

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I compare the out of v$sgainfo table during the performance test vs after completion of test and both output are same .

Memory is assigned to the instance when you start it. There is no particular reason why there should be any change due to running a workload. Given time, the self-tuning mechanisms will cause the allocations to the various structures to stabilize. Which is what you are seeing.

Quote:

So I have no clue how to calculate how much memory was used during the Test.

Your test does not use memory, it uses the instance.

Quote:

So that I can project, for this volume of the database, for such transaction per second, this much memory is required / used

There is no necessary correlation between transaction volume and the memory you should assign to the instance. At best, it would be a performance thing. What do you see in the AWR report's advisory statistics? If you post a report here covering the time of your test run, perhaps someone can comment.

Report message to a moderator

Re: SGA used [message #682301 is a reply to message #682299]

Thu, 15 October 2020 09:17

deepakdot
Messages: 91
Registered: July 2015

Member

Here is the SGA advisory.

SGA Target Advisory
SGA Target Size (M)	SGA Size Factor	Est DB Time (s)	Est Physical Reads
25,600	0.06	1,126,608	153,661,195
51,200	0.13	11,038,133	360,519,896
76,800	0.19	1,817,186	360,519,896
102,400	0.25	1,530,243	267,017,059
128,000	0.31	1,293,548	189,894,505
153,600	0.38	1,293,548	189,894,505
179,200	0.44	1,206,531	161,544,014
204,800	0.50	1,190,925	156,473,195
230,400	0.56	1,190,925	156,473,195
256,000	0.63	1,190,097	156,181,239
281,600	0.69	1,189,742	156,089,042
307,200	0.75	1,187,260	155,274,638
332,800	0.81	1,187,260	155,274,638
358,400	0.88	1,184,067	154,229,741
384,000	0.94	1,182,294	153,661,195
409,600	1.00	1,182,294	153,661,195
435,200	1.06	1,181,821	153,507,534
460,800	1.13	1,181,821	153,507,534
486,400	1.19	1,181,821	153,507,534
512,000	1.25	1,181,821	153,492,168
537,600	1.31	1,181,703	153,476,802
563,200	1.38	1,181,585	153,430,703
588,800	1.44	1,181,585	153,430,703
614,400	1.50	1,181,585	153,430,703
640,000	1.56	1,180,875	153,200,211
665,600	1.63	1,180,875	153,200,211
691,200	1.69	1,180,639	153,138,747
716,800	1.75	1,180,639	153,138,747
742,400	1.81	1,180,639	153,123,381
768,000	1.88	1,180,639	153,123,381
793,600	1.94	1,180,639	153,123,381
819,200	2.00	1,180,639	153,123,381

Report message to a moderator

Re: SGA used [message #682302 is a reply to message #682301]

Thu, 15 October 2020 09:22

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

I'm sure you can interpret that as well as I can: you could halve the SGA memory before there would be any adverse impact on performance. If you were to quarter it, then you might have to do some tuning.

Report message to a moderator

Previous Topic:	How to deal with statistics during migration
Next Topic:	How to get size in MB of all objects in a Schema in Oracle?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 23:36:06 CST 2025