Oracle Auto trace - Performance Tuning

Step1 :

clear the server result cache

exec dbms_result_cache.flush;

Step2 :
select /*+ result_cache */ c.id,c.name,sum(sale_amount),count(*) from customers c,orders o where

c.id=3956 group by c.id,c.name


select id,type,status,block_count,row_count*row_size_av as space_for_rows,space_overhead from
v$result_cache_objects where cache_id=:cache_id;


Run the Query Again


@autotrace.sql
=======================================
SQL> set autotrace traceonly

SQL> SELECT *
  2  FROM   scott.emp
  3  WHERE  sal > 1000;


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

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

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

   1 - filter("SAL">1000)


Statistics
----------------------------------------------------------
        487  recursive calls
          0  db block gets
         89  consistent gets
         13  physical reads
          0  redo size
       1305  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> select /*+ result_cache */ from scott.emp where sal > 1000;
select /*+ result_cache */ from scott.emp where sal > 1000
                           *
ERROR at line 1:
ORA-00936: missing expression


SQL> select * /*+ result_cache */ from scott.emp where sal > 1000;

12 rows selected.


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

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

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

   1 - filter("SAL">1000)


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