A collections of commands to check your Oracle database performance.


Prerequisites

  • ssh access to the database server as user oracle

  • (optional) Internet connectivity on the database server

  • (optional) Enterprise Edition license with Diagnostic Pack.


Check redo log activity

Run as oracle_user@database_server:

{
echo ""
echo "##################"
echo "Check redo log activity"
echo "##################"
sqlplus -s / as sysdba <<EOF
set lines 150 pages 200 feedback off
SELECT TO_CHAR(first_time,'MMDD') MMDD, TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') "21", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v\$log_history
GROUP BY TO_CHAR(first_time,'MMDD')
ORDER BY 1;
EOF
}
CODE

Example report:

Check for long running queries

Run as oracle_user@database_server:

{
echo ""
echo "####################"
echo "Check for long running queries"
echo "####################"
sqlplus -s / as sysdba << EOF
set lines 200 pages 100
col message format a100
col target format a30
SELECT target, elapsed_seconds, time_remaining, ROUND(sofar/totalwork*100,2) "%_COMPLETE", message
FROM v\$session_longops WHERE totalwork != 0 AND sofar <> totalwork;
EOF
}
CODE

List DB segments by size

Modify the script if your schema is not named CORPU.S:

{
echo ""
echo "####################"
echo "List DB segments by size"
echo "####################"
sqlplus -s / as sysdba <<EOF
set lines 140 pages 200
col table_name format a40
select table_name,num_rows,blocks,round(num_rows/blocks) as r_over_b,last_analyzed
from dba_tables where owner='CORPUS' and num_rows > 10000 and blocks > 0 order by 2; EOF
echo "NOTE: if you see -no rows selected- run CheckJDBC.sh as corpus at the application server"
}
CODE

Check database alert log

Run as oracle_user@database_server:

{
echo ""
echo "####################"
echo "Check database alert log"
echo "####################"
 test -f ~/orahome/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log && LAL=~/orahome/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log
 test -f ~/diag/rdbms/corpus/corpus/trace/alert_corpus.log && LAL=~/diag/rdbms/corpus/corpus/trace/alert_corpus.log
 test -f ~/admin/corpus/bdump/alert_${ORACLE_SID}.log && LAL=~/admin/corpus/bdump/alert_${ORACLE_SID}.log
 test -z "$LAL" &&
 echo "ERROR: No alert log was found on the default locations. Please inform the article contact person." ||
 tail -500 $LAL | egrep -i "error|warning|ORA-" | egrep -v "LICENSE_SESSIONS_WARNING" | cut -c -100
echo -e "\n\nINFO: log location is $LAL"
}
CODE

Check current wait events in the database

Run as oracle_user@database_server:

{
echo ""
echo "################################"
echo "Check current wait events in the database"
echo "################################"
 cd; test -f snapper.sql || { wget https://raw.githubusercontent.com/tanelpoder/tpt-oracle/master/snapper.sql ||
 echo "ERROR: Can't download the script. Check the internet connection or copy the script manually." ; }
 echo "Please wait 10 seconds";
 echo "@snapper ash 5 3 all \n exit;" | sqlplus -s / as sysdba ;
}
CODE


# alternative view showing censhare module and command
echo "@snapper ash=sid+sql_id+module+action 5 3 all \n exit;" | sqlplus -s / as sysdba
CODE

Example report:

Display query plan for specific SQL statement

Run as oracle_user@database_server:

{
echo ""
echo "##################################"
echo "Display query plan for specific SQL statement"
echo "##################################"
while
echo -n "Enter SQL ID : ";
do read sqlid; [ -z $sqlid ] || break;
done;

echo -e "select child_number from v\$sql where sql_id='$sqlid'; \n exit;" | sqlplus -s / as sysdba 

echo -n "Which child number do you want to analyze ? [0] : ";
read childn;
[ -z $childn ] && childn=0;

sqlplus -s / as sysdba <<EOF
alter session set nls_language='AMERICAN'; 
set lines 140 pages 200
select * from table (dbms_xplan.display_cursor('$sqlid', $childn));
EOF
}
CODE

Check backup

Run as oracle_user@database_server:

{
echo ""
echo "############"
echo "Check backup"
echo "############"
sqlplus -s / as sysdba <<EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
set lines 180
set pages 30
col MB_IN format 99999
col MB_OUT format 99999 col status format a15
select START_TIME, END_TIME, round(INPUT_BYTES/1024/1024) MB_IN, round(OUTPUT_BYTES/1024/1024) MB_OUT, OBJECT_TYPE, STATUS
from v\$RMAN_STATUS
where
--START_TIME > sysdate - 10 and
operation='BACKUP'
order by START_TIME;
EOF
}
CODE

Check parameters and memory advisors

Run as oracle_user@database_server:

{
echo ""
echo "############"
echo "Check parameters and memory advisors"
echo "############"
sqlplus -s / as sysdba <<EOF
set pages 20 lines 200 feedback off
col name format a40
col value format a40
select name,value from v\$parameter where name in ('optimizer_index_caching', 'optimizer_index_cost_adj','memory_max_target','memory_target','sga_target','sga_max_size','pga_aggregate_target','use_large_pages') order by name;
select * from v\$memory_target_advice order by 2;
select * from v\$sga_target_advice order by 2;
select * from v\$pga_target_advice order by 2;
EOF
}
CODE


{
echo ""
echo "######################"
echo "Check non-default DB parameters "
echo "######################"
sqlplus -s / as sysdba <<EOF
col name format a30
col value format a50
set lines 200 pages 200
select name, value from v\$parameter where isdefault = 'FALSE' and value is not null order by name;
EOF
}
CODE


Optional but required check before you send the ticket to Development:
- AdminClient Server action "check foreight key indexes"
- AdminClient Server action "check database schema"
CODE


Response-Time Analysis

Run as oracle_user@database_server:

{
echo ""
echo "############"
echo "Response-Time Analysis"
echo "############"
echo "source http://www.oracle.com/technetwork/articles/schumacher-analysis-099313.html"

sqlplus -s / as sysdba <<EOF
set pages 20 lines 200 feedback off
col name format a40
col value format a40
col sql_text format a60

select CASE METRIC_NAME WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)' WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)' ELSE METRIC_NAME END METRIC_NAME,
CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2) WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2) ELSE round(MINVAL) END MININUM,
CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2) WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2) ELSE round(MAXVAL) END MAXIMUM,
CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2) WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2) ELSE round(AVERAGE) END AVERAGE from SYS.V_\$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec', 'CPU Usage Per Txn','Database CPU Time Ratio','Database Wait Time Ratio','Executions Per Sec','Executions Per Txn','Response Time Per Txn','SQL Service Response Time','User Transaction Per Sec') ORDER BY 1;

select
case db_stat_name when 'parse time elapsed' then 'soft parse time' else db_stat_name end db_stat_name,
case db_stat_name when 'sql execute elapsed time' then time_secs - plsql_time when 'parse time elapsed' then time_secs - hard_parse_time else time_secs end time_secs,
case db_stat_name when 'sql execute elapsed time' then round(100 * (time_secs - plsql_time) / db_time,2) when 'parse time elapsed' then round(100 * (time_secs - hard_parse_time) / db_time,2) else round(100 * time_secs / db_time,2) end pct_time
from (select stat_name db_stat_name, round((value / 1000000),3) time_secs from sys.v_\$sys_time_model where stat_name not in('DB time','background elapsed time', 'background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time from sys.v_\$sys_time_model where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time from sys.v_\$sys_time_model where stat_name = 'PL/SQL execution elapsed time'), (select round((value / 1000000),3) hard_parse_time from sys.v_\$sys_time_model where stat_name = 'hard parse elapsed time') order by 2 desc;

select WAIT_CLASS, TOTAL_WAITS, round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS, ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS, round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
from (select WAIT_CLASS, TOTAL_WAITS, TIME_WAITED from V\$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle'), (select sum(TOTAL_WAITS) SUM_WAITS, sum(TIME_WAITED) SUM_TIME from V\$SYSTEM_WAIT_CLASS where WAIT_CLASS != 'Idle') order by 5 desc;

select * from (select SUBSTR(sql_text,0,60) sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time from sys.v_\$sqlarea order by 3 desc) where rownum < 7;
select * from (select SUBSTR(sql_text,0,60) sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time from sys.v_\$sqlarea order by 4 desc) where rownum < 7;
select * from (select SUBSTR(sql_text,0,60) sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time from sys.v_\$sqlarea order by 5 desc) where rownum < 7;

EOF
}
CODE


Generate ADDM report

This script is not working if the database is not licensed.

Run as oracle_user@database_server:

{
echo ""
echo "##################"
echo "Generate ADDM report"
echo "##################"
echo -n "How many hours do you want to analyse? [3] : ";
read snapshots;
test -z "$snapshots" && snapshots=3

cat <<EOF > /tmp/addmrpt_generate_definition.sql
spool /tmp/addmrpt_definition.sql
set hea off lines 130 pages 100
select 'define dbid = '|| dbid||';' from v\$database;
select 'define inst_num = '|| INSTANCE_NUMBER||';' from v\$instance;
select 'define num_days = 1;' from dual;
select 'define begin_snap = '|| (max(snap_id)-$snapshots) ||';' from dba_hist_snapshot;
select 'define end_snap = '|| max(snap_id)||';' from dba_hist_snapshot;
select 'define report_name = /tmp/addmrpt.txt;' from dual;
EOF

echo "@/tmp/addmrpt_generate_definition.sql \n exit ; " | sqlplus -s / as sysdba
sqlplus -s / as sysdba <<EOF
alter session set nls_language='AMERICAN';
@/tmp/addmrpt_definition.sql
@@?/rdbms/admin/addmrpti
exit;
EOF

echo "REPORT LOCATION is /tmp/addmrpt.txt";
}
CODE

Generate ASH report

This script is not working if the database is not licensed.

Run as oracle_user@database_server:

{
echo ""
echo "################"
echo "Generate ASH report"
echo "################"
echo -n "How many minutes do you want to analyse? [180] : ";
read minutes;
[ -z $minutes ] && minutes=180;

sqlplus -s / as sysdba <<EOF
alter session set nls_language='AMERICAN';
define report_type = 'text';
define begin_time = '-$minutes';
define duration = '';
define report_name = '/tmp/ashrpt.txt';
@?/rdbms/admin/ashrpt
exit
EOF
echo "REPORT LOCATION is /tmp/ashrpt.txt";
}
CODE


Generate AWR report

This script is not working if the database is not licensed.
Run as oracle_user@database_server


{
echo ""
echo "#################"
echo "Generate AWR report"
echo "#################"
echo -n "How many hours do you want to analyse? [3] : ";
read snapshots;
[ -z $snapshots ] && snapshots=3;

cat <<EOF > /tmp/awrrpt_generate_definition.sql
spool /tmp/awrrpt_definition.sql
set hea off lines 130 pages 100 select 'define dbid = '|| dbid||';' from v\$database;
select 'define db_name = '|| name||';' from v\$database;
select 'define inst_num = '|| INSTANCE_NUMBER||';' from v\$instance;
select 'define inst_name = '|| INSTANCE_NAME||';' from v\$instance;
select 'define num_days = 1;' from dual; select 'define begin_snap = '|| (max(snap_id)-$snapshots) ||';' from dba_hist_snapshot;
select 'define end_snap = '|| max(snap_id)||';' from dba_hist_snapshot;
select 'define report_name = /tmp/awrrpt.txt;' from dual;
select 'define report_type = text;' from dual;
EOF

echo "@/tmp/awrrpt_generate_definition.sql \n exit ; " | sqlplus -s / as sysdba

sqlplus -s / as sysdba <<EOF
alter session set nls_language='AMERICAN';
@/tmp/awrrpt_definition.sql
@@?/rdbms/admin/awrrpti
exit;
EOF

echo "REPORT LOCATION is /tmp/awrrpt.txt";
}
CODE