oracle优化sql与awr,ORACLE 性能优化-查询数据库慢SQL（其实还是awr和ash报告更方便~）...

查询数据库繁忙原因

V$SQLAREA; v$session;

1. 查看数据库逻辑读写前10sql

set linesize 300;

set pagesize 300;

set long 50000;

SELECT *

FROM (

SELECT sql_fulltext AS sql, SQL_ID,buffer_gets/ executions AS "Gets/Exec",buffer_gets, executions

FROM V$SQLAREA

WHERE buffer_gets > 10000

ORDER BY "Gets/Exec" DESC

)

WHERE rownum <= 10;

2. 查看数据库物理读前10SQL

set linesize 300;

set pagesize 300;

set long 50000;

SELECT *

FROM (

SELECT sql_fulltext AS sql,SQL_ID,disk_reads / executions AS "Reads/Exec",disk_reads, executions

FROM V$SQLAREA

WHERE disk_reads > 1000

ORDER BY "Reads/Exec" DESC

)

WHERE rownum <= 10;

3. 查看当前正在使用SQL

select t.*,

b.name,b.position,b.dup_position,

b.value_string

from v$sql t, v$sql_bind_capture b

where

t.hash_value=b.hash_value

and t.LAST_ACTIVE_TIME>(sysdate - interval '10'MINUTE)  --执行1分钟内的SQL语句

--and t.PARSING_SCHEMA_NAME = 'LSBIKE' --数据库

--and (t.MODULE = 'JDBC Thin Client') --不是在某些终端里的执行

--and lower(t.SQL_TEXT) like '%select%' --查询某类SQL语句

--order by t.LAST_ACTIVE_TIME desc

--order by t.excutions desc

4. 查看1小时内执行耗时最长sql

SELECT *

FROM (SELECT C.SQL_ID,

(C.ETIME - NVL(O.ELAPSED_TIME_TOTAL, 0)) / 1000000 ELAPSED_DELTA_TIME

FROM (SELECT *

FROM (SELECT SQL_ID,

PLAN_HASH_VALUE,

SUM(ELAPSED_TIME) ETIME

FROM V$SQL S

WHERE S.PLAN_HASH_VALUE <> 0

GROUP BY SQL_ID, PLAN_HASH_VALUE

HAVING MAX(LAST_ACTIVE_TIME) > SYSDATE - 1 / 24

ORDER BY SUM(ELAPSED_TIME) DESC)

WHERE ROWNUM < 100) C,

(SELECT SQL_ID, ELAPSED_TIME_TOTAL, PLAN_HASH_VALUE

FROM DBA_HIST_SQLSTAT HIS

WHERE HIS.SNAP_ID =

(SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT)

AND HIS.INSTANCE_NUMBER = USERENV('instance')) O

WHERE C.SQL_ID = O.SQL_ID

ANDC.PLAN_HASH_VALUE = O.PLAN_HASH_VALUE

ORDER BY ELAPSED_DELTA_TIMEDESC)

WHERE ROWNUM < 10;

5. 查询主机性能

select substr(snap_begin,1,10)jy_date,round(avg((usr1+sys1)*100/diff),2) "CPU%",

round(avg(io1*100/diff),2)"IOWait(%)"

from (

select  snap_id,  usr1,sys1, io1, nice1,snap_begin, snap_end ,

round(extract( day from diffs) *24*60*60*60+

extract( hour from diffs) *60*60+

extract( minute from diffs )* 60 +

extract( second from diffs )) diff

from ( select a.snap_id,

(usr1 - (lag( (usr1)) over (order bya.snap_id)))/100          usr1,

( sys1  - (lag( (sys1)) over (orderby a.snap_id)))/100        sys1,

( io1 - (lag( (io1)) over (order bya.snap_id)))/100          io1,

( nice1 - (lag( (nice1)) over (order bya.snap_id)))/100       nice1,

(to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DDHH24miss'))      snap_begin,

(to_char(END_INTERVAL_TIME,'YYYY-MM-DDHH24miss'))        snap_end,

(END_INTERVAL_TIME-BEGIN_INTERVAL_TIME)                      diffs

from(

select snap_id, avg(usr0) usr1,avg(sys0) sys1, avg(io0) io1, avg(nice0) nice1

from (

select a.snap_id,

case b.STAT_NAME

when 'USER_TIME' then a.value / (SELECT value num_cpus FROM v$osstat WHEREstat_name = 'NUM_CPU_CORES')

end usr0 ,

case b.STAT_NAME

when 'SYS_TIME' then  a.value / (SELECT value num_cpus FROM v$osstat WHEREstat_name = 'NUM_CPU_CORES')

end sys0 ,

case b.STAT_NAME

when 'IOWAIT_TIME' then  a.value / (SELECT value num_cpus FROM v$osstatWHERE stat_name = 'NUM_CPU_CORES')

end io0,

case b.STAT_NAME

when 'NICE_TIME' then  a.value / (SELECT value num_cpus FROM v$osstatWHERE stat_name = 'NUM_CPU_CORES')

end nice0

from  sys.WRH$_OSSTAT a,  sys.WRH$_OSSTAT_NAME b

where

a.dbid      =b.dbid       and

a.STAT_ID   = b.stat_id    and

instance_number = (select instance_number inst fromv$instance)    and

b.stat_name in ('USER_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME') and

a.snap_id in ( select snap_id from sys.wrm$_snapshot

where begin_interval_time>to_date(20170201,'yyyymmdd')

and begin_interval_time)

order by 1 desc

)

group by snap_id

order by snap_iddesc

)a,  sys.wrm$_snapshot s

where  a.snap_id = s.snap_id

and s.instance_number = (selectinstance_number inst from v$instance)

order by snap_id desc

)

)

where     to_number(substr(snap_begin,12,4))>=930

and to_number(substr(snap_begin,12,4))<=1505

and to_number(substr(snap_end,12,4))<=1505

and substr(snap_begin,1,10) not in

(

'2017-02-04',

'2017-02-05',

'2017-02-11',

'2017-02-12',

'2017-02-18',

'2017-02-19',

'2017-02-25',

'2017-02-26'

)

group by  substr(snap_begin,1,10)

order by  substr(snap_begin,1,10) asc

;