Oracle的SQL脚本总结

1、top memory

ps -eo size,pid,user,command --sort -size | awk '{ hr=$1/1024 ; printf("%13.2f Mb ",hr) } { for ( x=4 ; x

ps aux | awk '{print $6/1024 " MB\t\t" $11}' | sort -n

ps aux | head -1;ps aux |grep -v PID |sort -rn -k +4 | head -20

ps -eo pmem,pcpu,rss,vsize,args | sort -k 1 -n -r | less

2、查找替换

#linux

sed -i "s/EXCLUDE INSTR 'GRANT'/EXCLUDE INSTRWORDS 'GRANT REVOKE'/g" *.prm ------将所有 prm结尾的文件中，EXCLUDE INSTR 'GRANT'替换为EXCLUDE INSTRWORDS 'GRANT REVOKE'

sed -i "s/.3.74/.3.14/g" *.ini

sed -i "s/.3.14\/db/.3.12\/db/g" *.ini

#aix

find . -name '*.prm' | xargs perl -pi -e "s|EXCLUDE INSTRWORDS 'GRANT REVOKE'|EXCLUDE INSTR 'GRANT',EXCLUDE INSTR 'REVOKE'|g"

3、内存使用率

free -m | sed -n '2p' | awk '{print "used mem is "$3"M,total mem is "$2"M,used percent is "$3/$2*100"%"}'

ps aux | awk -F' ' '{sum+=$6};END{print sum}' ---所有内存之和

4、设置时间格式

export TIME_STYLE='+%Y/%m/%d %H:%M:%S'

5、各个目录的使用率，可以设置深度

du -h -x --max-depth=1

6、查看活动会话

select t.sql_id,

t.USERNAME,

t.EVENT,

t.MACHINE,

t.SID,

t.BLOCKING_SESSION bs,

(select sql_text

from v$sql s

where s.sql_id = t.sql_id

and rownum = 1) sql_text

from v$session t

where t.STATUS = 'ACTIVE'

and t.type='USER'

--and t.SERVICE_NAME not in ('SYS$USERS', 'SYS$BACKGROUND')

and t.sql_id is not null

--and t.MODULE = 'JDBC Thin Client'

7、实时监控SQL

select t.SQL_ID,

t.SQL_TEXT,

t.SQL_EXEC_ID exid,

t.STATUS,

t.USERNAME uname,

round(t.ELAPSED_TIME / 1000000, 2) eltm,

t.BUFFER_GETS buf,

t.DISK_READS dr,

to_char(t.SQL_EXEC_START, 'yyyy-mm-dd hh24:mi:ss') exec_start,

t.MODULE

from v$sql_monitor t

where t.SERVICE_NAME not in ('SYS$USERS', 'SYS$BACKGROUND')

and t.SQL_TEXT not like 'explain plan%'

order by t.LAST_REFRESH_TIME desc;

8、活动会话的历史

select cast(t.SAMPLE_TIME as date) sam_time,

t.SESSION_ID sid,

t.sql_id,

t.EVENT,

t.BLOCKING_SESSION bs,

(select sql_text

from v$sql t1

where t1.sql_id = t.sql_id

and rownum = 1) sql_text,

t.TIME_WAITED tw,

t.MACHINE,

t.WAIT_TIME wt

from v$active_session_history t

where t.sample_time > SYSDATE - 2 / (24 * 60)

order by t.SAMPLE_TIME

9、SQL的执行信息

select t.CHILD_NUMBER child_num,

t.EXECUTIONS execs,

t.ROWS_PROCESSED r_proc,

t.BUFFER_GETS buf,

round(t.BUFFER_GETS / nvl(t.EXECUTIONS,1)) buf_p,

t.ELAPSED_TIME ela_time,

round(t.ELAPSED_TIME / nvl(t.EXECUTIONS,1) / 1000000) ela_p,

t.PLAN_HASH_VALUE plan_hv,

t.SQL_PROFILE sp,

t.SQL_PLAN_BASELINE spb,

t.SQL_Patch spc,

t.LAST_ACTIVE_TIME,

t.FIRST_LOAD_TIME,

t.PROGRAM_ID progid,

t.PROGRAM_LINE# prog_line

from v$sql t

where t.sql_id = 'faqmvjk84j6mk'

order by t.BUFFER_GETS / nvl(t.EXECUTIONS,1)

10、长事务和进度情况

select t.SID,

t.USERNAME,

t.SQL_ID,

t.SQL_PLAN_HASH_VALUE,

case

when opname like '%aggregate%' then

'total'

else

opname

end opname,

trunc(sofar * 100 / totalwork, 2) || '%' progress,

units

from v$session_longops t

where totalwork > sofar

11、通过OS的pid查找会话和SQL

select t1.sid,

t2.spid,

t1.SERIAL#,

t1.USERNAME,

t1.STATUS,

t1.OSUSER,

t1.MACHINE,

t1.PORT,

t1.PROGRAM,

t1.SQL_ID,

t1.BLOCKING_SESSION bs,

t2.PNAME

from v$session t1, v$process t2

where t1.PADDR = t2.ADDR

and (t2.SPID = '1234' or t1.sid='1234')

12、查找锁的信息

select sess.sid,

sess.serial#,

lo.oracle_username,

lo.os_user_name,

ao.object_name,

lo.locked_mode

from v$locked_object lo, dba_objects ao, v$session sess

where ao.object_id = lo.object_id

and lo.session_id = sess.sid;

--alter system kill session '738,1429';