OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed
« How to create a replace script for a database job?

What is the difference between a database and an instance? »

Which segments have top LIO (Logical IO)/PIO (Physical I/O)

February 1st, 2006 By Karl Reitschuster

Do you know which segments in your database driven application have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and helps to target tuning efforts on these segments:

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;

HTH Karl

Works with >= 9.2

Filed under Administration, SQL, Tuning. Follow any responses to this entry through this RSS 2.0 feed.  

6 Responses to “Which segments have top LIO (Logical IO)/PIO (Physical I/O)”

  1. rjamya Says:
    February 1st, 2006 at 10:45 am

    What is wrong with

    SELECT *
FROM
(SELECT STATISTIC_NAME, ST.OWNER,ST.OBJ#,
  ST.OBJECT_TYPE,
  ST.OBJECT_NAME,ST.VALUE,
  DENSE_RANK() OVER(PARTITION BY STATISTIC_NAME ORDER BY ST.VALUE DESC) RNK
FROM   V$SEGMENT_STATISTICS ST)
WHERE  RNK

    rjamya

  2. OrcaXXX Says:
    February 1st, 2006 at 12:18 pm

    OH!
    You find a shortcut for this; will try your version soon. I must admit that i am not used to all kind of analytic functions. :-)
    Karl

  3. Eddie Awad Says:
    February 1st, 2006 at 12:36 pm

    I believe this is what rjamya had in mind:

    select *
from
  (select statistic_name,
     st.owner,
     st.obj#,
     st.object_type,
     st.object_name,
     st.value,
     dense_rank() over(partition by statistic_name
   order by st.value desc) rnk
   from v$segment_statistics st)
where rnk < = 10
 and statistic_name in ('logical reads', 'physical reads')

    Analytic functions are very powerful.

  4. rjamya Says:
    February 1st, 2006 at 1:04 pm

    Thanks Eddie,

    I don’t seem to be able to format very well. and I didn’t filter the statistic name. If you wish to filter, put it inside the inline view.

    Raj

  5. OrcaXXX Says:
    February 2nd, 2006 at 12:26 pm

    Thanks to Eddie and Ray;
    So that’s a good example too showing/comparing the power of analytic functions and the ‘old’ way :-)))
    Karl

  6. OrcaXXX Says:
    February 7th, 2006 at 1:18 am

    Hi, best results i got with this SQL; Value must be greater zero - otherwise dense_rank() puts always the same rank again on the retrieved rowset (had a lot of entries with same value 0)

    SELECT *
  FROM (SELECT Statistic_Name,
               St.Owner,
               St.Obj#,
               St.Object_Type,
               St.Object_Name,
               St.VALUE,
               Dense_Rank()
                 Over(PARTITION BY Statistic_Name ORDER BY St.VALUE DESC) Rnk
          FROM V$segment_Statistics St
         WHERE St.VALUE > 0
           AND St.Statistic_Name IN
               ('physical reads', 'physical writes', 'logical reads',
                'physical reads direct', 'physical writes direct'))
 WHERE Rnk < = 10

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question
« How to create a replace script for a database job?

What is the difference between a database and an instance? »