-
Title
Sharable Memory usage showing differing results -
Description
In the Configuration & Memory window | Shared Pool Utilization tab | Cursors screen, in the list of the cursors it appears to be showing incorrect values for Sharable Memory, compared with the same output you will find in Toad for Oracle with the statement:
select * from gv$sqlarea order by sharable_mem desc -
Resolution
In Spotlight, the SHARABLE_MEM column description is the amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors.
Spotlight gets the values for the Cursors from the following query which sorts the cursors by sharable memory before ranking them and then returning the top 100 by rank order.
SELECT NAME, TYPE, sharable_mem, loads, executions, kept
FROM ( SELECT o.name, o.type, o.sharable_mem,
o.loads, o.executions, o.kept,
RANK() OVER (ORDER BY sharable_mem desc) AS order_ranking
FROM v$db_object_cache o
WHERE o.executions > 0
AND o.type = 'CURSOR'
)
WHERE order_ranking <= 100
ORDER BY order_ranking;The difference between this and what Toad / the statement is doing, is that querying from v$sqlarea will return sharable memory for a cursor plus all child cursors associated with it.
You can check this by picking a sql statement and then summing the executions and sharable memory for the output from the Spotlight sql, and comparing this against the output for the same sql statement from the Toad query.