Scripts:查询每个session命中率的脚本perf_hit_ratio_by_session.sql
来源:互联网 发布:八叉树算法 js 编辑:程序博客网 时间:2024/05/16 00:42
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : perf_hit_ratio_by_session.sql |
-- | CLASS : Tuning |
-- | PURPOSE : Reports on all sessions along with their individual hit ratio. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 180
SET PAGESIZE 9999
COLUMN unix_id FORMAT a10 HEAD Username
COLUMN oracle_id FORMAT a10 HEAD OracleID
COLUMN os_user FORMAT a20 HEAD OS_User
COLUMN sid FORMAT 99999 HEAD SID
COLUMN serial_id FORMAT 999999 HEAD Serial#
COLUMN unix_pid FORMAT a9 HEAD UNIX_Pid
COLUMN consistent_gets FORMAT 999,999,999,999,999 HEAD Cons_Gets
COLUMN block_gets FORMAT 999,999,999,999,999 HEAD Block_Gets
COLUMN physical_reads FORMAT 999,999,999,999,999 HEAD Phys_Reads
COLUMN hit_ratio FORMAT 999.00 HEAD Hit_Ratio
SELECT
p.username unix_id
, s.username oracle_id
, s.osuser os_user
, s.sid sid
, s.serial# serial_id
, LPAD(p.spid,7) unix_pid
, sio.consistent_gets consistent_gets
, sio.block_gets block_gets
, sio.physical_reads physical_reads
, ROUND((consistent_gets+Block_gets-Physical_reads) /
(Consistent_gets+Block_gets)*100,2) hit_ratio
FROM
v$process p
, v$session s
, v$sess_io sio
WHERE
p.addr (+) = s.paddr
AND s.sid = sio.sid
AND (sio.consistent_gets + sio.block_gets) > 0
AND s.username is not null
ORDER BY hit_ratio
/
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : perf_hit_ratio_by_session.sql |
-- | CLASS : Tuning |
-- | PURPOSE : Reports on all sessions along with their individual hit ratio. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 180
SET PAGESIZE 9999
COLUMN unix_id FORMAT a10 HEAD Username
COLUMN oracle_id FORMAT a10 HEAD OracleID
COLUMN os_user FORMAT a20 HEAD OS_User
COLUMN sid FORMAT 99999 HEAD SID
COLUMN serial_id FORMAT 999999 HEAD Serial#
COLUMN unix_pid FORMAT a9 HEAD UNIX_Pid
COLUMN consistent_gets FORMAT 999,999,999,999,999 HEAD Cons_Gets
COLUMN block_gets FORMAT 999,999,999,999,999 HEAD Block_Gets
COLUMN physical_reads FORMAT 999,999,999,999,999 HEAD Phys_Reads
COLUMN hit_ratio FORMAT 999.00 HEAD Hit_Ratio
SELECT
p.username unix_id
, s.username oracle_id
, s.osuser os_user
, s.sid sid
, s.serial# serial_id
, LPAD(p.spid,7) unix_pid
, sio.consistent_gets consistent_gets
, sio.block_gets block_gets
, sio.physical_reads physical_reads
, ROUND((consistent_gets+Block_gets-Physical_reads) /
(Consistent_gets+Block_gets)*100,2) hit_ratio
FROM
v$process p
, v$session s
, v$sess_io sio
WHERE
p.addr (+) = s.paddr
AND s.sid = sio.sid
AND (sio.consistent_gets + sio.block_gets) > 0
AND s.username is not null
ORDER BY hit_ratio
/
0 0
- Scripts:查询每个session命中率的脚本perf_hit_ratio_by_session.sql
- Scripts:查询每个数据文件等待时间的脚本perf_file_waits.sql
- Scripts:查询每个数据文件使用效率的脚本perf_file_io_efficiency.sql
- 查询命中率的sql
- Scripts:报告所有用户session信息的脚本sess_user_sessions.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:查询数据文件IO使用率的脚本 perf_file_io.sql
- Scripts:查询回滚段信息的脚本rollback_segments.sql
- sql语句脚本之查询PGA,sga等命中率
- 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
- Scripts:查询sga中各组件使用率的脚本perf_sga_usage.sql
- 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)解释
- hdu 2112 HDU Today (dijkstra)
- b2Body刚体
- 九度-1384