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
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

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

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe