Oracle如何查阅热表?

最新推荐文章于 2025-04-24 17:45:16 发布

原创最新推荐文章于 2025-04-24 17:45:16 发布 · 1.7k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle #statistics

收录于

该博客介绍了如何使用Oracle的V$segment_Statistics视图来查找物理读写和逻辑读最多的前10个表。通过查询分别针对'logical reads', 'physical reads', 'physical writes'三个统计指标的数据，可以确定数据库中的热点表，从而进行性能优化。" 12983211,1951973,Oracle数据库字符集更改指南,"['数据库管理', '字符编码', 'Oracle数据库']

TOP10 物理读写和逻辑读

SELECT Rownum AS Rank,
       Seg_Lio.*
FROM (SELECT St.Owner,
               St.Obj#,
               St.Object_Type,
               St.Object_Name,
               St.VALUE,
               'LIO' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'logical reads'
         ORDER BY St.VALUE DESC) Seg_Lio
WHERE Rownum <= 10
UNION ALL
SELECT Rownum AS Rank,
       Seq_Pio_r.*
FROM (SELECT St.Owner,
               St.Obj#,
               St.Object_Type,
               St.Object_Name,
               St.VALUE,
               'PIO Reads' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'physical reads'
         ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE Rownum <= 10
UNION ALL
SELECT Rownum AS Rank,
       Seq_Pio_w.*
FROM (SELECT St.Owner,
               St.Obj#,
               St.Object_Type,
               St.Object_Name,
               St.VALUE,
               'PIO Writes' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'physical writes'
         ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE Rownum <= 10;