Page History

...

You can identify a performance problem by doing one or more of the following:

• Examining resource consumption of an entire instance and database
• Identifying heavy resource consumers
• Examining resource consumption over time
• Resolving blocking situations
• Identifying offending SQL statements
• Examining the scalability of your application or Sybase servers

Anchor
Examiningresourceconsumptionofanentireinstanceanddatabase Examiningresourceconsumptionofanentireinstanceanddatabase

Examining resource consumption of an entire instance and database

When performing a tuning audit it is very important to analyze and understand the performance behavior of your instances and databases. You may have already drilled down to analyze the performance of a single query or program. However, examining the entire instance's behavior can alert you to the health of your system. The Instance (or Database) represents the performance of the average application. This helps you answer questions such as: "This query suffers from Log wait, but does the entire system suffer from lock wait?" If, for example, you discover that your system suffers from I/O wait and you tune a query that performs memory operations, you most likely will not improve the entire system's I/O wait problem. So examining the entire instance can provide a quick overview of the dominant resources consumed.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click Overview. Examine the entire instance and determine which are the dominant resources that are consumed by your system.
4. On the Association controls in the Association area, click Databases.
5. On the Performance tab, place the cursor in the In Sybase column of the top database and view the information displayed on its resource consumption.

Anchor
Identifyingheavyresourceconsumers Identifyingheavyresourceconsumers

Identifying heavy resource consumers

Precise for Sybase lets you drill down to application components (such as Logins, Databases, Programs, Machines, and Statements) to determine which application component consumes the most resources. This is accomplished by clicking on an application component in the Association area. This process is iterative and you may continue to drill down until you discover the application component that you want to tune.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.

3. On the View controls in the Main area, click In Sybase to view a breakdown of the In Sybase time.

   Info

   When trying to determine which application component to drill down to, take into consideration not only the total In Sybase of the application, but also its number of Sessions or Executions and Duration (Avg). If you do not take these elements into consideration you may try to tune a program that runs once a year, consuming 20 hours, instead of tuning a program that consumes 2 seconds but runs every time a user logs on to the system.

4. On the Association controls in the Association area, click Programs.
5. On the Performance tab, place the cursor in the In Sybase column of the top program entity and view the information displayed regarding its resource consumption.
6. Drill down on the entity to focus on additional components.
7. Go back to the overview of the instance you originally selected.
8. In the Association area, use the Association controls to select different entities associated with the instance, and analyze the average duration of each entity.

Anchor
Examiningresourceconsumptionovertime Examiningresourceconsumptionovertime

Examining resource consumption over time

When you analyze and tune the performance of an application component (such as, database, program, or query) it is important to take into consideration its performance over time. Precise for Sybase lets you view the component's performance over a selected time period.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click In Sybase.
4. Move the cursor over the bars in the graph to view the instance's resource consumption over time.

Anchor
Resolvingblockingsituations Resolvingblockingsituations

Resolving blocking situations

While many think that locking is bad, the truth is that locking is good - it maintains data integrity. The situations to avoid are contentions. Contentions are situations in which one application waits for a resource that is being held by another application.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. In the Association area drill down to the database entity you want to analyze.
4. On the View controls in the Main area, click Locking.
5. Examine the locking of the selected entity in the graph, for the period of time you selected. Move the cursor over the bars in the graph to view the Lock Wait breakdown.
6. Examine the locks tree to observe all the contentions that occurred and determine which sessions were involved in contentions.
7. Assess who is blocking who (For example, determine which session is blocking which session. By drilling from the application component with Lock Wait to the Blocker Session entity, you can find the sessions that caused the contention).

Anchor
IdentifyingoffendingSQLstatements IdentifyingoffendingSQLstatements

Identifying 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 and SQL statements that use few resources but were executed frequently. In many cases analyzing the queries executed by the entity and tuning them will result in the most performance gain. For each statement, you can do the following:

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. In the Association area, drill down to the top resource-consuming database.
4. On the Association controls in the Association area, click Statements. On the Performance tab view which entities executed the statement and view the resources consumed by the statement.
5. In the Plan tab, in the Association area, view a summary of the statement's access plan.

Anchor
ExaminingthescalabilityofyourapplicationorSybaseservers ExaminingthescalabilityofyourapplicationorSybaseservers

Examining the scalability of your application or Sybase servers

Applications are called upon to support the addition of more and more users, and data, over the years. One of today's tasks is ensure that our application and servers are scalable for tomorrow's tasks.

...

1. In the Instance list, choose the item you want to analyze.
2. In the Time Frame list, choose the period of time you want to analyze.
3. On the View controls in the Main area, click Scalability.
4. In the Session Duration (Avg) vs. Sessions graph, observe the number of sessions and the impact of the average session duration, over the selected time period. For example, if you observe that average session duration decreases when the number of sessions increase, this may indicate that the application has a scalability problem.

...