Scripts:查询每个数据文件使用效率的脚本perf_file_io_efficiency.sql
来源:互联网 发布:八叉树算法 js 编辑:程序博客网 时间:2024/05/16 09:58
-- +----------------------------------------------------------------------------+
-- | 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_efficiency.sql |
-- | CLASS : Tuning |
-- | PURPOSE : This script produces a cumulative report that gives information |
-- | based on IO efficiency since the Oracle instance was started. |
-- | The report generated will list physical block reads and |
-- | efficiency (the efficiency number measures the percentage of |
-- | time Oracle asked for and got the right block the first time; |
-- | this is a function of the type of table scan and indexing). |
-- | |
-- | The relative low efficiency of the SYSTEM areas is normal. This |
-- | is due to indexes and tables being mixed together in the SYSTEM |
-- | tablespace. A classic case on Oracle's part of "Do what we say, |
-- | not what we do." |
-- | |
-- | * If your temporary tablespace shows an efficiency number, |
-- | someone is using if for data instead of temporary tables. |
-- | * Rollback efficiency should always be 100 percent; if not, |
-- | someone is using the rollback tablespace for tables/indexes. |
-- | * Index tablespace should always show high efficiencies; if |
-- | they don't, then either the indexes are bad or someone is |
-- | using the index tablespace for tables. |
-- | * An attempt should be made to even out IO. If a disk is |
-- | showing a considerable amount of IO, move some of the |
-- | datafiles to other disks. |
-- | 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
COLUMN ts FORMAT a15 HEADING 'Tablespace'
COLUMN fn FORMAT a38 HEADING 'Filename'
COLUMN rds HEADING 'Reads'
COLUMN blk_rds HEADING 'Block Reads'
COLUMN wrts HEADING 'Writes'
COLUMN blk_wrts HEADING 'Block Writes'
COLUMN rw HEADING 'Reads+Writes'
COLUMN blk_rw HEADING 'Block Reads+Writes'
COLUMN eff FORMAT a10 HEADING 'Effeciency'
SELECT
f.tablespace_name ts
, f.file_name fn
, v.phyrds rds
, v.phyblkrd blk_rds
, v.phywrts wrts
, v.phyblkwrt blk_wrts
, v.phyrds + v.phywrts rw
, v.phyblkrd + v.phyblkwrt blk_rw
, DECODE(v.phyblkrd, 0, null, ROUND(100*(v.phyrds + v.phywrts)/(v.phyblkrd + v.phyblkwrt), 2)) eff
FROM
dba_data_files f
, v$filestat v
WHERE
f.file_id = v.file#
ORDER BY
rds
/
-- | 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_efficiency.sql |
-- | CLASS : Tuning |
-- | PURPOSE : This script produces a cumulative report that gives information |
-- | based on IO efficiency since the Oracle instance was started. |
-- | The report generated will list physical block reads and |
-- | efficiency (the efficiency number measures the percentage of |
-- | time Oracle asked for and got the right block the first time; |
-- | this is a function of the type of table scan and indexing). |
-- | |
-- | The relative low efficiency of the SYSTEM areas is normal. This |
-- | is due to indexes and tables being mixed together in the SYSTEM |
-- | tablespace. A classic case on Oracle's part of "Do what we say, |
-- | not what we do." |
-- | |
-- | * If your temporary tablespace shows an efficiency number, |
-- | someone is using if for data instead of temporary tables. |
-- | * Rollback efficiency should always be 100 percent; if not, |
-- | someone is using the rollback tablespace for tables/indexes. |
-- | * Index tablespace should always show high efficiencies; if |
-- | they don't, then either the indexes are bad or someone is |
-- | using the index tablespace for tables. |
-- | * An attempt should be made to even out IO. If a disk is |
-- | showing a considerable amount of IO, move some of the |
-- | datafiles to other disks. |
-- | 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
COLUMN ts FORMAT a15 HEADING 'Tablespace'
COLUMN fn FORMAT a38 HEADING 'Filename'
COLUMN rds HEADING 'Reads'
COLUMN blk_rds HEADING 'Block Reads'
COLUMN wrts HEADING 'Writes'
COLUMN blk_wrts HEADING 'Block Writes'
COLUMN rw HEADING 'Reads+Writes'
COLUMN blk_rw HEADING 'Block Reads+Writes'
COLUMN eff FORMAT a10 HEADING 'Effeciency'
SELECT
f.tablespace_name ts
, f.file_name fn
, v.phyrds rds
, v.phyblkrd blk_rds
, v.phywrts wrts
, v.phyblkwrt blk_wrts
, v.phyrds + v.phywrts rw
, v.phyblkrd + v.phyblkwrt blk_rw
, DECODE(v.phyblkrd, 0, null, ROUND(100*(v.phyrds + v.phywrts)/(v.phyblkrd + v.phyblkwrt), 2)) eff
FROM
dba_data_files f
, v$filestat v
WHERE
f.file_id = v.file#
ORDER BY
rds
/
0 0
- Scripts:查询每个数据文件使用效率的脚本perf_file_io_efficiency.sql
- Scripts:查询每个数据文件等待时间的脚本perf_file_waits.sql
- Scripts:查询数据文件IO使用率的脚本 perf_file_io.sql
- Scripts:查询每个session命中率的脚本perf_hit_ratio_by_session.sql
- Scripts:查询等待事件的SQL脚本owi_event_names.sql
- Scripts:查询log file sync 等待的脚本lfsdiag.sql
- Scripts:查询db_block_buffer使用率的脚本perf_db_block_buffer_usage.sql
- Scripts:查询参数信息的脚本parms.sql
- Scripts:查询所有参数修改信息的脚本parm_mods.sql
- Scripts:查询回滚段信息的脚本rollback_segments.sql
- Scripts:查看数据文件使用率的脚本(包括临时表空间的文件哦)dba_file_space_usage.sql
- Scripts:报告数据库中数据文件控制文件临时文件redo文件的使用情况dba_file_use.sql
- Scripts:查询使用回滚段的用户rollback_users.sql
- Scripts:报告数据库中所有数据文件使用情况dba_files_all.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:查询每个数据文件等待时间的脚本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)解释
- hdu 2112 HDU Today (dijkstra)
- b2Body刚体
- 九度-1384
- 约瑟夫环
- Virtualbox命令行启动和操作虚拟机
- Debian中fcitx无法激活