How to generate Multiple AWR reports
Multiple AWR report generation script
The following script can be used for AWR reports generation, for specific intervals between the required Snapshots generated.
The below script (awr_report_generate.sh) is to generate AWR reports for the specific intervals between the required Snapshots generated.
Let say, there is a requirement to generate awr reports for every 2 hours of previous day from 00:00 hrs to current date 00:00 hrs ie., 24 hrs.
Reuirements:-
===========
First we need to get the Begin Snap ID and End Snap ID, in order to generate multiple awr reports using the below script.
$ sqlplus "/as sysdba"
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, SQL> @?/rdbms/admin/awrrpti.sql)
.
.
.
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: ---------------> press 'Enter' Key
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days:
Listing the last n days of Completed Snapshots ---------------> here 'n' represents the number of days we have entered for generating awr reports.
NOTE:-
====
Here, for the required period, make a note of the required Begin Snap ID and End Snap ID.
Create a script awr_report_generate.sh:-
======================================
SQL> exit
$ vi awr_report_generate.sh
#!/usr/bin/sh
echo "Enter the value for Begin Snapshot Id :"
read beginid
echo "Enter the value for End Snapshot Id:"
read endid
echo "Enter the value for interval between Snapshot Id's. To generate reports between consecutive Snapshop Id's, Enter '1'. Else, enter desired
value:"
read snapint
echo "Enter the value for report type: html/text"
read repfmt
echo "Enter the path for unix directory to generate the reports. Press 'Enter' to generate the reports in current working directory:"
read repdir
if [ "$repdir" = "" ]
then
repdir=$PWD
fi
while [ $beginid -lt $endid ]
do
tempid=`echo $beginid + $snapint |bc`
sqlplus -s '/as sysdba'<<EOF
set verify off
set feedback off
set pages 0
set serveroutput on
clear break compute;
repfooter off;
ttitle off;
btitle off;
set heading on;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 0 linesize 1500 newpage 1 recsep off;
set trimspool on trimout on define "&" concat "." serveroutput on;
set underline on;
col endid new_value endid;
col repname new_value repname;
col dbid new_value dbid;
col inst_num new_value inst_num;
define beginid=$beginid;
define tempid=$tempid;
variable repname varchar2(60);
variable dbid varchar2(10);
variable inst_num varchar2(2);
select dbid dbid from v\$database;
select instance_number inst_num from v\$instance ;
select '$repdir/AWR_'||(select instance_name inst_name from v\$instance)||'_'||(select to_char(END_INTERVAL_TIME,'DDMONYY_HH24MI')from
dba_hist_snapshot where snap_id='$beginid' and instance_number=(select instance_number from v\$instance))||'_'||(select to_char
(END_INTERVAL_TIME,'DDMONYY_HH24MI')from dba_hist_snapshot where snap_id='$tempid' and instance_number=(select instance_number from v\
$instance))||'.$repfmt' repname from dual;
spool &repname
select output from table(dbms_workload_repository.awr_report_$repfmt(&dbid,&inst_num,&&beginid,&&tempid,0));
spool off
exit
EOF
beginid=`echo $beginid + $snapint |bc`
done
-- Press esc
:wq
$ ls -lrt awr_report_generate.sh
Run the script:-
==============
$ sh awr_report_generate.sh
$ ls -lrt
The following query can be used to check the current settings for the AWR interval and AWR retention.
The query returns the current AWR interval values in minutes.
set pages 50000 lines 32767
col snap_interval format a20
col retention format a20
col topnsql format a20
select extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;
Snapshot Interval Retention Interval
----------------- ------------------
Labels: PERFORMANCE TUNING, SCRIPT
Database health checks in Oracle
Performing Database health checks, when there is an issue reported by Application users.
1. Check the Database details
2. Monitor the consumption of resources
3. Check the Alert Log
4. Check Listener log
5. Check Filesystem space Usage
6. Generate AWR Report
7. Generate ADDM Report
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database
9. Check for alerts in OEM
1. Check the Database details :-
=============================
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v
$database,gv$instance;
For RAC:
-------
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS')
"DB UP TIME" from v$database,gv$instance;
2. Monitor the consumption of resources :-
=======================================
select * from v$resource_limit where resource_name in ('processes','sessions');
The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.
3. Check the Alert Log :-
======================
$locate alert_<ORACLE_SID>
--- OR ---
UNIX/Linux command to locate the alert log file
-----------------------------------------------
$ find / -name 'alert_*.log' 2> /dev/null
vi <alert_log_location_of_the_above_output>
shift+g
?ORA- ---> press enter key
press 'n' to check backwards/up side and 'N' for forward/down side search.
:q! --and press enter, for exiting vi editor
--- OR ---
11G
===
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = 'Diag Trace';
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory
Before 11G
==========
$ sqlplus "/as sysdba"
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory
4. Check Listener log :-
=====================
$locate listener.log
--- OR ---
UNIX/Linux command to locate the listener log file
--------------------------------------------------
$ find / -name 'listener.log' 2> /dev/null
vi <listener.log>
shift+g
?TNS- ---> press enter key
press 'n' to check backwords and 'N' for forword search.
AND
shift+g
?error ---> press enter key
press 'n' to check backwords and 'N' for forword search.
:q! --and press enter, for exiting vi editor
--- OR ---
$lsnrctl status
from the output you can get the listener log location (see the value for "Listener Log File" in the output).
5. Check Filesystem space Usage :-
===============================
df -h (Linux / UNIX)
df -g (AIX)
6. Generate AWR Report :-
======================
Generate AWR report for current and before to compare
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, @?/rdbms/admin/awrrpti.sql - for each instance)
If Required,
SQL> @?/rdbms/admin/awrddrpt.sql ----> Produces Workload Repository Compare Periods Report
7. Generate ADDM Report :-
=======================
Generate ADDM report for current and before to compare.
ADDM report provides Findings and Recommendations to fix the issue.
SQL> @?/rdbms/admin/addmrpt.sql (For RAC, @?/rdbms/admin/addmrpti.sql - for each instance)
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-
========================================================================
Select * from v$lock;
Select * from gv_$lock; (For RAC)
A fast way to check blocking/waiting situations
-----------------------------------------------
SELECT * FROM v$lock WHERE block > 0 OR request > 0;
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process
p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;
Blocker Session and Waiting sessions
====================================
column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;
USERNAME SID TY LMODE REQUEST ID1 ID2
---------------- ------- -- ------------- ------------- ---------- --------
ORAPLAYERS 10 TX Exclusive None 123456 200
ORAPLAYERS 100 TX None Exclusive 123456 200
Session 10 is blocking(LMODE=Exclusive)
Session 100 is waiting(REQUEST=Exclusive)
The meaning of ID1 and ID2 depends on the lock TYPE.
• We can see situations where a session is both a Blocker and a Waiter.
• If there are only two sessions and both are Blockers and Waiters then we got a deadlock situation (which Oracle will solve automatically).
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' ||
s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid)
blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not
NULL order by 1;
9. Check for alerts in OEM :-
============================
Login to Oracle Enterprise Manager with valid username and password
click on "Alerts" tab
then select the below tabs one by one to see the alerts generated
Targets Down/Critical/Warning/Errors/
Oracle DBA Performance Tuning Scripts
Performance Tuning Scripts
Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History v
$active_session_history
View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time
in the past.
Top Recent Wait Events
set pages 50000 lines 32767
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
set pages 50000 lines 32767
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
set pages 50000 lines 32767
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, 2013.
set pages 50000 lines 32767
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2013'
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
set pages 50000 lines 32767
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 pages 50000 lines 32767
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
set pages 50000 lines 32767
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
set pages 50000 lines 32767
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
set pages 50000 lines 32767
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
set pages 50000 lines 32767
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, 2013.
set pages 50000 lines 32767
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2013'
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
set pages 50000 lines 32767
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
set pages 50000 lines 32767
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
*/
set pages 50000 lines 32767
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
/
How to find high CPU using sessions in Oracle Database
If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits. The v$sesstat view
can be queried to find high cpu using sessions and then SQL can be listed.
1.Find the 'CPU used by this session' statistic.
SQL>SELECT name ,statistic# FROM v$statname WHERE name LIKE '%CPU%session';
NAME STATISTIC#
----------------------------------- ----------
CPU used by this session 14
2. Then determine which session is using most of the cpu.
SQL>SELECT * FROM v$sesstat WHERE statistic# = 14;
SID STATISTIC# VALUE
---------- ---------- ----------
1 14 0
2 14 0
3 14 0
4 14 0
5 14 0
6 14 0
7 14 0
8 14 0
9 14 0
10 14 0
11 14 0
12 14 0
16 14 1930
3. Lookup details for the session which is using most of the cpu.
SQL>SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG
FROM v$sqlarea a, v$session s
WHERE sid = 16
AND s.sql_address = a.address
AND executions > 0
ORDER BY 5;
4. Use v$sqltext to extract the whole SQL text.
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
5. Once the whole SQL statement has been identified it can be tuned.
Explain the queries and examine their access paths.
Autotrace is a useful tool for examining access paths.
Syntax:-
Explain plan for 'sql statement';
Script to Gather data from the Linux OS
#####################################################################
### Unix script os_stats.sh ###
### Designed to be run periodically to collate information ###
### START OF SCRIPT ###
#####################################################################
#
LOG_FILE="OS_`hostname`_`date '+%m%d%y_%H%M'`.txt"
#
echo "**********************************************" >$LOG_FILE
date >> $LOG_FILE
echo "Running as `id`" >> $LOG_FILE
echo "**********************************************" >>$LOG_FILE
echo "uname -a" >>$LOG_FILE
uname -a >>$LOG_FILE
cat /etc/issue >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ulimit -a" >>$LOG_FILE
ulimit -a >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ulimit -Ha" >>$LOG_FILE
ulimit -Ha >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "Netstat -i" >>$LOG_FILE
netstat -i >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "netstat -an">> $LOG_FILE
netstat -an >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "netstat -s">> $LOG_FILE
netstat -s >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "sar -u 5 3">> $LOG_FILE
sar -u 5 3 >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "sar -q 5 3">> $LOG_FILE
sar -q 5 3 >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "ps -e -w -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args" >> $LOG_FILE
ps -e -ww -o user,pid,ppid,s,pcpu,pmem,vsz,rss,stime,time,args >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "iostat -t -x" >> $LOG_FILE
iostat -t -x >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "df -h" >> $LOG_FILE
df -h >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "mpstat 5 3" >> $LOG_FILE
/usr/bin/mpstat 5 3 >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "vmstat 5 3" >> $LOG_FILE
vmstat 5 3 >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "free -m -s 5 -c 3" >> $LOG_FILE
free -m -s 5 -c 3 >>$LOG_FILE
grep MemTotal /proc/meminfo >>$LOG_FILE
grep SwapTotal /proc/meminfo >>$LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
echo "IPCS data" >> $LOG_FILE
ipcs -l >> $LOG_FILE
echo " ---------------------------- " >> $LOG_FILE
ipcs -u >> $LOG_FILE
echo " ---------------------------- " >> $LOG_FILE
ipcs >> $LOG_FILE
echo " ---------------------------- " >> $LOG_FILE
ipcs -t >> $LOG_FILE
echo "++++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG_FILE
#####################################################################
### END OF SCRIPT ###
#####################################################################
Labels: LINUX, PERFORMANCE TUNING, SCRIPT
Script to Gather data from the database for Process information and User connections
REM
REM START OF SQL
REM
REM Filename : mzRunSQL.sql
REM Author : Mike Shaw
REM Date Updated : 29 August 2008
REM Purpose : Script to gather data from the database for process
REM information and user connections
REM
set serveroutput on size 1000000
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 110
col username form a10
col program form a30
col how_many forma 9999
col machine form a25
col module form a50
--
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') START_TIME from dual
/
rem Instance identification
select *
from v$instance
/
REM Summary of database connections
select s.module, s.machine, s.username, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by rollup(s.module,s.machine,s.username)
/
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM Get count of number of connected Apps 11i users
REM
col user_name format a15
col first_connect format a18
col last_connect format a18
col How_many_user_sessions format 9999999999
col How_many_sessions format 9999999999
REM
REM Summary of how many users
REM
select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
from icx_sessions icx
where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) >
sysdate
and counter < limit_connects
/
REM
REM Number of sessions per user
REM
select user_name, count(*) How_many_sessions
from icx_sessions icx, fnd_user u
where icx.user_id = u.user_id
and disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) >
sysdate
and counter < limit_connects
group by user_name
order by 2 desc
/
REM
REM Quick look for blockers and lockers
REM
col BLOCKER form a5
col Username format A15
col Sid format 9990 heading SID
col Type format A4
col Lmode format 990 heading 'HELD'
col Request format 990 heading 'REQ'
col Id1 format 999999999999
col Id2 format 999999999999
break on Id1 skip 1 dup
--
SELECT M.Sid,
M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',LTRIM(TO_CHAR(Lmode,'990')))
Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1,
M.Id2,
DECODE(block, 0, 'NO', 'YES' ) BLOCKER
FROM V$LOCK M
WHERE
M.Request ! = 0 or (M.Request = 0 and Lmode != 4 and (id1, id2) in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1 = M.Id1 and S.Id2
= M.Id2) )
ORDER BY Id1, Id2, M.Request
/
REM
REM Check for database statement leaks
REM May give symptoms like OutOfMemoryError
REM
set lines 132
set pages 1000
column module format A40
column program format A30
select oc.sid,oc.hash_value,oc.sql_text,count(*) How_Many
from gv$open_cursor oc
group by sid,hash_value,sql_text
having count(*) > 5
order by 4
/
rem show time
select to_char(sysdate, 'DD-MON-RR HH24:MI:SS') END_TIME from dual
/
exit;
REM
REM END OF SQL
REM
ORA-01502 index or partition of such index is in unusable state
ERROR:-
-----
ORA-01502: index "owner.index_name" or partition of such index is in unusable state
SOLUTION:-
--------
REBUILD UNUSABLE INDEXES in Oracle Database
$sqlplus "/as sysdba"
select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ---------- ---------- ------
owner index_name table_name INVALID
select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER SEGMENT_NAME SIZE IN MB
----- ------------ ----------
owner index_name 3000
alter session set current_schema='&schema_name';
Session altered.
alter index <index_name> rebuild;
Index altered.
select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ---------- ---------- ------
owner index_name table_name VALID
select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER SEGMENT_NAME SIZE IN MB
----- ------------ ----------
owner index_name 800
------------------------------------ OR ------------------------------------------------------
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
This will output statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.
How to find Tablespace Fragmentation in Oracle Database
How to find Tablespace Fragmentation in Oracle Database
To determine if your tablespaces are having a problem with fragmentation, you can use the below script:
set pages 50000 lines 32767
select tablespace_name,count(*) free_chunks,decode(round((max(bytes) / 1024000),2),null,0,
round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
from sys.dba_free_space group by tablespace_name order by 2 desc, 1;
Here,fragmentationindex column will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score
indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.
The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that
tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket
of free space.
How to find Table Fragmentation in Oracle Database
How to find Table Fragmentation in Oracle Database
What is Oracle Table Fragmentation?
If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves
behind holes in table which results in table fragmentation.
To understand it more clearly, we need to be clear on how oracle manages space for tables.
When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional
block accesses.
Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become
fragmented because DML does not release free space from the table below the HWM.
HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might
have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when
doing a full table scan.
DDL statement always resets the HWM.
What are the reasons to reorganization of table?
a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.
Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.
How to find Table Fragmentation?
In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables
(Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.
Steps to Check and Remove Table Fragmentation:-
=============================================
1. Gather table stats:
---------------------
To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual
fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this
value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.
exec dbms_stats.gather_table_stats('&schema_name','&table_name');
2. Check Table size:
-------------------
Now again check table size using and will find reduced size of the table.
select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';
3. Check for Fragmentation in table:
-----------------------------------
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing
table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-
round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name
='&table_Name' AND OWNER LIKE '&schema_name'
/
Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.
If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above
query, So he can proceed for removing fragmentation.
4. How to reset HWM / remove fragemenation?
---------------------------------------
We have four options to reorganize fragmented tables:
1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
(Depends upon the free space available in the tablespace)
2. Export and import the table:- (difficult to implement in production environment)
3. Shrink command (fron Oracle 10g)
(Shrink command is only applicable for tables which are tablespace with auto segment space management)
Here, I am following Options 1 and 3 option by keeping table availability in mind.
Option: 1 Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
------------------------------------------------------------------------------------------
Collect status of all the indexes on the table:-
----------------------------------------------
We will record Index status at one place, So that we get back them after completion of this exercise,
select index_name,status from dba_indexes where table_name like '&table_name';
Move table in to same or new tablespace:
---------------------------------------
In this step we will move fragmented table to same tablespace or from one tablespace to another tablespace to reclaim fragmented space. Find Current
size of you table from dba_segments and check if same or any other tablespace has same free space available. So, that we can move this table to same
or new tablespace.
Steps to Move table in to same tablespace:
-----------------------------------------
alter table <table_name> move; ------> Move to same tablespace
OR
Steps to Move table in to new tablespace:
----------------------------------------
alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;
Now, get back table to old tablespaces using below command
alter table table_name move tablespace old_tablespace_name;
Now,Rebuild all indexes:
-----------------------
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.
SQL> select status,index_name from dba_indexes where table_name = '&table_name';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_NAME -------> Here, value in status field may be valid or unusable.
SQL> alter index <INDEX_NAME> rebuild online; -------> Use this command for each index
Index altered.
SQL> select status,index_name from dba_indexes where table_name = '&table_name';
STATUS INDEX_NAME
-------- ------------------------------
VALID INDEX_NAME -------> Here, value in status field must be valid.
Gather table stats:
------------------
SQL> exec dbms_stats.gather_table_stats('&owner_name','&table_name');
PL/SQL procedure successfully completed.
Check Table size:
-----------------
Now again check table size using and will find reduced size of the table.
select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';
Check for Fragmentation in table:
--------------------------------
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-
round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name
='&table_Name' AND OWNER LIKE '&schema_name'
/
==================================================================================================================
Option: 3 Shrink command (fron Oracle 10g):-
------------------------------------------
Shrink command:
--------------
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.
This command is only applicable for tables which are tablespace with auto segment space management.
Before using this command, you should have row movement enabled.
SQL> alter table <table_name> enable row movement;
Table altered.
There are 2 ways of using this command.
1. Rearrange rows and reset the HWM:
-----------------------------------
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table <table_name> shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table <table_name> shrink space;
Table altered.
2. Directly reset the HWM:
-------------------------
SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.
Advantages over the conventional methods are:
--------------------------------------------
1. Unlike "alter table move ..",indexes are not in UNUSABLE state.After shrink command,indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.
Query is running slow for long time for SQL_ID
Query is running slow for long time for SQL_ID captured
Run SQL Tuning Adviosor for the sql_id
--------------------------------------
Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID.
So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the
SQL_ID.Recommendations from sqltrpt.sql will be providing the best Explain Plan. We can implement these profiles/index rebuild/... suggested after
checking with the SME of the database.
Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql
$sqlplus "/as sysdba"
Query to see current running sqls
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql
In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.
Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
--------------------------------------------------------------------
SQL> execute dbms_sqltune.accept_sql_profile(task_name => '<TASK_NAME>',task_owner => 'SYS', replace => TRUE, FORCE_MATCH => TRUE);
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;
Command to Drop SQL Profile in Oracle for the SQL_ID:
----------------------------------------------------
SQL> execute dbms_sqltune.drop_sql_profile('<SQL_PROFILE_NAME>');
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
Command to Alter SQL Profile in Oracle for the SQL_ID:
-----------------------------------------------------
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('<SQL_PROFILE_NAME>','STATUS','DISABLED');
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
If you don't know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%TABLE%NAME%';
Query
-----
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.
select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and sql_profile like nvl('&sql_profile_name',sql_profile)
and sql_profile is not null
order by 1, 2, 3
/
For More:-
--------
Migrate Oracle SQL Profile
http://www.allguru.net/database/migrate-oracle-sql-profile/
Swapping SQL Profiles
http://www.dba-oracle.com/t_swapping_sql_profiles.htm
How to find SQL,SQL_ID history on Oracle
Session related Queries
Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/
SQLs Running from longtime
--------------------------
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from
v$session_longops
/
Active Sessions running for more than 1 hour
---------------------------------------------
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;
Session details associated with SID and Event waiting for
---------------------------------------------------------
set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi')
CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;
Session details associated with Oracle SID
-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on
Checking for Active Transactions SID
------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
Session details from Session longops
-------------------------------------
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;
Session details with SPID
-------------------------
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid')
/
To find Undo Generated For a given session
------------------------------------------
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sid';
To list count of connections from other machines
------------------------------------------------
select count(1),machine from gv$session where inst_id='&inst_id' group by machine;
To get total count of sessions and processes
--------------------------------------------
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext thru sqladdress
-------------------------------
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;
To find sqltext for different sql hashvalue
-------------------------------------------
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
To list long running forms user sessions
----------------------------------------
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
To list inactive Sessions respective username
---------------------------------------------
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';
To find session id with set of SPIDs
------------------------------------
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');
To find Sql Text given SQLHASH & SQLADDR
----------------------------------------
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;
Oracle DBA Interview Questions and Answers - Performance Tuning
Oracle Performance Tuning Interview Questions and Answers
Application user is complaining the database is slow.How would you find the performance issue of SQL queries?
High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database
slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the session is
doing join your query v$session with v$session_wait.
SELECT NVL(s.username,'(oracle)') as username,s.sid,s.serial#,sw.event,sw.wait_time, sw.seconds_in_wait, sw.state FROM v$session_wait sw,v$session s
WHERE s.sid=sw.sid and s.username= '&username'ORDER BY sw.seconds_in_wait DESC;
1.Check the events that are waiting for something.
2.Try to find out the objects locks for that particular session.
3.Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database
datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as "db file sequential
read" (for index scan) or "db file scattered read" (for full table scan).When you see the event, you know that the session is waiting for I/O from
the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the
first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.Reduce the number of blocks retrieved by the SQL
statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it
can be rewritten to reduce the amount of data it retrieves.
4.Run SQL Tuning Advisor (@$ORACLE_HOME/rdbms/admin/sqltrpt.sql) by providing SQL_ID as the input for generating the findings and recommendations.
SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics.
SQL Tuning Advisor suggests indexes that might be very useful.
SQL Tuning Advisor suggests query rewrites.
SQL Tuning Advisor suggests SQL profile.
More:
1.Run TOP command in Linux to check CPU usage.
2.Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.
3.Enable the trace file before running your queries,then check the trace file using tkprof create output file.
According to explain plan check the elapsed time for each query,then tune them respectively.
What is the use of iostat/vmstat/netstat command in Linux?
Iostat – reports on terminal, disk and tape I/O activity.
Vmstat – reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat – reports on the contents of network data structures.
If you are getting high “Busy Buffer waits”, how can you find the reason behind it?
Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache. There could be the reason when the block may be busy
in the cache and session is waiting for it. It could be undo/data block or segment header wait.
Run the below two query to find out the P1, P2 and P3 of a session causing buffer busy wait
then after another query by putting the above P1, P2 and P3 values.
SQL> Select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait Where event = 'buffer busy waits';
SQL> Select owner, segment_name, segment_type from dba_extents
Where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
What to Look for in AWR Report and STATSPACK Report?
Many DBAs already know how to use STATSPACK but are not always sure what to check regularly.
Remember to separate OLTP and Batch activity when you run STATSPACK, since they usually
generate different types of waits. The SQL script “spauto.sql” can be used to run STATSPACK
every hour on the hour. See the script in $ORACLE_HOME/rdbms/admin/spauto.sql for more
information (note that JOB_QUEUE_PROCESSES must be set > 0). Since every system is different,
this is only a general list of things you should regularly check in your STATSPACK output:
¦ Top 5 wait events (timed events)
¦ Load profile
¦ Instance efficiency hit ratios
¦ Wait events
¦ Latch waits
¦ Top SQL
¦ Instance activity
¦ File I/O and segment statistics
¦ Memory allocation
¦ Buffer waits
What is the difference between DB file sequential read and DB File Scattered Read?
DB file sequential read is associated with index read where as DB File Scattered Read has to do with full table scan.
The DB file sequential read, reads block into contiguous memory and DB File scattered read gets from multiple block and scattered them into buffer
cache.
Which factors are to be considered for creating index on Table? How to select column for index?
Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report
then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of
select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create
unique index.
It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to
perform on various index segments and table simultaneously.
Is creating index online possible?
YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on
that table. You can perform DML operations while the index building is taking place, but DDL operations are not allowed. Parallel execution is not
supported when creating or rebuilding an index online.
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
How to recover password in oracle 10g?
You can query with the table user_history$. The password history is store in this table.
How can you track the password change for a user in oracle?
Oracle only tracks the date that the password will expire based on when it was latest changed. Thus listing the view DBA_USERS.EXPIRY_DATE and
subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. You can also check the last password change time directly from the
PTIME column in USER$ table (on which DBA_USERS view is based). But If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile
assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account.
SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#;
What is Secure External password Store (SEPS)?
Through the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores
signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username
and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without
changing application code whenever username and password change.
Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?
If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE
option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be
performed.
What is the difference between Redo,Rollback and Undo?
I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty
close.
Redo: Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and
committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.
Rollback: More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo
log which is a record of the insert/update/deletes.
Undo: Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read
consistent view of data.
You have more than 3 instances running on the Linux server? How can you determine which shared memory and semaphores are associated with which
instance?
Oradebug is undocumented oracle supplied utility by oracle. The oradebug help command list the command available with oracle.
SQL>oradebug setmypid
SQL>oradebug ipc
SQL>oradebug tracfile_name
Why drop table is not going into Recycle bin?
If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we
have already SET recycle bin parameter TRUE.
Select * from v$parameter where name = 'recyclebin';
Show parameter recyclebin;
Temp Tablespace is 100% FULL and there is no space available to add datafiles to increase temp tablespace. What can you do in that case to free up
TEMP tablespace?
Try to close some of the idle sessions connected to the database will help you to free some TEMP space. Otherwise you can also use ‘Alter Tablespace
PCTINCREASE 1’ followed by ‘Alter Tablespace PCTINCREASE 0’
When a Tablespace reaches 90%, what action you will take? How you decide whether to go for Resize or Add datafile?
What is the max possible size of a SMALL and BIG datafiles?
SUPPOSE A QUERY IS RUNNING SLOW OR HUNG STATE, What will you do?
SUPPOSE DATAABSE IS RUNNING SLOW, What will you do?
As a DBA what Pro active steps do you take for a Highly utilized Undo?
AWR Vs. ASH?
AWR Contents?How to interpret AWR?
Wait Events?
Difference between DB File Sequential Read and DB file Scattered Reads?
how to cache a table or pin a table?
What is explain plan?
What is Database Replay?
What is Fragmentaion?
What are MATERIALIZED VIEWS?
What are Trace files?
What is ORA-600?
What is OLTP AND DATAWAREHOUSING?
How to change character set of database?
What are unused indexes?How to reuse unused indexes?
What is Row Chaning and Row Migration?
Row Migration:
A row migrates when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A
migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new
block and the entire row is moved.
Row Chaining:
A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of
8KB into it, Oracle will use 3 blocks and store the row in pieces.
Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to
having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.
How to find out background processes ?
SQL> select SID,PROGRAM from v$session where TYPE='BACKGROUND';
SQL> select name,description from V$bgprocess;
How to findout background processes from OS:
$ ps -ef|grep ora_|grep SID
To Find and Delete bigger size and older files in Linux
--To find out files size more than 5MB
find . -size +5000 -exec ls -ltr {} \;
-- To **Remove** files size more than 5MB
find . -size +5000k -exec rm -rf {} \;
--To find out files older than 30days
find . -mtime +30 -exec ls -ltr {} \;
--To find **Remove** files older than 30days
find . -mtime +30 -exec rm -rf {} \;
1008 TRACE file
ISSUE:-
-----
Below error received when trying to run the code having bind variables.
But,same code works correctly in other datadases maintained at same version.
Oracle Database Version: 11.2.0.2.0 - 64 bit (SQL>SELECT BANNER FROM v$version;)
PL/SQL : 11.2.0.2.0
OS Version : Linux x86_64 ($uname -ms)
OS Version
----------
$uname -ms
Linux x86_64
ERROR:
-----
ERROR at line 1:
ORA-01008:not all variables bound
ORA-06512:at line 77
Running in to Bug 14458214 (5/rdbms/partitioning) Unexpected ORA-01008 from select on composite partitioned table.
The workaround mentioned in Bug 14458214 is as follows:
Try executing this from the sqlplus session before you execute the anonymous block.
alter session set "_and_pruning_enabled"=false;
alter session set "_subquery_pruning_enabled"=false;
alter session set "_optimizer_table_expansion"=false;
--execute the anonymous PL/SQL block which fails, here
How to Generate 1008 TRACE file from Oracle Database
----------------------------------------------------
Set an 1008 ERRORSTACK event to confirm the stack.
Database Details
----------------
sqlplus "/as sysdba"
set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Generate 1008 TRACE file at session level
-----------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST1008';
alter session set events '1008 trace name ERRORSTACK level 4';
--execute the statement which fails, here
alter session set events '1008 trace name context off';
Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace'; -------------- 11G
INST_ID NAME VALUE
------- ---------- ---------
Diag Trace /trace/file/location/
exit
Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST1008
10046 TRACE file
How to Generate 10046 TRACE file from Oracle Database
-----------------------------------------------------
Database Details
----------------
sqlplus "/as sysdba"
set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Generate 10046 TRACE file at session level
------------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST10046';
alter session set events '10046 trace name context forever, level 12';
--execute the statement which fails, here
alter session set events '10046 trace name context off';
Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace'; -------------- 11G
INST_ID NAME VALUE
------- ---------- ---------
Diag Trace /trace/file/location/
exit
Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST10046
Longops Query using v$session_longops
Longops.sql
-----------
set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN
MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2)
"%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND sofar<>totalwork AND time_remaining > 0
/
Note:
Get the SID from v$session_longops and plug it into v$session to check the SQL command details.
Current Running SQLs
--------------------
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id and b.sid='&sid'
/
set pages 50000 lines 32767
col USERNAME for a10
col OSUSER for a10
col MACHINE for a10
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.status,s.process fg_pid,s.longon_time,s.machine,p.spid bg_pid from gv$session s,gv$process p
where s.addr=p.addr and s.sid='&sid'
/
$ps -ef | grep <spid>
set pages 50000 lines 32767
SELECT INST_ID, SID, SERIAL#, SQL_ID,USERNAME, PROGRAM, MACHINE, SERVICE_NAME
FROM GV$SESSION
WHERE SID IN ('<SID_NO.1>','<SID_NO.2>')
/
Active Running SQLs
--------------------
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
OR - Use the below Query
Longops.sql
-----------
set pages 50000 lines 32767
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork, l.start_time,l.last_update_time,round
(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS", round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS",
ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE", l.message,s.sql_text
FROM gv$session_longops l
LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND l.time_remaining > 0
/
Hanganalyze
oradebug setmypidoradebug unlimit;
oradebug hanganalyze 3 or 12
oradebug tracefile_name;
oradebug close_trace
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266
oradebug tracefile_name;
oradebug close_trace
Labels: PERFORMANCE TUNING
November 18, 2012
AWR
Understanding AWR quick
Quick Instructions For Obtaining The Automatic Workload Repository (AWR) Report [ID 1086120.1]
FAQ: How to Use AWR reports to Diagnose Database Performance Issues [ID 1359094.1]
How to Read PGA Memory Advisory Section in AWR and Statspack Reports [ID 786554.1]
How to Interpret the OS stats section of an AWR report [ID 762526.1]
AWR report
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrrpti.sql ------- RAC
AWR snapshot interval
select * from dba_hist_wr_control;
-----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
AWR snapshots available
set line 150
col startup_time for a30
col END_INTERVAL_TIME for a30
col BEGIN_INTERVAL_TIME for a30
SELECT snap_id, startup_time,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
FROM dba_hist_snapshot
ORDER BY 1,2;
-----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
DROP_SNAPSHOT_RANGE
Drop a range of snapshots dbms_workload_repository.drop_snapshot_Range(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
exec dbms_workload_repository.drop_snapshot_range(1105, 1199);
select sql_id,sql_text from dba_hist_sqltext where sql_id='&sql_id';
Labels: PERFORMANCE TUNING
October 29, 2012
Explain plan for an Old Query
Explain plan for an Old Query :
set pages 9999 lines 300
select SESSION_ID,SESSION_SERIAL#,SQL_ID,SQL_PLAN_HASH_VALUE from DBA_HIST_ACTIVE_SESS_HISTORY where SESSION_ID=<session_id> and
SESSION_SERIAL#=<session_serial>;
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'));
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID','&plan_hash_value'));
select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced +peeked_binds'));
SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID'));
SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID', '&child_number'));
Explain plan for Query :
alter session set current_schema=<schema_name>;
select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display_cursor);
select /*+ gather_plan_statistics */ count(*) from hist_test where id1>1000000;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
select distinct sql_id, PLAN_HASH_VALUE,TIMESTAMP from dba_hist_sql_plan where sql_id='&sql_id';
No comments:
Post a Comment