Large Pool ???
A reader, February 03, 2002 - 7:57 am UTC
Hi Tom,
Do u mean to ay that since the large pool takes much of the load off the Shared pool the aging of the Pkgs and Procs withing the shared pool will be less and hence there is no need to pin the packages.
Correct ??
Regards,
Ganesh R
February 03, 2002 - 9:24 am UTC
I have this discussion in full in my book -- how memory is used.
Pinning was necessary in 7.x especially when using MTS (shared server) because all memory for the UGA as well as the library cache came from the same place. The shared pool (the variable size of the SGA). Now, shared sql (sql/plsql/library stuff) is managed in an LRU -- least recently used -- with aging. The older you are in the pool, the less you've been used -- out you go. also, the shared pool was setup for small allocations, typically 4k in size or so.
So, now we start using this shared pool for memory allocations that are NOT age based -- our UGA memory -- out session memory. Also, these allocations are non-trivial in size (consider, sort_area_retained_size for example). Now, we have memory in the shared pool that is allocated "big" (needs big contigous chunks) and it is needed for a duration of time -- it is not aged like library cache data is.
This is where the problem came in. We needed large contigous free chunks in the shared pool - a memory pool where 4k chunks ruled AND we needed it for a fixed period of time, not on an LRU schedule.
Well the contigous part was hard -- we would free up 4k chunks where possible but would still have lots of 4k chunks out there. You could have 50% of the shared pool FREE but still get and ora-4031 trying to allocate 16k of ram (every other 4k chunk of memory was free, every other chunk was allocated -- largest free chunk was 4k). The shared pool would get fragmented and flushing won't do a thing (we need those other chunks, they aren't flushable right now).
The fixed length was bad as well. Your session would come in an allocate 1m of sort_area_retained_size in the shared pool, use it for a bit and then free it. It's in an LRU so its freeable -- but not freed. It is aged out on the LRU method -- even though it will NEVER be used again (we are done with that result set). Your memory would sit there while other useful but older bits would be aged out.
So, enter pinning as a temporary solution for some of these problems. The concept is pin all code in the "front" of the shared pool in a contigous fashion (decreases the fragementation). Also, make it non-flushable -- lets your UGA memory flush faster.
Well, the better solution is to get that UGA memory which is big and not LRU based into a pool that provides large allocations and does not use an LRU. Enter the large pool..... No need to pin as you are not mixing these allocation types.
Nothing But Excellent
A reader, February 03, 2002 - 11:56 pm UTC
Thanks Tom.
Regards,
Ganesh R
what if I can't recompile the package?
Paul Druker, March 14, 2002 - 8:21 am UTC
I have a situation when a package is always in use. So when I modify the package and try to "create or replace" it, it's just sitting and waiting at SQL*Plus prompt (I guess getting ORA-54 errors).
I guess I can solve this problem by flushing the shared pool, but it's not appropriate for the Production system. What's the correct approach?
March 14, 2002 - 8:32 am UTC
Well, updating ANYTHING in a production system that is never down is always a problem isn't it. Consider if this were the binary executable -- you wouldn't be able to replace that as long as people are running it...
In 9i, we have an alter database quiesce available with the resource manager. This'll let you "pause" the system (put people on hold in effect) so you can zap the code. You'll still have potential issues with "existing state of packages has been discarded" but you'll be able to compile it.
The only think I can suggest is that you schedule downtime for upgrades in a production system. You would have a half hour scheduled downtime where you shutdown, startup with restricted session, patch the application, test it, and then remove the restricted session. Much safer and more efficient then trying to do a patch on a hot system.
v$db_object_cache
A reader, May 14, 2003 - 8:39 am UTC
Hi
My understanding of library cache is it contains parsed form of SQL, PL/SQL & Java Classes code (and the documentation suggests the same). However my reading about v$db_object_cache suggests me it contains more than that such as tables, clusters, dimension etc. These are not SQL, PL/SQL nor Java Classes how can they be in the library cache?
May 14, 2003 - 3:14 pm UTC
the shared pools contain lots of stuff -- it is a library cache. they list some "for examples" but preclude nothing from being in there.
but it specifically say library cache and not shared pool
A reader, May 14, 2003 - 5:28 pm UTC
Hi
The docu specifically explains about shared pool as dictionary cache and library cache as follows (quoting)
Dictionary Cache Concepts
Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.
Library Cache Concepts
The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code.
The view v$db_object_cache also specifically says it's about library cache! If it was shared pool I can understand we are probably talking about objects metadata as well but no! It's only about library cache... Also v$db_object_cache bases on x$kglob which is about library cache too!
pinning
Reader, May 14, 2003 - 5:53 pm UTC
Please correct me. pinning a package means I have made sure that the compiled version of the package is stored in shared pool so users can share. If i don't pin, it might age out later as my other sqls and pl/sqls from the shared pool. so why pinning is not required if i use version 8 onwards as you suggested. also, could you clarify "No need to pin as you are not mixing these allocation types." ok my uga will be in large pool (mts). do you mean to say that we always can find spce for our pl/sqls in shared pool and pinning is not necessary. Thanks.
May 14, 2003 - 11:38 pm UTC
the code will be loaded in 4k chunks.
pretty much every thing is.
If you use it -- it'll stay.
If you don't -- it'll go.
If you pin it -- it'll stay -- meaning, if you use it, it would stay anyway. If you DON'T use it, why did you pin it?
pinning was "useful" for big packages before the 4k pagesize stuff.
after that, not very useful
v$db_object_cache
A reader, May 15, 2003 - 10:55 am UTC
v$db_object_cache must be referring the shared pool in general and not library cache otherwise it doesnt make sense at all because table, cluster, index etc definitions are stored in dictionary cache and not lirary cache
May 15, 2003 - 5:52 pm UTC
dictionary objects are library objects -- think "generic term"
as someone already pointed out
A reader, May 15, 2003 - 6:02 pm UTC
Someone already referred from the documentation that the library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes so v$db_object_cache must be talking about the shared pool and not only library cache!
Objects informations are stored in dictionary cache!
May 15, 2003 - 6:33 pm UTC
tell me, where is the dictionary cache. they are all in the shared (variable component) pool of the sga.
that;s my point!
A reader, May 16, 2003 - 12:55 am UTC
yes my point is v$db_object_cache should be talking about shared pool and NOT only library cache!
The reference guide says the view extracts informations in library cache but if table, clusters, indexes etc informations are not in library cache then how on earth can v$db_object_cache bases on library cache!
library cache ---> SQL, PL/SQL, Java
dictionary cache ---> objects, users, privileges etc informations
v$db_object_cache ---> contains info of SQL, PL/SQL, Java, objects, users, privileges etc information so v$db_object_cache must be talking about SHARED POOL and NOT only library cache as the documentation says!
May 16, 2003 - 9:51 am UTC
it is a teeny tiny semantic and not very important distinction IMO. It doesn't really bother me, especially since the dictionary cache is just really a subcache of the library cache since version 7.0 -- the ability to size and tune the "dictionary cache" went away with the end of version 6
should I consider pinning packages ?
a reader, September 17, 2003 - 1:52 pm UTC
You said :
"So, enter pinning as a temporary solution for some of these problems. The concept is pin all code in the "front" of the shared pool in a contigous fashion(decreases the fragementation). Also, make it non-flushable -- lets your UGA memory flush faster.
Well, the better solution is to get that UGA memory which is big and not LRU based into a pool that provides large allocations and does not use an LRU.
Enter the large pool..... No need to pin as you are not mixing these allocation types. "
Please consider this situation
u@j> lsps -a
Physical Volume Size %Used Active Auto Type
hdisk0 1024MB 72 yes yes lv
hdisk5 1024MB 72 yes yes lv
u@j> lsps -s
Total Paging Space Percent Used
2048MB 72%
I cannot increase memory, should I consider pinning packages ?
September 17, 2003 - 5:33 pm UTC
umm, no?
why do you think you should?
should I consider pinning packages ?
a reader, September 17, 2003 - 1:53 pm UTC
You said :
"So, enter pinning as a temporary solution for some of these problems. The concept is pin all code in the "front" of the shared pool in a contigous fashion(decreases the fragementation). Also, make it non-flushable -- lets your UGA memory flush faster.
Well, the better solution is to get that UGA memory which is big and not LRU based into a pool that provides large allocations and does not use an LRU.
Enter the large pool..... No need to pin as you are not mixing these allocation types. "
Please consider this situation
u@j> lsps -a
Physical Volume Size %Used Active Auto Type
hdisk0 1024MB 72 yes yes lv
hdisk5 1024MB 72 yes yes lv
u@j> lsps -s
Total Paging Space Percent Used
2048MB 72%
I cannot increase memory, should I consider pinning packages ?
should I consider pinning packages ? (cont)
a reader, September 17, 2003 - 2:01 pm UTC
We are using 8.1.7.4.0 with mts, and XA.
September 17, 2003 - 5:36 pm UTC
use the large pool, configure a shared pool
the shared pool is a nice LRU, if you use it, it'll stay. if you do not, it'll go. if you pin it and don't use it, it'll just gobble up memory.
Thanks
a reader, September 17, 2003 - 6:34 pm UTC
Demand paged
Ron Chennells, April 27, 2004 - 5:09 am UTC
Tom
Does your reference to 4k chunks mean that PL/SQL packages
are demand paged in a similar way to OS pages ? If so
is there an equivalent to the RSS to determine the working
set of a package ?
Thank
April 28, 2004 - 12:14 pm UTC
it pretty much loads it up -- in 4k pieces that can be scattered all about. not really "demand paged", just chunked.
You can see the size in effect in v$db_object_cache.
pin anything
Eduardo, August 27, 2004 - 12:59 pm UTC
Tom,
Let me see if I understood what you said, and then I'll ask you a question:
You said that we does not need to pin ANYTHING in the library cache anymore, since Oracle 8 onwards, because of the new Large Pool and the ability to divide bigger objects in 4k chunks inside the library cache. Is it correct?
If so, could you explain our situation: we have a 9.2.0.4 production database, running Oracle Apps, and we were experiencing lots of ORA-4031 and library cache pin waits. After we pin some PL/SQL basic packages, like DUTIL, STANDARD, DIANA, DBMS_STANDARD, etc (we did it 2 days ago), this errors and waits disappeared until now.
If you need more details for the environment, please let me know.
Thanks in advance.
August 27, 2004 - 1:14 pm UTC
think this could be "false causality" here.
those packages are used so often, they would not age out. I cannot see the case where pinning them would fix an ora-4031.
me again
Eduardo Claro, December 01, 2004 - 1:15 pm UTC
Tom, it's me again (Eduardo).
In that same environment, we are experiencing too many INVALIDATIONS in Library Cache SQL AREA namespace. This instance is for Oracle EBS.
I realized (using auditing and event triggers) that APPS schema does lots of DDLs, like CREATE, DROP and TRUNCATE, and they are the cause of the invalidations of course.
Looking at V$SQL to find invalidated SQL, I saw that the same SQL appears many times, like the excerpt following:
select sql_text,sharable_mem,EXECUTIONS,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,child_number
from v$sql where invalidations>0
...
SQL_TEXT SHARABLE_MEM EXECUTIONS LOADS FIRST_LOAD_TIME INVALIDATIONS CHILD_NUMBER
------------------------------------------------------------ ------------ ---------- --------- -------------------- ------------- ------------
SELECT userenv('SESSIONID') from sys.dual 9678 0 1 2004-11-29/06:00:35 1 0
SELECT userenv('SESSIONID') from sys.dual 9678 0 27 2004-11-29/06:00:35 9 1
SELECT userenv('SESSIONID') from sys.dual 9678 0 19 2004-11-29/06:00:35 9 2
SELECT userenv('SESSIONID') from sys.dual 23662 27588 6 2004-11-29/06:00:35 3 3
SELECT userenv('SESSIONID') from sys.dual 20590 2 7 2004-11-29/06:00:35 6 4
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 5
SELECT userenv('SESSIONID') from sys.dual 9678 0 7 2004-11-29/06:00:35 7 6
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 7
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 8
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 9
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 10
SELECT userenv('SESSIONID') from sys.dual 9678 0 7 2004-11-29/06:00:35 7 11
SELECT userenv('SESSIONID') from sys.dual 9678 0 8 2004-11-29/06:00:35 8 12
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 13
SELECT userenv('SESSIONID') from sys.dual 9678 0 4 2004-11-29/06:00:35 4 14
SELECT userenv('SESSIONID') from sys.dual 9678 0 2 2004-11-29/06:00:35 2 15
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 4 16
SELECT userenv('SESSIONID') from sys.dual 9678 0 8 2004-11-29/06:00:35 8 17
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 18
SELECT userenv('SESSIONID') from sys.dual 9678 0 4 2004-11-29/06:00:35 4 19
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 20
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 21
SELECT userenv('SESSIONID') from sys.dual 9678 0 5 2004-11-29/06:00:35 5 22
SELECT userenv('SESSIONID') from sys.dual 9678 0 2 2004-11-29/06:00:35 2 23
SELECT userenv('SESSIONID') from sys.dual 9678 0 3 2004-11-29/06:00:35 3 24
SELECT userenv('SESSIONID') from sys.dual 9678 0 3 2004-11-29/06:00:35 3 25
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 26
SELECT userenv('SESSIONID') from sys.dual 9678 0 3 2004-11-29/06:00:35 3 27
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 28
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 29
SELECT userenv('SESSIONID') from sys.dual 9678 0 2 2004-11-29/06:00:35 2 30
SELECT userenv('SESSIONID') from sys.dual 9678 0 2 2004-11-29/06:00:35 2 31
SELECT userenv('SESSIONID') from sys.dual 9678 0 3 2004-11-29/06:00:35 3 32
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 33
SELECT userenv('SESSIONID') from sys.dual 9678 0 6 2004-11-29/06:00:35 6 34
SELECT userenv('SESSIONID') from sys.dual 9678 0 4 2004-11-29/06:00:35 4 35
SELECT userenv('SESSIONID') from sys.dual 9678 0 2 2004-11-29/06:00:35 2 36
SELECT userenv('SESSIONID') from sys.dual 9678 0 1 2004-11-29/06:00:35 1 37
SELECT userenv('SESSIONID') from sys.dual 9678 0 1 2004-11-29/06:00:35 1 38
SELECT userenv('SESSIONID') from sys.dual 9678 0 1 2004-11-29/06:00:35 1 39
...
My questions:
1) Why this unique SQL (it's the same SQL in all the lines above, isn't it?) appears many times?
2) What means the CHILD_NUMBER column? Latch?
3) What could invalidate this simple query against the dual table?
Thanks a lot, in advance.
December 01, 2004 - 3:10 pm UTC
1) use v$sql shared cursor in 9i and up to see why the different versions
2) child number is just 1, 2, 3, 4, .... Nth child (nth copy)
3) grants, statistics for example
another question
Eduardo Claro, December 03, 2004 - 10:04 am UTC
ok, thanks for your response.
Another question: after the code in the shared_pool is invalidated, at what moment does Oracle recompiles it:
1) right after the invalidation
2) when the code is requested again (a new execution)
3) other (please explain)
December 03, 2004 - 11:01 am UTC
when someone executes it.
Loading large objects
Sven, April 05, 2005 - 7:12 am UTC
Hi Tom,
If I have a PL/SQL code or SQL code which size is bigger than 4k (assuming lib. cache use 4k memory chunks), how this code will be loaded in lib.cache? If this code is for example 100k, does we need to have 100k contiguous lib.cache memory chunks or this code will be scattered all around lib.cache in 4k chunks?
How this mechanism of loading large PL/SQL or SQL objects in lib.cache works?
Thanks a lot,
Sven
April 05, 2005 - 7:37 am UTC
we try to chunk up big things, into smaller "pages".
A reader, August 22, 2005 - 7:36 am UTC
If I have a really big sized database package and I call only a function in it that is very small, does only this tiny function's code loaded on to the Shared Pool?
Or does the entire package get loaded?
If the first answer is true, its a myth that the user who executed the package first helps everyone else in the queue, because the whole lot didn't load up?
Again if this is true, it really doen't matter what the size of any package is (other than modularity issues). We can bundle all packages into one and still Oracle will only load those components that is necessary to run?
August 22, 2005 - 7:52 am UTC
The package is loaded in small pieces so as to not cause fragmentation in the shared pool, but it'll be loaded. And it is ok, good, correct, perfect that it does since the fact that this small function is in a large package means the other functions are related and therefore will be called very soon (and if that is not the case, one wonders what this small function is doing in that package, someone put it in the wrong place perhaps)
package run once a day
Ravi, November 28, 2008 - 5:32 pm UTC
Hi Tom,
We have a package that is run once a day. I am thinking that the queries used in the package are flushed from the memory by the time it is run next time (the next day). The reason is, when I run the package it is taking longer to finish the first time. And takes only one thirds of the time if it is run immediately after the first run (after rollback). Should I consider this package for pinning?
Please advice,
Ravi.
November 28, 2008 - 5:39 pm UTC
why not look instead of "i am thinking"? v$sql would tell you if they were there. A tkprof would tell you if they were hard parsed on a given run. A tkprof would actually tell you a LOT.
and why not think about why it would would much faster the second time right away....
Might it have a lot less to do with shared sql than it does with "the blocks I query are already in the buffer cache the second time right after - 24 hours later, since we haven't touched them - they are gone from the cache and we have to do physical IO again"