Saturday 20 January 2007

Shared_pool latching

Trying to get answer for this from Gurus like Thomas Kyte and Jonathan Lewis.
SHARED POOL LATCHING in LIBRARY CACHE. Why is it bad?


Shared pool is where the SQL code and the data dictionary lives. Isn’t this structure ‘mostly’ READ_ONLY? Why latch/lock? Why do you care to latch? What will be corrupted in the library cache? Why does Oracle have to “protect” it, as you say above?

To stop latching/parsing and the bad scalability which comes with it, Tom Kyte says we must use bind variables, yes I agree, but why latch the shared pool? What will that other “nasty” user do to your SQL statement in the shared pool apart from sharing the SQL with you.

Shared Pool is not like the Data Buffer where data lives . In the Data Buffer I understand the reason latching/locking (shared locks, exclusive locks during updates, selects) is bad, and why it inhibits concurrency, ‘Consistent Gets’ ACID theory of transactions, locks, transactions trying to concurrently modify same data, phantom reads and dirty reads etc, they all come to mind, fine.
But why in SHARED POOL, it is just SQL text, what is going to be corrupt? Why does it have to be concurrent and use latches (serialization devices) ?

Wiating Response from :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::
P11_QUESTION_ID:1513005503967#58524800346079273

http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/

1 comment:

Kubilay said...

Jonathan Lewis answered my question. Check the link http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/

In summary: The only limit seems to be the hardware. The OS will put up its’ own set of locks (outside ACID) anyway and those locks are expensive and scarce!