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:
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 }
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" }
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" }
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 ; }
# alternative view showing censhare module and command echo "@snapper ash=sid+sql_id+module+action 5 3 all \n exit;" | sqlplus -s / as sysdba
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 }
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 }
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 }
{ 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"
Response-Time Analysis
Run as oracle_user@database_server:
Generate ADDM report
This script is not working if the database is not licensed.
Run as oracle_user@database_server:
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"; }
Generate AWR report
This script is not working if the database is not licensed.
Run as oracle_user@database_server