oracle 10g awr报告,oracle 10g awr 报告中内容的所对应的sql

----------------------------------------------SQL ordered by Sharable Memory

select *

from (select /*+ ordered use_nl (b st) */

e.sharable_mem,

sqt.exec,

decode(:e_spm, 0, 0, 100 * e.sharable_mem/:e_spm),

e.sql_id,

decode(e.module, null, null, 'Module: ' || e.module),

nvl(st.sql_text, to_clob('** SQL Text Not Available **'))

from dba_hist_sqlstat e,

(select sql_id, sum(executions_delta) exec

from dba_hist_sqlstat

where dbid            = :b_dbid

and instance_number = 1

and :b_snap_id            < snap_id

and snap_id        <= :e_snap_id

group by sql_id) sqt,

dba_hist_sqltext  st

where sqt.sql_id(+)          = e.sql_id

and e.snap_id              = :e_snap_id

and e.dbid                 = :b_dbid

and e.instance_number      = 1

and st.sql_id(+)           = e.sql_id

and st.dbid(+)             = :b_dbid

and e.sharable_mem         > 1048576

order by nvl(e.sharable_mem, -1) desc, e.sql_id)

where rownum < 65;

---------------------------------------------SQL ordered by Version Count

select *

from (select /*+ ordered use_nl (b st) */

e.version_count,

sqt.exec,

e.sql_id,

decode(e.module, null, null, 'Module: ' || e.module),

nvl(st.sql_text, to_clob('** SQL Text Not Available **'))

from dba_hist_sqlstat e,

(select sql_id, sum(executions_delta) exec

from dba_hist_sqlstat

where dbid            = :b_dbid

and instance_number = 1

and :b_snap_id            < snap_id

and snap_id        <= :e_snap_id

group by sql_id) sqt,

dba_hist_sqltext     st

where sqt.sql_id(+)          = e.sql_id

and e.snap_id              = :e_snap_id

and e.dbid                 = :b_dbid

and e.instance_number      = 1

and st.sql_id(+)           = e.sql_id

and st.dbid(+)             = :b_dbid

and e.version_count        > 20

order by nvl(e.version_count, -1) desc, e.sql_id)

where rownum < 65;

-----------------------------------------------Instance Activity Stats

select b.stat_name st,

e.value - b.value,

round((e.value - b.value)/:ela,2),

round((e.value - b.value)/:tran,2)

from  dba_hist_sysstat b,

dba_hist_sysstat e

where b.snap_id         = :b_snap_id

and e.snap_id         = :e_snap_id

and b.dbid            = :b_dbid

and e.dbid            = :b_dbid

and b.instance_number = 1

and e.instance_number = 1

and b.stat_id         = e.stat_id

and e.stat_name not in

('logons current',

'opened cursors current',

'workarea memory allocated',

'session cursor cache count')

and e.value          >= b.value

and e.value          >  0

order by st;

---------------------------------------------Instance Activity Stats - Absolute Values

select b.stat_name st,

b.value,

e.value

from dba_hist_sysstat b,

dba_hist_sysstat e

where b.snap_id         = :b_snap_id

and e.snap_id         = :e_snap_id

and b.dbid            = :b_dbid

and e.dbid            = :b_dbid

and b.instance_number = 1

and e.instance_number = 1

and b.stat_id         = e.stat_id

and e.stat_name in

('logons current',

'opened cursors current',

'workarea memory allocated',

'session cursor cache count')

and e.value >  0;

---------------------------------------------Instance Activity Stats - Thread Activity

select 'log switches (derived)',

e.sequence# - b.sequence# ,

(e.sequence# - b.sequence#)/(:ela/3600)

from dba_hist_thread e,

dba_hist_thread b

where b.snap_id                = :b_snap_id

and e.snap_id                = :e_snap_id

and b.dbid                   = :b_dbid

and e.dbid                   = :b_dbid

and b.instance_number        = 1

and e.instance_number        = 1

and b.thread#                = e.thread#

and b.thread_instance_number = e.thread_instance_number

and e.thread_instance_number = 1;

-------------------------------------------Tablespace IO Stats

select e.tsname                                               tsname,

sum(e.phyrds - nvl(b.phyrds,0))                        reads,

sum(e.phyrds - nvl(b.phyrds,0)) / :ela                 rps,

decode(sum(e.phyrds - nvl(b.phyrds, 0)),

0, 0,

10 * (sum(e.readtim - nvl(b.readtim, 0)) /

sum(e.phyrds  - nvl(b.phyrds,  0))))      atpr,

decode(sum(e.phyrds - nvl(b.phyrds,0)),

0, 0,

sum(e.phyblkrd - nvl(b.phyblkrd,0)) /

sum(e.phyrds   - nvl(b.phyrds,0)))              bpr,

sum(e.phywrts    - nvl(b.phywrts,0))                   writes,

sum(e.phywrts    - nvl(b.phywrts,0)) / :ela            wps,

sum(e.wait_count - nvl(b.wait_count,0))                waits,

decode(sum(e.wait_count - nvl(b.wait_count, 0)),

0, 0,

10 * (sum(e.time       - nvl(b.time,0)) /

sum(e.wait_count - nvl(b.wait_count,0)))) atpwt,

sum(e.phyrds  - nvl(b.phyrds,0))  +

sum (e.phywrts - nvl(b.phywrts,0))                     ios

from dba_hist_filestatxs e,

dba_hist_filestatxs b

where b.snap_id(+)         = :b_snap_id

and e.snap_id            = :e_snap_id

and b.dbid(+)            = :b_dbid

and e.dbid               = :b_dbid

and b.dbid(+)            = e.dbid

and b.instance_number(+) = 1

and e.instance_number    = 1

and b.instance_number(+) = e.instance_number

and b.tsname(+)          = e.tsname

and b.file#(+)           = e.file#

and b.creation_change#(+) = e.creation_change#

and ((e.phyrds  - nvl(b.phyrds,0))  +

(e.phywrts - nvl(b.phywrts,0))) > 0

group by e.tsname

union all

select e.tsname                                             tsname,

sum(e.phyrds - nvl(b.phyrds,0))                      reads,

sum(e.phyrds - nvl(b.phyrds,0))/:ela                 rps,

decode(sum(e.phyrds - nvl(b.phyrds,0)),

0, 0,

(sum(e.readtim - nvl(b.readtim,0)) /

sum(e.phyrds  - nvl(b.phyrds,0)))*10)        atpr,

decode(sum(e.phyrds - nvl(b.phyrds,0)),

0, to_number(NULL),

sum(e.phyblkrd - nvl(b.phyblkrd,0)) /

sum(e.phyrds   - nvl(b.phyrds,0)))            bpr,

sum(e.phywrts    - nvl(b.phywrts,0))                 writes,

sum(e.phywrts    - nvl(b.phywrts,0)) / :ela          wps,

sum(e.wait_count - nvl(b.wait_count,0))              waits,

decode(sum(e.wait_count - nvl(b.wait_count, 0)),

0, 0,

(sum(e.time       - nvl(b.time,0)) /

sum(e.wait_count - nvl(b.wait_count,0)))*10) atpwt,

sum(e.phyrds  - nvl(b.phyrds,0)) +

sum(e.phywrts - nvl(b.phywrts,0))                    ios

from dba_hist_tempstatxs e,

dba_hist_tempstatxs b

where b.snap_id(+)         = :b_snap_id

and e.snap_id            = :e_snap_id

and b.dbid(+)            = :b_dbid

and e.dbid               = :b_dbid

and b.dbid(+)            = e.dbid

and b.instance_number(+) = 1

and e.instance_number    = 1

and b.instance_number(+) = e.instance_number

and b.tsname(+)          = e.tsname

and b.file#(+)           = e.file#

and b.creation_change#(+) = e.creation_change#

and ((e.phyrds  - nvl(b.phyrds,0))  +

(e.phywrts - nvl(b.phywrts,0))) > 0

group by e.tsname

order by ios desc, tsname;

---------------------------------------------------File IO Stats

select e.tsname

, substr(e.filename, 1, 52)                       filename

, e.phyrds- nvl(b.phyrds,0)                       reads

, (e.phyrds- nvl(b.phyrds,0))/:ela                rps

, decode ((e.phyrds - nvl(b.phyrds, 0)), 0, to_number(NULL),

((e.readtim  - nvl(b.readtim,0)) /

(e.phyrds   - nvl(b.phyrds,0)))*10)         atpr

, decode ((e.phyrds - nvl(b.phyrds, 0)), 0, to_number(NULL),

(e.phyblkrd - nvl(b.phyblkrd,0)) /

(e.phyrds   - nvl(b.phyrds,0)) )             bpr

, e.phywrts - nvl(b.phywrts,0)                    writes

, (e.phywrts - nvl(b.phywrts,0))/:ela             wps

, e.wait_count - nvl(b.wait_count,0)              waits

, decode ((e.wait_count - nvl(b.wait_count, 0)), 0, 0,

((e.time       - nvl(b.time,0)) /

(e.wait_count - nvl(b.wait_count,0)))*10)   atpwt

from dba_hist_filestatxs e

, dba_hist_filestatxs b

where b.snap_id(+)         = :b_snap_id

and e.snap_id            = :e_snap_id

and b.dbid(+)            = :b_dbid

and e.dbid               = :b_dbid

and b.dbid(+)            = e.dbid

and b.instance_number(+) = 1

and e.instance_number    = 1

and b.instance_number(+) = e.instance_number

and b.tsname(+)          = e.tsname

and b.file#(+)           = e.file#

and b.creation_change#(+) = e.creation_change#

and ( (e.phyrds  - nvl(b.phyrds,0)  ) +

(e.phywrts - nvl(b.phywrts,0) ) ) > 0

union all

select e.tsname

, substr(e.filename, 1, 52)                       filename

, e.phyrds- nvl(b.phyrds,0)                       reads

, (e.phyrds- nvl(b.phyrds,0))/:ela                rps

, decode ((e.phyrds - nvl(b.phyrds, 0)), 0, to_number(NULL),

((e.readtim  - nvl(b.readtim,0)) /

(e.phyrds   - nvl(b.phyrds,0)))*10)         atpr

, decode ((e.phyrds - nvl(b.phyrds, 0)), 0, to_number(NULL),

(e.phyblkrd - nvl(b.phyblkrd,0)) /

(e.phyrds   - nvl(b.phyrds,0)) )             bpr

, e.phywrts - nvl(b.phywrts,0)                    writes

, (e.phywrts - nvl(b.phywrts,0))/:ela             wps

, e.wait_count - nvl(b.wait_count,0)              waits

, decode ((e.wait_count - nvl(b.wait_count, 0)), 0, to_number(NULL),

((e.time       - nvl(b.time,0)) /

(e.wait_count - nvl(b.wait_count,0)))*10)   atpwt

from dba_hist_tempstatxs e

, dba_hist_tempstatxs b

where b.snap_id(+)         = :b_snap_id

and e.snap_id            = :e_snap_id

and b.dbid(+)            = :b_dbid

and e.dbid               = :b_dbid

and b.dbid(+)            = e.dbid

and b.instance_number(+) = 1

and e.instance_number    = 1

and b.instance_number(+) = e.instance_number

and b.tsname(+)          = e.tsname

and b.file#(+)           = e.file#

and b.creation_change#(+) = e.creation_change#

and ( (e.phyrds  - nvl(b.phyrds,0)  ) +

(e.phywrts - nvl(b.phywrts,0) ) ) > 0

order by tsname, filename;