Release 1.5.0
A57689-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Performance Monitoring User's Guide Release 1.5.0 A57689-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, Analyze Objects, Deallocate Used Space, Reorganize Objects, Coalesce, 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 Tablespace Analyzer tool. This tool submits a job which either computes or estimates statistics for selected tables, indexes, and clusters.
Starts the Tablespace Deallocation 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 Reorganization 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.
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 and Extents Information page that displays.
The Segments and Extents Information property sheet includes the following elements:
Lists the information associated with the extent of the segment selected in the Segment Multi-column list. Extent information includes the following elements:
Number of blocks in the extent.
ID of the first block in the extent.
ID of the extent.
Full name and path of the datafile; the physical location of the extent.
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 multi-column list is blank.
Click on a segment listed in this list to graphically highlight how the extents in the selected tablespace (or datafile) are allocated for this segment. Click on the desired column header to sort the list accordingly. The multi-column list consists of the following columns.
Name of the segment name. Segments are sorted alphabetically by segment name.
Name of the segment owner. Segments are first sorted alphabetically by segment owner, then alphabetically by segment name.
Segment type.Options include: table, index, cluster, table partition, index partition, rollback, cache, LOB (large object) segment, and LOB index. Segments are sorted alphabetically by segment type, then alphabetically by segment name.
Number of extents within the segment. Segments ar sorted first by the number of extents in the segment, then alphabetically by segment name (default).
Number of blocks in the extent. Segments are sorted according to the number of blocks in the extent (lowest to highest).
Graphically displays space allocation 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.
Clicking on the property sheet button Extent Legend displays a quick reference pop-up defining significance of the different colors used in the Allocation Graphic.
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.
To view the segment related to an extent, click on an entry in the Segment multi-column list. 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.
If you select more than one segment from the Segments multi-column list, the Multiple Object Selection Statistics page appears. 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.
To reorganize objects in a tablespace, select the desired segment(s) from the Segments multi-column list on the Segments and Extents Information page.
Because Oracle Tablespace Manager uses the Oracle Enterprise Manager job subsystem to perform table segment defragmentation, the Oracle Enterprise Manager Console must be running in order to complete the defragmentation process.
Select the Reorganize Objects option from the Tools menu. This option 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 Reorganizer wizard 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"
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 move on to the next page of the wizard. You can then customize the following parameters to suit your needs:
This page allows you to set specific options on the objects you already selected in the previous step.
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.
Percentage 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.
Number 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.
Initial 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.
Click on this button to assign the default storage parameters to the selected object(s).
Use the Other Options page to specify the general options.
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.
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.
Choose 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.
Choose 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.
Select 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 Reorganization wizard 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 Reorganization wizard.
To deallocate unused space within the tablespace, select the desired segment(s) in the Segments multi-column list located on the Segments and Extents Information property sheet.
Start the Tablespace Deallocation wizard by selecting the Deallocate Unused Space option in the Tools menu. The wizard guides you through the space deallocation job definition process, job scheduling, and finally provides a job summary so that you can check the job settings before submission to the Oracle Enterprise Manager job system.Defining Job Name and Scheduling Choices.
You specify the default value for the amount of unused space to be reserved in each segment. Space can be specified in units of megabytes or kilobytes.
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 move on to the next page of the wizard. You can then customize the following parameters to suit your needs:
After specifying default value for the reserved amount of unused space, you can also set objects to reclaim unused space, you can also specify the reserved amount of unused space per segment. This setting overrides the default setting specified earlier.
After setting the deallocation job parameters, you can now schedule the job for execution by the Oracle Enterprise Manager job system.
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 Tablespace Deallocation wizard without defragmenting any of the selected segment(s).
Click the Finish button to accept these options and start the defragmentation job. The Segments and Extents Information 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 Tablespace Deallocation wizard without defragmenting the selected segment(s).
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.
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.
After selecting objects to analyze, you can also perform additional analyses on specific objects, beyond the default options set earlier. The same analysis options available for the default options definition are also available at the object level. See "Defining Default Options" on page 19.
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.
