ttPLSQLMemoryStats

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

Required Privilege

This procedure requires no privilege.

Related Views

This procedure has this related view.

SYS.V$PLSQL_MEMORY_STATS

Syntax

ttPLSQLMemoryStats()

Parameters

ttPLSQLMemoryStats takes no parameters.

Result Set

ttPLSQLMemoryStats returns the results in the following columns:

Column Type Description

paramName

TT_VARCHAR(30) NOT NULL

The name of the result statistic returned in this row.

paramValue

BINARY_FLOAT NOT NULL

The value of the result statistic returned in this row.

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 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 MB, 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;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.