database

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

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

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