Application Advanced Tuning

Oracle RAC Database Admin

#Remove fragmentation on Table Level
Node-1 Only

Online Segment Shrink

ALTER TABLE EMP ENABLE ROW MOVEMENT;

ALTER TABLE EMP SHRINK SPACE CASCADE;

ALTER TABLE EMP disable ROW MOVEMENT;

ALTER TABLE DEPT ENABLE ROW MOVEMENT;

ALTER TABLE DEPT SHRINK SPACE CASCADE;

ALTER TABLE DEPT disable ROW MOVEMENT;

more

whitepaper

#Generate table and Index stats
Node-1 Only

exec dbms_stats.gather_table_stats ('SCOTT','EMP', cascade=>TRUE);
exec dbms_stats.gather_table_stats ('SCOTT','DEPT', cascade=>TRUE);
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'NIDXUQ_CORESESS_CID', estimate_percent=>100, degree=>4);

To check Index stats

SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

ANALYZE INDEX NIDXUQ_CORESESS_CID VALIDATE STRUCTURE;

SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
IF required Rebuild Index

flush the shared pool and buffer cache
Node-1 and Node-2

#flush the shared pool for fore fully generate the new plan for sql statement
alter system flush shared_pool;

#flush the buffer cache for clean the sga for age out of some bad blocks from sga.
alter system flush buffer_cache;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s