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
- 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.
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
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
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.
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
- 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.
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
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
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.
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.
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.
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.
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
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?