Page History
...
The Tuning Action buttons in the Activity tab enable you to perform the following actions:
See “Editing the properties of a statement” on page 31.
| Anchor | ||||
|---|---|---|---|---|
|
The Define Extended Collection button opens the Define Extended Collection dialog box, allowing you to define a period where more detailed Oracle Performance information is collected.
...
- Click the Define Extended Collection button to open the Define Extended Collection dialog box.
- In the Instance list, select the appropriate instance name.
- If appropriate, type in the Collection name.
- For Start At date and time, select the following data:
- day (number)
- month (name)
- year (number)
- hour (number)
- minute (number)
- Type the desired maximum number of megabytes for collection information.
- Define the criteria for activities to be gathered in the collection. For each entity listed in the Entity column, define whether to include or exclude the entity to the collection.
- Click OK to accept or Cancel to reject the defined entities.
| Anchor | ||||
|---|---|---|---|---|
|
The Import Statement button opens the Import Statements from File dialog box, allowing you to import SQL statements from a file. This dialog box includes three tabs: Source, Catalog, and Options. See “Editing the properties of a statement” on page 31. See “About the Imported Statement entity” on page 78.
...
- Check the appropriate box(es) if you want to ignore strings enclosed by single or double quotes.
- Check the appropriate box(es) if you want to ignore specific types of comments.
- Check the appropriate box(es) if you want to convert hyphens or periods to underscores.
- Check the appropriate box(es) regarding statement parameters (select, insert, update, or delete).
- Check the appropriate box if you want to ignore specific statements.
- Check the appropriate box if you want to scan the data dictionary for a column name.
- Click OK to accept or Cancel to reject the Source tab information.
| Anchor | ||||
|---|---|---|---|---|
|
There are two possible actions you can take to stop extended collections. To stop extended collections
...
You can identify a performance problem by doing one or more of the following:
- Examining resource consumption of an entire instance
- Identifying heavy resource consumers
- Examining resource consumption over time
- Identifying offending SQL statements
- Correlating Oracle data with ERP data
- Examining the scalability of your application
- Examining EMC Storage devices
| Anchor | ||||
|---|---|---|---|---|
|
When performing a tuning audit, it is very important to analyze and understand the performance behavior of your instances. 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 represents the performance of the average application. This view will help you answer questions such as: This query suffers from lock 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.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- 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. For our example, we have an Activity tab that shows an instance that is fairly busy. In the Main area, the instance shows that it has spent 1 day out of 7 executing. But it has spent 40% of that time on Other Lock Wait. This is an unproductive wait.
Launch to the Statistics tab to find out more about the underlying Oracle wait events comprising the Other Lock Wait. It is most likely to be an enqueue. In fact, the instance has spent over 50% of its time in unproductive waits (not using CPU or I/O).
Info As a rule of thumb, a healthy system should have a high Using CPU value; a 10-15% I/O Wait, and the remaining non-productive wait states should show values that are as low as possible. For databases that process large data volumes, such as a data warehouse, the I/O Wait percentage may be higher. It is true, however, that applications/statements that experience high Using CPU or I/O Wait times may be performing excessive logical I/Os and should be investigated. To determine this, it is important to look at the actual time spent waiting (in hh:mm:ss) as well as just the percentage.
| Anchor | ||||
|---|---|---|---|---|
|
Precise for Oracle lets you drill down to application components (such as Programs, Users, Host Users, Machines, Statements, and so on) to determine which application component consumes the most resources. To do so, click Programs from the Association controls to show a breakdown of the Oracle activity by program. You can now click any of the programs to see just the activity for that program and the entities that contributed to that activity. This process is iterative and you may continue to drill down until you discover the application component that you want to tune.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the Association controls in the Association area, click Programs. This shows a breakdown of the Oracle activity by program.
- You can now click any of the programs to see just the activity for that program and the entities that contributed to that activity.
- Continue to drill down until you discover the application component that you want to tune.
| Anchor | ||||
|---|---|---|---|---|
|
When you analyze and tune the performance of an application component (such as Program or Statement), it is important to consider its performance over time. Precise for Oracle lets you view the component's performance over a selected time frame.
...
Although this particular example is performing a simple load test, in a production application you may want to consider running such statements outside of working hours.
| Anchor | ||||
|---|---|---|---|---|
|
Having identified a major resource consuming entity, you can further drill down to the statements and PL/SQL executed by that entity. These may include long running SQL statements and SQL statements that use few resources but were executed frequently. See “Identifying heavy resource consumers” on page 83.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- In the Association area, click the General tab so as to view the SQL text, number of executions, average In Oracle time, and the percentage contribution to the overall In Oracle time. In the Main area, the bar graph shows a breakdown of the In Oracle time so that you can easily see if a statement is CPU or I/O bound, or waiting on other things.
- In the Association area, click the Statistics tab to view Oracle statistics for each statement. For example, you can see the number of Logical Reads, which is the total of the Consistent Gets + DB Block Gets. Similarly, you can see the number of Physical Reads, which is the number of reads that involved a physical I/O from disk because the data was not available in the Oracle Buffer Cache.
- The tab shows that the number of logical and physical reads is high and contributing to the I/O wait time. In addition, it indicates the number of Sorts and how many were performed in memory compared to disk. Disk sorts take longer than sorts in memory, so attempts should be made to avoid them where possible by sizing the Oracle sort areas settings appropriately and tuning statements that do unnecessary sorting. For our example, the number of sorts is low and there is little sorting to disk.
| Anchor | ||||
|---|---|---|---|---|
|
Installing the Precise for Oracle Interpoint extension lets you correlate Oracle information with ERP information. For example, if you install Interpoint for SAP, you will be able to display the breakdown of your Oracle activity into SAP Work Types.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- In the Activity tab, the overtime graph indicates that the major bottleneck is the I/O wait. However, in this 24 hour period, nearly all the I/O wait time arises from batch jobs (SAP Background).
- Check the interactive sessions (SAP Dialog). Interactive sessions (SAP Dialog) are not waiting much on I/O. If the emphasis in this application is on tuning interactive sessions, then despite first appearances, tuning the I/O is not going to make an appreciable difference.
- Drill down to the entity whose data you want to correlate. For our example, drill down to SAP Background to view the transactions and statements, to investigate why batch jobs are taking so long.
| Anchor | ||||
|---|---|---|---|---|
|
Applications are called upon to support the addition of more and more users, and data, over the years. One of today's tasks is to ensure that our applications and servers are scalable for tomorrow's tasks.
...
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click Scalability. The Scalability view shows that the number of Executions has remained fairly constant.
- Check the In Oracle time. For our example, the In Oracle time for the statement is now much lower (towards the right-hand side of the graph), after some tuning has taken place. In other words, it should be possible to increase the number of executions and hence the throughput of the application by a factor of 3 for the same resource consumption.
| Anchor | ||||
|---|---|---|---|---|
|
Precise for Storage, Oracle version, supports EMC devices and lets you see the resource consumption of the EMC Storage devices from the application’s point of view.
...