This section includes the following topics:
The Statistics tab displays statistical information on your Oracle instances. All information is derived from Oracle's own statistics and loaded into the PMDB to provide you with a long-term historical view. The Statistics tab provides hundreds of raw performance counters. While examining a single counter is very important, the real value of the Statistics tab lies in examining the ready-to-use sets of graphs that display several related counters.
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.
It is not the purpose of this section to define the meaning of each Oracle statistic or how to tune them. For additional information on Oracle statistics and how to tune them, see the relevant Oracle documentation. This section does describe which Oracle statistics are available in Precise for Oracle and how to use the Statistics tab.
The Statistics tab displays information on a selected entity and its associated entities. For example, it is possible to associate to all Wait Events that are related to a specific Instance, by selecting the Wait Events entity from the Association controls.
When you open the Statistics tab from another tab, you will either be focused on an individual instance, or on the entire Tier (if you selected the ALL row in the Dashboard, or All Oracle Instances in the Activity tab). The tab is always launched at the instance level, even if the currently selected entity is not an instance.
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 Statistics tab is the Tier. You can view information on an instance by selecting it from the Instance list.
The Main area in the Statistics tab 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 on Oracle Wait Events or Oracle Memory Allocation, or I/O Activity.
The Association area in the Statistics tab provides corresponding information on the entities associated with the selected entity (displayed in the Main area). You can view information on one type of entity at a time, such as instances only, by selecting an item from the Association controls. The selection you make is reflected in the Association area only; the Main area remains unchanged.
From the Association area, you can also drill down to another entity by clicking on a row in the table. A drill-down affects the whole tab. When you drill down to another entity, the Tab heading displays the new selection; the Main area displays information on the newly selected entity, and the Association area displays the entities associated with the selected entity.
For example, when you want to view information on a specific active session, from the Association controls, click Wait Events
. The Association area changes to display information on the wait events. Note that the Tab heading and the Main area remain unchanged.
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 most tabs are structured.
The Statistics tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views. Entities that share the same set of views are grouped into one topic. They appear separately in the user interface.
The following entities are available:
The Tier, Instance, and Database entities in the Statistics tab display the same type of information. To display information on the Tier level, click All Oracle Instances from the Instance list. This provides a view of all Oracle instances in the application. This is particularly useful in a Parallel Server or RAC application, where you want to observe combined instance statistics. To display information on the instance level, select a specific instance from the Instance list.
When you view information at the Tier level, some of the views show aggregated information (for example, Physical and Logical I/O) and some show averaged information (for example, Buffer Cache Hit Ratio and PGA Size).
The Database entity lets you compare databases. The following views are available in the Main area:
You can associate the Tier, Instance, or database entity with the following entities: Wait Events, Block Contentions, Latches, Statistics, Database Time Statistics (Oracle 10g and Later only), Operating System Statistics (Oracle 10g and later only). In addition, you can associate from the Tier to its instances.
The Overview, in the Statistics tab, displays the following pie charts:
V$SYSTEM_EVENT
). The 10th ranked wait event is the sum of the time spent waiting for all the remaining wait events.sql*net message from client
, rdbms ipc message
, or pmon timer
, where Oracle is effectively sleeping and not consuming any resources.Wait Events
from the Association controls.Oracle RAC handles the in-memory data caches of each computer belonging to the same cluster as a single, global cache. The RAC Database Cache view, in the Statistics tab, displays statistics that can help you determine the coherency of the global cluster cache.
The following graphs are displayed:
Table 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:
|
The Instance in Database view, in the Statistics tab, displays the following additional RAC database information:
Table 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. |
The Load view, in the Statistics tab, displays the bar graphs for the selected time period. The Load view displays the following bar graphs:
The Logical I/O view, in the Statistics tab, displays bar graphs for the selected time period. The Logical I/O view displays the following bar graphs:
The Physical I/O (Datafiles) and the Physical I/O (Temp Files) views, in the Statistics tab, display bar graphs for the selected time period.
The Physical I/O (Datafiles and Temp Files) display the following bar graphs:
The Memory view, in the Statistics tab, displays bar graphs for the selected time period. The Memory view displays the following bar graphs:
V$SGASTAT
. From Oracle 9i onwards, the size of the buffers in the SGA may vary dynamically over time as Oracle tries to adjust to the workload. Using this graph, it may be possible to return some of the SGA memory to the operating system. Alternatively, it is possible to offset an increase in one area with a decrease another area, without increasing the total SGA size. For example, you may be able to increase the size of the Buffer Cache at the expense of the Java Pool.PGA Size. It displays the total amount of PGA memory allocated to all dedicated server processes (derived from V$PGASTAT
from Oracle 9i onwards), plus the Aggregated PGA Target (defined by the pga_aggregate_target
Oracle initialization parameter). If you observe that the total PGA allocated has exceeded the PGA target many times, consider increasing the PGA target.
PGA size data is only displayed for Oracle 9.2 and later. |
The Buffer Cache view, in the Statistics tab, displays bar graphs for the selected time period. The Buffer Cache view displays the following bar graphs:
The Parsing view, in the Statistics tab, displays bar graphs for the selected time period. The Parsing view displays the following bar graphs:
The Sorting view, in the Statistics tab, displays bar graphs for the selected time period. The Sorting view displays the following bar graphs:
The Redo Activity view, in the Statistics tab, displays bar graphs for the selected time period. The Redo Activity view displays the following bar graphs:
The Checkpoints view, in the Statistics tab, displays bar graphs for the selected time period. The Checkpoints view displays the following bar graphs:
The SQL*Net view, in the Statistics tab, displays bar graphs for the selected time period. The SQL*Net view displays the following bar graphs:
If these figures are unusually high, it may be an indication that queries are returning more data than necessary, rather than the minimum set of rows and columns. Another possibility is that the application may be inefficient in terms of networking; you may want to examine the SQL*NET parameters, such as the Session Data Unit (SDU) size.
The Latching view, in the Statistics tab, displays bar graphs for the selected time period. The Latching view displays the following bar graphs:
The Operating System (Oracle 10g and Later) view, in the Statistics tab, displays bar graphs for the selected time period.
The Operating System view displays the following bar graphs:
The System Time Model (Oracle 10g and Later) view, in the Statistics tab, displays graphs for the selected time period. The System Time Model view displays the following graphs:
The Wait Event entity, in the Statistics tab, displays Oracle wait event information that is derived from the V$SYSTEM_EVENT table.
The Over Time view, in the Statistics tab, displays graphs for the selected time period. The Over Time view displays the following graphs:
You cannot associate a wait event with any other entity.
When you view information on wait events in the Association area of the Statistics tab, the information is displayed in a table.
The following table shows Wait event information in the Association area.
Table 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. |
The Block Contention entity, in the Statistics tab, displays information that is derived from the V$WAITSTAT table.
The Over Time view, in the Statistics tab, displays a graph for the selected time period. The Over Time view displays the following graph:
When you view information on block contentions in the Association area, the following information is displayed in the table:
Table 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. |
The Latch entity, in the Statistics tab, displays information that is derived from the V$LATCH table.
The Over Time view, in the Statistics tab, displays graphs for the selected time period. The Over Time view displays the following graphs:
When you view information on latches in the Association area, the following information is displayed in the table:
Table 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. |
The Statistics entity, in the Statistics tab, displays information that is derived from the V$SYSSTAT table.
The Over Time view, in the Statistics tab, displays a bar graph for the selected time period. The Over Time view displays the following bar graph:
You cannot associate statistics with any other entity.
When you view statistics information in the Association area, the following information is displayed in the table:
Table 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. Some statistics are classified more than once. For example, Buffer is Pinned Count is classified as both Cache and SQL.
The Database Time Statistics (Oracle 10g and later) entity, in the Statistics tab, displays information that is derived from the V$SYS_TIME_MODEL table.
The Over Time view, in the Statistics tab, displays a graph for the selected time period. The Over Time view displays the following graph:
You cannot associate database time statistics with any other entity.
When you view database time statistics information in the Association area, the following information is displayed in the table:
Table 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. |
The Operating System Statistics (Oracle 10g and later) entity, in the Statistics tab, displays information that is derived from the V$OSSTAT table.
The Overview, in the Statistics tab, displays processor-related and paging-related statistics for the selected time period.
Precise for Oracle only provides operating system statistics as reported by Oracle; some operating systems may not necessarily report accurate statistics, and Oracle may provide different statistics depending on the specific operating system. |
You cannot associate operating system statistics with any other entity.
When you view database time statistics information in the Association area, the following information is displayed in the table:
Table 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. |
To determine whether Oracle is performing optimally, it is necessary to monitor the health of your instances, and at times to tune your instances. The Statistics tab provides many performance counters grouped into several predefined graphs that display related counters that enable you to locate performance problems in your system.
You can identify a performance problem by doing one or more of the following:
We should begin our analysis with the Instance overview. This view displays the Top 10 Wait Events in a pie chart.
The pie chart will not display what are considered to be idle events. |
See Examining latches.
To examine top 10 wait events
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.
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.
We can tune statements by examining the current number of consistent gets for a period of time.
To examine consistent gets
We can also tune statements by examining the number of DB block gets.
To examine DB block gets
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.
To examine physical I/Os
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.
To examine memory
The Association area lists all Oracle events that are in the Shared Pool Wait category. As would be expected (for a shared pool that is not full), there has been very little wait time.
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.
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.
To examine 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.