Category: Performance

ASH and AWR Performance Tuning Scripts

Top Recent Wait Events

col EVENT format a60 

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/

Top Wait Events Since Instance Startup

col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

First determine the snapshot id values for the period in question.

In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10 
select segment_name,object_type,total_physical_reads
 from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
 where statistic_name in ('physical reads')
 order by total_physical_reads desc)
 where rownum

Top 5 SQL statements in the past one hour

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum

SQL with the highest I/O in the past one day

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum

Top CPU consuming queries since past one day

select * from (
select 
	SQL_ID, 
	sum(CPU_TIME_DELTA), 
	sum(DISK_READS_DELTA),
	count(*)
from 
	DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
	group by 
	SQL_ID
order by 
	sum(CPU_TIME_DELTA) desc)
where rownum

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.

In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

Analyse a particular SQL ID and see the trends for the past day

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

Source

Session State

#1 Get information on the sessions waiting and working

Query for displaying sessions, session state, and events:

select sid,
decode(state, ‘WAITING’,’Waiting’,
‘Working’) state,
decode(state,
‘WAITING’,
‘So far ‘||seconds_in_wait,
‘Last waited ‘||
wait_time/100)||
‘ secs for ‘||event
“Description”
from v$session
where username = ‘TEST’;

SID  STATE  DESCRIPTION

556 Waiting So far 610498 secs for SQL*Net message from client

#2 Sessions from a specific user

select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = ‘TEST’;

#3 Sessions from a specific machine

select sid, username, program,
decode(state, ‘WAITING’, ‘Waiting’,
‘Working’) state,
last_call_et, seconds_in_wait, event
from v$session
where machine = ‘an23’;

#4 Get the SQL

SQL statement a session is executing, which will provide more insights into the workings of the session

select sql_id
from v$session
where sid = 3089;

Reference

DISK_ASYNCH_IO

DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans).  If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

  • For optimum performance make sure you use asynchronous I/Os.
  • TRUE is the default parameter value for the majority of platforms.

SQL>SHOW PARAMETER disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------
disk_asynch_io boolean TRUE
SELECT * FROM v$parameter WHERE lower(name) = 'disk_asynch_io' ;

These parameters enable or disable the operating system’s asynchronous I/O facility. They allow query server processes to overlap I/O requests with processing when performing table scans.

Asynchronous operations are currently supported for parallel table scans, hash joins, sorts, and serial table scans. However, this feature can require operating system specific configuration and may not be supported on all platforms.

Source/Reference Links:

Quick STATSPACK

Install statspack

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Drop and install statspack
sqlplus "/ as sysdba" @spcreate.sql -- Enter tablespace names when prompted

Take performance snapshots of the database

sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
-- or
exec perfstat.statspack.snap(i_snap_level=>10); 

 -- or instruct statspack to do gather more details in the snapshot
 -- (look up which oracle version supports which level)

The spauto.sql script can be customized and executed to schedule the collection of STATPACK snapshots.

Statspack reporting

-- Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- Enter two snapshot id's for difference report

Source:

Oracle KEEP and Recycle Cache

Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.

Note that in Oracle 10g, the terms are changed to the keep cache and the recycle cache. However, many people still refer to these two as pools. Some of the Oracle documentation still uses the term pool when referring to these caches.

Keep Buffer Pool

Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer. DB_KEEP_CACHE_SIZE initialization parameter is used to create Keep buffer Pool. If DB_KEEP_CACHE_SIZE is not used then no Keep buffer is created. Use following syntax to create a Keep buffer pool of 40 MB.

DB_KEEP_CACHE_SIZE=40M

To pin an object in Keep buffer pool use DBMS_SHARED_POOL.KEEP method.

Recycle Buffer Pool

Blocks loaded in Recycle Buffer pool are immediate removed when they are not being used. It is useful for those objects which are accessed rarely. As there is no more need of these blocks so memory occupied by such blocks is made available for others data. For example if ASM is enabled then available memory can be assigned to other SGA components . Use following syntax to create a Recycle Buffer Pool

DB_RECYCLE_CACHE_SIZE=20M

Default Pool

If an object is not assigned a specific buffer pool then its blocks are loaded in default pool DB_CACHE_SIZE initialization parameter is used to create Default Pool. For more information on Default Pool visit following link,

Database Buffer Cache


BUFFER_POOL value in storage clause of schema objects lets you to assign an object to a specific Buffer pool. Value of BUFFER_POOL can be KEEP,RECYCLE or DEFAULT.

buf_keep_pool.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
 o.owner owner,
 o.object_name object_name,
 o.subobject_name subobject_name,
 o.object_type object_type,
 count(distinct file# || block#) num_blocks
from
 dba_objects o,
 v$bh bh
where
 o.data_object_id = bh.objd
and
 o.owner not in ('SYS','SYSTEM')
and
 bh.status != 'free'
group by
 o.owner,
 o.object_name,
 o.subobject_name,
 o.object_type
order by
 count(distinct file# || block#) desc
;

select
 'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
 t1,
 dba_segments s
where
 s.segment_name = t1.object_name
and
 s.owner = t1.owner
and
 s.segment_type = t1.object_type
and
 nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
 buffer_pool <> 'KEEP'
and
 object_type in ('TABLE','INDEX')
group by
 s.segment_type,
 t1.owner,
 s.segment_name
having
 (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;

spool off;

Source / Reference Links:

Indexes on Foreign Keys

Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. Below query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.

SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
 ||'['||acc.position||'])'||' ***** Missing Index'
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 AND (acc.owner, acc.table_name, acc.column_name, acc.position)
 IN
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 MINUS
 SELECT table_owner, table_name, column_name, column_position
 FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
 acc.column_name, acc.position;

By creating an index on the foreign key of the child table,  “table-level” locks can be avoided.

Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it’s often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.

Source / Reference links:

Quick TKPROF

1. Find the session to be monitored

SELECT username, sid, serial#, program
FROM v$session
WHERE username = <User_Name>;

2. Enable SQL Tracing

a, Current Session:

ALTER SESSION SET sql_trace = TRUE;
or
execute dbms_session.set_sql_trace(true);

b, Different Session (as SYSDBA):

execute dbms_system.set_sql_trace_in_session(sid, serial#, sql_trace);
e.g.
execute dbms_system.set_sql_trace_in_session(114, 4667, TRUE);

3. Enable Oracle database to gather statistics

ALTER SYSTEM SET timed_statistics = true;  -- at system level
ALTER SESSION SET timed_statistics = true; -- at session level

4. Formatting the output with TKPROF 

TKPROF inputfile outputfile [OPTIONS]
e.g.
tkprof mydb_ora_11915.trc /tmp/tkprof1.txt SYS=NO

5. Find directory where trace file is generated

SELECT value
FROM v$parameter
WHERE name='user_dump_dest';

6. Identify trace file generated

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid
FROM v$session s,
 v$process p
WHERE p.addr = s.paddr and s.username = <User_Name>;

7. Disable tracing for the session

ALTER SESSION SET sql_trace = TRUE;
OR
EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);

Daily Check List

Daily Procedures:

1. Verify all instances are up or not

Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.

Optional implementation: use Oracle Enterprise Manager’s ‘probe’ event.

2. Check for new entries in alert log file

• Connect to each managed system.
• Use ‘telnet’ or comparable program.
• For each managed instance, go to the background dump destination, usually $ORACLE_BASE/<SID>/bdump.

Make sure to look under each managed database’s SID.
• At the prompt, use the Unix ‘tail’ command to see the alert_<SID>.log, or otherwise examine the most recent entries in the file.
• If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in <file>.

3. Verify DBSNMP is running

1. Log on to each managed machine to check for the ‘dbsnmp’ process.
For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to “verify that DBSNMP is NOT running”.)

4. Verify success of database backup

5. Verify success of database archiving to tape

6. Verify enough resources for acceptable performance

6.1 Verify free space in tablespaces.

For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of <date>, the minimum free space for <repeat for each tablespace>: [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least <time to order, get, and install more disks> days’ data growth.

a) Go to each instance, run free.sql to check free mb in tablespaces.
Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct.

free.sql

select  tablespace_name as name,
sum ( blocks ) as free_blk,
trunc ( sum ( bytes ) / (1024*1024) ) as free_m,
max ( bytes ) / (1024) as big_chunk_k,
count (*) as num_chunks
from   dba_free_space
group by tablespace_name;

Output Example:


b) Go to each instance, run space.sql to check percentage free in tablespaces.
Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.

6.2 Verify rollback segment

Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE.

a) Optional: each database may have a list of rollback segment names and their expected statuses.
b) For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT.
c) For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.

6.3 Identify bad growth projections.

Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it’s an object that is supposed to get large, upgrade the max_extents to unlimited.

a) To gather daily sizing information, run analyze5pct.sql. If you are collecting nightly volumetrics, skip this step.
b) To check current extents, run nr_extents.sql
c) Query current table sizing information
d) Query current index sizing information
e) Query growth trends

6.4 Identify space-bound objects.

Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE <tablespace> COALESCE. Or add another datafile.

a) Run spacebound.sql. If all is well, zero rows will be returned.

6.5  Processes to review contention for CPU, memory, network or disk resources.

a) To check CPU utilization, go to x:webphase2default.htm =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.

7. Copy Archived Logs to Standby Database and Roll Forward

If you have a Standby Database, copy the appropriate Archived Logs to the expected location on the standby machine and apply those logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.

The copying of logs, the applying of them, or both, can in some cases be automated. If you have automated them, then your daily task should be to confirm that this happened correctly each day.

8. Read DBA manuals for one hour

Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists.

References:

  • Loney, Kevin Oracle8 DBA Handbook
  • Cook, David Database Management from Crisis to Confidence
  • Cox, Thomas B. The Database Administration Maturity Model

Hit / Miss Ratios

Draft Article *

Buffer Hit Ratio

Consistent Gets – The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets – The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads – The cumulative number of blocks read from disk.
Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
 sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
 sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
 round((sum(decode(name, 'consistent gets',value, 0)) +
 sum(decode(name, 'db block gets',value, 0)) -
 sum(decode(name, 'physical reads',value, 0))) /
 (sum(decode(name, 'consistent gets',value, 0)) +
 sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Is the Oracle buffer hit ratio a useless metric for monitoring and tuning? – Bur

Data Dictionary Hit Ratio

Gets – Total number of requests for information on the data object.
Cache Misses – Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select sum(GETS),
 sum(GETMISSES),
 round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache

SQL Cache Hit Ratio

Pins – The number of times a pin was requested for objects of this namespace.
Reloads – Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select sum(PINS) Pins,
 sum(RELOADS) Reloads,
 round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache

Library Cache Miss Ratio

Executions – The number of times a pin was requested for objects of this namespace.
Cache Misses – Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select sum(PINS) Executions,
 sum(RELOADS) cache_misses,
 sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycache

Source / Reference Links:

Performance Tuning Areas

Just picking those area that require tuning! Source – OraFAQ

  •     Database Design (if it’s not too late):

Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the “data access path” in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

  •     Application Tuning:

Experience shows that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

  •     Memory Tuning:

Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your wait events, buffer hit ratios, system swapping and paging, etc. You may also want to pin large objects into memory to prevent frequent reloads.

  •     Disk I/O Tuning:

Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

  •     Eliminate Database Contention:

Study database locks, latches and wait events carefully and eliminate where possible.

  •     Tune the Operating System:

Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.