Sometimes things go slow in the database even if the original setup was done with a lot of care. The data traffic the usage of the application can sometimes impose the need of extra indices in addition to the original indices added by the enterprise application client that uses the Oracle database.
The following procedure describes the steps that must be performed to add new relevant indices:
STEP 1 Generate AWR
Generate an AWR report for the period when the slowdown is observed in the executed queries. Usually you will obtain a nice html format file global_awr_report with the title “WORKLOAD REPOSITORY REPORT (RAC) ”
STEP 2 Find the long running queries
Go to the section “SQL Statistics”.
Look at all the subsections :
In all this sections my top 3 queries were the same.
STEP 3 Create the indices
In any of the tables from the above SQL sections click on the link under the SQL id column for the first query. You will be directed to the definition of that query.
Select the query and paste it into “Oracle SQL Developer”, do right click into the editor window and then Format (“Ctrl – F7”) to get a formated query, then replace any variables and execute the query.
If all is good then do an Explain Plan (F10) to get the statistics of this query. If there is any full table scan entry then there is where you need an index.
Create the new index with the SQL query:
create index IDX_MYINDEX_ on MyTable(tran_id);