ttPLSQLMemoryStats

Description

This procedure returns result statistics about PL/SQL library cache performance and activity.

Required privilege

This procedure requires no privilege.

Syntax

ttPLSQLMemoryStats(paramName, paramValue )

Parameters

ttPLSQLMemoryStats takes no parameters.

Parameters

ttPLSQLMemoryStats returns the results in the following columns:

The following statistics are returned:

• Gets: Number of times a lock was requested for a PL/SQL object.

• GetHits: Number of times a PL/SQL object's handle was found in memory.

• GetHitRatio: Ratio of GetHits to Gets.

• Pins: Number of times a PIN was requested for PL/SQL objects.

• PinHits: Number of times all of the metadata pieces of the library object were found in memory.

• PinHitRatio: Ratio of PinHits to Pins.

• Reloads: Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from the database.

• Invalidations: Total number of times objects in this namespace were marked invalid because a dependent object was modified.

• CurrentConnectionMemory: The total amount of heap memory, in Megabytes, allocated to PL/SQL on this database connection.

• DeferredCleanups: Total number of times a deferred cleanup occurred.

Examples

connect "DSN=sample";
Connection successful:
DSN=sample;UID=timesten;DataStore=/scratch/timesten/sample;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;TypeMode=0;PLSQL_MEMORY_SIZE=32;PLSQL_MEMORY_ADDRESS=20000000;PLSQL=1;
(Default setting AutoCommit=1)
Command> create procedure hello is begin dbms_output.put_line('Hello, World!');
end;
    > /
Procedure created.
Command> call ttPlsqlMemoryStats; 
< Gets, 485.00000 >
< GetHits, 444.000000 >
< GetHitRatio, .9154639 >
< Pins, 260.00000 >
< PinHits, 178.000000 >
< PinHitRatio, .6846154 >
< Reloads, 4.000000 >
< Invalidations, 0.000000e+00 >
< CurrentConnectionMemory, 56.00000 >
9 rows found.