Originally posted by OracleDoc Ever since I've pinned it, the loads have stayed the same.
Have you monitored the total number of loads though? If DBMS_PIPE is now pinned then I expect that other code is being aged out faster and suffering more loads. I'd try monitoring total loads, not just this one package.
From the Tom Kyte thread, this is the most interesting comment for me ...
If you use it -- it'll stay.
If you don't -- it'll go.
If you pin it -- it'll stay -- meaning, if you use it, it would stay anyway. If
you DON'T use it, why did you pin it?
pinning was "useful" for big packages before the 4k pagesize stuff.
after that, not very useful
It makes sense ... and if you pin a package then it has to be at the expense of others, right?
Originally posted by slimdave Have you monitored the total number of loads though? If DBMS_PIPE is now pinned then I expect that other code is being aged out faster and suffering more loads. I'd try monitoring total loads, not just this one package.
From the Tom Kyte thread, this is the most interesting comment for me ...
It makes sense ... and if you pin a package then it has to be at the expense of others, right?
I agree with SlimDave. Which is usually a safe bet.
Originally posted by gandolf989 I haven't yet found a use for pinning objects. If you keep you code modular and efficient, reduce the number of hard parses, and make sure your sga is sized correctly, you should not need to pin anything. i.e. the less unique sql and pl/sql that hits your database the more likely it is to already be in memory. Think bind variables and bulk binding whenever possible.
Originally posted by OracleDoc Oracle has a great article on this task and makes recommendations as to what should be pinned.
Code:
SELECT owner, name, o.type, sharable_mem,
loads, executions, kept
FROM v$db_object_cache o
WHERE loads > 0 AND o.type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE')
ORDER BY sharable_mem DESC
Of course you can modify it to check the number of executions and such.
Both the 8i and 9i reference manuals I've looked at indicate that the executions column of v$db_object_cache is not used. They suggest using the executions column of v$sqlarea instead. Anyone know why?
Also just wanted to say thanks to everyone for all the input!
Reply With Quote
Bookmarks