Release 1.4.0
A53699_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Performance Monitoring User's Guide Release 1.4.0 A53699_01 |
|
This chapter covers how you use Oracle Tablespace Manager, including the following topics:
You start Oracle Tablespace Manager as you would start any typical application integrated with Oracle Enterprise Manager. See "Starting Performance Monitoring Applications" for a description of how to do so.
After you start Oracle Tablespace Manager, the main window is displayed. Figure 10-1 shows the Oracle Tablespace Manager main window.
The Oracle Tablespace Manager main window includes the following components:
The title bar of the Oracle Tablespace Manager main window displays the name of the application and the name of the database instance to which a connection has been made.
The Oracle Tablespace Manager toolbar includes icons that enable you to perform the following menu commands: Change Database, Refresh, Filter Folder, and Help.
The Oracle Tablespace Manager status bar displays information about the current operation on the left.
As Figure 10-1 shows, the Oracle Tablespace Manager main window includes a tree list in the left pane of the window. The Oracle Tablespace Manager tree list operates like the Oracle Enterprise Manager tree list, except that it only provides information about the tablespaces of the database instance.
When you start Oracle Tablespace Manager and the main window appears, the top container in the tree list shows the database instance being monitored. The Tablespaces container containing the tablespaces of that instance is also displayed. From the Tablespaces container you can drill down as follows:
For more information on how a tree list is populated, see "Navigator" in the Oracle Enterprise Manager Administrator's Guide.
The Oracle Tablespace Manager menu bar includes the following menus:
The File menu items allow you to change the database connection and exit the Oracle Tablespace Manager application.
The File menu includes the following menu items:
Allows you to connect to another database instance.
Displays the Enable Roles dialog box, from which you can select roles to enable. For more information, see "Overview of Database Tools" in the Oracle Enterprise Manager Administrator's Guide.
Exits the Oracle Tablespace Manager application.
The View menu items allow you to change what is displayed in the window. For more information, see "Overview of Database Tools" in the Oracle Enterprise Manager Administrator's Guide.
The View menu includes the following menu items:
Refreshes the data displayed by Oracle Tablespace Manager.
Allows you to specify filters for displaying tree list objects.
Expands the selected container in the Oracle Tablespace Manager tree list by one level of detail.
Hides the level(s) of detail below the selected container in the Oracle Tablespace Manager tree list.
Hides all levels of detail below the database container in the Oracle Tablespace Manager tree list.
Shows/hides the toolbar.
Shows/hides the status bar.
The Tools menu includes the following menu items:
Starts the Defragmentation Wizard tool. This tool submits a job which defragments schema objects using storage parameter defaults and basic analysis options. You can specify when the job will run.
Starts the Tablespace Organizer tool. This tool submits a job which defragments schema objects and lets you set storage parameters, analysis options, and scheduling options. You can specify when the job will run.
Starts the Tablespace Analyzer tool. This tool submits a job which either computes or estimates statistics for selected tables, indexes, and clusters.
Joins adjacent free extents in the database.
The Help menu includes the following menu items:
Displays an overview of the Oracle Performance Monitoring applications.
Displays an alphabetical list of Help topics.
Displays information about using the Help system.
Displays version information for this release of Oracle Tablespace Manager.
To obtain an overview of the tablespaces in the database instance, single-click on the Tablespaces container in the left pane. The right pane of the main window displays a multi-column list displaying storage information for each tablespace of the database instance. This list includes the following information:
Tablespace name.
Number of datafiles in the tablespace.
Total number of blocks in the tablespace.
Number of free blocks in the tablespace.
Percentage of total number of blocks in the tablespace that are free blocks. A horizontal bar in the background of this field graphically represents the percentage of free blocks in the tablespace.
Number of adjacent free extents in the tablespace.
To obtain an overview of the datafiles in a given tablespace, from the Oracle Tablespace Manager tree list, single-click on the Datafiles container of the tablespace of interest to you. The right pane of the main window displays a multi-column list including information for each datafile of this particular tablespace. This table includes a subset of the fields described in the previous section, as follows:
Datafile name, including its directory path.
Total number of blocks in the datafile.
Number of free blocks in the datafile.
Percentage of total number of blocks in the datafile that are free blocks. A horizontal bar in the background of this field graphically represents the percentage of free blocks in the datafile.
To monitor the segments of a given tablespace, in the tree list, single-click on the tablespace of interest. Figure 10-2 shows an example of the Segments page that displays. You can also view the Space Usage and Extent Information pages.
The Segments page includes the following elements:
Lists the segments of the tablespace (or datafile) selected in the tree list. When the Segments page first displays, the first segment in the list is automatically selected. If no segments have been allocated for the selected tablespace, the left pane is blank.
Click on a segment listed in this pane to graphically highlight in the right pane how the extents in the selected tablespace (or datafile) are allocated for this segment.
Displays the space allocation graphic for the selected tablespace (or datafile), showing how space for its segments and extents have been allocated.
White areas denote free space. Colored areas denote used space, as follows:
Black vertical lines separate extents in the space allocation graphic. If you see large black sections, it is because there are so many extents in the segment that the colors representing the extents themselves are not visible. To minimize this problem, maximize the size of the Oracle Tablespace Manager main window.
To view the segment related to an extent, click on an extent in the Right pane. All of the extents comprising the segment associated with the extent you just clicked are then highlighted in yellow, and the associated segment is then automatically selected in the Left pane. In this case, a dashed line outlines the extent you selected in the Right pane.
Click on a sorting criterion in this combination box to organize the list of segments in the left pane. Options include:
Segments are sorted first by the number of extents in the segment then alphabetically by segment name (default).
Segments are sorted alphabetically by segment name.
Segments are sorted alphabetically by segment owner then by segment name.
Segments are sorted alphabetically by segment type then by segment name.
Segment InformationIf you select only one segment in the segment list, you see the following information:
Number of extents in the selected segment.
Total number of blocks and kilobytes in the selected segment.
Owner of the selected segment.
Type of the selected segment. Options include: table, index, cluster, table partition, index partition, rollback, cache, LOB (large object) segment, and LOB index.
1 line =Number of blocks in a single line of the space allocation graphic. Because Oracle Tablespace Manager attempts to display all of the extents of a selected tablespace (or datafile), this value can vary if you resize the Oracle Tablespace Manager main window.
Allocated extents for all segments not selected in the tree list are displayed in cyan (light blue).
Allocated extents of the selected segment in the tree list are displayed in yellow.
Extents available in the tablespace that have not been assigned to any segment are displayed in white. These extents are available for allocation next time a segment needs to be extended.
First blocks of a file are used as a header and are grey.
Defragment ButtonStarts the Defragmentation Wizard. This button is disabled if the type of segment is anything other than Table or if the segment owner is SYS. For more information about using the wizard, see "Defragmenting a Segment - Using Default Parameters".
Displays Help for the Segments page.
The Space Usage page provides segment information that results from the Oracle SQL ANALYZE command. There can only be one segment highlighted in the tree list. The elements listed on the Space Usage page depend upon the type of segment selected. The elements listed for Table and Cluster segments are described in "Space Usage Elements for Table and Cluster Segments". The elements listed for Index segments are described in "Space Usage Elements for Index Segments".
Type of segment.
Name of the segment.
Schema object that owns the segment.
Date that the segment was last analyzed.
Number of rows in the segment.
The number of rows used to estimate the statistics. For example, for a very large table you might take the first 100 rows and extrapolate the statistics from those rows.
The number of allocated data blocks that have never been used.
The average amount of available free space in each data block, in bytes.
The number of chained rows.
The average length of a row, including the row's overhead, in bytes.
The average amount of available freelist block space.
The number of freelist blocks available.
Type of segment.
Name of the segment.
Schema object that owns the segment.
Date that the segment was last analyzed.
Number of rows in the segment.
The number of rows used to estimate the statistics. For example, for a very large table you might take the first 100 rows and extrapolate the statistics from those rows.
The number of distinct index values.
The number of leaf blocks.
The average number of leaf blocks per index value.
The average number of data blocks per index value (for an index on a table).
An indication of how well ordered the rows are about the indexed values.
Starts the Defragmentation Wizard. For more information about using the wizard, see "Defragmenting a Segment - Using Default Parameters".
Displays Help for the Space Usage page.
The Extent Information page displays the information associated with the extents of the selected segment. The Extent Information page includes the following elements:
Type of segment highlighted in tree list.
Name of the segment highlighted in tree list.
Name of table to which the segment belongs.
Number of blocks in the selected extent.
ID of the first block in the selected extent.
ID of the selected extent.
Name of file where the extent is located; the physical location of the extent.
This page is only available when you select more than one segment in the segment list. It shows the owner of the selected segments if they belong to the same owner or <Multiple Owners> if they belong to more than one owner. A table similar to the following is displayed that contains information about the segments.
.
Starts the Defragmentation Wizard. For more information about using the wizard, see "Defragmenting a Segment - Using Default Parameters".
Displays Help for the Selection Tally page.
There are two methods to defragment a schema object. You can use the Defragmentation Wizard which uses default defragmentation options or you can use the Tablespace Organizer tool and customize the options you want to use. Each method is described in the following sections.
For both methods it is important that Oracle Enterprise Manager be running. This is because Oracle Tablespace Manager uses the Oracle Enterprise Manager job subsystem to perform table segment defragmentation.
To defragment a segment using default parameters, select the desired segment(s) on the Segments page. You can select more than one segment to defragment by clicking and dragging the mouse over multiple segments.
To start the Defragmentation Wizard you can either click the Defragment button on the Segments page or you can select the Defragmentation Wizard option in the Tools menu. The Defragment Wizard is disabled if the type of any of the selected segments is anything other than Table or if any of the segment owners is SYS.
You can accept the default or use any character for the jobname except apostrophe and ampersand. For ease of use, keep the jobname short and simple.
Select this option to start the job now. When you click the Finish button, the Summary page displays, as described in the following section.
Select this option to run the job at a later time. When you click the Next button, additional fields display where you define scheduling options. Refer to "Defining Scheduling Options" for details.
The Defragment Wizard Summary page summarizes all the information you entered.
The summarized properties are:
If, while reviewing the Summary page, you find options you want to change, click the Back button to make the necessary changes.
Click the Cancel button at any time to close the Defragment Wizard without defragmenting any of the selected segment(s).
Click the Finish button to accept these options and start the defragmentation job. The Segments page of the Oracle Tablespace Manager main window displays.
Check the Console job subsystem to make sure the defragmentation job has completed execution.
To see the results of the defragmentation, choose Refresh from the View menu, reselect the tablespace (or datafile) of interest, display the Segments page, and then select the defragmented segment from the segments list.
Click the Cancel button at any time to close the Defragmentation Wizard without defragmenting the selected segment(s).
To defragment a segment using custom parameters, select the desired segment(s) on the Segments page. You can select more than one segment to defragment by clicking and dragging the mouse over multiple segments.
Select the Tablespace Organizer tool from the Tools menu. This tool is disabled if the type of any of the selected segments is anything other than Table or if any of the segment owners is SYS.
The Tablespace Organizer tool prompts you as shown in the following sections.
Use any character for the jobname except apostrophe and ampersand. For ease of use, keep the jobname short and simple.
You can choose either to reorganize data by defragmenting tables and clusters or to deallocate unused space at the end of segments. If you choose to deallocate unused space, you can specify a default size to keep, in either kilobytes or megabytes.
For more information about reorganizing data, see "Selecting Objects to Reorganize"
For more information about deallocating space, see "Selecting Objects to Deallocate"
From the tree list on the Object Selection page, click on the checkbox for each object that you want to reorganize. The selection status of each object is denoted as follows:
To specify parameters, select an object and click the right mouse button. You can then customize the following parameters to suit your needs:
Name of the tablespace containing the segment you want to defragment.
Click this button (default) if you want to defragment the selected segment by compressing it into a single extent.
Size of the first extent of the table segment. Click either the K (Kilobytes) or M (Megabytes) button to specify the size of the measurement.
Size of the next extent to be allocated to the segment. The default value is the size of five data blocks. The smallest permissible value is the size of one data block. Click either the K (kilobytes) or M (megabytes) button to specify the size of the measurement.
Percent by which each extent grows (after the second extent) compared to the previous extent.
Total number of extents allocated when a segment is created. The default value is 1. You can enter a value of 1 or greater.
Total number of extents, including the first, that Oracle can allocate for the segment. You can enter a value of 1 or greater. The default value varies, depending on the database block size.
Enabling this option disables the user-specified Maximum field and instead allows the maximum number of extents the system permits. For example, on many systems, the number is approximately 2.1 billion (2,147,483,645). The exact value varies depending on the platform. However, it is not recommended having a segment which uses unlimited extents. Rogue transactions containing inserts, updates, or deletes that continue for a long time will continue to create new extents until a disk is full.
Space UsagePercentage of space in each of the data blocks of the segment that is reserved for future updates to the segment. You can enter a value from 0 to 99.
Minimum percentage of used space that an Oracle database maintains for each data block of the table segment. A block becomes a candidate for row insertions when its used space falls below the % Used value. You can enter a value from 1 to 99. The default value is 40.
Free ListsNumber of free lists for each of the free list groups for the table, cluster, or index. You can enter a value of 1 or more. The default value is 0.
Number of groups of free lists for a table, cluster, or index. You can enter a value of 1 or more. The default value is 1.
Number of TransactionsInitial number of transaction entries allocated within each data block allocated to the table segment. You can enter a value from 1 to 255. The default is 1 for tables. For clusters and indexes, the minimum and default value is 2.
Maximum number of concurrent transactions that can update a data block allocated to the segment. You can enter a value from 1 to 255.
Load DefaultClick on this button to assign the default storage parameters to the selected object(s).
From the tree list on the Object Selection page, click on the checkbox for each object for which you want to deallocate unused space. The selection status of each object is denoted as follows:
To display a dialog box in which you can specify the amount of space to keep at the end of each segment, select an object and click the right mouse button.
Use the Organizer Options page to specify the general options, export/import options, and statistics on import.
Enable this option to save export and temporary files after the defragmentation job has executed. You can keep the constraint and object files as an audit trail for auditing purposes or reuse the export file to copy the data or save as a backup.
You can enter the directory path on the server in which the export file and temporary files (object and constraints files) are to be written. This directory path cannot contain any environment variables.
This section lets you specify the following options:
Size of the buffer. Click either the K (Kilobytes) or M (Megabytes) button to specify the size of the measurement.
Move the contents of the buffer cache directly to the output dump file. This method is quicker because no SQL processing is required on the data.
Based on the size of the buffer, commit after each array of rows has been inserted into the database.
Generate StatisticsEnabling this option executes the Oracle SQL ANALYZE command. It is limited to objects that already had statistics before they were exported.
Gives the exact statistics for all the segments. This can take a bit of time.
Approximates the statistics for all the segments either by percentage or number of rows.
The Scheduling Options page allows you to schedule the execution of the reorganization.
Select the frequency with which you want the job executed. The choices are:
Schedules the job as soon as you click the Submit button on the Summary page. The job executes only one time.
Schedules the job to occur once at the date and time you choose.
Allows you to schedule a specific time interval between job executions. The interval can be a combination of hours and minutes, or number of days. Select the value you want to change in the Time field and click on the scroll buttons. You can also type in a new value.
Allows you to schedule the job on one or multiple days (Sunday, Monday, etc.) of the week. Click on the days of the week in the Date field to select the days on which you want the job to occur.
Allows you to schedule the job on one or multiple days (1-31) of the month. Click on the dates of the month in the Date field to select the dates on which you want the job to occur.
Start ExecutionChoose the first date and time that you want the job executed. This is the starting time for any job scheduled on an interval.
Select the month, day, or year in the Date field and click on the scroll buttons to change the value. You can also type in new values.
Select the hour, minute, or AM/PM in the Time field and click on the scroll buttons to change the value. You can also type in new values.
End ExecutionChoose the last date and time that you want the job executed. This option does not apply if you chose the Immediately or Once execution options.
Select the month, day, or year in the Date field and click on the scroll buttons to change the value. You can also type in new values.
Select the hour, minute, or AM/PM in the Time field and click on the scroll buttons to change the value. You can also type in new values.
Time ZoneSelect the time zone from the pull-down list. The choices are:
The agent schedules the job execution at each destination based on the actual system time of each agent. Jobs are not necessarily run simultaneously.
The agent schedules the job execution simultaneously on all destinations based on the system time of the console.
The agent schedules the job execution simultaneously on all destinations based on Greenwich mean time.
The Tablespace Organizer Summary page summarizes all the information you entered while using the Tablespace Organizer tool.
The summarized properties are:
If, while reviewing the Summary page, you find options you want to change, click the Back button to make the necessary changes.
Click the Cancel button at any time to close the Tablespace Organizer without reorganizing the selected segment(s).
Once you are satisfied with the information, click the Finish button to start the reorganization process.
Click the Help button to display Help for the Tablespace Organizer tool.
The Tablespace Analyzer tool submits a job that analyzes various statistics and validates structure for selected tables, clusters, indexes, and partitions.
On this page you define the jobname and default analysis options.
Use any character for the jobname except apostrophe and ampersand. For ease of use, keep the jobname short and simple.
The default options you can choose are as follows:
Computes exact statistics about the analyzed object and stores them in the data dictionary.
Deletes any statistics about the analyzed object that are currently stored in the data dictionary.
Estimates statistics about the analyzed object and stores them in the data dictionary. You can specify a number of rows or a percentage of rows to use in the calculation.
Verifies the integrity of the structure of an index, table, or cluster. Enable the Cascade option to validate the structure of all indexes on the cluster's tables, including the cluster index.
Lists rows that have moved from one data block to another (migrated) or rows that are contained in more than one block (chained). Migrated and chained rows can cause excessive I/O, so you may want to identify them in order to eliminate them.
From the tree list on the Object Selection page, select the objects you want to analyze. The selection status of each object is denoted as follows:
To choose the various analysis options, select an object and click the right mouse button. The choices you make will override the default values.
The Scheduling Options page allows you to schedule the execution of the analysis. This page is the same as the one used for the Tablespace Organizer. Refer to "Defining Scheduling Options" for details.
The Tablespace Analyzer Summary page summarizes all the information you entered while using the Tablespace Analyzer tool.
The summarized properties are:
If, while reviewing the Summary page, you find options you want to change, click the Back button to make the necessary changes.
Click the Cancel button at any time to close the Tablespace Analyzer without analyzing the selected segment(s).
Once you are satisfied with the information, click the Finish button to start the analysis process.
Click the Help button to display Help for the Tablespace Analyzer tool.
If you administer an active database, you may want to join adjacent free blocks in the database on a frequent basis. (If you administer an active Release 7.3 database, you will rarely, if ever, need to use this Oracle Tablespace Manager feature, because adjacent free blocks are automatically joined on a regular basis.)
To use Oracle Tablespace Manager to join adjacent free blocks in the database, take the following steps:
Each group of adjacent free blocks in the space allocation graphic of the Segments page should now appear as a single free block.
