Peter Ramm's Oracle performance analysis stuff

Update 2023-05:  Checked the above issues against Rel. 19.18 It works much better now than before in Rel. 12.x.  Not completely without the risk of getting migrated rows and not deterministic at all. But with much smaller amount of situations resulting in increasing storage footprint and migrated rows.

Starting with release 10.1 Oracle introduced automatic shared memory management. Setting only SGA_TARGET=x without defining limits for the SGA-components (buffer cache, shared pool, large pool, java pool) allows your database to redistribute SGA memory between components due to current needs. This will work properly in many cases but sometimes there are some pitfalls: A very common problem on Oracle databases are applications without usage of bind variables in their SQL statements. Not using bind variables leads to excessive SGA memory consumption in shared pool and parse effort if you have a large number of SQLs executed with different values in filter conditions stored as literals. For example: SELECT a, b FROM Item WHERE ItemNo = 453612; SELECT a, b FROM Item WHERE ItemNo = 876886; etc. instead of SELECT a, b FROM Item WHERE ItemNo = :ItemNo; In such a situation the DB may resize the SGA. The size of the shared pool will grow thus reducing the size of the buffer cache