Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • How to find who is using which Rollback segment and how many rows or blocks in that rollback segments, Oracle
  • FGA Part-I Oracle
  • move_arch_files.ksh Linux/Unix
  • on IBM-AIX for display Linux/Unix
  • oracle 11g RAC on vmware Oracle
  • Changing default shell Linux/Unix
  • online_ts_bkup.sql Oracle
  • sess_server.sql Oracle
  • find the files that are 1 day old. Linux/Unix
  • How does one SELECT a value from a table into a Unix variable? From SQL to Shell Linux/Unix
  • v$backup.status information Oracle
  • Good notes for shared pool Oracle
  • move_arch_files.ksh /* Good One */ Linux/Unix
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • Temporary tablespace explaination Oracle

Another Tuning Article for subheap of shared pool

Posted on 16-Feb-2007 By Admin No Comments on Another Tuning Article for subheap of shared pool

http://www.mail-archive.com/oracle-l@fatcity.com/msg79721.html

Oh yeah, I forgot to say that in 9.2.0.3 the shared pool was broken up into “heaps” (Oracle terminology) but whatever fancy stuff they were trying to accomplish by doing this (I think part of the magical self-tuning initiative) was buggy and this hastened the fatal 4031 situation. There is a parameter (so I have been told) Shared pool has always been a heap (inside SGA heap, and can contain heaps as well if requested). Shared pool heap is physically divided to equal sized extents. But space inside shared pool heap is allocated in “chunks” and there are freelists&lru lists for tracking free and unpinned recreable

tchunks. There is actually 255 freelists, each for different allocation size range, starting from 16 bytes up to about 64+ kilobytes. Depending on how much memory allocation request has been made, the proper freelist is scanned. When matching size chunks aren’t found, the closest match is split. Anyway, lets call these 255 freelists a shared pool “freelist set” (don’t

know the correct Oracle term). But people often suffer from shared pool latch contention when a lot of shared pool memory allocation occurs (no bind variables are used in SQL etc etc).

Lots of concurrent memory allocation requests mean lots of processes trying to acquire shared pool latch and scan through relevant freelist, if no sufficiently large chunk found, then LRU list as well. During these operations, the shared pool latch is being hold by session doing the scanning causing shared pool latch contention. Various “experts” suggesting to increase your shared pool make the situation even worse, because in time, the LRU and freelists get even longer, thus

one scan takes more time to complete or fail.

_kghsidx_count = 1

(The correct name is _kghdsidx_count)

So, in 9i Oracle introduced the ability to create several heaps for

serving

shared pool. If you’ve set the shared_pool_size to 64M and

_kghdsidx_count

to 4, you’ll have 4*16M shared pool heaps, every heap having its own

descriptor, extents, LRU lists, freelists and shared pool child latch

(there

is no such child latch in 8i). Shorter lists mean faster scanning, more

latches mean more scalability when serializing access to a resource. The

downside is, that usually the “resource” has to be split that every latch

protects one and only one part of resource. Thus if the shared pool is

split

into 4 parts and all memory allocation requests happen to use the same

freelist for some reason, only 25% of memory can be used. This is the

reason

why you can avoid ORA-4031s when setting _kghsidx_count to 1 – this

practically enables the old behaviour.

In 8i there is only one set of freelists, in 9i there can be more sets,

default is 1 and max limit is 7 as far as simple testing on my

9.2.0.4/W2k

has showed – I have only 7 shared pool child latches and didn’t find a

way

to increase them.

When you increase _kghdsidx_count, you see more lines in x$kghlu as well,

one for each heap (normally there was only one). Also you can verify the

behaviour when taking SGA heapdump at level 2 and search for “HEAP DUMP”

or

“FREE LISTS” in trace, there are as many free lists in dump, as you’ve

stated with _kghdsidx_count init parameter. (there is one “extra” heap in

dump, this is the SGA parent heap for shared pool heaps).

that makes the shared pool one big memory area.

One of the “fixes” in 9.2.0.4 is to make this the default now (so I

have

been told).

Can’t get my hands oon 9.2.0.2 or 0.3, but in 0.4 (on Windows), the

_kghsidx_count defaults to 1 anyway, check it out on your systems.

Tanel.

Oracle, SQL scripts

Post navigation

Previous Post: good note for shared pool tunnig
Next Post: To Find Orphan OS processes.

Related Posts

  • Consolidated Reference List Of Notes For Migration / Upgrade Service Requests -ID 762540.1 Oracle
  • How to specify 2 arch location to avoid any kind of DB hanging. Oracle
  • create database syntax Oracle
  • Oracle Data Direct to TAPE Oracle
  • Jai Shree Ram Oracle
  • Renaming the column name Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • move_arch_files.ksh Linux/Unix
  • DBMS_UTILITY PACKAGE Oracle
  • sesswait.sql Oracle
  • Physical Standby switchover with session active Oracle
  • oracle Dba site Oracle
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • Changing default shell Linux/Unix
  • Histogram information Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme