CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy

SET TIMING ON;           -- TIMING    を有効化しています。
SET AUTOTRACE TRACEONLY; -- AUTOTRACE を有効化しています。

-- サンプルSQL
SELECT /*+ MONITOR */
       B.ITEM_NAME
     , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY
     , COUNT(*)
  FROM ORDER_TBL A
     , ITEM_TBL B
 WHERE A.ITEM_NO  = B.ITEM_NO
--   AND B.ITEM_NO BETWEEN 1 AND 100
   AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801'
 GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD')
 ORDER BY 1;

Connected.

261 rows selected.

Elapsed: 00:00:05.22 ★SET TIMING ON によって出力されたSQLの経過時間です。

Execution Plan
----------------------------------------------------------
Plan hash value: 44130803

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     8 |   208 |    30   (7)| 00:00:01 |
|   1 |  SORT GROUP BY      |           |     8 |   208 |    30   (7)| 00:00:01 |
|*  2 |   HASH JOIN         |           |    26 |   676 |    29   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| ORDER_TBL |    26 |   286 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| ITEM_TBL  |   300 |  4500 |    26   (4)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ITEM_NO"="B"."ITEM_NO")
   4 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."REGIST_DATE"),'YYYYMMDD')='2
              0120801')

Statistics ★SET AUTOTRACE TRACEONLY によって出力されたSQLの実効統計
----------------------------------------------------------
         97  recursive calls
          0  db block gets
       9012  consistent gets
      15579  physical reads
          0  redo size
       7401  bytes sent via SQL*Net to client
        795  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
        261  rows processed

：
Elapsed: 00:00:05.22 ←コレを確認する。
：

：
Statistics
----------------------------------------------------------
         97  recursive calls
          0  db block gets
       9012  consistent gets ★SQLの読込ブロック数 ←これはよく見る
      15579  physical reads　★SQLのDisk IOブロック数 ←これはよく見る
          0  redo size
       7401  bytes sent via SQL*Net to client
        795  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
        261  rows processed
：