Hi Ranier,
>> Will pinning a SP really also pin cursors build from running it ?
I don't know, that's an excellent question!
I found this, titled how to pin a cursor:
CODE
conn / as sysdba
GRANT select ON gv_$open_cursor TO uwclass;
conn uwclass/uwclass
-- SQL statement to load cursor into the shared pool
VARIABLE x REFCURSOR
BEGIN
OPEN :x for
SELECT *
FROM all_tables;
END;
/
--Determine address and hash value of the SQL statement
SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';
-- substitute your query results for mine, below.
exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');
conn / as sysdba
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND TYPE = 'CURSOR';
******************************************
>> Was not able to test it so far.
The best way to test it would be to run the queries and then flood the library cache with new SQL and see it it stays!