Physical and logical IO counts

I would like to be able to run some kind of a show plan (similar to SYBASE) in oracle which will show the following, on each query or stored procedure:

1. Physical IO's used on each statement.
2. Logical IO's used on each statement.
3. Indexes used on each statement.

This is very simple for me in sybase. I have an analy开发者_如何学Gozer tool that does that and I spend most of my time actually resolving the high IO items.

I was told to do the following:

set autotrace on statistics;
EXPLAIN PLAN FOR
SELECT * FROM SOMETABLE
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

This is fine but What is ROWS? Is it the Physical or logical IO? Also, what is the Plan hash value: 1611616177? Is that the total IO? I am relatively new to oracle and have lots of queries to investigate.

---

In SQL*Plus, you can do something like

SQL> set autotrace on;
SQL> select empno, ename from emp;

     EMPNO ENAME
---------- ----------
      7623 PAV
      7369 smith
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER

     EMPNO ENAME
---------- ----------
      7876 ADAMS
      7900 SM0
      7902 FORD
      7934 MILLER
      1234 BAR

16 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   160 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    16 |   160 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        997  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

Looking at the statistics at the bottom, consistent gets is a measure of logical I/O. Physical reads measures the number of physical reads that were necessary. This also shows the query plan which includes things like the optimizer's estimate of the number of rows that will be returned by each step of the query (i.e. the optimizer correctly estimates that a full scan of the EMP table will return 16 rows comprising 160 bytes of data).

---

If you want even more detail (and can get access to the trace files) then lookup TKPROF

see: http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/sqltrace.htm