Pmon Unable To Acquire Latch Child Library Cache
Vaniria Setser
Nowadays, I am working on performance tuning guide of 10gR2 and I found it is worthy to write about shared pool management after reading the metalink note Note:396940.1 Troubleshooting and Diagnosing ORA-4031 Error.
Shared pool is used to cache different types of data such as textual and executable forms of PL/SQL blocks and SQL statements, dictionary cache data, and other data. If you use shared pool effectively you can reduce resource consumption in at least four ways
Pmon Unable To Acquire Latch Child Library Cache
Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool which is also divided into subpools. Smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory. Once the memory allocated from the reserved list is freed, it returns to the reserved list.
By using automatic shared memory management (ASMM) option available with 10G, which is activated by setting SGA_TARGET parameter with a value greater than 0, shared pool size is started to be managed by Oracle, under the limits of SGA_TARGET and other SGA components.
This select returns no more than 10 rows and then erases the contents of the X$KSMLRU table so be sure to SPOOL the output. The X$KSMLRU table shows which memory allocations have caused the MOST memory chunks to be thrown out of the shared pool since it was last queried. This is sometimes useful to help identify sessions or statements which are continually causing space to be requested.
You can use the sizing advices from the view v$shared_pool_advice. This view displays information about estimated parse time in the shared pool for different pool sizes and the sizes range from %10 to %200 of current shared pool size. This can give you idea for sizing SGA and obliquely shared pool by the help of ASMM.
High updates with low pct_succ_gets can be a clue of performance problems when accessing that dictionary object. For frequently accessed dictionary caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%, depending on the application. If this ratio is higher and every previous control is OK then you should consider to increase the shared pool size
Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error. Objects causing a large number of other objects been flushed out from the shared pool are candidates to be pinned into the shared pool using dbms_shared_pool.keep procedure. You can check the x$ksmlru fixed table to see the candidates. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. Because this is a fixed table, once you query the table, Oracle will automatically reset the table so first insert the contents to temporary table like below,
The primary problem that occurs is that free memory in the shared pool becomes fragmented into small pieces over time. Any attempt to allocate a large piece of memory in the shared pool will cause large amount of objects in the library cache to be flushed out and may result in an ORA-04031 out of shared memory error. But how to understand the fragmentation ?
I have been working on Oracle over 10 years. Your web site gave me a very impression. I still can learn something and update my knowledge from here. Also you looks like an artist and make this web so beautiful.
To be honest I have lost my concentration to blogging after changing , country that I live , language I speak, marital status from single to married in the same year, but today I started writing again. I hope this time there wont be a long break period like this.
My prod DB is facing with Ora-07445 error,to my knoweldge or research it searching for a CHILD CURSORS which is not been in shared pool,can you please advice me,if needed i will share my alert & user trace file as well
ORA-07445:
exception encountered: core dump [string] [string] [string] [string] [string] [string]
Cause: An OS exception occurred which should result in the creation of a core file. This is an internal error.
Action: Contact your customer support representative.
The sum(sharable_mem) from v$sql and v$sqlarea for a sqlid is different. Which sharable_mem should be considered for the actual space utilized by a sql_id in shared_pool? Also what is the difference between v$sql and v$sqlarea?
thats also what I observe on 11.2.0.2, looks like it is a bug on 11.2.0.3 this problem disappears. I can see on 11.2.0.2 same sql has got x number of childs but v$sqlarea claims it is less then x number. If you have issues I suggest open an SR with ORacle
Halo,
I have a question. Our production was upgraded from oracle 10g to 11g. Since then there has been concurrency issue in the form of Library cache mutex x waits and shared pool contention which slows down the DB performance. This has been there for almost 4 months. We have tried the basic trouble shooting methods to resolve mutex waits and shared pool contention but still the problem exists. We have also raised an SR in metalink. Would be great if you could help us with your suggestions. Appreciate your time.
sir,very good post,i just want to know that can i increase shared pool and yesterday increased size of shared pool 900m but in enterprise manager after restarting database shows 0 why so? and reserverd pool is 76336332
SQL> SELECT NAME, BYTES
2 FROM v$sgainfo;
Excellent information. I was able to find some areas for improvement. For the high invalidations and reloads which I see I am trying to fix them. Your queries help us figure out the issues it would be great if we could have the solutions to fix those issues like which particular parameter needs to be changed. The 11th point to check the reserved pool sizehad a query to check for the particular value and if it is >0 increase the pool size. That point helped me fix it quickly. I am in a situation where the queries cannot be tuned as it is a package but have to try improving the complete daily load by tuning the database as much as I can.
NAMESPACEPINSPINHITSRELOADSINVALIDATIONS
Could it be just me or do some of the remarks appear like they are
coming from brain dead visitors? ? And, if you are
writing on additional places, I would like to keep up with everything fresh you have to post.
Could you make a list all of your public sites like your linkedin profile, Facebook page or twitter feed?
Hi Coskan,
Thanks for posting the details. My question is can a single statement (about 600 lines SQL create table as ) using parallel(16) hint (yes, no table alias to parallel) cause 4031? Last few days we identified one specific user causing this and crashed the PMON. For now I have asked that user to stop doing it. I also unset sga_target after trace file indicated lots of SGA resizing ops that was waiting. some MOS articles pointing to unsetting it. I am using 11.1.0.7 on Solaris.10.
This is a great article. I have same question as posed by Vinu about discrepancies between stats reported by v$sql and v$sqlarea. One possible explanation provided by some of my colleagues is not all cursors shown in v$sql are in cache and hence show up in v$sqlarea. So I tried to run join against v$open_cursor and v$sql and this helps to some extent but even with this I am not 100% satisfied. I opened an SR with oracle too.
Pay attention to the columns IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE. Column IS_BIND_SENSITIVE tells whether output of the query is sensitive to values of bind variable. Meaning if we change the value of bind variable, will the output change significantly?
As per Oracle definition, IS_BIND_SENSITIVE VARCHAR2(1) Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan.
You can see now, we have 2 executions but same cursor is being used. During the second run, we used different bind value and optimizer becomes aware that using different bind value is actually fetching different number of records and plan is expensive.
As per Oracle definition, IS_BIND_AWARE VARCHAR2(1) Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive.
Sometimes optimizer just creates a new cursor with same plan and it might go for index range scan even for selecting majority of rows from the table. This happened with me when I was trying this example. In such cases, cost of plan is determined by optimizer_index_cost_adj parameter. In my case, value of this parameter is set to lowest value of 1 so it will going for index range scan in all cases. Lower value of this variable represents lower cost for index access and it favors use of index. I set the value of this parameter to 50 and after that it was going for full table scan.
As we can see it created cursor 2 which is bind aware. So now cursor 0 is not shareable and will be removed from shared pool in sometime but cursor 1 and 2 are shareable and will be used by further executions.
Cursor sharing plays an important role when we are using adaptive cursor sharing. CURSOR_SHARING parameters determines if a cursor can be shared or a new cursor should be created. This was discussed very well on _adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
7fc3f7cf58