Page History
...
The Statistics tab is primarily intended for instance tuning. You can also use the tab to periodically monitor the health of your instance. It lets you provide answers to the following types of questions: Is Oracle using resources efficiently? Is the SGA correctly sized? Do our systems have enough memory to enable us to keep adding dedicated server processes? Are we performing too many logical I/Os?.
With each release of the database, Oracle introduces new statistics. For example, operating system-related statistics were introduced in Oracle 10g. Precise for Oracle displays this information when available.
...
If you want detailed information on a wait event, in the Association area, click the row of the event that you want to view detailed information for. The Tab heading indicates the newly selected entity, and the Main area displays over time graphs for the wait event you drilled down to. There is no Association Area data for a wait event. See “How How most tabs are structured” on page 22structured.
| Anchor | ||||
|---|---|---|---|---|
|
...
The following graphs are displayed:
Table 10- 1 Graphs of the RAC Database Cache view
| Graph | Description |
|---|---|
| GC Blocks Transfer | It displays the total aggregated number of global cache current blocks and global cache CR blocks that have been transferred. |
| Avg. GC Blocks Access Latency (Msec.) | It displays the following average end-to-end elapsed time, or latency for a global cache block request:
|
| CR Blocks Request Latency (Avg.) | It displays the following breakdown of the average end-to-end elapsed time, or latency for a CR block request:
|
| Current Blocks Request Latency (Avg.) | It displays the following breakdown of the average end-to-end elapsed time, or latency for a current block request:
|
About information on instances in the RAC database
The Instance in Database view, in the Statistics tab, displays the following additional RAC database information:
Table 10- 2 Additional RAC database information
| Information | Description |
|---|---|
| Database | Displays the logical name of the RAC database. |
| DB Name | Displays the Oracle database ID for the instance. |
| DB ID | Displays the Oracle database ID. |
| Instance Number | Displays the number of the instance in the RAC database. |
| Instances | Displays the number of instances that share the same RAC database with the selected instance. |
| Instance Sessions | Number of current instance sessions in the database. |
| Database Sessions | Number of total database sessions. |
| Time Non-Idle Wait Event | Pie chart comparing the amount of non-idle wait time experienced by the selected instance vs. the entire database. |
| Latches | Pie chart comparing the amount of latch wait time experienced by the selected instance vs. the entire database. |
| Logical I/O | Pie chart comparing the amount of logical I/O experienced by the selected instance vs. the entire database. |
| Statement Executions | Pie chart comparing the number of statements executed by the selected instance vs. the entire database. |
About the load of a Tier, instance, or database
...
The following table shows Wait event information in the Association area.
Table 10- 3 Wait event information in the Association area
| Column | Description |
|---|---|
| Wait Event | Name of the Oracle wait event. |
| In Oracle Sub-State | Equivalent Precise for Oracle wait sub-state. See |
| Wait States. | |
| Idle | Indicates whether Precise for Oracle categorizes the wait event as an idle event. Precise for Oracle considers certain events, such as sql*net message from client, rdbms ipc message or pmon timer, as idle because Oracle is effectively sleeping and not consuming any resources. The categorization affects the top 10 wait events displayed in the Wait Event view of the Tier or an instance. |
| Waits | Total number of occurrences of the wait event. |
| Timeouts | Total number of occurrences of the wait event that exceeded the timeout. Some events have a timeout associated with them. |
| Times Waited (Sum.) | Total time waited, displayed as a duration and bar graph. |
About the Block Contentions entity
...
When you view information on block contentions in the Association area, the following information is displayed in the table:
Table 10- 4 Block contention information in the Association area
| Column | Description |
|---|---|
| Block Contention | Name of the block contention. |
| Waits | Total number of block contention waits. |
| Times Waited (Sum.) | Total time waited, displayed as a duration and bar graph. |
About the Latch entity
The Latch entity, in the Statistics tab, displays information that is derived from the V$LATCH table.
...
When you view information on latches in the Association area, the following information is displayed in the table:
Table 10- 5 Latch information in the Association area
| Column | Description |
|---|---|
| Latch | Name of the latch. |
| Class | Precise for Oracle categorization of the latch. Precise for Oracle uses these categories to display a breakdown of latch types in the Latching view of a Tier or instance. |
| Misses | Bar graph showing the number of Willing to Wait misses and No Wait misses. Willing to Wait misses is equivalent to MISSES in V$LATCH; No Wait misses is equivalent to IMMEDIATE_MISSES in V$LATCH. |
| Willing to Wait Miss Radio | Percentage of requests to get a latch that were unsuccessful but the caller was willing to wait. Equivalent to the ratio of MISSES to GETS in V$LATCH. |
| No Wait Miss Radio | Percentage of requests to get a latch that were unsuccessful but the caller was not willing to wait. Equivalent to the ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS in V$LATCH. |
| Wait Times (Sum.) | Total time Oracle spent sleeping, waiting on the latch to become available, displayed as a duration and bar graph. |
About the Statistics entity
...
When you view statistics information in the Association area, the following information is displayed in the table:
Table 10- 6 Statistics information in the Association area
| Column | Description |
|---|---|
| Statistic | Name of the Oracle statistic. |
| Class | Oracle class of statistic. |
| Value | Statistic value. |
You can use the More... option in the Association controls to populate the association area with a subset of statistics based on class. For example, you can click Statistics>Cache or Statistics>Debug Statistics > Cache or Statistics > Debug. Some statistics are classified more than once. For example, Buffer is Pinned Count is classified as both Cache and SQL.
...
When you view database time statistics information in the Association area, the following information is displayed in the table:
Table 10- 7 Database time statistics information in the Association area
| Column | Description |
|---|---|
| Statistic | Name of the statistic. |
| Times Waited (Sum.) | Total time waited, displayed as a duration and bar graph. |
About the Operating System Statistics entity
...
When you view database time statistics information in the Association area, the following information is displayed in the table:
Table 10- 8 Operating system statistics information in the Association area
| Column | Description |
|---|---|
| Statistic | Name of the statistic. |
| Type | Type of statistic. Values are Processor or Paging |
| Processor Time | Amount of CPU time consumed. This and the Utilization column are only available for Processor-related statistics. |
| Utilization | CPU utilization. |
| Paged Bytes | Total number of bytes paged in or out. This and the Paged Bytes/sec column are only available for Paging-related statistics. |
| Paged Bytes/Sec. | Average number of bytes paged in or out per second. |
| Description | Provides a description of the statistic. |
| Anchor | ||||
|---|---|---|---|---|
|
...
You can identify a performance problem by doing one or more of the following:
- Examining top 10 wait events
- Examining latches
- About examining logical I/Os
- Examining physical I/Os
- Examining memory
- Examining parse times
| Anchor | ||||
|---|---|---|---|---|
|
We should begin our analysis with the Instance overview. This view displays the Top 10 Wait Events in a pie chart.
| Info |
|---|
The pie chart will not display what are considered to be idle events. |
See “Examining latches” on page 176 Examining latches.
To examine top 10 wait events
- In the Time Frame list, choose the period of time you want to analyze.
- In the Instance list, choose the item you want to analyze.
- In the Association area, click Wait Events to observe the wait events that are associated with the selected instance and their wait duration.
| Anchor | ||||
|---|---|---|---|---|
|
After we have examined the top 10 wait events, we now need to determine which latch or latches are contributing to the latch free event.
To examine latches, in the Association area, click Latches from the Association controls. The Statistics tab displays the latches that are associated with this instance.
| Anchor | ||||
|---|---|---|---|---|
|
One of the recommended ways to tune statements is to reduce the number of logical I/Os, because logical I/Os tend to dominate response time. It is important to understand how the number of logical I/Os varies over time. The instance statistics in the Statistics tab re-enforce the resource consumption figures observed in the Activity tab.
| Anchor | ||||
|---|---|---|---|---|
|
We can tune statements by examining the current number of consistent gets for a period of time.
...
- In the Time Frame list, choose the period of time you want to analyze.
- Open the statement you want to analyze in the Statistics tab. For our example, the Statistics tab shows the logical I/O for All Oracle Instances. We can see that the number of logical I/Os remained fairly constant on the evening of September 22nd. The logical I/Os are nearly all made up of Consistent Gets (that is, fetching read-consistent copies of data).
In the Association area, you see that the logical I/O count is dominated by ora920 on linuxi4o2. This is the instance we should look at when considering tuning of logical I/Os. Observe the number of logical/Os per execution. Notice the upward trend. This, together with the total number of logical I/Os, indicates that fewer statements are being run, but each statement is processing more data. This may indicate that the application is I/O bound because it cannot handle more than a certain number of I/Os.
Examining DB block gets
We can also tune statements by examining the number of DB block gets.
...
- In the Time Frame list, choose the period of time you want to analyze.
- Open the statement you want to analyze in the Statistics tab. In our example, we observe two major peaks in I/O activity, one on the 13th of September and one on the 17th. For our example, the Logical I/O Operations graph shows an increase on the 17th and that the logical I/O was split fairly evenly. The logical I/O was split between DB Block Gets, which are fetching the current version of the data, and DB Block Changes, which are updating the current version of the data. This indicates that there was heavy DML activity on these two days.
The Logical I/O Operations per Execution graph shows that the number of logical I/O operations per execution remained roughly constant on these two days. Together, both graphs will imply that approximately twice as many statements were executed on the 17th.
| Anchor | ||||
|---|---|---|---|---|
|
It is usually good practice to examine logical I/Os first because it is a more reliable measure than physical I/Os, due to caching effects that can complicate an analysis of physical I/Os.
...
- First examine logical I/Os. See “Examining consistent gets” on page 176. Examining consistent gets.
- Now examine physical I/Os. For our example, the Statistics tab shows sampled data from the same time period as that shown for consistent gets over time. We can see that the average read and write time follows the number of physical I/O operations reasonably close, particularly the first peak on September 22nd. This indicates that there is some contention between I/O operations, perhaps because they are all reading from different points on the same disk, causing a rise in seek time.
| Anchor | ||||
|---|---|---|---|---|
|
If you are looking to reduce the amount of memory used by Oracle, or looking to increase one of the memory buffers without increasing the overall memory footprint, you can use the memory view.
...
For this example, the Aggregated PGA Target has been set to 24 MB and a maximum of 19 MB has been used. This leaves us with very little scope by which to reduce total PGA memory.
| Anchor | ||||
|---|---|---|---|---|
|
Oracle 10g provides even more statistics than were provided by previous versions. Precise for Oracle displays graphs of some of the more important statistics, such as CPU utilization and parse times.
...
For our example, we will examine how a number of hard parses affects the parse time. If we look at the Hard Parse Ratio graph in the Main area, we can observe that the hard parse ratio increased to around 50% at 6 PM, meaning that some new statements were executed or required reloading. Consequently, the total parse time increased to around 15 minutes. Then the number of statements that required parsing remained fairly constant (shown on the Soft Parse graph), but the hard parses fell away and so did the parse times. This example explains why it is a good idea to use bind variables or cursor sharing.
Precise. Performance intelligence from click to storage. Learn more > >
...
...