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  |
Andrey_R
Messages: 441
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>
SQL> select count(*) from v$sql;
COUNT(*)
----------
216622
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>
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  |
John Watson
Messages: 8960
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 |
Andrey_R
Messages: 441
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 |
John Watson
Messages: 8960
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Wed, 09 March 2022 09:18It 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 #685706 is a reply to message #685704] |
Thu, 10 March 2022 04:06 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Thu, 10 March 2022 09:17Your 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 
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
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 #685710 is a reply to message #685709] |
Thu, 10 March 2022 11:18 |
John Watson
Messages: 8960
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.
|
|
|
|
Goto Forum:
Current Time: Thu Nov 28 02:20:08 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
