Page History
This section includes the following topics:• About
...
...
...
| Anchor | ||||
|---|---|---|---|---|
|
Precise for Sybase allows you to proactively monitor, analyze, and tune Sybase instances. By capturing, measuring, and correlating performance metrics from all critical system components, Precise for Sybase provides a complete view of your application's performance. It detects business performance problems and helps you find suitable and comprehensive solutions thereby ensuring that your business applications always perform at peak efficiency.
...
For example, you may want to start at the Instance level and then drill down to different Login names and the SQL statements they executed. You can view the status of current sessions, or focus your analysis on a particular time period, and examine their consumption patterns during the last minute, or further back in the past, such as yesterday, last week, or last year. You can also examine changes in resource consumption patterns over time in time slices of 15 minutes, hours, four hours, days, weeks, or months.
| Anchor | ||||
|---|---|---|---|---|
|
The Current tab displays near real-time information on all sessions that were connected to your Sybase instance during the last minute. Use the Current tab to solve bottlenecks, such as those caused by locked sessions or runaway processes. The Current tab displays instance activity information up to the last time slice of activity.
...
If you identified a blocking situation that is currently in progress (for example, there is a session waiting for a lock), select the Locks view to view the current sessions that are blocking the other sessions. The information is displayed in a tree format, which helps you easily identify which are the locked sessions, who they are being locked by, and what is the lock level. You can drill down to the sessions involved in the lock to obtain more information about what is causing the lock. For example, you can drill down to a locking session to determine which SQL and which program are currently being executed.
Analyzing instance performance
While the Current tab displays near real-time data, it is recommended to begin the actual tuning process is the Activity tab.
...
The Overview view provides a breakdown of the In Sybase states, such as, CPU Usage, I/O Waits, Lock Waits, and Remote Waits. Examining this data allows you to pinpoint the type of resource causing the bottleneck. If for example you identify that Sybase is suffering from an I/O wait problem, upgrading your server to a much more powerful machine will probably fail to provide you with the expected results.NOTE A
| Info |
|---|
A high CPU rate exceeding 90% may indicate that indexes are not being used. |
Identifying resource consumers
Precise for Sybase lets you drill down to additional levels to determine which entities are responsible for a particular performance problem. In the Association area you can observe different entities, such as databases, login names, users, programs, and client machines, to pinpoint the application component that consumes the most database resources.
For each entity (as, for example, the program entity), you can access the following information:• Entities
- Entities that are related to the entity (such as, the logins or machines that executed the program).
...
- Resources consumed by the entity. This enables you to determine if the entity is CPU or I/O bound, or waiting for a lock.
...
- Resource consumption patterns, which allows you to observe and identify trends in resource consumption. You can drill down even further to other entities associated with the selected entity in the Association area.
For example, once you have identified a problem in a specific program you can drill down to the batches or statements the program issued, to determine the source of the problem.
Associating with offending SQL statements
Having identified a major resource-consuming entity, you can further drill down to the statements and batches executed by that entity. These may include long-running SQL statements or SQL statements that use few resources but are executed frequently.
For each statement or batch, you can access the following information in Precise for Sybase:• Entities
- Entities that executed the statement or batch.
...
- Resources consumed by the statement or batch, which enables you to determine if the statement or batch is CPU or I/O bound, or waiting for a lock, and so on.
...
- Resource consumption patterns that allow you to observe if the statement or batch execution is affected by excessive use of the same resource, by another statement or batch.
...
Analyzing previous blocking situations
One of the main reasons that statements possess long execution times is that they are waiting for a resource that is locked by another session.
Using Precise for Sybase, you can easily identify the following:• Statements
- Statements waiting for a locked object
...
- Locked objects (that is, contended objects)
...
- Sessions causing locks
In Precise for Sybase, a statement waiting for the lock on a resource to be freed is in a Lock Wait. The session holding the lock is called a Blocker session.
| Anchor | ||||
|---|---|---|---|---|
|
Most problems occur in SQL statements or batches. Precise for Sybase lets you to easily identify the most resource-consuming SQL statement or batch and view a detailed explanation of the execution plan of the SQL statement or batch. It can also help you resolve the problem by recommending which index would provide a better execution plan and reduce resource consumption.
Identifying the most resource consuming statements or batches
Use the Activity tab to identify the most resource-consuming SQL statements or batches over a selected time period. By focusing in on a specific time period you can apply your efforts to tuning a problematic time frame, such as a batch window or end-of-the-month processing.
...
When selecting an SQL statement, you can view a breakdown of its resource consumption over time, its text, and all locking contentions that it participated in (time spent waiting for a resource).
Collapsing constants into bind variables
When an application uses constant values and literals instead of bind variables (parameter markers), many different SQL statements actually constitute the same SQL statement with different values.
...
Precise for Sybase enables you to select a Collapsed statement and associate to all the different execution plans of the SQL statements that were grouped together.
About viewing an execution plan
To view the execution plan of the statement and understand how the optimizer chooses to perform the statement, launch to the SQL tab. Each branch in the execution plan represents one operation. Each operation is displayed using the following format:
[<execution order>] <operationtype> (<options>) <accessed object>[<cost in percentage>]
When examining the execution plan, always start by exploring it from right to left and top to bottom (according to the execution order in a branch on the tree). The first operation to be performed has execution order 1, followed by 2, and so on.
Understanding an execution plan
By observing the access plan of a selected statement or batch, you can analyze the access path chosen by the Sybase Optimizer. Click the Statistics tab to compare the costs of the execution plan operators and quickly locate the most resource-consuming operation in the execution plan.
...
The information displayed in the Operations tab helps you understand the nature of the execution plan, its major operations, and the major access types to objects (index scan, index seek, table scan, and so on).
Analyzing the statement execution plan over time
Precise for Sybase allows you to track Access Plan changes and compare them with the resource consumption of the statement to understand how the changes in the Access Plan affected its performance and execution plan.
Examining object space usage over time
Another way to tune a database object is to examine its space usage. The Collect Space Utilization process collects table and index space information daily (by default).
Space usage information allows you to examine space allocated versus space used by a table or an index. This enables you to easily keep track of object growth over time and decide how to allocate memory and connect objects to database devices in the specified database.
| Anchor | ||||
|---|---|---|---|---|
|
The Precise for Sybase Collector Instance Statistics process collects a wide range of performance counters from the operating system, some which belong to Sybase and some to the operating system (OS) itself. These performance counters are sampled every minute and displayed in the Current and Statistics tab. When loaded into the PMDB, the counters are aggregated and kept at a slice level. The counters are then aggregated to daily, weekly, and monthly levels.
Observing instance statistics over time
The Statistics tab provides many performance counters grouped into several predefined overtime graphs that enable you to locate various aspects of performance problems, such as CPU, paging, I/O, and network. The statistics tab can be used to monitor your system's current state as well as previous states. It is also possible to monitor a specific counter associated with a particular performance group by selecting the required counter.
...
Precise. Performance intelligence from click to storage. Learn more > >
...
Newtabfooter |
Newtabfooter |
Newtabfooter |
Newtabfooter |
Newtabfooter |
| |||||||||||||||||||||||||||||||
| Newtabfooter | ||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...