oracle index usage check

http://docs.oracle.com/cd/B10500_01/server.920/a96533/autotrac.htm

Autotrace Setting	Result
SET AUTOTRACE OFF	No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN	The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS	The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON	The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY	Similar to SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Tracing Statements for Performance Statistics and Query Execution Path

If the SQL buffer contains the following statement:

SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
  FROM EMPLOYEES E, JOBS J
    WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

The statement can be automatically traced when it is run with the following:

SET AUTOTRACE ON
/

The output is similar to the following:

LAST_NAME                     SALARY JOB_TITLE
------------------------- ---------- -----------------------------------
King                           24000 President
Kochhar                        17000 Administration Vice President
De Haan                        17000 Administration Vice President
Russell                        14000 Sales Manager
Partners                       13500 Sales Manager
Hartstein                      13000 Marketing Manager

6 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS'
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        848  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

Tracing Statements Without Displaying Query Data

To trace the same statement without displaying the query data, enter the following:
SET AUTOTRACE TRACEONLY



This option is useful when you are tuning a large query, but do not want to see the query output.

Tracing Statements Using a Database Link

To trace a statement using a database link, enter:
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM EMPLOYEES@MY_LINK;

Execution Plan
-----------------------------------------------------------
0      SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'EMPLOYEES'  MY_LINK.DB_DOMAIN




Monitoring Disk Reads and Buffer Gets

To monitor disk reads and buffer gets, execute the following command:
SET AUTOTRACE ON TRACEONLY STATISTICS



Example 11-4 shows typical results.

Example 11-4 Monitoring Disk Reads and Buffer Gets
Statistics
----------------------------------------------------------
        70 recursive calls
         0 db block gets
       591 consistent gets
       404 physical reads
         0 redo size
       315 bytes sent via SQL*Net to client
       850 bytes received via SQL*Net from client
         3 SQL*Net roundtrips to/from client
         3 sorts (memory)
         0 sorts (disk)
         0 rows processed



If consistent gets or physical reads is high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.





set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics



Setting autotrace allows to display some statistics and/or anquery execution plan for DML statements.

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

SQL) conn id/pw
SQL) set autotrace on explain;
SQL) set linesize 300
SQL) set pagesize 1000

SQL) select count(*) FROM table

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |     1 |   135 |   390   (2)|       |       |
|   1 |  SORT AGGREGATE                          |                        |     1 |   135 |            |       |       |
|   2 |   NESTED LOOPS OUTER                     |                        |    58 |  7830 |   390   (2)|       |       |
|   3 |    NESTED LOOPS OUTER                    |                        |    58 |  6438 |   352   (2)|       |       |
|   4 |     NESTED LOOPS OUTER                   |                        |    58 |  5974 |   293   (2)|       |       |
|*  5 |      HASH JOIN OUTER                     |                        |    58 |  5510 |   235   (2)|       |       |
|*  6 |       HASH JOIN OUTER                    |                        |    58 |  5220 |   218   (1)|       |       |
|   7 |        TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE                  |    58 |  3654 |   213   (1)|    74 |    74 |
|*  8 |         INDEX RANGE SCAN                 | TABLE_IX_04            |    58 |       |     5   (0)|       |       |

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

5 - access("MAIN"."WHERE"="CODE"."CODE"(+))

SQL> SELECT A.uniqueness, b.* FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.index_name = b.index_name AND a.table_name=upper('TABLE');

UNIQUENES INDEX_OWNER                    INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
NONUNIQUE PID                            TABLE_IX_01               PID                            TABLE
COLUMN
              1             7           7 ASC

NONUNIQUE PID                            TABLE_IX_01               PID                            TABLE
COLUMN
2           200         200 ASC

select * from user_indexes a, user_ind_columns b where a.index_name = b.index_name and a.table_name = upper('TABLE');
SELECT A.uniqueness, b.* FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.index_name = b.index_name AND a.table_name=upper('TABLE');