10g & 11g Tuning features
10G features:
ASSM- Automatic segment space management.
It’s a query tuning based feature. We can do a rebuild online.
Sql> alter table emp enable row movement;
Sql> alter table emp shrink space;
We can de-fragment the table by using above two methods. Internally, it will move the data block by block and there will be slowness.
Sql> alter table emp shrink space compact; – Compact only work, when the table is not used by anyone.
Sql> alter table emp shrink space cascade; – It will shrink both table and indexes.
We can use advisory to use the better option.
Sql> select * from dba_advisor_logs;
Sql> alter table emp disable row movement;
Best practice: Use alter shrink.
ASMM – Automatic shared memory management
It’s a memory based feature. Dynamic parameter.
Parameter:
SGA_Target= sga_max_size – Equal or Less than that
SGA_Target < sga_max_size
SGA_Target will control the following parameters.
Shared_pool_size, DB_cache_size, large_pool_size & Java_pool_size. Default it will be zero.
SGA_Target will not control the following parameters.
Log_buffer, DB_keep_cache_size, DB_recycle_cache & streams_pool_size.
SGA_Target is not have space, use below query and get advice.
Sql> select * from v$sga_target_advice;
11G features:
AMM – Automatic memory management
Dynamic parameter.
Memory_target = Memory_max_target
Memory_target < Memory_max_target
Memory_target = SGA_target+ PGA_Aggregate_target – It will handle both.
EX:
Sql> alter system set memory_max_target = 16G scope=spfile;
Sql> alter system set memory_target = 12G scope=spfile;
Sql> alter system set SGA_target = 0 scope=spfile;
Sql> alter system set PGA_aggregate_target = 0 scope=spfile; Create a pfile.
Sql>shutdown immediate; –
SQL> startup
We can get advice from advisory .
Sql> select * from v$memory_target_advice;
Automatic undo retention tuning
Undo_retention= 900
Undo_tablespace=undotbs1
Undo_management=Auto
How to fix ORA-1555 snapshot old error.
We can set undo grantee .
Sql> select grantee from v$tablespace;
Sql> alter tablespace undotbs1 retention grantee / nograntee;
Check the retention grantee needed.
Sql> select * from v$undo_advice;
