Library cache latch mapping

2 April 1999

alter session set events 'immediate trace name library_cache level 3';

The trace file will contain stuff like this ...

name=SYS.STANDARD
hash=86458413 timestamp=07-04-1994 00:00:00
namespace=TABL/PRCD flags=TIM/SML/[02000000]
kk-dd-aa-ll=00-1d-00-1d lock=0 pin=0 latch=0

open cursors current

12 April 1999

declare
  cursor c is select empno from emp;
  crs number;
  n number;
begin
  select value into crs from v$mystat where statistic# = 3;
  dbms_output.put_line('curr.op.cursors: ' || crs);
  open c;
  fetch c into n;
  close c;
  select value into crs from v$mystat where statistic# = 3;
  dbms_output.put_line('curr.op.cursors: ' || crs);
end;

curr.op.cursors: 2
curr.op.cursors: 4

SQL> select value from v$mystat where statistic# = 3;
    VALUE
---------
        1

Why does the cursor c remain opened after the CLOSE command is submitted?

Hard and soft parses

12 April 1999

Library cache lock

16 June 1999

Invalidations

20 August 1999

select sql_text, invalidations from v$sqlarea where invalidations > 0;

To demonstrate, create a test table, select from it, grant and revoke some permissions for it, and you should see the invalidations.

Hash values don't match

27 August 1999

decode(sign(HV), -1, HV + power(2,32), HV)

where HV is the hash value column name. This of course suppresses the use of the index on hash value once again, but that leaves you no worse off than you would have been under 7.3!

_kgl_latch_count

8 September 1999

Who is doing what?

5 October 1999

sql_address = address and
sql_hash_value = hash_value

ORA-600 [17033]

21 October 1999

ORA-00600: internal error code, arguments: [17033], [509], [128], [], [], [], [], []

An entry is being generated every few seconds, and the trace files keep filling up the file system. For now I'm moving them off, but it looks like queries are being affected as well. This happened one other time earlier this summer, and bouncing the database took care of the problem. The only thing I can find on MetaLink is this reference to the _kgl_bucket_count parameter:

Bug 381193 defines a scenario where ORA-600 [17033] is signaled due to lack of resources for the library cache hash table. The workaround for this bug is to set _kgl_bucket_count = 4. Again, this should only be set if it has been determined that the ORA-600 is actually caused by this bug as it can be signaled for other reasons.

Library cache hash table expansion

27 October 1999

It starts at 509 buckets by default. If the number of named objects exceeds twice the number of buckets, then a new hash table is allocated at twice the current size, and all objects are rehashed onto the new table before the old hash table is deallocated.

To avoid rehashing, and the simple doubling which leads to a non-prime hash table size, _kgl_bucket_count can be used to set the initial size of the hash table. The parameter is an index into a fixed array, as follows:

0:	509 buckets
1:	1021 buckets
2:	2039 buckets
3:	4093 buckets
4:	8191 buckets
5:	16381 buckets
6:	32749 buckets
7:	65521 buckets
8:	131071 buckets

To measure the impact of dynamic rehashing on performance, I would suggest that you capture the output of this script and correlate that with other performance indicators. I expect that you will find that it is heavily dependent on the parse rate. Unfortunately, the higher the parse rate, the greater would be the intrusion effect of running the script regularly.

KQLS heap

30 October 1999

In Oracle7 this stuff was in the dictionary cache. However, given that they were "subordinate" caches, only every accessed through their parent, and did not need rowcache enqueue locks to be applied, it was not necessary to have them in the dictionary cache. So they were moved into the library cache in 8.0 as heap number 10 under the library cache handle for the parent object. They are recurrent objects, subject to normal shared pool LRU semantics (rather than those of the dictionary cache). You can get rid of them by flushing, but they will be loaded again if anyone applies a KGL pin to the parent handle. So there is not a lot you can do at a system level. If a lot of memory is required, it is because of the complexity of the schema, and that amount of memory must just be regarded as necessary shared pool overhead. It will not continue to grow, it will reach equilibrium, at a level dependent on the memory pressure in the shared pool.

_kgl_latch_count

20 November 1999

cursor_sharing

11 January 2000

The 8.1.6 cursor sharing facility will address some problems with the library cache latches, but will not be a panacea for shared pool latch contention. Yes, it may reduce load on the latch somewhat, but you still need to get the shared_pool_size right in order to avoid long free lists and contention under a high execute rate.

shared pool latch again

11 January 2000

High version count

2 February 2000

Public synonyms

10 February 2000

For a statement that refers to a single table only, the CPU usage during the parse phase is approximately 1:2:4 for a fully-qualified reference, a private synonym and a public synonym respectively. For complex SQL statements, the cost of synonym usage is even greater. The synonym based solutions involve potentially large library cache dependency tables, and are sensitive to the aging out of the library cache objects representing the table name in the users' own schemas. These library cache objects cannot be "kept". In an instance with high parse rates, the use of synonyms increases CPU usage dramatically, and often causes contention on the latches that control access to the library cache, dictionary cache and shared pool.

The suggestion that a large number of private synonyms can cause performance problems in the library cache because they all hash onto the same hash bucket is mistaken. There can only be one user of each name, and the user name is part of the object name on which the hash is calculated. Thus private synonyms do not increase the risk of library cache hash collisions.

People often claim that synonyms are needed to allow code to be tested against differently named schemas, particularly in development. However, as well as the ALTER SESSION SET CURRENT_SCHEMA command, there are numerous code management techniques to obtain the same flexibility without using synonyms. The ALTER SESSION SET CURRENT_SCHEMA command can be issued from an application startup procedure, without any security concerns. However, in versions prior to 8.1, it must be executed before any other SQL is parsed, or there may be risk of data corruption (which is why it was previously unsupported).

8.1.5 performance

6 March 2000

There have also been dramatic changes in the management of Oracle's buffer cache in release 8.1. In general, these are good for performance and result in higher cache hit rates. However, some cache-intensive "bad" SQL does worse under the new algorithms. The queries affected are those that use a small enough set of blocks to fit in cache, but larger than can remain concurrently pinned, and then use those blocks repeatedly and intensively.

Performance using roles

6 March 2000

SUB_SYSTEM_SEL_ROLE
SUB_SYSTEM_INS_ROLE
SUB_SYSTEM_UPD_ROLE
SUB_SYSTEM_DEL_ROLE
SUB_SYSTEM_ALL_ROLE

There is however a clear case for reducing the number of grants against each object (by using roles). The grants live in the KQLS heap, which is logically part of the dictionary cache, but physically part of heap 10 of the library cache object. Large numbers of grants increase the size of heap 10, and the CPU required to scan it, so roles may be useful to reduce these costs. The way cursor authorization works is that the library cache object for a cursor contains an accesses table which enumerates the privileges required on all the objects accessed by the cursor. For a user to be authorized to execute the cursor, the user must have the required permission on each accessed object. For each access, the library cache object is pinned, and the grants are scanned linearly for a match against their enabled roles. The authorization is then cached in the library cache object for the cursor, so that subsequent executions of that cursor by the same user do not require re-authorization.

V$OPEN_CURSOR

8 March 2000

shared pool latch contention

16 March 2000

Of course Oracle is claiming to remedy this with the new cursor_sharing initialization parameter in 8.1.6. Have you tried this out yet?

I believe that 8.2 will address this problem by rounding up all memory allocations to one of a set of fixed sizes. This may waste a lot of shared pool memory, but will kill the fragmentation issue entirely. Controlling (or randomizing) the sizes of SQL statements is not feasible at present, because most of the chucks that affect the problem presently are not actually from the SQL area.

Dictionary cache miss rate

24 March 2000