Home » RDBMS Server » Server Administration » Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL (Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production)
Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685696] Tue, 08 March 2022 06:50 Go to next message
Andrey_R
Messages: 403
Registered: January 2012
Location: Israel
Senior Member
Hi all,
I've found that in a prod environment there is a difficulty querying v$SQL , as it gets inflated by many records.

I did

SQL>  alter system flush shared_pool;

System altered.

SQL>
And 1 day after I see:

SQL> select count(*) from v$sql;

  COUNT(*)
----------
    216622
So I go checking parameters responsible for capacity of shared pool:

SQL>
SQL> show parameter shared_pool

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------------------------
shared_pool_reserved_size            big integer                                  2738041651
shared_pool_size                     big integer                                  0
SQL>
SQL>
SQL> select 2738041651/1024/1024/1024 as gb from dual;

        GB
----------
      2.55

SQL>
I tried to check documentation in https://docs.oracle.com/database/121/REFRN/GUID-8B0F0388-B135-4661-84A0-1C3C78360E71.htm#REFRN10201
and generally I understand that the recommended and default value is "5% of the value of SHARED_POOL_SIZE"

In other "more healthy" similar product systems for same 0.5TB size of data and concurrent users - it is around 25-32MB.

So I suspect that there is an issue here that may be related to an inflated "allowance to grow" with that parameter.


My assumption, is that setting the SHARED_POOL_SIZE from 0 to a limited number, say 50M , I can get the V$SQL queryable and be able to analyze and tune SQLs better.

However, I didn't find any recommendations to reduce it or a recommended threshold apart from "Maximum: one half of the value of SHARED_POOL_SIZE",
So subsequently, I take it as if my SGA is large and my shared_pool parameter is 0 ( implicitly managed ) - it means that it can get quit large.

So I will be happy for any input/advise:

Q1: Is there sense in cases when I go decreasing shared_pool size
Q2: Any documentation I missed that could help understanding this case better


Thank in advance,
Andrey

[Updated on: Tue, 08 March 2022 06:55]

Report message to a moderator

Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685697 is a reply to message #685696] Tue, 08 March 2022 07:29 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
My opinion (which is open to debate) is that shared_pool_reserved_size is an historical anomaly. In the Old Days, objects loaded into the shared pool required contiguous memory, so it could get fragmented into all sorts of odd sized bits. Reserving part of it for large objects helped with that. But for several releases now, objects do not need contiguous memory: they can be distributed across any bits of shared pool memory that happen to be free. So you can forget that parameter completely.
As for the size of the shared pool, I have noticed that the ASSM algorithms do seem to allocate a lot of memory to it: more than I would if managing the pools manually. You seem to be of the same opinion. An oversized shared pool isn't a problem (it used to be years ago - the hashing algorithms for finding items were not optimized for zillions of objects) unless you think the memory could be better used in the db buffer cache. In which case, set db_cache_size to a value beneath which you do not want it to go, and let Uncle Oracle do the rest.
There is no point in setting shared_pool_size=50m, that is far too low and the ASSM mechanism will always push it way above that.
(I haven't looked up any references for my comments above, I am relying on memory).
Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685698 is a reply to message #685697] Tue, 08 March 2022 10:38 Go to previous messageGo to next message
Andrey_R
Messages: 403
Registered: January 2012
Location: Israel
Senior Member
First of all, many thanks for your reply an attention.

As per the input,

John Watson wrote on Tue, 08 March 2022 15:29
...
An oversized shared pool isn't a problem (it used to be years ago - the hashing algorithms for finding items were not optimized for zillions of objects) unless you think the memory could be better used in the db buffer cache.
Well, not directly the shared pool alltogether, but the part of it that makes V$SQL grow in an enormous rate to enormous size ( 300k new values per day, starts to "re-cycle" itself when reaching some 1.8M rows ).
At that point, I both see that as indication of an applicative problem ( such as under usage of bind variables et.c, however, same application with same faults produces far less v$sql rows i.e ~10k, which tells me that this is definitely not the main cause ), as well as something that makes it difficult to query performance metrics data in V$SQL since it's inflated and the wait times for simple retrievals of ordered data (by executions, by time per exec , et.c ) are long.


John Watson wrote on Tue, 08 March 2022 15:29

In which case, set db_cache_size to a value beneath which you do not want it to go, and let Uncle Oracle do the rest.
There is no point in setting shared_pool_size=50m, that is far too low and the ASSM mechanism will always push it way above that.
(I haven't looked up any references for my comments above, I am relying on memory)
It's 0, therefore system-managed. if I set it to a minimum, say 100M I understand it won't grow "too low", however I seem to understand that my problem is that it grows "too large".
Maybe not the shared poll itself, again, but the allowance to V$SQL resource..


So I understand that if I set shared pool 50M I can deprive other "tenant" resources using the shared pool, which makes it not such a good idea?
How in that case can I restrain the storage allowance for the V$SQL to grow ?


Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685699 is a reply to message #685698] Wed, 09 March 2022 01:18 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
Quote:
How in that case can I restrain the storage allowance for the V$SQL to grow ?
You have it the wrong way round. If you do not want the shared pool to grow "too large", you set minimum sizes for the other components. The buffer cache is the one that matters. For example:

sga_target=16g
db_cache_size=12g

It is now absolutely impossible for the shared pool to grow beyond 4g. Assuming some space is also needed for large pool and Java pool, it will be less.


Quote:
such as under usage of bind variables
It could be the Adaptive Cursor Sharing mechanism creating lots of child cursors for statements with bind variables.
Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685700 is a reply to message #685699] Wed, 09 March 2022 09:47 Go to previous messageGo to next message
Andrey_R
Messages: 403
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Wed, 09 March 2022 09:18
It could be the Adaptive Cursor Sharing mechanism creating lots of child cursors for statements with bind variables.


Cursor sharing is exact, so I tend to think this is not the case


John Watson wrote on Wed, 09 March 2022 09:18

You have it the wrong way round. If you do not want the shared pool to grow "too large", you set minimum sizes for the other components. The buffer cache is the one that matters. For example:

sga_target=16g
db_cache_size=12g

It is now absolutely impossible for the shared pool to grow beyond 4g. Assuming some space is also needed for large pool and Java pool, it will be less.



So if I go and check the parameters:


SQL> show parameter db_cache_size

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------------------------
db_cache_size                        big integer                                  0
SQL>
SQL>
SQL> show parameter sga_target

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------------------------
sga_target                           big integer                                  270G
SQL>

And then check the usage:


SQL> set lines 900 pages 20000
SQL> col name for a32
SQL> select v.NAME, to_char(v.BYTES/1024/1024/1024,'990.99') gb, v.RESIZEABLE from v$sgainfo v;

NAME                             GB                           RESIZEABLE
-------------------------------- ---------------------------- ------------
Fixed SGA Size                      0.01                      No
Redo Buffers                        0.49                      No
Buffer Cache Size                 200.50                      Yes
In-Memory Area Size                 0.00                      No
Shared Pool Size                   62.50                      Yes
Large Pool Size                     2.00                      Yes
Java Pool Size                      3.50                      Yes
Streams Pool Size                   1.00                      Yes
Shared IO Pool Size                 0.50                      Yes
Data Transfer Cache Size            0.00                      Yes
Granule Size                        0.50                      No
Maximum SGA Size                  270.00                      No
Startup overhead in Shared Pool    12.67                      No
Free SGA Memory Available           0.00

14 rows selected.


This means that the reason that I have an inflated V$SQL is that I have 60GB of shared_pool, and the solution would be to ( as an example) set DB_CACHE_SIZE to, lets say 240G, leaving less memory for the components of SHARED_POOL, which will subsequently reduce V$SQL size threshold ?



Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685704 is a reply to message #685700] Thu, 10 March 2022 01:17 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Cursor sharing is exact, so I tend to think this is not the case
Your thinking is not correct; you need to read up on Adaptive Cursor Sharing.


Quote:
This means that the reason that I have an inflated V$SQL is that I have 60GB of shared_pool, and the solution would be to ( as an example) set DB_CACHE_SIZE to, lets say 240G, leaving less memory for the components of SHARED_POOL, which will subsequently reduce V$SQL size threshold ?
Yes. If you really think that the cache needs to be larger. Your whole approach is a bit unusual. Most people tune memory structures for the users' queries, not the DBA's queries. Up to you, I suppose Smile
Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685706 is a reply to message #685704] Thu, 10 March 2022 04:06 Go to previous messageGo to next message
Andrey_R
Messages: 403
Registered: January 2012
Location: Israel
Senior Member
John Watson wrote on Thu, 10 March 2022 09:17
Your thinking is not correct; you need to read up on Adaptive Cursor Sharing.
Noted, Will do.


Quote:
Yes. If you really think that the cache needs to be larger. Your whole approach is a bit unusual. Most people tune memory structures for the users' queries, not the DBA's queries. Up to you, I suppose Smile
Well, the main "puzzle" came up when I couldn't query V$SQL in a sensible time, followed by reviewal of row numbers and comparing to other systems, which indicated a problematic situation by itself.

So off course I won't just go and tune it for myself, but it was great to start understanding a bit more on the way it works and why, to consider my next steps in a more aware state.

I still tend to think, though, that the way Oracle distributes resource on managing the shared_pool has some weaknesses, where (knowledgeable) man beats machine Smile


I have some follow up question in my mind about "Can you throw *too much* resource allocation on a system to cause problems".
In this case it's a 270G SGA when the storage data is around 500G.. I think I probably should start a new topic for that one.


Many thanks for your time and attention, and help enlightening my view on things, much appreciated.


Andrey
Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685709 is a reply to message #685706] Thu, 10 March 2022 10:27 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Quote:
I have some follow up question in my mind about "Can you throw *too much* resource allocation on a system to cause problems".
Absolutely possible, especially with memory structures. The more resource you allocate, the more other resources (memory and cpu) have to be dedicated to managing it. Say you allocate x-gb for a buffer cache. It will take a certain amount of processing overhead to keep track of what is where within that cache. Increase the cache by a factor of 10, and there will be that much more space - and potential objects within that space - to have to keep track of. The potential iexits for the additional overhead of managing the larger resource outweighs the gain of increasing the resource in the first place.

At the very least, you can run into issues where you over-allocate a memory structure and as a result cause more page swapping at the OS level.

I wouldn't sweat a few m-bytes but, yes, it is possible to have a negative impact from over-allocating.
Re: Large SHARED_POOL_RESERVED_SIZE, inflated V$SQL [message #685710 is a reply to message #685709] Thu, 10 March 2022 11:18 Go to previous message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
In principle, Ed must be correct: oversized structures must add some overhead.

In practical terms, I wonder if the effect is measurable. I already mentioned the oversized shared pool issue in early releases. That was pretty bad. I remember disastrous latch waits in an 8.something instance when a DBA increased it from, IIRC, 40M to 4000M just because new hardware had a shed load of RAM. But changes in the management algorithms in 9i (I think) fixed that.

The buffer cache did not have similar issues, something to do with spawning multiple hash chains as the cache gets bigger. So the hashing algorithm does not have to manage a ludicrous number of addresses. Though perhaps too many hash chains could be a problem?

An oversized log buffer was seriously bad. Because when a session COMMITs, it hangs while the log buffer is flushed to disc. So, in principle, the smaller the log buffer is then the faster the commit. If your log buffer were huge and there had not been a COMMIT for some time, then when you did COMMIT you would wait while a lot of data was written. So a standard fix to log_file_sync waits was to reduce the log_buffer. That was fixed in 8.something when Oracle added an extra trigger to the log writer: it will write, worst case, when there is 3MB of redo. So you can create a log buffer the size of Jupiter and it will never be all used.

The large pool needs to be big enough to support PQ, or whatever else needs it. Same for the Streams pool. Anything bigger has no benefit. The Java pool is more complicated: a bit too small and performance degrades because of having to re-instantiate objects; a lot too small, and you get ora-4031s.

However, all the above may be garbage now because of multitenant. From 19 (I think?) you can specify shared pool and buffer cache per PDB. Upper and lower limits? Something like that? I have not had occasion to do any research on the effect of that.

Thanks for the question, gave me something to think about during a long upgrade operation.
Previous Topic: ORA-01805 Timezone files error
Next Topic: tnsping sqlnet.ora not listing
Goto Forum:
  


Current Time: Sun Aug 14 16:30:50 CDT 2022