Oracle database performance checks

{
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
}

{
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
}

{
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"
}

{
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"
}

{
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 ;
}

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

{
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
}

{
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
}

{
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
}

{
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
}

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

{
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
}

{
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";
}

{
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";
}

{
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";
}