Tweaking _Shared_pool_reserved_min_alloc and ORA – 4031

Here comes one more article dedicated towards diagnosing ORA- 4031 and related parameters. While googling on ORA-4031 error or working on TAR with Oracle Support you will most likely hit this parameter “_Shared_pool_reserved_min_alloc “ and find a advice for altering this parameter to 4000 or so. And many times you go and implement it as this advice has come from Oracle support or you find it on one of your Favourite Oracle DBA’s site.
But how many times have you exactly gone ahead and tried to understand the validity of this suggestion.

Oracle Shared pool is managed by Heap Memory manager and is divided into memory chunks (Can visualize as blocks in tablespace) and the free memory chunks are managed by memory freelists. When a SQL statement is parsed in shared pool , it requires memory for storing execution plan and other internal structures. When the execution is complete the memory is freed and released back to the freelist. This repated use and release of memory causes fragmentation in shared pool.

Now algorithm for memory allocation is such that it will first try to get memory from the shared pool and then if the requested memory size is greater then _Shared_pool_reserved_min_alloc , then it will get the memory from Shared Pool Reserved area. By default this value is set to 4400 bytes. You can use below sql to find the current value for your database

col Parameter format a40
col “Instance Value” format a25
select a.ksppinm “Parameter”, b.ksppstvl “Value” from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like (‘%shared%’);

Suppose you get error like

ORA-04031: unable to allocate 4200 bytes of shared memory (“shared pool”,”unknown object”,”sga heap”,”state objects”)

Here we see failure size is 4200 bytes. In this case if you set _Shared_pool_reserved_min_alloc to 4100 then it will make memory available from Shared_pool_reserved_size and ORA -4031 will be avoided. But note that this is just to delay the ORA – 4031 error (Temporary Relief) and will not resolve the error. To actually resolve the issue you need to have a look at application and see if you are using Bind Variables so that you can reuse the sql statements and avoid shared pool fragmentation. You can read following discussion on AskTom Website to know more about Bind variables

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832

Note: – “_Shared_pool_reserved_min_alloc ” cannot be set below value of 4000

Recently while starting up database, I came across following error which was quite cryptic and took lot of time to resolve

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

This was contradicting the statement which I made above but I still tried to set it to less value but it errored out. On further investigation it turned out that there was blank space between M and amount of memory specified for one of memory parameters. i.e

DB_CACHE_SIZE=1200 M

Whereas it should have been

DB_CACHE_SIZE=1200M

Hope you see my post before wasting lot of time 🙂

7 April, 2008 by 10g 11g Performance 5

Undo Tablespace Sizing

In this post I will be discussing various types of Undo extents which can be helpful in determining cause of ORA-30036 i.e unable to extend segment by %s in undo tablespace ‘%s’ and ORA-1555 – Snapshot too old Error

While using Automatic Undo management (Set UNDO_MANAGEMENT initialization parameter to AUTO), we specify Undo_Retention parameter to set the time for which we want undo to be available.
You can query Status column of DBA_UNDO_EXTENTS to check the various kind of extents present.

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

This would return three values for STATUS field

1)ACTIVE

This means that undo extent is Active and being currently used by a transaction

2)UNEXPIRED

This represents the extents which are required to satisfy the time specified by Undo_retention Initialisation parameter.

3)EXPIRED

These are extents which are not being used by transaction and have crossed the time specified by Undo_retention .

So generally when undo segment space is full then we try to use the Expired extents and if there is no space to either get a new extent or reuse expired extents, then we go and use Unexpired extents. This sometimes lead to ORA-1555 error.

Starting from 10g, a new feature called Retention Guarantee has been introduced. If Retention Guarantee is enabled then you can be assured that you will not be reusing the Unexpired extents but this will mean higher chances of getting ORA-30036 error i.e failing to extend Undo Rollback segment.

So in case you get ORA-30036 , then query DBA_UNDO_EXTENTS to see if there are any expired segments. If there are none and Retention Guarantee is set, then it means that your Undo Tablespace is not properly sized.

You can make use of Undo Advisor available in 10g.

There is one more article on Undo Advisor which can be found Here

You can also manually find required size of Undo by using the below query

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS “Bytes”
FROM (SELECT value AS UR FROM v$parameter WHERE name = ‘undo_retention’),
(SELECT (SUM(undoblks)/SUM(((end_time – begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = ‘undo_tablespace’));

Where

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

5 April, 2008 by 10g 11g 0

Sign on Letter to Larry Ellison on AWR and ASH Licensing

While surfing across the blog’s, I found this particular link, a compaign which is aimed at getting free access to the ASH and AWR reports. I believe most of us know that we need to pay extra fees for the Diagnostic Tuning Pack which also includes ASH and AWR report. (If you’re not already familiar with these restrictions, you can read about them in the Oracle 10g Licensing Information Manual)

We all have seen how much better these reports are , when it comes to diagnosing general performance problem or some transient problems.

I believe this is a good initiative by Mark Brinsmead and I have already signed onto the letter. You can also join in by signing here.

29 March, 2008 by Random 0

HW enqueue contention with LOB

Definition
Enqueues are local locks that serialize access to various resources. Enqueue wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.

HW Enqueue – High Watermark enqueue is acquired when a segment’s high water mark is moved, which will normally happen during a heavy insert operation on the segment.

Detailed explanation
The HW enq is used to move the segment hwm and also to reclaim the space in lob segments.HW enqueue is required for extending segments in locally managed as well as dictionary managed tablespace. The period for which it is held will be shorter for locally managed tablespaces but we do hold it to serialize extensions.

When lobs are deleted or updated, space freed up is not reclaimed immediately. Deleted lob chunks are maintained in the lob index segment for purpose of CR – the committed free space is reclaimed from the lob index when the free space in the lob-base segment is exhausted. Delayed reclaimation is indirectly controlled by PCTVERSION or RETENTION. Movement of free space from lob index to lob-base segment is done under hw enqueue.

When inserts happen, space for new rows are satisfied in the following orderly ways:

a. Allocate free blocks from the segment directly (these blocks are not mapped by the Lobindex).

b. Reclaim freed blocks (from previous updates/ deletes, the ones mapped by the lobindex) that are not needed to satisfy PCTVERSION or RETENTION. In extreme cases, reclaimed blocks even if it means not being able to guarantee RETENTION. This requires HW-enqueue on the lobindex segment.

c. Extend the segment.

To Identify HW – contention
Finding Blockers:

If a session is stuck waiting on an “enqueue” wait use View V$LOCK to find the blocker/s:

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

To view the waiters:

SELECT * FROM v$lock WHERE request > 0;

Description:

* V$LOCK.TYPE is the lock type from P1
* V$LOCK.ID1 is the lock ID1 from P2
* V$LOCK.ID2 is the lock ID2 from P3

In v$lock Id1 is the tablespace number in which the segment (that is having its high water mark changed) resides and ID2 is the tablespace relative DBA of the segment header.

To identify the segment on which HW enqueue wait is being observed, run the following:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
from v$lock
where type = ‘HW’;

select owner, segment_type, segment_name
from dba_extents
where file_id = <>
and <> between block_id and block_id + blocks – 1;

Reducing/ Resolving Contention:

1. Recreate the objects with the freelist groups and preallocate extents using ALTER TABLE…ALLOCATE EXTENT. This will help improve performance

2. Review the freelist and freelist group setting. If it is too high – we may be moving more blocks to freelists and hold segment hw for a longer period of time since it takes time to format all the blocks.

If preallocation does not work, increase the number of free lists. The speed of high water mark movement depends on the number of free lists.

3. If this is during lob space reclamation, review the pctversion setting. Depending on the usage of the blob you can set pctversion to 0 or small values to avoid space reclamation. The default is 10%.

4. Most issues with HW contention on LOBs and space reclaimation have been resolved by applying patch# 6376915 and setting the event 44951.

This fix causes ASSM LOB space to batch up reclaim instead of just reclaiming the requested/required number of LOB chunks. To enable this fix, set event 44951 to the maximum number of chunks that you would like to have reclaimed per attempt. The maximum allowed is 1024. Anything larger becomes 1024. However, if the requested amount of space is larger than the event’s value, the reclaim will be for the requested amount of space.

5. Other considerations:
a. Partitioning the LOB segment.
b. Increasing the chunk size.
c. Rebuild the table or truncate the table.

One such issue which we faced in one of our environment.

AWR Report

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 129618 27-Jun-12 01:00:10 240 2.1
End Snap: 129631 27-Jun-12 03:10:18 276 2.1
Elapsed: 130.13 (mins)
DB Time: 1,683.99 (mins)

Cache Sizes

Begin End
Buffer Cache: 7,136M 7,136M Std Block Size: 8K
Shared Pool Size: 1,536M 1,536M Log Buffer: 19,456K

Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 12.9 0.2 0.01 0.00
DB CPU(s): 0.6 0.0 0.00 0.00
Redo size: 1,512,791.1 24,438.7
Logical reads: 42,623.7 688.6
Block changes: 6,243.5 100.9
Physical reads: 2,725.6 44.0
Physical writes: 364.1 5.9
User calls: 4,557.4 73.6
Parses: 1,646.8 26.6
Hard parses: 0.0 0.0
W/A MB processed: 0.6 0.0
Logons: 0.1 0.0
Executes: 1,647.5 26.6
Rollbacks: 0.8 0.0
Transactions: 61.9

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: HW – contention 119,796 57,558 480 56.97 Configuration
direct path read 1,293,467 16,330 13 16.16 User I/O
log file sync 554,365 9,730 18 9.63 Commit
db file sequential read 274,737 5,536 20 5.48 User I/O
DB CPU 4,789 4.74

We can see in above report that this is 2 hour report and 57% of DB time is spent on waits for enq: HW -Contention. Other wait events are also related to I/O and this also indicates slow I/O device (Avg wait for db file sequential read is 20 ms, normally you would see 6 ms). We  move to section for “SQL Ordered by Elapsed Time” to see if we can identify sql

SQL ordered by Elapsed Time

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
63,955.96 958,651 0.07 63.30 0.50 8.76 16w9z88mxsyz3 insert into SCOTT.JBPM4_…
15,336.34 0 15.18 4.81 95.54 7paqx6x20zdy3 JDBC Thin Client SELECT /*+ ORDERED PARALLEL (…

As per AWR report ,sql_id 16w9z88mxsyz3 is taking 64% of total elapsed time for all the sql and this is insert statement with very high execution rate. Digging further into structure of table, we identified that it has 2 clob columns and its residing in tablespace of 4M extent size. We can also look at section “Segments by Physical Writes” ,”Segments by Direct Physical Writes” to identify the segment and notice that they are Lob segments associated with this table. Solution in this case was to hash partition this table (64 partitions) to distribute the inserts into different segments and reduce the number of concurrent requests to allocate extents.

28 March, 2008 by Performance 4

Shared Sub Pools

Today I will discuss about Shared pool subpools which have been introduced in release 9iR2 and higher (To be precise >9.2.0.5) and can be controlled by a hidden parameter “_kghdsidx_count “

Most of you would not have come across this term/parameter unless you have faced ORA-4031 error.

As you all are aware that Shared Pool (part of SGA) contains the library cache, the dictionary cache , buffers for parallel execution messages, and control structures and also UGA in case of Shared Server Configuration(If Large pool is configured then UGA would be stored in Large Pool). The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Shared Pool Latch is used to protect and achieve the concurrency in Shared Pool. Subpools were introduced so as to have multiple Latches and relieve load on the single Latch.

Oracle determines the pools to be configured based on few factors

1) One subpool for every 4 CPU’s
2)Minimum amount of memory in each subpool.

Actually this varies from release to release. Please find below minimum size requirement for various versions

9.2.0.5 – 9.2.0.8 – 128Mb per subpool
10.1.0.1 – 10.2.0.3 – 256 Mb per subpool
10.2.0.4 > – 512 Mb
11.1.0.6 > 512 Mb

This means if on 10.2.0.2 , you have allocated shared_pool_size to 600M on a 12 CPU machine, you will have 2 Subpools to satisfy both the conditions.

In case you are using SGA_TARGET, the condition changes for calculating the number of subpools. In this case we assume that the maximum value to be allocated for shared_pool_size will be 50% of the target size. So considering SGA_TARGET is set to 600 M in above case, shared_pool_size will be assumed to be 300M so we will have only 1 Subpool.

3)Maximum of 7 subpools can be present

Note that even Shared Pool reserved area and Large Pool will be having subpools equal to the subpools in Shared Pool. We cannot set different value of subpools for these pools.

To determine number of subpools, you can use below query

SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm like ‘%kghdsidx%’ order by 1 ;

NAME VALUE
—————————— ——————–
_kghdsidx_count 4

Above query indicates that there are 4 subpools

In case you get ORA-4031 and trace file gets generated, then the trace file can also be used to know the number of subpools configured. To do this search on “Memory Utilization of Subpool”
e.g
Memory Utilization of Subpool 1
========================
free memory 10485760
Memory Utilization of Subpool 2
========================
free memory 20971520

This means that there are two subpools configured for your database.

Now there is no recommendation from Oracle on Number of subpools, though they suggest having 500M as minimum subpool size. I will say that in case you are not facing serious Shared pool Latch contention, 2 subpools should be sufficient (though I believe most of contention issues can be solved by tuning the application). But if anyone has seen performance improvement by changing the number of subpools then I would like him/her to share their findings.

Anyways to change the number of subpools, we need to set parameter _kghdsidx_count in pfile or spfile and restart the database

Spfile
——–
alter system set “_kghdsidx_count”=1 scope=spfile;

Restart of database is required as it is a Static parameter.

25 March, 2008 by 10g 11g Performance 2

Diagnostics For Database Hang

Many times Oracle DBA’s are in a situation when the database is hung and does not seem to be responding. In some scenarios, state is such that you cannot even connect to the sqlplus session. Majority people restart the database (Sometimes I wonder if this is due to the fact that most of us started working on Computer’s on Microsoft Windows 🙂 ) and then log a ticket with Oracle support . They inturn happily inform us that “They do not have any diagnostic information to diagnose and resolve the issue and we need to wait for next occurrence to collect some diagnostic information)

Based on my experiences , I am writing this article to assist my fellow Oracle DBA’s to diagnose the problem and collect the required information. So Let’s Start.

1) First of all we need to ensure that this is really a database hung situation and not a slow database condition. This can be done by asking some questions to users.

a) Is a particular user complaining of database hang or its the condition for all the users. If one or few user are reporting then are these users executing a batch job?

b)Are you able to make new connections to database?

c)Also check if any initialisation parameter has been changed recently?

d)Check if any resource manager plan is in effect.

One more way to establish if database is hung is t0 try to query v$session_wait view to find events being waited on

select sid,event,seq#,p1,p2,p3 from V$session_wait where wait_time=0 and event not like ‘%message%’;

This will give the events for all the waiting session. In case you see something like ‘log file switch (archiving required)’ then this problem is caused by archiving issue. See if there is free space in archiving destination.

Suppose this gives events like row cache enqueue or latches, then we need to gather Hanganalyze and Systemstate for the support.

Else it could be that you are experiencing a slow database. In this case use AWR or statspack to diagnose the issue. Look out for top timed events. In case you see Library Latch or shared pool latch consuming lot of time, then look at the Hard parses per sec section in Load profile.

2)Look at database alert log and see if any messages are present. In case you are facing Latching or Enqueue issues, then you might see errors like below

PMON failed to acquire latch, see PMON dump

Errors in file /u01/BDUMP/test10_pmon_12864.trc:

In this case you are required to upload tracefile (reported in alert log) to Oracle support.

Note : -Make sure that max_dump_file_size is set to unlimited so that the tracefile contains the complete data.

Coming back to Hanganalyze and Systemstate. Find details for them below

A)Hanganalyze

HANGANALYZE is used to determine if a session is waiting for a resource, and reports the relationships between blockers and waiters.

Use following syntax and take hanganalyze from two sessions at interval of 1 min

SQL>sqlplus “/ as sysdba”

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug hanganalyze 3

SQL>oradebug tracefile_name

Last command will report the tracefile name which has to be uploaded to Oracle support.

Alternatively you can use

SQL>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3’;

In case you wish to understand how to interpret hanganalyze file, then use Metalink Note:215858.1: Interpreting HANGANALYZE trace files to diagnose hanging and performance problems

B)Systemstate

Systemstate is used to dump the process information which is useful for Oracle support to diagnose why the sessions are waiting.

For 9.2.0.6 and above gather systemstate as below

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug dump systemstate 266

SQL>oradebug tracefile_name

Last command will report the tracefile name which has to be uploaded to Oracle support. Perform this 2-3 times at interval of 1 min.

Again you can use

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266’;

For Oracle 9.2.0.5 and less use level 10 instead of 266

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266’;

Level 266 includes short stacks (Oracle function calls) which are useful for Oracle Developers to determine which Oracle function’s are causing the problem. This is also helpful in matching existing bugs.

In case you are unable to connect to database then capture systemstate using below note

Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

Apart from this, following information can also be captured

a)Database alert log

b)AWR report /statspack report for 30-60 min during database hang

c)Output of OS tools to ensure that everything is fine at OS level.

E.g

$vmstat 2 20

This will capture 20 snapshots at 2 seconds interval. Look for CPU contention or swapping issues

In Addition to above , you can use utility called LTOM which has got some predefined rules based on which it determines that database is in Hung situation and takes systemstate and hanganalyze dump automatically .

Please refer to following Metalink note for more details

Note:352363.1: LTOM – The On-Board Monitor User Guide

In case you are able to narrow down to a blocking session manually, then you can very well take errorstack for the blocking process as below

connect / as sysdba

oradebug setospid 1234

oradebug unlimit

oradebug dump errorstack 3

wait 1 min

oradebug dump errorstack 3

wait 1 min

oradebug dump errorstack 3

oradebug tracefile_name

* In case the ospid for blocking session is 1234

Last command will report the tracefile name which has to be uploaded to Oracle support.

In case you are able to capture the above information, you stand 99% chance of getting solution . I have kept 1 % for the cases when Oracle Support will ask for setting up some events and waiting for Next Hang Occurence for getting more information.

21 March, 2008 by Performance 6

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers