Examining the relationship between Precise for Oracle database objects

This section includes the following topics:

• About the Objects tab
• How the Objects tab is structured
• About Objects tab entities
• How the Objects tab can help you identify performance problems

About the Objects tab

It is important to understand the relationship between database objects and statements to make sound and well-founded tuning decisions.

The Objects tab is a comprehensive browser that lets you understand the relationships and associations between database schema objects. It also provides further understanding into the relationships between data dictionary components and the statements that access them. This information lets you identify the statements that will be affected by changes to a database object, and the schema relationship impacts that are associated with an object change. The Objects tab also lets you observe storage utilization, over time and at varying levels, such as double-space, table, index, and Oracle file.

While the Activity tab focuses on SQL tuning, the Object tab focuses on database object tuning. In many cases, you may want to tune a database object rather than a specific query or program. This is especially true if you did not personally write the application in question, as for example, in ERP and CRM applications, and cannot therefore change the text of any statement.

Object tuning is a powerful tool. Taking into consideration important aspects regarding objects can ensure maximum application performance. Precise for Oracle lets you examine your index structure by displaying the usage of each index. Storage layout can be optimized by analyzing the object storage configuration and metric information.

Because Precise for Oracle tracks changes in the database objects, the effect of schema changes on performance can be easily observed.

How the Objects tab is structured

The Objects tab consists of two different views: Explore Objects and Tune Objects.

The Explore Objects view displays information on a selected entity and its associated entities. The selected parent entity is always reflected in the Tab heading, which serves as a point of orientation. The highest-level entity you can view information for in the Objects tab is Instance. You can choose another instance from the Instance selector.

The Tune Objects view displays performance and usage information per table or index, based on new and existing data sources.

About the Explore Objects view

When you open the Objects tab from the Activity tab (and the selected entity is Statement, Table, Index, Oracle File or Storage Device) or the SQL tab (Table, Index or Column) you launch to the tab in context, and information on the entity you selected is displayed. In all other circumstances the settings from your last visit to the Objects tab are retained. This is similar to clicking the History button and returning to a previous tab.

Although a large number of entities can be displayed in the Objects tab, they nearly all share the same set of Main Area views and Associations.

About the Main area in the Explore Objects view

The Main area, in the Objects tab, shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, you can focus exclusively on Instance Parameters, or Instance Parameters Changes.

The following table explains the standard views that are available for most entities in the Objects tab.

Table 1 Standard views in the Objects tab

About the Association area in the Explore Objects view

The Association area, in the Objects tab, provides corresponding information on the entities that are related to or can be associated with the selected entity (the entity displayed in the Tab heading). You can view information on one type of associated entity at a time, such as Statements or Tablespaces, by selecting an entity from the Association Controls list. The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information. Clicking a tab changes the columns of the table displayed for the associated entities.

From the Association area, you can also drill down to another entity by clicking on a table row. A drill down affects the entire tab. When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the newly selected entity, and the Association area shows data on the entities that are associated with the selected entity.

For example, when you want to view information on a specific Tablespace, from the Association controls, click Tablespaces. The Association area changes to display tablespace-related information. Note that the Tab heading and the Main area remain unchanged. Click the row of the tablespace you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays an overview of the tablespace you drilled down to, and the Association area shows a list of Oracle files that belong to the tablespace and their properties.

The following table explains the standard tabs that are available for most entities in the Association area.

Table 2 Standard tabs in the Association area of the Objects tab

See “About Objects tab entities” on page 95 and “About Precise for Oracle tabs” on page 21.

About the Tune Objects view

You can launch directly to the Tune Objects view of the Objects tab from the following tabs:

Table 3 Direct launch

The Main area of the Tune Objects view displays information on the table you drilled down to and its indexes. You can also directly open a table by using the Open option.

Selecting a table to tune

You can select a table to tune by using the Open option. To open a table for tuning

1. On the Tune Objects tab in the Objects tab, click Open.
2. In the Open Table dialog box, in the Instance drop-down list, select the instance related to the table you want to tune.
3. In the Owner drop-down list, select the owner of the table you want to tune.
4. In the Table drop-down list, select the name of the table you want to tune.
5. Click Ok.

About the Main area in the Tune Objects view

The segment table, located in the upper left corner of the Main area of the Objects tab, displays information on the selected table and its corresponding indexes. Additional tabs (Details, Partitions, and Columns), located in the lower left corner, provide additional information on the selected table. You can view this information by clicking the + sign to expand the view.

The following table provides an explanation of the information displayed in the Segments table.

Table 4 Segments table

The Details area displays relevant data dictionary information for the selected index, table or segments. The Columns area shows information on the columns of the selected tables or index.

The following table describes the information displayed in the Dictionary and Columns areas.

Table 5 Information displayed in the Dictionary and Columns area

About the Details area in the Tune Objects view

The Details area, in the Objects tab, is divided into the following tabs:

• Highlights
• Overtime
• Statements
• Joins
• Partitions

About the Highlights tab

The Highlights tab shows the statement access type distribution for the segment, indicating how the segment was accessed. The Findings table displays information on problematic objects and provides links to the appropriate tabs where you can continue your analysis or tune the object. It displays the percentage of time the object spends in the Oracle database, with respect to the amount of time spent by all segments in the Oracle database.

When you click on a row in the Findings table, additional details on the selected finding are displayed in the Details area below the table.

If you want to continue your examination of a particular finding, click on the launch icon to launch to the appropriate tab with the selected finding in context.

About the Overtime tab

The Overtime tab shows the segment's I/O wait distribution, by read/writes, over time and the segment changes that took place during the selected time period.

The following table describes the information displayed in the Overtime tab.

Table 6 Overtime graphs

About the Statements tab

The Statements tab shows the statement access type distribution for the segment, indicating how the segment was accessed. Each table is populated according to the row you select in the table above it. The Statements table will show information on the accessing statements using the access type you selected in the Access Types table. The Columns table provides information on how columns are being used in the statement.

The following table describes the information displayed in the Statement tab.

Table 7 Statement table

About the Joins tab

The Joins tab displays information on the tables (and their corresponding statements), that participate in joins with the selected table.

The following table describes the information displayed in the Joins tab.

Table 8 Joins Tables

About the Partitions tab

The Partitions tab displays partition details for the selected segment or object.

The following table describes the information displayed in the Partitions tab.

Table 9 Partitions table

If you want to continue your examination of a particular partition, click on the launch icon to launch to the Activity tab with the selected partition in context.

About Objects tab entities

The Objects tab displays information on many different entities. This section provides an overview of all these entities, their meaning, and their views.

In the Objects tab, you can examine the following entities:

• Instance entity
• Oracle File entity
• Tablespace entity
• Table entity
• Index and unused index entities
• Column entity
• Statement entity
• PL/SQL entity
• Object entity
• View entity
• Attribute entity
• Cluster entity
• Constraint entity
• Index Partition and Index Sub-Partition entities
• Index-type entity
• LOB entity
• Materialized View entity
• Method entity
• Rollback entity
• Sequence entity
• Synonym entity
• Table Partition and Table Sub-Partition entities
• Trigger entity
• User entity
• User-defined type entity

Some entities are related to storage devices and are only available if you install the EMC Symmetrix and Clariion or HP SureStore versions of Precise for Storage. The Objects tab lets you see details for all the storage arrays connected to the server where the currently selected instance is running. You can then drill down inside an array and see details of its components. The performance counters are available for all devices and not just for those devices used by the selected instance. This gives you a view of the overall performance of the arrays and lets you identify less busy devices that you can move your Oracle files to.

The following entities are only available if you have installed Precise for Storage:

• EMC Units
• Disk director entity
• Port entity
• Hyper entity
• HP Storage Device entity

For more information on the supported platforms, see the Precise Installation Guide.

About the Instance entity

The Instance entity, in the Objects tab, shows object-related information on the instance level. The following views are available:

• Overview
• Init parameters
• Database options
• Database option changes
• Parameter changes
• Redo log changes
• Rollback segment changes
• Tablespace and datafile changes
• Schema changes

It is possible to associate from an instance to Active Objects and to the Users and Tablespaces defined in the instance, and to the Oracle Files that make up the instance. You can display additional entities by using the More... option.

About the overview of instance-related objects

The Overview displays general instance information and an overtime graph that illustrates the time (Sum.) that the instance spent In Oracle during the selected time period.

The following table describes the general information displayed in this view.

Table 10 Instance Overview

Additionally Logical Reads and Space Allocation graphs are shown.

About init parameters for an instance

The Init Parameters view displays all Oracle initialization or INIT.ORA parameters.

The following table describes the table shown in this view.

Table 11 Init Parameters view

About database options

The Database Options view displays the available database options. The following table describes the table shown in this view.

Table 12 Database Options view

About examining changes to database options

The Database Options Changes view displays the changes made to database options during the selected time period. The following table describes the table shown in this view.

Table 13 Database Options Changes view

About parameter changes

The Parameter Changes view displays the changes to the database initialization parameters during the selected time period.

The following table describes the table shown in this view.

Table 14 Parameter Changes view

About changes to the redo logs

The Redo Log Changes view displays the changes to the redo logs during the selected time period. The following table describes the table shown in this view.

Table 15 Redo Log Changes view

About changes to rollback segments

The Rollback Segment Changes view displays the changes to rollback segments during the selected time period. The following table describes the table shown in this view.

Table 16 Rollback Segment Changes view

About changes to tablespaces and datafiles

The Tablespace and Datafile Changes view displays the changes to tablespaces and datafiles during the selected time period.

The following table describes the table shown in this view.

Table 17 Tablespace and Datafile Changes view

About schema changes

The Schema Changes view displays the changes made to schema objects, such as tables and indexes, during the selected time period, but expressed as DDL. For example: DROP INDEX or ALTER TABLE

The following table describes the table shown in this view.

Table 18 Schema Changes view

About the Oracle File entity

The Oracle File entity is comprised of information about datafiles and temporary files. This information lets you find objects that reside on the Oracle file and the statements that access these objects. You can view the I/O statistics of the Oracle file to understand the I/O read and write time and the number of physical I/Os. The Oracle File entity displays general details, I/O activity, and storage information for the selected file.

The following views are available:

• Overview
• RAC Database
• Dictionary
• Storage

About the overview of an Oracle file

The Overview, in the Objects tab, displays general Oracle file information and several overtime graphs. The following table describes how the general information is structured.

Table 19 Structure of general information for an Oracle file

The following table describes the overtime graphs for an Oracle file.

Table 20 Overtime graphs for an Oracle file

About the RAC Database of an Oracle file

The RAC Database view, in the Objects tab, compares the file activity of the entire RAC database to the file activity of the selected RAC instance. This view shows aggregated wait states, and load balancing and indicates whether only the specific instance is effected by an event, or whether the entire database is effected by the event.

The following table describes the information displayed on Oracle files in the RAC Database view.

Table 21 Information displayed on Oracle files in the RAC Database view

About the dictionary of an Oracle file

The Dictionary view, in the Objects tab, displays the general properties of the Oracle File. The following table describes how the information in the Dictionary view is structured.

Table 22 Structure of information in the Dictionary view

About storage information related to an Oracle file

The Storage view, in the Objects tab, displays storage information related to the Oracle File. The following table describes how the information in the Storage view is structured.

Table 23 Structure of information in the Storage view

Associating an Oracle file with other entities

You can associate an Oracle file in the Main area of the Objects tab with the following entities in the Association area:

• Tables and Indexes that are stored in the file.
• Statements that are run against those tables and indexes in the file.
• Tablespaces that the file belongs to.
• Storage devices that the file is mounted on. This option is only available if Precise for Storage is installed. You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• Dictionary tab. Displays general properties of the Oracle File.
• Storage tab. Display storage information related to the Oracle File.
• Physical I/O tab. Displays a table of total I/O activity against the Oracle File, over the selected time period. On this tab, the table contains the following columns:
  • File. The name of the Oracle file.
  • Physical I/O Operations. A bar graph that shows the number of physical reads and writes.
  • Physical Reads. The total number of physical reads executed during the selected time period. Physical Writes    The total number of physical writes executed during the selected time period.
  • Avg. Read REQ time (Msec.). The average number of physical reads per minute, executed during the selected time period.
  • Avg. Write REQ time (Msec.). The average number of physical writes per minute executed during the selected time period.
  • File ID. ID of the file.

About the Tablespace entity

The Tablespace entity displays general details, I/O activity and storage information for the selected Tablespace. The following views are available:

• Overview
• RAC Database
• Dictionary
• Storage

About the overview of a tablespace

The Overview, in the Objects tab, displays general tablespace information and a stacked overtime graph that illustrates the physical reads and physical writes to the tablespace.

The following table describes how the general information is structured.

Table 24 Structure of general information for a tablespace

Additionally a Physical I/O Operations graph is shown.

About the RAC Database of a tablespace

The RAC Database view, in the Objects tab, compares the tablespace activity of the entire RAC database to the tablespace activity of the selected RAC instance. This view shows aggregated wait states, and load balancing and indicates whether only the specific instance is effected by an event, or whether the entire database is effected by the event.

The following table describes the information displayed on tablespaces in the RAC Database view.

Table 25 Information displayed in the RAC Database view for tablespaces

About the dictionary of a tablespace

The Dictionary view, in the Objects tab, displays the general properties of the tablespace. The following table describes how the information in the Dictionary view is structured.

Table 26 Structure of information in the Dictionary view

About storage information related to a tablespace

The Storage view, in the Objects tab, displays storage information related to the tablespace, including used and free space, and extent information.

The following table describes how the information in the Storage view is structured.

Table 27 Structure of information in the Storage view

Associating a tablespace with other entities

You can associate a tablespace in the Main area of the Objects tab with the following entities in the Association area:

• Oracle Files that are used for storage
• Statements that have accessed the Tablespace
• Tables and Indexes in the tablespace

You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• Dictionary tab. Displays general properties of the Tablespace.
• Storage tab. Displays storage information related to the tablespace, including used and free space, and extent information.
• Physical I/O tab. Displays a table of total I/O activity against the tablespace, over the selected time period. On this tab, the table contains the following columns:
  • File. The name of the file.
  • Physical I/O Operations. A bar graph showing the number of physical reads and writes.
  • Avg. Read REQ Time (Msec.).    Average read request time in miliseconds.
  • Avg. Write REQ Time (Msec.). Average write request time in miliseconds.

About the Table entity

The Table entity, in the Objects tab, displays general details, storage information, statistical information, and change-related information for the selected table.

The following views are available:

• Overview
• RAC Database
• Dictionary
• Storage
• Statistics
• Changes

About the overview of a table

The Overview, in the Objects tab, displays general table information and several overtime graphs. The following table describes how the general information is structured.

Table 28 Structure of general information for a table

The following table describes the overtime graphs for a table.

Table 29 Overtime graphs for an table

The changes, blocks and rows data is dependent on the Collect Schema Changes process. Precise for Oracle reports statistical changes when this process is run.

About the RAC Database of a Table

The RAC Database view, in the Objects tab, compares the table activity of the entire RAC database to the table activity of the selected RAC instance. This view shows aggregated wait states, and load balancing and indicates whether only the specific instance is effected by an event, or whether the entire database is effected by the event.

The following table describes the information displayed on tables in the RAC Database view.

Table 30 Information displayed on tables in the RAC Database view

About the dictionary of a Table

The Dictionary view, in the Objects tab, displays the general properties of the table. The following table describes how the information in the Dictionary view is structured.

Table 31 Structure of information in the Dictionary view

About storage information related to a table

The Storage view, in the Objects tab, displays storage information related to the table, including used and free space, and extent information.

The following table describes how the information in the Storage view is structured.

Table 32 Structure of information in the Storage view

About the statistics of a table

The Statistics view, in the Objects tab, displays statistics on the table. This information is generated by the Oracle statistics collection mechanism.

The following table describes how the information in the Statistics view is structured.

Table 33 Structure of information in the Statistics view

About table-related changes

The Changes view, in the Objects tab, displays details of the changes that were made to the table and the indexes defined on the table.

The following table describes how the information in the Changes view is structured.

Table 34 Structure of information in the Changes view

Associating a table with other entities

You can associate a table in the Main area of the Objects tab with the following entities in the Association area:

• Columns that make up the table
• Indexes that are related to the table
• Statements that access the table
• Table partitions
• Table subpartitions

You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• Dictionary tab. Displays data dictionary information for the selected table.
• Storage tab. Displays storage information for the selected table.
• Statistics tab. Displays the statistics for the table, generated by the Oracle statistics collection mechanism

About the Index and Unused Index entities

The Index entity, in the Objects tab, displays general details, storage information, Oracle statistics information, and change-related information for the selected index or unused index.

An index is unused if it has not been referenced in an explain plan. Until some statements have been explained using Precise for Oracle, all indexes are, by definition, unused. For this reason, it is important that all functions within an application are exercised before you search for unused indexes. Statements are automatically explained by Precise for Oracle FocalPoint once a day (by default), so there may be a lag before a statement is explained following its execution.

The following views are available:

• Overview
• RAC Database
• Dictionary
• Storage
• Statistics
• Changes

About the overview of an index

The Overview, in the Objects tab, displays general index information and several overtime graphs. The following table describes how the general information is structured.

Table 35 Structure of general information for an index

The following table describes the overtime graphs for a table.

Table 36 Overtime graphs for a table

The changes, distinct keys, and clustering factor data is dependent on the Collect Schema Changes process. Precise for Oracle reports statistical changes when this process is run.

About the RAC Database of an index

The RAC Database view, in the Objects tab, compares the index activity of the entire RAC database to the index activity of the selected RAC instance. This view shows aggregated wait states, and load balancing and indicates whether only the specific instance is effected by an event, or whether the entire database is effected by the event.

The following table describes the information displayed on indexes in the RAC Database view.

Table 37 Information displayed on indexes in the RAC Database view

About the dictionary of an index

The Dictionary view, in the Objects tab, displays the general properties of the index. The following table describes how the information in the Dictionary view is structured.

Table 38 Structure of information in the Dictionary view

About storage information related to an index

The Storage view, in the Objects tab, displays storage information related to the index. The following table describes how the information in the Storage view is structured.

Table 39 Structure of information in the Storage view

About the statistics of an index

The Statistics view, in the Objects tab, displays statistics that are related to the index and generated by the Oracle statistics collection mechanism.

The following table describes how the information in the Statistics view is structured.

Table 40 Structure of information in the Statistics view

About changes to indexes

The Changes view, in the Objects tab, displays details of the changes made to the index. The following table describes how the information in the Storage view is structured.

Table 41 Structure of the information in the Changes view

Associating an index with other entities

You can associate an index in the Main area of the Objects tab with the following entities in the Association area:

• Columns in the index
• Tables that are indexed
• Statements that make use of the index (only if the index is used. By definition, no statements are associated with an unused index.)
• Index partitions and subpartitions

You can display additional entities by using the More... option.

The information in the Association area is displayed on the following tabs:

• Columns tab. Displays information about the columns that make up the index.
  • Name. Displays the name of the index.
  • Owner. Displays the Oracle user that owns the index.
  • Columns. Displays the number of columns that make up the index.
  • Column List. Displays the names of the columns that make up the index.
• Dictionary tab. Displays data dictionary information for the selected index, similar to that described in the Main area.
• Storage tab. Displays storage information for the selected index, similar to that described in the Main area.
• Statistics tab. Ddisplays statistics related to the index, as generated by the Oracle statistics collection mechanism.
  • Index. Displays the name of the index.
  • Owner. Displays the Oracle user that owns the index.
  • Distinct keys. Displays the number of distinct values in the index.
  • Clustering Factor. Displays the clustering factor of the index. The clustering factor is an important factor in determining how efficiently an index range scan will retrieve data from the table. It measures the degree to which the data in the index and its table are in the same order or, put another way, the probability that the next row to be fetched from the table is in the same block as the current row. It can vary between the number of blocks in the table (the best case because they are in the same order) and the number of rows in the table (the worst case because they are completely out of sync). The clustering factor tends to become worse over time as data is inserted and deleted. Note that the clustering factor makes no difference for a unique index lookup.
  • Leaf Blocks. Displays the number of blocks at the lowest index level.
  • Leaf Blocks per Key. Displays the average number of leaf blocks in which each distinct key value appears. There must be at least one leaf block for a unique key.
  • Data Blocks per Key. Displays the average number of data blocks in which each distinct key value appears. There must be at least one leaf block for a unique key.
  • B-Levels. Displays the number of levels above the leaf blocks in the index (indicates the depth of the index).
  • Last Analyzed on. Displays the date when the index was last analyzed.

About the Column entity

The Column entity, in the Objects tab, displays general details and Oracle analysis statistics for the selected column. The following views are available:

• Overview
• Statistics
• Buckets

About the overview of a column

The Overview, in the Objects tab, displays general column information and three overtime graphs that show the variation in the number of distinct values, the number of nulls, and the density. Density is the proportion of selective values divided by the number of distinct selective values. For uniformly distributed data, this equates to one over the number of distinct values.

The data is dependent on the Collect Schema Changes process. Precise for Oracle reports statistics changes when this process is run.

The following table describes how the general information is structured.

Table 42 Structure of general information for a column

Additionally the Distinct Values, Null Values, and Density graphs are shown.

About the statistics of a column

The Statistics view, in the Objects tab, displays statistics related to the column and generated by the Oracle statistics collection mechanism.

The following table describes how the information in the Statistics view is structured.

Table 43 Structure of information in the Statistics view

About the buckets of a column

The Buckets view, in the Objects tab, displays the distribution of column data in the Oracle statistics that were gathered. This information is typically generated for indexed columns with skewed data by running a statistics collection.

The following table describes how the information in the Buckets view is structured.

Table 44 Structure of information in the Buckets view

Associating a column with other entities

You can associate a column in the Main area of the Objects tab with the following entities in the Association area:

• Statements that refer to the column
• Tables that the column belongs to
• Constraints that the column is involved in
• Indexes that the column belongs to

You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• Dictionary tab. Displays data dictionary properties of the column similar to those displayed in the Main area overview.
• Statistics tab. Displays statistics on the column, as generated by the Oracle statistics collection mechanism.
• Usage tab. Displays information on how the column has been accessed within explain plans.
  • Column. Displays the name of the column.
  • References. Displays the total of the counts displayed in the columns that followed, viewed as a numeric figure or bar chart.
  • As Indexable. Displays the number of times a column could have been used as part of an index.
  • In Select. Displays the number of times a column appeared in a Select clause.
  • In Where. Displays the number of times a column appeared in a Where clause.
  • In Update. Displays the number of times a column appeared in an Update clause.
  • In Join. Displays the number of times a column appeared in a Join predicate.
  • In Aggregate. Displays the number of times a column appeared in an Aggregate function.
  • In Distinct. Displays the number of times a column appeared in a Distinct clause.
  • In Order. Displays the number of times a column appeared in a Sort by clause.
  • In Group. Displays the number of times a column appeared in Group by clause.
  • Position. Displays the numeric position of the column within the entities.

About the Statement entity

The Statement entity, in the Objects tab, displays activity information, In Oracle performance, the text of the statement, and the storage location for the selected statement.

The following views are available:

• In Oracle
• Overall activity
• Text
• Workshop

About In Oracle information for a statement

The In Oracle view, in the Objects tab, displays an overtime graph of the In Oracle wait states. See “Wait States” on page 35.

About the overall activity of a statement

The Overall Activity view, in the Objects tab, displays a graph that illustrates the statement's overall activity over the selected time period. See “Wait States” on page 35.

About the text of a statement

The Text view, in the Objects tab, displays the full SQL text of the selected statement.

About information on the cabinet or folder related to a statement

The Workshop view, in the Objects tab, displays details on the cabinet or folder where the statement or PL/SQL is saved and on how it will be explained.

In general, if you prefer that a statement be referenced by a user-defined name in all tabs, rather than by a numeric identifier, you can save it to a particular cabinet or folder and assign a name to it. If required, you can also edit it later or add a tuning comment. See “Editing the properties of a statement” on page 31 and “About Precise for Oracle tabs” on page 21.

The following table describes how the information in the Workshop view is structured.

Table 45 Structure of information in the Workshop view

About associating a statement with other entities

You can associate a statement in the Main area of the Objects tab with the following entities in the Association area:

• Tables
• Indexes
• Views

You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• General tab. Displays the statement name and a portion of the SQL. You can also click on the tuning icon to launch the SQL tab to further your analysis.
• Workshop tab. Displays similar information to the information displayed in the Workshop view in the Main area. It is possible to associate from a Statement to the Tables, Indexes, and Views that it accesses.

About the PL/SQL entity

The PL/SQL entity, in the Objects tab, displays general details and the text of the selected PL/SQL. The following views are available:

• Overview
• Text

About the overview of a PL/SQL

The Overview, in the Objects tab, displays general information on the selected PL/SQL. The following table describes how the general information is structured.

Table 46 Structure of general information for a PL/SQL

About the text of a PL/SQL

The Text view, in the Objects tab, displays the full text of the selected PL/SQL.

Associating a PL/SQL with other entities

You can associate a PL/SQL in the Main area of the Objects tab with the following entities in the Association area:

• Objects referenced in the text
• Views referenced in the text
• Tables referenced in the text

You can display additional entities by using the More... option.

About the View entity

The View entity, in the Objects tab, displays general details and the text for the selected view. The following views are available:

• Overview
• Text

About the overview of a view

The Overview, in the Objects tab, displays general information on the selected view.

The following table describes how the general information is structured.

Table 47 Structure of general information for a view

About the text of a view

The Text view, in the Objects tab, displays the full text of the selected view.

Associating a view with other entities

You can associate a view in the Main area of the Objects tab with the following entities in the Association area:

• Statements that access the view
• Columns that the view includes

You can display additional entities by using the More... option.

About the Attribute entity

The Attribute entity, in the Objects tab, displays general information (Overview) that is retrieved from the data dictionary.

Associating an attribute with other entities

You can associate an attribute, in the Objects tab, with the following entities:

• User-defined type of attribute
• User-defined type containing attribute

You can display additional entities by using the More... option.

About the Cluster entity

The Cluster entity, in the Objects tab, displays information from the data dictionary (Overview and Storage view) and lists the schema changes that have been made to the cluster during the selected time period (Schema Changes view).

Associating a cluster with other entities

You an associate a cluster in the Objects tab with the following entities:

• Tables
• Columns
• Indexes

You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• Dictionary tab. Displays data dictionary information for the associated clusters, such as status, cache, creation date and when they were last modified.
• Storage tab. Display storage information related to the associated clusters, such as tablespace, initial extent and next extent, and percent increased, used and free.

About the Constraint entity

The Constraint entity, in the Objects tab, displays general information that is retrieved from the data dictionary.

Associating a constraint with other entities

You can associate a constraint with the following entities:

• Columns
• Tables
• Views
• Constraints

You can display additional entities by using the More... option.

About the Index Partition and Index Sub-Partition entities

When an index partition (or index sub-partition) is displayed in the Main area of the Objects tab, the Overview and the Storage view are available. Both views display information from the data dictionary.

Associating an index partition or sub-partition with other entities

You can associate an index partition or sub-partition in the Main area of the Objects tab with the following entities in the Association area:

• Columns in the index (sub-)partition
• Index that owns the index (sub-)partition
• Columns in the partition key.

You can display additional entities by using the More... option.

The information in the Association area table is displayed on the following tabs:

• Dictionary tab. Displays information from the data dictionary.
• Storage tab. Displays information from the data dictionary.
• Statistics tab. Displays Oracle analysis statistics.

About the Indextype entity

When an indextype is displayed in the Main area of the Objects tab, the Overview provides general information from the data dictionary.

About associating an indextype with other entities

You can associate an indextype in the Main area of the Objects tab with User Defined Types and PL/SQLs. You can display additional entities by using the More... option.

About the LOB entity

When an LOB is displayed in the Main area of the Objects tab, the Overview provides general information from the data dictionary.

About associating an LOB with other entities

You can associate an LOB in the Main area of the Objects tab with the table that it belongs to. You can display additional entities by using the More... option.

The information in the Association area is displayed on the Dictionary and Storage tabs. Both display information from the data dictionary.

About the Materialized View entity

When a materialized view is displayed in the Main area of the Objects tab, it shows general information from the data dictionary (Overview) and the definition of the materialized view (Text view).

Associating a materialized view with other entities

You can associate a materialized view in the Main area of the Objects tab with its related tables, views, and nested materialized views. You can display additional entities by using the More... option.

The information in the Association area is displayed on the Dictionary tab, which shows information from the data dictionary, and the Text tab, which shows the view's definition.

About the Method entity

When a method is displayed in the Main area of the Objects tab, it shows general information from the data dictionary (Overview) and the definition of the method (Text view).

Associating a method with other entities

You can associate a method in the Main area of the Objects tab with its return type, that is, a user-defined type. You can display additional entities by using the More... option.

About the Rollback entity

When a rollback is displayed in the Main area of the Objects tab, the following views are available:

• Overview. Displaying information from the data dictionary.
• Statistics. Displays information relating to the Oracle analysis statistics.
• Rollback Wait Graph. Displays a graphical view of the rollback wait activity. You cannot drill down from an imported statement to other entities.

When the rollback is selected in the Association area, information is displayed on the following tabs:

• Details. Displays the same information as the Overview in the Main area.
• Statistics. Displays Oracle analysis statistics; lists only rollback segments with an online status.

You can display additional entities by using the More... option.

About the Sequence entity

When a sequence is displayed in the Main area of the Objects tab, the Overview provides general information from the data dictionary.

Associating a sequence with other entities

You can associate a sequence in the Main area of the Objects tab with PL/SQLs or other objects that use it, for example triggers and synonyms. You can display additional entities by using the More... option.

About the Synonym entity

When a synonym is displayed in the Main area of the Objects tab, the Overview displays general information from the data dictionary.

When a synonym is displayed in the Association area, it displays data dictionary information.

Associating a synonym with other entities

You can associate a synonym in the Main area of the Objects tab with its referenced object. You can display additional entities by using the More... option.

About the Table Partition and Table Sub-Partition entities

When a table partition or sub-partition is displayed in the Main area of the Objects tab, the Overview and the Storage view are available. Both views display information from the data dictionary.

When a table partition or table sub-partition is displayed in the Association area, information is displayed on the following tabs:

• Dictionary. Displays data dictionary information.
• Storage. Displays data dictionary information.
• Statistics. Displays Oracle analysis statistics.

You can associate an table partition or sub-partition in the Main area with the following entities in the Association area:

• Columns in the table (sub-)partition
• Columns in the partition key
• Table that spans the table (sub-)partition

You can display additional entities by using the More... option.

About the Trigger entity

When a trigger is displayed in the Main area of the Objects tab, the following views are available:

• Overview. Displays information from the data dictionary.
• Text. Displays the trigger definition.
• SchemaChanges. Displays changes that have been made to the trigger during the selected time period.

When a trigger is displayed in the Association area, it displays data dictionary information.

Associating a trigger with other entities

You can associate a trigger in the Main area of the Objects tab with the table or view that the trigger is defined for. You can display additional entities by using the More... option.

About the User entity

When a user is displayed in the Main area of the Objects tab, the Overview displays general information from the data dictionary.

When a user is displayed in the Association area, it displays data dictionary information.

Associating a user with other entities

You can associate a user in the Main area of the Objects tab with the tables, indexes, or views owned by the user, or with the statements that the user has executed. You can display additional entities by using the More... option.

About the User-Defined Type entity

When a user-defined type is displayed in the Main area of the Objects tab, the following views are available:

• Overview. Displays information from the data dictionary.
• Text. Displays a definition of the user defined type.

When a user-defined type is displayed in the Association area, it displays data dictionary information.

Associating a user-defined type with other entities

You can associate a user-defined type in the Main area of the Objects tab with attributes of that type, its own attributes and methods, and the objects where it is declared. You can display additional entities by using the More... option.

About the EMC Unit entity

This entity is only available, on the Objects tab, if you have installed Precise for Database & Storage. For more information on the supported platforms, see the Precise Installation Guide. For more information on the Storage Device entity, see “Storage Device entities” on page 123.

An EMC Unit is the highest level Symmetrix or Clariion entity and comprises the entire storage array. It contains devices, front-end directors, back-end directors and remote-directors. A unit may store data for many servers and applications. The EMC Unit entity displays configuration details and performance counters for units connected to the server that the currently selected instance is running on.

To determine if a unit is used by the selected instance, check the Used by Instance check box in the Association area. For more information on counters that are related to the current instance, drill down to the device level in either the Objects or Activity tabs.

The following views are available:

• Overview
• Performance
• Load
• Throughput

About the overview of an EMC unit

The Overview, in the Objects tab, displays general information on the Symmetrix unit, such as ID, model, and number of physical disks.

About the performance of an EMC unit

The Performance view, in the Objects tab, displays two overtime graphs that illustrate the following information:

• The number of sequential reads and the total number of read and write requests during the selected time period.
• The read, write, and overall cache hit ratios over the same time period.

About the load of an EMC unit

The Load view, in the Objects tab, displays an overtime graph that illustrates the megabytes of data read and written during the selected time period.

About the throughput of an EMC unit

The Throughput view, in the Objects tab, displays an overtime graph that illustrates the I/O rate (requests per second) and the throughput (megabytes per second) over the selected time period.

Associating an EMC unit with other entities

You can associate an EMC unit in the Main area of the Objects tab with the disk directors, front end directors, remote directors, and devices configured for the unit. You can display additional entities by using the More... option.

How the Objects tab can help you identify performance problems

The Objects tab helps you understand the relationship between database objects and statements. You can identify a performance problem by doing one or more of the following:

• Examining INIT.ORA parameters
• About examining schema changes and tablespaces

Examining INIT.ORA parameters

INIT.ORA parameters can help you when tuning a statement, object or instance in the Objects tab. To examine INIT.ORA parameters

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to view.
3. In the Main area, observe the Init Parameters view.

About examining schema changes and tablespaces

You can observe information on the schema changes in the Objects tab, by selecting an instance and the Schema Changes view.

To examine schema changes and tablespaces

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. On the View controls in the Main area, click Schema Changes to open a view for that instance.
4. Now take a look at the Tablespaces entities, to understand how objects grow over time and what effect their growth has on performance. For example, the Objects tab shows storage information on tablespaces in Instance ora920, including the used and free space. Clicking a tablespace reveals details of an individual tablespace.

In another example, we analyze the number of reads and writes to a tablespace storing indexes. Here the number of physical write operations is much higher than the physical reads, probably as a result of heavy insert or update activity. This may indicate that the performance of insert or update statements is suffering because of the requirement to maintain the indexes.

For example, the number of physical reads from all the tables in tablespace XDB has increased over time. This is probably due to index range scans or full table scans that need to return more rows.