10G Segment Advisor

The 10G segment advisor identifies segments that have become fragmented as a result of update and delete operations. Oracle describes these objects as being sparsely populated. Not only do sparsely populated objects waste space but they can also contribute to SQL performance problems.

This article will show you how to use the 10G segment advisor to identify sparsely populated segments.

A Walk Down Memory Lane:

I've been burned a couple of times in my career by sparsely populated segments. You would think that I would have learned my lesson after the second (or third) time. Hey, what can I say, sometimes I'm a slow learner.

Here's a quick rundown of the sequence of events that occurs:

I get notified that a SQL statement is running too long.
The developer says that although the query's access path is a full table scan, there aren't that many rows in the table to justify the long execution times.
I forget to ask the developer if a large amount of data was recently removed from the table by SQL delete statements.
I begin looking at all the common culprits (poor operating system or I/O performance, external influences affecting the job, etc.)
I waste a few hours tracking down potential problems that aren't the cause of the statement's poor performance.

If the developer had responded yes to my question on any recent data deletions (had I asked the question), I could have possibly found and resolved the performance problem with a few minutes. There are tables that, by their nature, have data volumes that expand and contract on a regular basis. The common phrase we use is "the data volumes are very fluid." That means that application programs add and remove data from the table during normal day-to-day processing.

Oracle uses an indicator called a high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But as row deletes and updates shrink the amount of data in the object, the high water mark is not altered to reflect the segment's new characteristics. The high water mark not being adjusted as the data shrinks has the tendency to create a somewhat confusing performance problem. A problem that irritates me to no end when it catches me by surprise.

During a full table scan, Oracle scans all blocks up to the table's high water mark. This happens regardless of whether those blocks contain data or not. Business needs often require that all rows of a table be scanned. A common method of improving the performance of table scans is to purge the data on a regular basis. The less data to be scanned, the faster the scan becomes. The purge is often accomplished by using SQL delete statements to remove the unwanted data. The problem is that even though the delete statement may remove a lot of data from the segment, the high water mark will not be moved and the table scans will continue to perform poorly.

If all of the rows are being deleted, the administrator should use the SQL truncate statement to remove the unwanted rows. Truncate adjusts the high water mark to the first block in the segment. If a partial purge is being performed, the administrator must reclaim the unused space using the methods described in the next blog.

We have learned that removing unused space helps to improve the performance of full table scans when they are eventually shrunk. It should also be pretty clear that freeing unused space allows us to use it elsewhere. But it is also important to note that reclaiming unused space also compacts the segment, which leads to improvements in buffer cache performance.

Segment Advisor Demo

Like all advisors, we access the segment advisor by clicking on the segment advisor link that is displayed on 10G Grid Control's Advisor Central page. Clicking on this link notifies 10G Grid Control to display the Segment Advisor Home page. The Segment Advisor Home page allows administrators to analyze space at a couple of different levels:

Object level analysis - The segment advisor analyzes an entire object (table, all partitions of a table).
Segment level analysis - The segment advisor analyzes a single segment such as a non-partitioned table or index.
Tablespace level analysis - The segment advisor analyzes segments in the specified tablespace.

In addition, the home page also allows us to choose the depth of analysis to perform. Pay close attention to the text displayed below the comprehensive option radio button. It is almost the exact same warning that 10G Grid Control displays next to the SQL Tuning Advisor's comprehensive option. This is because the comprehensive analysis does incur a noticeable overhead on the database. Here are my recommendations:

Only run the comprehensive option during periods of low database activity.
If you must run the comprehensive option during peak periods, only run it on one or two objects at a time. The smaller the object is, the better. How big is too big? It's hard for me to provide you with a blanket recommendation because there are so many factors involved. How fast is the box you are running it on? How heavy is the current workload on the system? On some of our systems here, I can safely run the advisor during daylight hours on segments that are 500 to 600 MEGs in size. On other systems, analyzing a segment that large would bring the environment to its knees.
You can run the limited option during periods of peak activity but don't run it on dozens and dozens (and dozens) of objects in one execution. Break them up into groups.

I selected the Schema Objects radio button to run the segment advisor on a set of specific schema objects. 10G Enterprise Manager displays the Segment Advisor Schema Objects Page. I add schema objects by clicking on the Add button, which activates the Schema Objects Add Page.

The Schema Objects Add Page allows me to make selections based on the object's type, its schema owner, the object's name, the tablespace it resides in or its size. The tool also allows you to specify combinations of selection criteria to narrow the search.

I clicked on the little flashlight next to the box titled "Schema" to activate the Search and Select: Schema search page. I selected the radio button next to the value "Foot" to tell 10G Grid Control to display objects owned by that schema. 10G Grid Control returns me to the Schema Objects Add page and populates the results section of the panel with the objects owned by the schema owner "Foot".

Since this is a test system and I'm not worried about consuming too many resources, I selected all of the objects and clicked OK to continue the process. 10G Grid Control displays the Segment Advisor: Options page which allows me to select the depth of the analysis to perform. Since we are using my test system, I chose to run a comprehensive analysis on all selected objects.

10G Grid Control displays the Segment Advisor: Schedule page. The panel provides me with several different options to run the advisor during off-peak maintenance windows (predefined windows will be discussed in an upcoming blog). This should give you another hint that the segment advisor is resource intensive, especially when it is asked to analyze large objects.

Choosing the Standard option tells Enterprise Manager to refresh the Segment Advisor: Schedule page and display some additional boxes that allow me to run the advisor immediately or schedule its execution at some specified time in the future. In addition, I am also given the option of scheduling the job to run on a repeating basis. Check out the text that has the red line underneath it. It's your last warning before you run the advisor.

Clicking OK displays the Segment Advisor: Review page. This panel displays a listing of the objects to be analyzed as well as the depth of analysis to be performed. I clicked on the Submit button to schedule the segment advisor job for execution. 10G Grid Control returns me to the Advisor Central Home page.

We learned previously that the Advisor Central Home page displays the output of all advisor job executions. Note that the status of my segment advisor job is "Running". I opened up a Telnet window and used NMON to measure the load that the segment advisor was placing on my test platform. The advisor did have a noticeable impact on system resources during its execution. When the system resources returned to normal levels, I took that as an indication that the segment advisor was finished processing. I returned to the Advisor Central home page and my guess was indeed correct. The segment advisor had completed its execution and the results were ready to be reviewed.

I clicked on the segment advisor's job name SHRINK3440621 to view the job's output. 10G Grid Control displays the Segment Advisor Task:3440621 output page. The page displays job execution information and contains a row for each object that was analyzed. The row begins with metadata about the object (owner, type, tablespace it resides in, etc.).

The output page also displays information that will help us determine if shrinking the segment is justified. The far right of each row displays:

The amount of space allocated to the object.
The amount of space that the object actually consumes.
The amount of space that will be reclaimed if a shrink operation is performed.
A recommendation stating if the shrink operation should be performed and if so, what steps should be taken to perform the shrink effectively.

Take a look at the recommendations; each one advises that row movement be enabled to facilitate the shrink operation. When I click on the Show SQL button, Oracle displays the SQL that will be executed if the recommendation is implemented. The SQL execution file contains an "ENABLE ROW MOVEMENT" statement for each of the tables analyzed.

Now we know how to identify candidates for shrink operations. But why do we need to enable row movement? And what exactly does a shrink operation do?