Performance Tuning

                                          Performance Tuning

 

Routine Day–to-Day DB Health Management Tasks:

 

Gathering Statistics :

a)Table Stats(statistics)

Sql> exec dbms_stats.gather_table_stats(‘HZ’,’EMP’);

Sql> select table_name,to_char(last_analyzed,’hh24:mi:ss dd-mon-yy’) from dba_tables where table_name=’EMP’;

b)Schema Stats

Sql> exec dbms_stats.gather_schema_stats(‘HZ’);

c)Database Stats

Sql> exec dbms_stats.gather_database_stats();

d)Dictionary Stats

Sql>exec dbms_stats.gather_dictionary_stats();

e)Index Stats

Sql>exec dbms_stats.gather_index_stats(‘HZ’,’IND_EMP’);

 

Deleting Stats :

 a)Table Stats

 Sql>exec dbms_stats.delete_table_stats(‘HZ’,’EMP’);

 b) Schema Stats

 Sql>exec dbms_stats.delete_schema_stats(‘HZ’);

 c) Database Stats

 Sql>exec dbms_stats.delete_database_stats();

 

Defragmentation :

 

Table :

 

Sql> alter table table_name shrink space compact;

 

Index:

 

Making index invisible/visible

SQL>select index_name,visibility from user_indexes where index_name=’IND_EMP’;

SQL>alter index ind_emp invisible;

SQL>alter index ind_emp visible;

 

 

Analyze First

 

Sql>analyze index index_name validate structure;

sql>select height,name from index_stats where name='IND_NAME';

 

Rebuild/Coalesce:

Sql> alter index index_name rebuild online;

Sql> alter index index_name coalesce;

 

 

 

Pin /Unpin Database objects :

If dbms_shared_pool package is not  there then run the .sql script

Sql> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Pinning  object

Sql> exec dbms_shared_pool.keep(‘DBMS_SPACE_ADMIN’);

Sql> select * from v$db_object_cache where kept=’YES’;

Unpining  object

Sql> exec dbms_shared_pool.unkeep(‘DBMS_SPACE_ADMIN’);

 

 

 

 

Shrinking temporary tablespace

SQL>alter tablespace temp shrink space keep 100m;

 

Checking and Compiling DB Invalid Objects (Packages,Procedures )

 

Compiling DB invalid objects

Sql> select count(*) from dba_objects where status=’INVALID’;

Compiling Packages

Sql > alter package pack_name compile;

Sql> alter view view_name compile;