Session 4: Pinpointing Object Performance Inefficiencies Using Space Analyst for Oracle

Go Up to Space Analyst for Oracle tutorial

Space Analyst can greatly accelerate the process of locating objects in your database that are degrading overall response times because of their current storage/space situation.

Finding Space-related Table Problems With Space Analyst for Oracle

To quickly determine if any tables in your database may be contributing to performance slowdowns, do the following:

1. In DBArtisan connect to the database you would like to check.
2. Open Space Analyst.
3. Select the Tables tab.

Space Analyst runs diagnostics against your database and graphically display counts of tables that are found to contain various types of space problems.

The graph at the top of the Tables tab shows the overall count of problem tables, but you can also view the details for each type of problem by using the Details grid at the bottom of the tab. For example, if Space Analyst identifies tables that have block inefficiencies, to find the actual tables and view details regarding the identified space problem, do the following in the Details grid:

1. In the Tablespace list, select All.
2. In the Table Owner list, select All.
3. In the Display list, select Tables with inefficient block usage.
4. Click Execute.

   Space Analyst displays the actual tables that suffer from block inefficiencies along with their demographic details. To take corrective action on any object, select one or more tables and click the right mouse button to bring up a menu of possible actions.

Finding Space-related Index Problems with Space Analyst for Oracle

To quickly determine if any indexes in your database may be contributing to performance slowdowns by following these steps:

1. In DBArtisan connect to the database you would like to check.
2. Open Space Analyst.
3. Select the Indexes tab.

The graph at the top of the Indexes tab shows the overall counts of problem indexes, but you can also view the details for each type of problem by using the Details grid at the bottom of the tab. For example, if Space Analyst identifies indexes that have next extent problems, then to find the actual indexes and view details regarding the space problem, do the following in the Details grid:

1. In the Tablespace list, select All.
2. In the Index Owner list, select All.
3. In the Display list, select Indexes with NEXT_EXTENT space deficits.
4. Click Execute.

   Space Analyst displays the actual indexes that suffer from next extent space problems along with their demographic details. If you wanted to take corrective action on any object, select one or more indexes and click the right mouse button to bring up a menu of possible actions.

   Note: Another critical statistic in determining index problems is deleted leaf rows. To display deleted leaf row statistics in the Details grid, do the following:

   • On the Command toolbar, select Get Deleted Leaf Row Statistics.

Proceed to Session 5: Correcting Space-Related Problems Using Space Analyst for Oracle