Archive
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;
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,
http://exploreoracle.com/2009/03/31/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.
-- *************************************************
-- 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);
