How to find index is fragmented?

First analyze index
SQL>analyze index INDEX_NAME validate structure;

Then query INDEX_STATS view
1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.
2. If height is 4 then index should be rebuild.
3. If lf_rows is lower than lf_blks then index should be rebuild.

SQL> column status format a10
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||’%’ “status” from index_stats;
status
———-
21.83%

How to remove index fragmentation?
There are two way to remove fragmentation.
1. index coalesce
2. index rebuild

What is difference between coalesce and rebuild please go through below link for more details
http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548

SQL> alter index INDIA coalesce;
SQL> alter index INDIA rebuild;
SQL> alter index INDIA rebuild online;

Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.

SQL> analyze index idx_obj_id validate structure;
Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||’%’ “status” from index_stats;
status
——–
   0%

Note: Index rebuild when index is fragmented or it is needed, otherwise index rebuilding is myth for improve performance.

You can also enable Index Monitoring ‘ON’ to check if queries/statements are using index objects or not.

SQL> ALTER INDEX india MONITORING USAGE;
SQL> select index_name, table_name, monitoring, used, start_monitoring, end_monitoring from v$object_usage where index_name = ‘INDIA’ order by index_name;

INDEX_NAME                   TABLE_NAME               MON     USE        START_MONITORING       END_MONITORING
—————————— —————————— — — ——————- ———————————-  ———————————-
INDIA                       SYS_EXPORT_SCHEMA_01    YES       YES           06/01/2013                          23:31:34

To disable the monitoring:

SQL> ALTER INDEX my_index_i NOMONITORING USAGE;

Below provided is the sample ADDM (Automatic Database Diagnostic Monitor) from one of my Test Database. Before that i would like to explain what is this ADDM and it’s advantages:

To view in more efficient way, click the Link: My ADDM (Sample) Report.txt – Notepad

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

ADDM Report for Task ‘TASK_964’
——————————-

Analysis Period
—————
AWR snapshot range from 99 to 105.
Time period starts at 03-APR-13 07.08.18 PM
Time period ends at 04-APR-13 07.20.18 PM

Analysis Target
—————
Database ‘REDANT’ with DB ID 629811920.
Database version 11.2.0.1.0.
ADDM performed an analysis of instance orcl, numbered 1 and hosted at
prashant.

Activity During the Analysis Period
———————————–
Total database time was 452 seconds.
The average number of active sessions was .01.

Summary of Findings
——————-
Description                          Active Sessions      Recommendations
Percent of Activity
———————————–  ——————-  —————
1  I/O Throughput                       .01 | 100            3
2  Undersized instance memory           0 | 7.75             1
3  Hard Parse Due to Invalidations      0 | 5.3              1
4  “Other” Wait Class                   0 | 4.95             0
5  Commits and Rollbacks                0 | 4.14             1
6  PL/SQL Compilation                   0 | 4.05             1
7  Checkpoints Due to MTTR              0 | 3.44             1
8  Checkpoints Due to DROP or TRUNCATE  0 | 3                0
9  Hard Parse Due to Sharing Criteria   0 | 2.14             1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Findings and Recommendations
—————————-

Finding 1: I/O Throughput
Impact is .01 active sessions, 100% of total activity.
——————————————————
The throughput of the I/O subsystem was significantly lower than expected.

Recommendation 1: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
—————————————————————–
Action
Consider increasing the throughput of the I/O subsystem. Oracle’s
recommended solution is to stripe all data files using the SAME
methodology. You might also need to increase the number of disks for
better performance.
Rationale
During the analysis period, the average data files’ I/O throughput was
14 K per second for reads and 3.2 K per second for writes. The average
response time for single block reads was 14 milliseconds.

Recommendation 2: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
—————————————————————–
Action
Consider slowing down RMAN or Data Pump activity, or scheduling these
jobs when user activity is lower.
Rationale
The I/O throughput on data and temp files was divided as follows: 0% by
RMAN, 29% by Data Pump, 0% by Recovery and 70% by all other activity.

Recommendation 3: Host Configuration
Estimated benefit is .01 active sessions, 100% of total activity.
—————————————————————–
Action
The performance of some data and temp files was significantly worse than
others. If striping all files using the SAME methodology is not
possible, consider striping these file over multiple disks.
Rationale
For file /u01/app/oracle/oradata/orcl/sysaux01.dbf, the average response
time for single block reads was 132 milliseconds, and the total excess
I/O wait was 479 seconds.
Related Object
Database file
“/u01/app/oracle/oradata/orcl/sysaux01.dbf”

Symptoms That Led to the Finding:
———————————
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.

Finding 2: Undersized instance memory
Impact is 0 active sessions, 7.75% of total activity.
—————————————————–
The Oracle instance memory (SGA and PGA) was inadequately sized, causing
additional I/O and CPU usage.
The value of parameter “memory_target” was “500 M” during the analysis period.

Recommendation 1: Database Configuration
Estimated benefit is 0 active sessions, 7.75% of total activity.
—————————————————————-
Action
Increase memory allocated to the instance by setting the parameter
“memory_target” to 875 M.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.

Finding 3: Hard Parse Due to In-validations
Impact is 0 active sessions, 5.3% of total activity.
—————————————————-
Cursors were getting invalidated due to DDL operations. This resulted in
additional hard parses which were consuming significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 5.3% of total activity.
—————————————————————
Action
Investigate appropriateness of DDL operations.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.

Finding 4: “Other” Wait Class
Impact is 0 active sessions, 4.95% of total activity.
—————————————————–
Wait class “Other” was consuming significant database time.
Database latches in the “Other” wait class were not consuming significant
database time.

No recommendations are available.

Finding 5: Commits and Rollbacks
Impact is 0 active sessions, 4.14% of total activity.
—————————————————–
Waits on event “log file sync” while performing COMMIT and ROLLBACK operations
were consuming significant database time.

Recommendation 1: Host Configuration
Estimated benefit is 0 active sessions, 4.14% of total activity.
—————————————————————-
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 6 K and the
average time per write was 3 milliseconds.
Rationale
The total I/O throughput on redo log files was 1.3 K per second for
reads and 1.5 K per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 52% by Log Writer, 0% by Archiver, 0% by Streams AQ and 47% by
all other activity.

Symptoms That Led to the Finding:
———————————
Wait class “Commit” was consuming significant database time.
Impact is 0 active sessions, 4.14% of total activity.

Finding 6: PL/SQL Compilation
Impact is 0 active sessions, 4.05% of total activity.
—————————————————–
PL/SQL compilation consumed significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 4.05% of total activity.
—————————————————————-
Action
Investigate the appropriateness of PL/SQL compilation. PL/SQL
compilation can be caused by DDL on dependent objects.

Finding 7: Checkpoints Due to MTTR
Impact is 0 active sessions, 3.44% of total activity.
—————————————————–
Buffer cache writes due to setting of the obsolete parameters
“fast_start_io_target”, “log_checkpoint_interval” and “log_checkpoint_timeout”
were consuming significant database time.

Recommendation 1: Database Configuration
Estimated benefit is 0 active sessions, 3.44% of total activity.
—————————————————————-
Action
Oracle’s recommended solution is to control MTTR setting using the
“fast_start_mttr_target” parameter instead of the
“fast_start_io_target”, “log_checkpoint_interval” and
“log_checkpoint_timeout” parameters.

Symptoms That Led to the Finding:
———————————
The throughput of the I/O subsystem was significantly lower than
expected.
Impact is .01 active sessions, 100% of total activity.
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.

Finding 8: Checkpoints Due to DROP or TRUNCATE
Impact is 0 active sessions, 3% of total activity.
————————————————–
Buffer cache writes due to DROP and TRUNCATE operations had a significant
impact on the throughput of the I/O subsystem.

No recommendations are available.

Symptoms That Led to the Finding:
———————————
The throughput of the I/O subsystem was significantly lower than
expected.
Impact is .01 active sessions, 100% of total activity.
Wait class “User I/O” was consuming significant database time.
Impact is 0 active sessions, 19.25% of total activity.
Wait class “Application” was consuming significant database time.
Impact is 0 active sessions, 3.8% of total activity.

Finding 9: Hard Parse Due to Sharing Criteria
Impact is 0 active sessions, 2.14% of total activity.
—————————————————–
SQL statements with the same text were not shared because of cursor
environment mismatch. This resulted in additional hard parses which were
consuming significant database time.
Common causes of environment mismatch are session NLS settings, SQL trace
settings and optimizer parameters.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.14% of total activity.
—————————————————————-
Action
Look for top reason for cursor environment mismatch in
V$SQL_SHARED_CURSOR.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
———————-

Miscellaneous Information
————————-
Wait class “Configuration” was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class “Network” was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database’s maintenance windows were active during 23% of the analysis
period.

End of Report
Report written to addmrpt_1_99_105.txt
SQL> !
[oracle@prashant ~]$ ls
addmrpt_1_99_105.txt  Desktop  on.lst
[oracle@prashant ~]$ vi addmrpt_1_99_105.txt
Wait class “Application” was consuming significant database time.
Impact is 0 active sessions, 3.8% of total activity.

Finding 9: Hard Parse Due to Sharing Criteria
Impact is 0 active sessions, 2.14% of total activity.
—————————————————–
SQL statements with the same text were not shared because of cursor
environment mismatch. This resulted in additional hard parses which were
consuming significant database time.
Common causes of environment mismatch are session NLS settings, SQL trace
settings and optimizer parameters.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.14% of total activity.
—————————————————————-
Action
Look for top reason for cursor environment mismatch in
V$SQL_SHARED_CURSOR.

Symptoms That Led to the Finding:
———————————
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 20.38% of total activity.
Contention for latches related to the shared pool was consuming
significant database time.
Impact is 0 active sessions, 2.69% of total activity.
Wait class “Concurrency” was consuming significant database time.
Impact is 0 active sessions, 2.94% of total activity.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
———————-

Miscellaneous Information
————————-
Wait class “Configuration” was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class “Network” was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database’s maintenance windows were active during 23% of the analysis
period.