grep -i error /var/log/messages | grep -i ‘disk’

dd if=/dev/zero of=/dev/xxx oflag=direct --测试写IO。默认写OS cache，direct 表示跳过OS cache .

dd if=/dev/xxx of=/dev/null --测试读IO

注意：使用dd命令务必要小心，不当使用，可能导致数据丢失。

grep -i error /var/log/messages | egrep -i ‘up|down’

ping -s 4096 192.168.237.1

select

client_addr,

application_name,

usename,

count(*)

from

sys_stat_activity

where

client_addr is not null

and application_name is not null

and usename is not null

group by

grouping sets(

(client_addr),

(application_name),

(usename),

()

)

select

usename,

state,

count(*)

from

sys_stat_activity

group by

rollup(

usename

state,

)

select

query,

extract(epoch from now)- extract(epoch from query_start ) as execute_seconds

from

sys_stat_activity

where

state = 'active'

and execute_seconds > 0

order by execute_seconds desc

CONNSTR="dbname=${DBNAME} user=${USERNAME} host=${DBHOST} port=${DBPORT} password=${PASSWORD}"

get_seswait()

{

ksql "${CONNSTR}" <<HERE

\t on

select '============================= Session Wait Start: '||current_timestamp||'=============================' from dual;

select 'DAT9999'||'#'||wait_event_type||'#'||wait_event||'#'||pid||'#'||query_start||'#'||

usename||'#'||application_name||'#'||client_hostname||'#'||datname||'#'||state||'#'||query

from sys_stat_activity

where wait_event_type is not null and state is not null

order by wait_event_type,wait_event,query,query_start;

select '============================= Session Wait Done: '||current_timestamp||'=============================' from dual;

\q

HERE

}

format_output(){

grep 'Session Wait Start:' ${TMPFILE}

grep '^ DAT9999' ${TMPFILE} | awk -F# 'BEGIN{

v_event=""; v_type=""; v_event_prev=""; v_type_prev=""; v_event_cnt=0; v_type_cnt=0;

print " wait_event_type wait_event pid query_start";

print " usename application_name client_hostname datname state";

print " query";

print " -------------------- ------------------------------ ---------- ----------------------------------------";

print " -------------------- -------------------- -------------------- -------------------- -------------------";

print " -------------------------------------------------------------------------------------------------------"

}

{

v_type=$2;

v_event=$3;

if (v_event != v_event_prev && v_event_prev != "" ) {print "\n *************** Total "v_event_cnt" of wait event "v_event_prev" ***************"; v_event_cnt=0;}

if (v_type != v_type_prev && v_type_prev != "" ) {print " ############### Total "v_type_cnt" of wait event type "v_type_prev" ###############\n\n"; v_type_cnt=0;}

printf " %-20s %-30s %-10s %-30s\n %-20s %-20s %-20s %-20s %-20s\n %-100s\n", $2, $3, $4, $5, $6, $7, $8, $9, $10, $11;

v_event_cnt = v_event_cnt+1;

v_type_cnt = v_type_cnt+1;

v_event_prev = v_event;

v_type_prev = v_type;

}

END{

print "\n *************** Total "v_event_cnt" of wait event "v_event_prev" ***************";

print " ############### Total "v_type_cnt" of wait event type "v_type_prev" ###############";

}'

grep 'Session Wait Done:' ${TMPFILE}

}

while [ 1 ];do

get_seswait 2>&1 > ${TMPFILE}

format_output | tee -a $CURRDIR/kbsw_$CURRTIME.log

echo -e "\n"

sleep 10

done

if [ $# -eq 2 ] ; then

EVENT=$1

CNT=$2

elif [ $# -eq 1 ] ; then

EVENT=$1

CNT=60

else

echo "Usage: $0 event_name [seconds]"

exit 1

fi

source ./set_conf.sh

TMPSQL_waitevent="select 'waitevent'||'#'||backend_type||'#'||wait_cnt||'#'||sss.query from

(select backend_type,query_id,count(1) wait_cnt from perf.session_history

where event='${EVENT}' and ts > sysdate - round(${CNT}/86400::numeric,5)

group by backend_type,query_id) tmp1 left join sys_stat_statements sss on tmp1.query_id=sss.queryid

order by tmp1.backend_type,wait_cnt desc;"

format_output(){

echo -e " TOP SQL waiting for '${EVENT}' in last ${CNT} seconds"

echo -e " ========================================================================================================================================="

grep "^ $1" ${TMPFILE} | awk -F# 'BEGIN{

printf " %-20s %-15s %-100s\n","backend_type","wait_cnt","query"

print " -------------------- --------------- ----------------------------------------------------------------------------------------------------"

}

{

printf " %-20s %-15s %-100s\n", $2, $3, $4;

}'

echo -e "\n"

}

ksql "${CONNSTR}" -q -c "${TMPSQL_waitevent}" 2>&1 > ${TMPFILE}

echo ""

format_output "waitevent"