Greatly Boosting Buffer Cache Efficiency with IOT

One very important thing regarding the IOT is the clustering factor.

Since the data are stored together according to the PK, as a bonus you decrease the amount of buffer cache usage.

Martin Widlake wrote a wonderful article about it:

http://mwidlake.wordpress.com/2011/08/08/iot-part-4-greatly-boosting-buffer-cache-efficiency/

 

How to find out what is eating your PGA and possible gonna eat your temp seg

select sql_id,MAX_MEM_USED from  v$sql_workarea_active order by 2;

SQL_ID        MAX_MEM_USED
————- ————
3mfa13g41v37x            0
59x35kd3vbd8f        71680
3z294wg0udavm        71680
9m2ctqgmdmw5w        88064
1rwxnm0m9vmc3       306176
9t202yjs4usz2       337920

Tuning shared_pool_reserved_size

select case when (request_failures>0 and request_misses>0)
then ‘increase reserved’
—
when (request_failures>0 and free_space*2>(select to_number(value) from v$parameter where name=’shared_pool_reserved_size’))
then ‘increase the shared_pool_size’
—
when (request_failures=0 and free_space*2>(select to_number(value) from v$parameter where name=’shared_pool_reserved_size’))
then ‘decrease the shared_pool_reserved_size’
—
else
‘If your suffering 4031 – Pin Objects and if possible increase shared_pool’
end
from v$shared_pool_reserved;

You may also check the  _shared_pool_reserved_min_alloc

 

How to have a raw idea about the table growing usage

This just will work for tables already populated.

You must connect as sysdba

SET SERVEROUT ON;
CREATE OR REPLACE PROCEDURE ESPACO(VOWNER IN VARCHAR2,VTABELA IN VARCHAR2,VPROJETADO IN NUMBER)
IS
SAIDA NUMBER;
BEGIN
EXECUTE IMMEDIATE ‘SELECT BYTES/(SELECT COUNT(*) FROM ‘||VOWNER||’.’||VTABELA||’)*’||VPROJETADO||’ FROM DBA_SEGMENTS WHERE SEGMENT_NAME=:1 and owner=:2′ into saida using vtabela,vowner;
dbms_output.put_line(TRUNC(saida));
END;
/