Wednesday , July 26 2017
Home / Oracle DBA / ORACLE INSTANCE TUNING WITH REPORTS

ORACLE INSTANCE TUNING WITH REPORTS

ORACLE INSTANCE TUNING

ORACLE INSTANCE TUNING

1.   If even after performing all the steps in database tuning, performance problem exists,
we need to instance level tuning

TKPROF report

1.     Transient kernel profiler is a report which show details like time taken, cpu utilization
in every phase (parse, execution and fetch) of sql execution

# Steps to take TKPROF report

SQL> grant alter session to scott;
SQL> alter session set sql_trace=TRUE;
SQL> select * from emp;
SQL> alter session set sql_trace=FALSE;
The above steps will create a trace file in udump
location
[oracle@server1 udump]$tkprof prod_ora_7824.trc
tkprof_report.lst
2.      From
TKPROF report if we observe that statement is getting parsed everytime and if
it is frequently executed query, reason could be statement flushing out from
shared pool because of less size. So increasing shared pool size is the
solution
3.    If we
observe fetching is happening everytime, it could be because of data flushing
from buffer cache for which increasing the size is the solution
4.     If the
size of database buffer cache is enough to hold the data bit still data is
flushing out, in such cases we can use keep & recycle caches

# To enable keep & recycle caches

SQL> alter system set db_keep_cache_size=50m
scope=both;
SQL> alter system set db_recycle_cache_size=50m
scope=both;

# To place table in keep or recycle caches

SQL> alter table scott.emp storage (buffer_pool
keep);
SQL> alter table scott.emp storage (buffer_pool
recycle);
5.     If a table is placed in KEEP cache, it will be there in the instance till its
lifetime without flushing. If a table is placed in RECYCLE cache, it will be
flushed immediately without waiting for LRU to occur
Note: Frequently used tables should be placed in keep
cache whereas full scan tables should be placed in recycle cache
STATSPACK REPORT
1      It is a report which details database performance during a given period of time

# Steps for generating statspack report

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
This will create a PERFSTAT user who is responsible
for storing statistical data
[oracle@server1 udump]$sqlplus perfstat/perfstat
SQL> exec statspack.snap; à begin time
SQL> exec statspack.snap; à end time
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
2.     Statspack report can have levels from 1 to 10 and the default is 5
3.   In 8i, the concept of collecting statistics can be done using utlbstat.sql and
utlestat.sql scripts
*A separate hand out is given for learning how to analyze statspack report
View More:
ORACLE RECOVERY CATALOG CONFIGURING
ORACLE RMAN RECOVERY SCENARIOS WITH EXAMPLES
RECOVERY MANAGER (RMAN) ARCHITECTURE

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

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