Performance Tuning -1

                                          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;