Scripts:查询数据文件IO使用率的脚本 perf_file_io.sql
来源:互联网 发布:java抽象类的特点 编辑:程序博客网 时间:2024/05/16 01:38
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : perf_file_io.sql |
-- | CLASS : Tuning |
-- | PURPOSE : Reports on Read/Write datafile activity. This script was |
-- | designed to work with Oracle8i or higher. It will include all |
-- | tablespaces using any type of extent management as well as true |
-- | TEMPORARY tablespaces. (i.e. use of "tempfiles") |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN ts_name FORMAT a15 HEAD 'Tablespace'
COLUMN fname FORMAT a45 HEAD 'File Name'
COLUMN phyrds FORMAT 999,999,999 HEAD 'Physical Reads'
COLUMN phywrts FORMAT 999,999,999 HEAD 'Physical Writes'
COLUMN read_pct FORMAT 999.99 HEAD 'Read Pct.'
COLUMN write_pct FORMAT 999.99 HEAD 'Write Pct.'
BREAK ON report
COMPUTE SUM OF phyrds ON report
COMPUTE SUM OF phywrts ON report
COMPUTE AVG OF read_pct ON report
COMPUTE AVG OF write_pct ON report
SELECT
df.tablespace_name ts_name
, df.file_name fname
, fs.phyrds phyrds
, (fs.phyrds * 100) / (fst.pr + tst.pr) read_pct
, fs.phywrts phywrts
, (fs.phywrts * 100) / (fst.pw + tst.pw) write_pct
FROM
sys.dba_data_files df
, v$filestat fs
, (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst
, (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst
WHERE
df.file_id = fs.file#
UNION
SELECT
tf.tablespace_name ts_name
, tf.file_name fname
, ts.phyrds phyrds
, (ts.phyrds * 100) / (fst.pr + tst.pr) read_pct
, ts.phywrts phywrts
, (ts.phywrts * 100) / (fst.pw + tst.pw) write_pct
FROM
sys.dba_temp_files tf
, v$tempstat ts
, (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst
, (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst
WHERE
tf.file_id = ts.file#
ORDER BY phyrds DESC
/
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : perf_file_io.sql |
-- | CLASS : Tuning |
-- | PURPOSE : Reports on Read/Write datafile activity. This script was |
-- | designed to work with Oracle8i or higher. It will include all |
-- | tablespaces using any type of extent management as well as true |
-- | TEMPORARY tablespaces. (i.e. use of "tempfiles") |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN ts_name FORMAT a15 HEAD 'Tablespace'
COLUMN fname FORMAT a45 HEAD 'File Name'
COLUMN phyrds FORMAT 999,999,999 HEAD 'Physical Reads'
COLUMN phywrts FORMAT 999,999,999 HEAD 'Physical Writes'
COLUMN read_pct FORMAT 999.99 HEAD 'Read Pct.'
COLUMN write_pct FORMAT 999.99 HEAD 'Write Pct.'
BREAK ON report
COMPUTE SUM OF phyrds ON report
COMPUTE SUM OF phywrts ON report
COMPUTE AVG OF read_pct ON report
COMPUTE AVG OF write_pct ON report
SELECT
df.tablespace_name ts_name
, df.file_name fname
, fs.phyrds phyrds
, (fs.phyrds * 100) / (fst.pr + tst.pr) read_pct
, fs.phywrts phywrts
, (fs.phywrts * 100) / (fst.pw + tst.pw) write_pct
FROM
sys.dba_data_files df
, v$filestat fs
, (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst
, (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst
WHERE
df.file_id = fs.file#
UNION
SELECT
tf.tablespace_name ts_name
, tf.file_name fname
, ts.phyrds phyrds
, (ts.phyrds * 100) / (fst.pr + tst.pr) read_pct
, ts.phywrts phywrts
, (ts.phywrts * 100) / (fst.pw + tst.pw) write_pct
FROM
sys.dba_temp_files tf
, v$tempstat ts
, (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst
, (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst
WHERE
tf.file_id = ts.file#
ORDER BY phyrds DESC
/
0 0
- Scripts:查询数据文件IO使用率的脚本 perf_file_io.sql
- Scripts:查询db_block_buffer使用率的脚本perf_db_block_buffer_usage.sql
- Scripts:查询每个数据文件等待时间的脚本perf_file_waits.sql
- Scripts:查询每个数据文件使用效率的脚本perf_file_io_efficiency.sql
- Scripts:查看数据文件使用率的脚本(包括临时表空间的文件哦)dba_file_space_usage.sql
- Scripts:查询sga中各组件使用率的脚本perf_sga_usage.sql
- Scripts:查询等待事件的SQL脚本owi_event_names.sql
- Scripts:查询log file sync 等待的脚本lfsdiag.sql
- Scripts:查询参数信息的脚本parms.sql
- Scripts:查询所有参数修改信息的脚本parm_mods.sql
- Scripts:查询每个session命中率的脚本perf_hit_ratio_by_session.sql
- Scripts:查询回滚段信息的脚本rollback_segments.sql
- Scripts:报告物理数据库增长情况(注意脚本是看你数据库添加数据文件的时间哦)dba_db_growth.sql
- Scripts:查询物理读最多的10个SQL的脚本hphy10.sql
- 查询表空间使用率的脚本
- Scripts:列出用户信息的脚本sec_users.sql
- Scripts:查询library cache lock和hang的脚本library_cache_locks_pins.sql
- Scripts:根据sid,ospid来查询进程信息的脚本os_pid.sql
- android TabHost 导航标签
- Scripts:查询所有参数修改信息的脚本parm_mods.sql
- 书本Applet程序练习------同一页Applet之间的通信
- NYOJ 题目79 拦截导弹
- 新辰:90后大学生创业开口笑馒头店火爆全市 卖馒头日赚2000!
- Scripts:查询数据文件IO使用率的脚本 perf_file_io.sql
- Scripts:查询每个数据文件等待时间的脚本perf_file_waits.sql
- 游起来吧!超妹!(物理小试题)
- Scripts:查询每个session命中率的脚本perf_hit_ratio_by_session.sql
- Object C Lesson1
- Android Activity布局之RelativeLayout
- Scripts:查询每个数据文件使用效率的脚本perf_file_io_efficiency.sql
- 终于理解动态规划,最简单运用~
- java的几种对象(PO,VO,DAO,BO,POJO)解释