Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, S.

Asked: May 04, 2016 - 4:35 pm UTC

Last updated: May 07, 2016 - 8:01 am UTC

Version: 11g2

Viewed 10K+ times! This question is

You Asked

Suppose we run a query like select a.*,b.* from emp a,dept b where a.dno=b.dno and b.dept_name not in ('C-Level','MIS');

how to find more accurate total disk IO like physical disk IO and logical disk IO ?

I have not able to access a AWR Report because of using 11 SE edition.
And I have some confuse like physical read is same as Disk or Physical IO?

and Chris said...

Here's two ways:

In SQL*Plus, run:

set autotrace on

Then run your query. When it finishes you'll get stats like:

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        342  bytes sent via SQL*Net to client
        471  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


The consistent gets and physical reads tell you how much logical IO and physical IO the query used.

Or you can trace your query, then parse it using tkprof:

https://oracle-base.com/articles/8i/tkprof-and-oracle-trace

Again, this will show you how many blocks Oracle accessed from disk.

Note physical IO will change between executions. Typically the second run will have less physical IO because the data is in the buffer cache.

Physical IO/read = disk access

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 05, 2016 - 4:17 am UTC

How to know Logical and physical IO on Store procedure run by user?
Connor McDonald
May 05, 2016 - 6:22 am UTC

For a procedure, there might be lots of SQL calls etc within it, so you would need to find the session level stats before the call, and compare to after, for example:

SQL> select
  2     s.name, st.value
  3  from v$statname s, v$mystat st
  4  where st.STATISTIC# = s.STATISTIC#
  5  and s.name in ('session logical reads','physical reads');

NAME                                                    VALUE
-------------------------------------------------- ----------
session logical reads                                   26152
physical reads                                             29

SQL> exec my_proc

SQL> select
  2     s.name, st.value
  3  from v$statname s, v$mystat st
  4  where st.STATISTIC# = s.STATISTIC#
  5  and s.name in ('session logical reads','physical reads');

NAME                                                    VALUE
-------------------------------------------------- ----------
session logical reads                                   29123
physical reads                                            689


Note - there's lots of *different* ways of doing a logical and/or physical read nowadays, you might need more than just those two stats, for example

SQL> @statname physical

STATISTIC# NAME
---------- ----------------------------------------------------------------
        50 physical read total IO requests
        51 physical read total multi block requests
        52 physical read requests optimized
        53 physical read total bytes optimized
        54 physical read partial requests
        55 physical read total bytes
        56 physical write requests optimized
        57 physical write total bytes optimized
        61 physical write total IO requests
        62 physical write total multi block requests
        63 physical write total bytes
        64 cell physical IO interconnect bytes
       106 physical read snap IO requests base
       107 physical read snap IO requests copy
       108 physical read snap IO requests no data
       109 physical read snap bytes base
       110 physical read snap bytes copy
       111 physical write snap IO requests new allocations
       141 physical reads
       142 physical reads cache
       143 physical read flash cache hits
       144 physical reads direct
       145 physical read IO requests
       146 physical read bytes
       152 physical writes
       153 physical writes direct
       154 physical writes from cache
       155 physical write IO requests
       157 physical reads direct temporary tablespace
       158 physical writes direct temporary tablespace
       159 physical write bytes
       171 physical writes non checkpoint
       217 physical reads cache prefetch
       218 physical reads prefetch warmup
       222 physical reads retry corrupt
       223 physical reads direct (lob)
       224 physical writes direct (lob)
       228 physical reads for flashback new
       233 physical reads cache for securefile flashback block new
       234 physical reads direct for securefile flashback block new
       430 cell physical IO bytes saved during optimized file creation
       431 cell physical IO bytes saved during optimized RMAN file restore
       432 cell physical IO bytes eligible for predicate offload
       433 cell physical IO bytes saved by storage index
       434 cell physical IO bytes sent directly to DB node to balance CPU
       449 cell physical IO interconnect bytes returned by smart scan
       459 cell physical IO bytes saved by columnar cache
       460 cell physical write bytes saved by smart file initialization
       716 cell simulated physical IO bytes eligible for predicate offload
       717 cell simulated physical IO bytes returned by predicate offload
      1150 cell physical write IO bytes eligible for offload
      1151 cell physical write IO host network bytes written during offloa



A reader, May 07, 2016 - 3:40 am UTC

when multiple sessions are there and so how to find only my plsql or sql statement IO.
Connor McDonald
May 07, 2016 - 8:01 am UTC

The query I sent you was against v$mystat, so its only the statistics for *your* session.

If you want stats for multiple sessions, use v$sesstat.

Hope this helps.
Connor

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.