ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql监控的sh

sql监控的sh

原创 Linux操作系统 作者:myfriend2010 时间:2019-06-29 15:21:04 0 删除 编辑

export ORACLE_BASE=/oracle
export AIXTHREAD_SCOPE=S
export TMP=/tmp
export TEMP=/tmp
export TMPDIR=/tmp
umask 022
export CRS_HOME=$ORACLE_BASE/crs
export ORA_CRS_HOME=$ORACLE_BASE/crs
export ORACLE_HOME=$ORACLE_BASE/db10g
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$CRS_HOME/lib:$ORACLE_HOME/lib32:$CRS_HOME/lib32
export PATH=$ORACLE_HOME/bin:/oracle/OPatch:$CRS_HOME/bin:$PATH
export ORACLE_SID=zgdb1
export NLS_LANG=american_america.ZHS16GBK

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:$ORACLE_HOME/bin:/sbin:.

export PATH

if [ -s "$MAIL" ] # This is at Shell startup. In normal
then echo "$MAILMSG" # operation, the Shell checks
fi # periodically.
prex=`date "+%Y%m%d"`
sqlplus "/ as sysdba">>/oracle/zxc/zgdb1_sql_$prex.out<set pagesize 50000 linesize 300
set echo off

column executions heading "Execs" format 99999999
column rows_processed heading "Rows Procd" format 99999999
column loads heading "Loads" format 999999.99
column buffer_gets heading "Buffer Gets"
column disk_reads heading "Disk Reads"
column elapsed_time heading "Elasped Time"
column cpu_time heading "CPU Time"
column sql_text heading "SQL Text" format a60 wrap
column avg_cost heading "Avg Cost" format 99999999
column gets_per_exec heading "Gets Per Exec" format 99999999
column reads_per_exec heading "Read Per Exec" format 99999999
column rows_per_exec heading "Rows Per Exec" format 99999999

break on report
compute sum of rows_processed on report
compute sum of executions on report
compute avg of avg_cost on report
compute avg of gets_per_exec on report
compute avg of reads_per_exec on report
compute avg of row_per_exec on report
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

PROMPT
PROMPT Top 10 most expensive SQL (Elapsed Time)...
PROMPT
select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v$sqlarea
where elapsed_time > 20000
order by elapsed_time desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (CPU Time)...
PROMPT

select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v$sqlarea
where cpu_time > 20000
order by cpu_time desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (Buffer Gets by Executions)...
PROMPT

select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < 11
/


PROMPT Top 10 most expensive SQL (Physical Reads by Executions)...
PROMPT

select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < 11
/

PROMPT Top 10 most expensive SQL (Rows Processed by Executions)...
PROMPT

select rownum as rank, a.*
from (
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text
from v$sqlarea
where rows_processed > 10000
order by rows_processed desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (Buffer Gets vs Rows Processed)...
PROMPT

select rownum as rank, a.*
from (
select buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
executions, loads,
(decode(rows_processed,0,1,1)) *
buffer_gets/ decode(rows_processed,0,1,
rows_processed) avg_cost,
sql_text
from v$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc) a
where rownum < 11
/


select rownum as rank, a.*
from (
select upper(substr(sql_text, 1, 65)) sqltext, count(*)
from v$sqlarea
group by upper(substr(sql_text, 1, 65))
having count(*) > 1
order by count(*) desc) a
where rownum < 11
/
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
!


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/694276/viewspace-51425/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-01

  • 博文量
    187
  • 访问量
    126638