Oracle 10g & 11g Tuning features-19


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.


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.


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



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;

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

44 + = 49