How to Enable Statistics IO in sql developer

Hi Guys,

This is a part of Explaination of Explain plan and Statistics IO.Meant as trouble shooting article.

Note -- The statistics shown in sql developer and Sql plus are different. Check below for sql plus statistics from autotrace. Those are the most important ones .








Its not possible to show consistent gets in sql developer using Autotrace. So you need to use Sql Plus . It gives you the Execution plan. The plan that was actually used by sql to run.  Below are commands

How to use SQL Plus

SET long 500 longchunksize 500

SET LINESIZE 1024

SET AUTOTRACE TRACEONLY;

c:\users\kapil\desktop>sql plus user/password@tnsname >outputfilename.txt@fileofsql.sql

Below is example of Statistics IO 

   Statistics
-----------------------------------------------------------
               3  user calls
               0  physical read total bytes
               0  physical write total bytes
               0  spare statistic 3
               0  commit cleanout failures: cannot pin
               0  TBS Extension: bytes extended
               0  total number of times SMON posted
               0  SMON posted for undo segment recovery
               0  SMON posted for dropping temp segment
               0  segment prealloc tasks

If you try below command in sql developer

 set statistics IO on --For sql plus only.  
 set Autotrace on --- For sql developer it will show at the end of the script the statistics and the explain plans

you will get below error 

The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

Some more options for Autotrace

set autotrace on: Shows the execution plan as well as statistics of the statement.
set autotrace on explain: Displays the execution plan only.
set autotrace on statistics: Displays the statistics only.
set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off: Disables all autotrace

Why this Error

Sqltrace is not installed by default with oracle installation.We need to login with user with admin right and then follow below steps.

Steps

1) Login - sys as sysdba  password leave blank just press enter
2) My path of file

Path of the file.
E:\app1\ADMIN\product\11.2.0\dbhome_1\sqlplus\admin

Just write @ before E and then ; at the end and press enter.See screenshot below

3) Close sql developer and open again

4) select * from dual

Run as script you will get this




Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

   Statistics
-----------------------------------------------------------
               3  user calls
               0  physical read total bytes
               0  physical write total bytes
               0  spare statistic 3
               0  commit cleanout failures: cannot pin
               0  TBS Extension: bytes extended
               0  total number of times SMON posted
               0  SMON posted for undo segment recovery
               0  SMON posted for dropping temp segment
               0  segment prealloc tasks