This section includes the following topics:

About the Objects tab

The Objects tab is a comprehensive browser, which allows you to understand the relationships and associations between database schema objects. It enables a more productive tuning process by reducing the need of another tool for exploring the Sybase catalog tables.

The Objects tab also allows you to observe storage utilization, over time, at varying levels, such as, database, segment, database device, and so on.

How the Objects tab is structured

The Objects tab displays information on a selected entity and its associated entities. When you open the Objects tab the selected entity is by default Instance, meaning that information is displayed on the instance level. When you launch the Objects tab from the Activity tab with the database entity - the tab is launched in-context with the database you selected in the Activity tab.

If you open the Objects tab from another tab, the historical settings (meaning those settings which were selected when you left the tab, such as the last entity you drilled down to) are taken into account and the information displayed the last time you viewed this tab is displayed (similar to clicking the History button and returning to a previous tab).

The selected 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 the Instance level. You can select an instance from the Instance list.

The entities displayed in the Association area are associated with the selected entity displayed in the Main area. At times, the relationship between the entity displayed in the Main area and those displayed in the Association area is that of parent to child and sometimes it merely represents that there is a relationship between the selected entity and the entities displayed in the Association area.

About the Main area in the Objects tab

The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. You can, for example, focus exclusively on Index Properties, Index Statistics, or Index Histograms.

About the Association area in the Objects tab

The Association area provides corresponding information on the child entities. You can view information on one type of child entity at a time, such as only instances or only databases, by selecting a control from the View Controls list. The selection you make is only reflected in the Association area; the Main area remains unchanged.

From the Association area, you can also drill down to another entity by clicking a table row. A drill-down affects the whole 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 its associated entities.

For example, when you want to view information on a specific database, choose Database from the Association controls. The Association area changes to display all the databases associated with the current instance. Note that the Tab heading and the Main area remain unchanged.

In the Association area, click the row of the database you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays an overview of the database you want to investigate, and the Association area shows general details on the tables that are associated with the database you selected. For more information about Precise for Sybase tabs, see About Precise for Sybase tabs.

About the entities you can examine in the Objects tab

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

About the Instance entity

The Instance entity displays configuration information, and performance information, as collected by the Precise for Sybase Collector agent for the entire instance. The following views are available:

About getting an overview of instance configuration and performance information

The Overview displays configuration and performance information for the entire instance. The Overview is divided into the following areas:

About viewing instance configuration parameters

The Instance Parameter view displays the selected instance configuration parameters. This includes the possible range values, configuration (pending) values and run values, for each parameter.

About the Database entity

The Database entity displays configuration information, and performance information collected by the Precise for Sybase collector of the selected database. The following views are available:

About getting an overview configuration, performance, and Space Allocation information for a selected database

The Overview displays configuration, performance, and Space Allocation information for the selected database, as follows:

About viewing Database options

The Database Options view displays database options, such as abort tran on log full and trunc. log on chkpt, as follows:

About the Replication Agent configuration parameter view

This table describes the Replication Agent configuration parameters for the specified database. This view is only enabled for published databases. The Replication Agent Configuration Parameters in the Main area can be accessed as follows:

  1. In the Objects tab in Precise for Sybase, select the desired database in the Association area.
  2. Click Overview in the Main area, and then click Replication Agent Configuration Parameters. The Replication Agent Configuration Parameters appear in the Main area.

The headings in the Replication Agent Configuration Parameters main area are described below.

Table 1 Replication Agent Configuration Parameters headers

ColumnDescription
Parameter NameDisplays the parameter name.
Default ValueDisplays the default value for each parameter.
Configuration ValueDisplays the configuration value for each parameter.
Run ValueDisplays the run value for each parameter.

About the Database entity in the Association area

You can view the entities associated with the selected Database entity in the Association area. It is possible to associate to the Database entity from the Instance entity.

About the Table entity and Tables Contain Same Column entity

The Table entity displays general details regarding space utilization for the selected table.

The Tables Contain same Column Entity displays tables that have the same column as the column displayed in the Main area. The information displayed is the same as displayed in the Table entity and can only be associated to from the Column entity.

The following views are available:

About getting an overview of table entities

The Overview displays general table properties, along with space utilization, and number of rows in the selected table during the selected time frame. It is divided in the following areas:

About viewing data storage and fragmentation information

The Optdiag view displays data storage and fragmentation information for the selected table. The following information is displayed:

The page number of its first page identifies an extent. If the extents 8, 16, 24, or 32 make up an index, then we can see that the extents are contiguous on the disks, which means there are no gaps. On the other hand, if the extents 8, 24, 32, and 64 make up an index, then we can see that we have a gap. The extent fragmentation is computed by dividing the number of gaps by the number of extents, so in this example, the extent fragmentation is 1/4, or 25 percent.

The lower the number, the less fragmentation there is.

This number is not relevant to heaps because physical order has no importance in the data pages.

Derived statistics may show incorrect data, when table statistics are out-of-date. It is advised to run update statistics, before reviewing these figures.

About viewing data on table partitions

The Table Partitions view displays size and distribution of table storage over all of its partitions. The following information is displayed:

About the Index entity

The Index entity displays general details regarding space utilization for the selected index. The following views are available:

About getting an overview of index entities

The Overview displays the following information:

About viewing data storage and fragmentation information for the selected index

The Optdiag view displays data storage and fragmentation information for the selected index. The following information is displayed:

About viewing derived statistics

The items in the following table are only displayed for instances in version 12.5.0.3 and above.

About viewing clustered index details

This section applies to a clustered index on an APL table. The counters shown are the same as those shown at table level. This section is only shown if the index is a clustered index.

About viewing density and selectivity statistics

The Statistics view displays density and selectivity statistics for each prefix subset of the selected index keys. Histograms for the index columns can be seen at the relevant column statistics view.

The following information is displayed:

Index entity Association area

You can view general details on the entities associated with the selected Index entity in the Association area. It is possible to associate to the Index entity from the following entities: Database, Table, Column, User, Segment, and Data Cache.

About the Column entity

The Column entity displays general details for the selected column. The following views are available:

About getting an overview of the selected column

Displays general details on the selected column such as type, length, and default value.

About viewing column statistics

This view shows column-level statistics including:

The following information is displayed:

Distribution

Shows the column histogram as the distribution of values within the column.

The histogram contains the step numbers, the weights, an operator used to help indicate the type of cell (Range / Frequency), and the value of the cell. The weight value indicates the percentage of rows in the column that are occupied by values in the cell.

For example, a weight of 0.050000 means that the value(s) in the cell occupy 5% of the rows in the column. The weight is very useful to determine when a value or values is becoming highly duplicated-if the weights are fairly uniform, the data is well distributed.

Column entity Association area

You can view general details on the entities that are associated with the selected Column entity in the Association area. It is possible to associate to the Column entity from the Table, Index, and View entities.

About the View entity and Views Contain Same Column entity

The View entity displays general details for the selected view.

The Views Contain Same Column entity displays views that have the same column as the column displayed in the Main area. The information displayed is the same as displayed in the View entity and can only be associated to from the Column entity.

About getting an overview of the View entity and Views Contain Same Column entity

The Overview displays general details on the View entity such as creation date, number of columns, and the view text.

View entity and Views Contain Same Column entity Association area

You can display general details on the entities that can be associated with the selected View entity in the Association area. It is possible to associate to the View entity from the Database, Table, Column and Index entities.

About the User entity

The User entity displays general details of the selected user, such as, Roles and Group Name.

User entity Association area

You can view general details on the entities that can be associated to the selected User entity in the Association area. It is possible to associate to the User entity from the Database entity.

About the Database Device entity

The Database Device entity displays general details, I/O Activity and storage information on the selected device. The following views are available:

About getting an overview of the database device entity

The Overview displays the following information:

About viewing I/O statistics on a database device

Displays I/O statistics on the device during the selected time frame. The following information is displayed:

Database Device entity Association area

You can view general details on the entities associated to the Database Device entity in the Association area. It is possible to associate to the Database Device entity from the following entities: Instance, Database, Segment, Table, and Index.

About the Segment entity

The Segment entity displays general details and space utilization during the selected time frame, for the selected Database Segment.

About getting an overview of segment details

The Overview displays general Segment details, such as number of device extents the Segment occupies and information regarding the segment's space utilization during the selected time frame. The following information is displayed:

Segment entity Association area

You can view general details on the entities that are associated to the selected Segment in the Association area. It is possible to associate to the Segment from the following entities: Database, Table, Index, and Database Device.

About the Data Cache entity

The Data Cache entity displays general information about the selected Data Cache. The following views are available:

About getting an overview of data cache pools

The Overview displays general Data Cache details, such as size, replacement policy and pool configuration.

About viewing data cache pools

Data Cache Pools can be configured in each cache to enable Sybase to perform large I/Os. The Data Cache Pools view displays information regarding the pools configuration of the selected Data Cache.

The following information is available:

Data Cache entity Association area

You can view general details on the selected Data Cache entity in the Association area. It is possible to associate to the Data Cache entity from the Instance, Database, Table and Index.

About the Unused Indexes entity

The Unused Indexes entity displays all indexes that are not used by any explained statement that is associated to a database, table, user or segment.

About the Statement entity

The Statement entity displays statements associated to tables and indexes in the Object tab. The following views are available:

About getting an overview of a statement

The Overview displays the following information:

About viewing the performance of a selected statement

The Performance view displays the following information:

About viewing the SQL text of a specific statement

The SQL Text view displays the SQL text of the statement.

Statement entity Association area

You can view general details on the statement associated with the selected Table, or Index entity in the Association area. It is possible to associate from the statement to the Table or Index accessed by this statement. There are Table, Index, and Object lists which can be associated to a statement.

The table below describes the information displayed in the Performance tab.

Table 2 Performance tab

ColumnDescription
Launch icon to SQL tabLaunches into SQL tab.
Statement IDDisplays the statement ID.
In SybaseDisplays the sum of each In Sybase parameter in the time period from the statistics table - bar with separated values or sum of all In Sybase values.
TextDisplays the statement's text.

The table below describes the information displayed in the Plan tab.

Table 3 Plan tab

ColumnDescription
Launch icon to SQL tabLaunches into SQL tab.
Statement IDDisplays the statement ID.
DatabaseDisplays the database name.
Parsing UserDisplays the parsing user name.
Most Recent PlanDisplays the date of the most recent plan.
Last Show PlanDisplays the date of the last shown plan.
Total Estimate CostDisplays a graph/number.
Table ScanDisplays Yes or No.
Clustered Index ScanDisplays Yes or No.
Clustered Index SeekDisplays Yes or No.
Index ScanDisplays Yes or No.
Index SeekDisplays Yes or No.
Table LookupDisplays Yes or No.
SortDisplays Yes or No.
Merge JoinDisplays Yes or No.
JoinDisplays Yes or No.
Nested Loop JoinDisplays Yes or No.
Parallel AccessDisplays Yes or No.

About the Batch entity

The Batch entity displays batches associated to tables and indexes in the Object tab. The following views are available:

About getting an overview of batch details

The Overview displays general batch details such as most recent plan and last show plan, as follows:

About viewing the performance of a batch

The Performance view displays resource consumption and number of statement executions for a batch, as follows:

About viewing the SQL text of a batch

The SQL text view displays the SQL Text of the batch.

Batch entity Association area

You can view general details on the batch associated with the selected Table, or Index entity in the Association area. It is possible to associate from the batch to the Table or Index entity accessed by this batch. There are Table, Index, and Object lists that can be associated to a batch.

The table below describes the information displayed in the Performance tab.

Table 4 Batch entity Performance tab

ColumnDescription
Launch icon to SQL tabLaunches into SQL tab.
Batch IDDisplays the batch ID.
In SybaseDisplays the sum of each In Sybase parameter in the time period from the statistics table - bar with separated values or sum of all In Sybase values.
TextDisplays the text of the batch.

The table below describes the information displayed in the Plan tab.

Table 5 Batch entity Plan tab

ColumnDescription
Launch icon to SQL tabLaunches into SQL tab.
Batch IDDisplays the batch ID.
DatabaseDisplays the database name.
Parsing UserDisplays the parsing user name.
Most Recent PlanDisplays the date of the most recent plan.
Last Show PlanDisplays the date of the last shown plan.
Total Estimate CostDisplays a graph/number.
Table ScanDisplays Yes or No.
Clustered Index ScanDisplays Yes or No.
Clustered Index SeekDisplays Yes or No.
Index ScanDisplays Yes or No.
Index SeekDisplays Yes or No.
Table LookupDisplays Yes or No.
SortDisplays Yes or No.
Merge JoinDisplays Yes or No.
JoinDisplays Yes or No.
Nested Loop JoinDisplays Yes or No.
Parallel AccessDisplays Yes or No.

How the Objects tab can help you identify performance problems

It is possible to use the Objects tab to identify performance problems by:

Examining an object's space usage over time

Space usage information, collected by the Collect Space Utilization process, lets you examine the space allocated versus the space used, in a table and index. This enables you to easily keep track of object growth over time and decide how to spread database segments over various devices and how to connect objects to segments in the specified database.

Examining fragmentation of table data and indexes

Fragmentation of table data and indexes may affect the performance of your system. The fragmentation occurs as a result of data modification (INSERT, UPDATE, and DELETE statements) performed on the table. Fragmentation increases the amount of page reads performed by the query, increasing their resource consumption.

To examine fragmentation of table data and indexes

  1. Use the Optdiag view in the Table or Index entity to observe fragmentation information.
  2. Determine whether the table or index is heavily fragmented and needs to be reorganized. For more information about table and view entities, see About the Table entity and Tables Contain Same Column entity and About the View entity and Views Contain Same Column entity.

Examining distribution statistics of indexes and columns

Density and selectivity statistics of an index can help you determine whether or not the index is useful to the query optimizer. The information displayed in the Statistics view of the Index entity indicates the selectivity of an index; the more selective an index is, the more useful it is, because this means more rows can be eliminated from consideration. You can also determine what columns in the index contribute to the selectivity and thus decide whether all should be included in the index.

To examine distribution statistics of indexes and columns, use the Statistics view of the Column entity to help you see the distribution of values in that column. For more information about viewing these statistics, see About viewing density and selectivity statistics and About viewing column statistics.


 |    |  |   |   |