Tom's playground

the Oracle Tuning Advisor

Oracle has now included an Oracle tuning advisor. Its purpose is to advise on a SQL statement. For me, it provides an answer on whether to add an index or not. I was taught that one should always start with tables that have no indices. Only when it is demonstrated that indices are used, one should create an index. The idea is that maintenance of indices cost time, which must be offset against the gains from an index. If no gains are expected, it is useless to create such index. Of course, we could use the explain plan for that. This reveals whether an index is used or not.
First, such a SQL statement should be properly included in a structure where it can be analysed. Such a structure can be created with (with SQL Plus:):

The existence of this structure can be shown if we look into the enterprise manager under the heading Performance > SQL Tuning Advisor > Manual.
There, we see the entry that we just created.
Subsequently, we may execute this entry with: