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:
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:
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:
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:
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?
We'll discuss those topics when we investigate shrinking in my next blog.
Thanks for reading!
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21113653/viewspace-594476/，如需转载，请注明出处，否则将追究法律责任。