This section includes the following topics:
■ About the Statistics tab
■ How the Statistics tab is structured
■ About the entities you can examine
■ How the Statistics tab can help you identify performance problems
About the Statistics tab
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.
How the Statistics tab is structured
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.
About the Main area in the Statistics tab
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.
About the Association area in the Statistics tab
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” on page 22.
About the entities you can examine
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:
■ Tier, Instance, and Database entities
■ Wait events entity
■ Block contentions entity
■ Latches entity
■ Statistics entity (Statistics tab)
■ Database time statistics entity (Oracle 10g and later)
■ Operating system statistics entity (Oracle 10g and later)
About the Tier, Instance, and Database entities
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:
■ Overview
■ RAC Database Cache
■ Instances in Database
■ Load
■ Logical I/O
■ Physical I/O (Datafiles) and physical I/O (Temp files)
■ Memory
■ Buffer cache
■ Parsing
■ Sorting
■ Redo activity
■ Checkpoints
■ SQL*Net
■ Latching
■ Operating system (Oracle 10g and later)
■ System Time Model (Oracle 10g and later)
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.
About the overview of a Tier, instance, or database
The Overview, in the Statistics tab, displays the following pie charts:
■ Top 10 Wait Events—displays the top 10 Oracle wait events that occurred during the selected time period, ranked by the amount of time spent waiting for these events (derived from V$SYSTEM_EVENT). The 10th ranked wait event is the sum of the time spent waiting for all the remaining wait events.
The pie chart excludes wait events that are classified by Precise for Oracle as idle events, such as sql*net message from client, rdbms ipc message or pmon timer, where Oracle is effectively sleeping and not consuming any resources.
To see a full list of Oracle wait events and their classifications, click Wait Events from the Association controls.
■ Physical I/O Operations
This pie chart illustrates the proportions and volume of read and write requests to datafiles and temp files during the selected time period.
About RAC database cache information
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 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.
Table 10-1 Graphs of the RAC Database Cache view
Graph Description
Avg. GC Blocks Access Latency
(Msec.) It displays the following average end-to-end elapsed time, or latency for a global cache block request:
■ Average current blocks receive time (in milliseconds)
■ Average CR blocks receive time (in milliseconds)
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:
■ Average global cache CR block send time
■ Average global cache CR block flush time
■ Average global cache CR block build time
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:
■ Average global cache current block send time
■ Average global cache current block flush time
■ Average global cache current block pin time
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 Load view, in the Statistics tab, displays the bar graphs for the selected time period. The Load view displays the following bar graphs:
■ Logons
It displays the number of user sessions.
■ Executions per Logon
It displays the average number of statements executed by a session.
■ Commits and Rollbacks
It displays the number of transactions either committed or rolled back.
■ Executions per Transaction
It displays the average number of statements executed in a transaction.
About the logical I/O of a Tier, instance, or database
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:
■ Logical I/O Operations
It displays the total number of logical I/O operations broken down into the number of DB Block Gets, Consistent Gets, DB Block Changes and Consistent Changes. The first two represent logical read operations, while the latter two represent logical write operations.
■ Logical I/O Operations per Execution
It displays the average number of logical I/O operations per executed statement.
About the physical I/O of a Tier, instance, or database
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:
■ Physical I/O Operations
It displays the total number of physical read and write requests to all datafiles (or temp files).
■ Avg. Physical Read Time (Msec.)
It displays the average duration of a physical read.
■ Avg. Physical Write Time (Msec.)
It displays the average duration of a physical write.
About the memory of a Tier, instance, or database
The Memory view, in the Statistics tab, displays bar graphs for the selected time period. The Memory view displays the following bar graphs:
■ SGA Size
It displays the size of the fixed and variable parts of the SGA together with the amount used and available. This information is derived from 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.
Note: PGA size data is only displayed for Oracle 9.2 and later.
About the buffer cache of a Tier, instance, or database
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:
■ Buffer Cache Hit Ratios
It displays the buffer cache hit ratio, expressed as a percentage, for each of the buffer pools. From Oracle 9i onwards, different tablespaces may be defined with different block sizes. Each block size has its own buffer pool.
■ Waits on Buffer Cache
It displays the number of waits on all pools, divided into the number of buffer busy waits and free buffer waits.
About parsing information for a Tier, instance, or database
The Parsing view, in the Statistics tab, displays bar graphs for the selected time period. The Parsing view displays the following bar graphs:
■ Hard Parse Ratio
It displays the percentage of statements that required a hard parse as a proportion of total parses. A high figure may indicate that Oracle is unable to share statements in the shared pool, for example, if the application uses literals instead of bind variables.
■ Soft Parses
It displays the number of statements requiring a soft parse.
■ Library Cache Hit Ratio
It displays the percentage of statements that were already in the shared pool when Oracle began parsing them. A low figure may indicate that the shared pool is too small, or that the applications are not using bind variables or cursor sharing.
■ Parse Times (Sum.) - Oracle 10g
It displays the total duration of all parse times for Oracle 10g. This information is taken from
V$SYS_TIME_MODEL.
About sorting information for a Tier, instance, or database
The Sorting view, in the Statistics tab, displays bar graphs for the selected time period. The Sorting view displays the following bar graphs:
■ Disk Sorts
It displays the number of sorts that required a write to disk and could not be done entirely in memory.
■ Disk Sorts Ratio
It displays the percentage of sorts requiring a write to disk compared with the total number of sort operations
(in-memory and to disk). It is much faster to perform sorts entirely in memory if possible and so a high ratio may indicate a performance problem.
■ Rows Sorted
It displays the total number of rows sorted.
About the redo activity of a Tier, instance, or database
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:
■ Generated Redo Log
It displays total amount of redo log data generated.
■ Redo Wait Time
It displays the amount of time that applications waited on Redo Log Buffer wait, Log Switch, and Clear wait. Note that these are Precise for Oracle wait categories—the underlying Oracle waits are log file sync, log file switch completion, and so on.
■ Redo Writes
It displays the total number of writes to the redo log buffer.
■ Commits and Rollbacks
It displays the total number of transactions committed or rolled back.
About the checkpoints of a Tier, instance, or database
The Checkpoints view, in the Statistics tab, displays bar graphs for the selected time period. The Checkpoints view displays the following bar graphs:
■ Background Checkpoints
It displays the total number of checkpoints completed or interrupted.
■ Physical Writes
It displays the total number of checkpoint physical writes and other physical writes to disk. This parameter provides an understanding of the checkpoint contribution to total write activity.
About SQL*Net information for a Tier, instance, or database
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:
■ SQL*Net Client Throughput
It displays the amount of data sent from the Oracle server process to the client process.
■ SQL*Net Client Roundtrips
It displays the total number of client requests. This is mainly the number of times a cursor is opened and executed, and the number of fetches required to retrieve all the data.
■ SQL*Net DB Link Throughput
It displays the amount of data sent from the Oracle server process to a remote Oracle server process using a database link.
■ SQL*Net DB Link Roundtrips
It displays the total number of messages sent/received from the Oracle server process to a remote Oracle server process using a database link.
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.
About latching information for a Tier, instance, or database
The Latching view, in the Statistics tab, displays bar graphs for the selected time period. The Latching view displays the following bar graphs:
■ Wait Times (Sum.)
It displays the total amount of time that Oracle has spent waiting for a latch to become available. The time is broken down into various categories according to latch type. To see the full list of latches and their categorization, click Latches from the Association controls in the Association Area. Note that this categorization is defined within Precise for Oracle.
About operating system information for a Tier, instance, or database
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:
■ CPU Utilization
It displays the percentage of all the available CPUs on the server used by Oracle, broken down into system time and user time.
■ Paging
It displays the average number of bytes paged-in and paged-out per second by Oracle.
About system time model information for a Tier, instance, or database
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:
■ Oracle (DB and Background) Time
It displays Oracle time broken down into DB CPU time, elapsed CPU background time, and elapsed non-CPU
background time
■ DB Elapsed Time
It displays database time only, broken down into sequence load elapsed time, parse time, SQL execute time, connection management time, PL/SQL execution time, PL/SQL compilation time, inbound PL/SQL RPC time and Java execution time.
About the Wait Event entity
The Wait Event entity, in the Statistics tab, displays Oracle wait event information that is derived from the
V$SYSTEM_EVENT table.
Examining a wait event over time
The Over Time view, in the Statistics tab, displays graphs for the selected time period. The Over Time view displays the following graphs:
■ Event Waits vs. Timeouts
It displays the total number of occurrences of the wait event vs. the number that exceeded the timeout.
■ Times Waited (Sum.)
It displays the total time waited.
You cannot associate a wait event with any other entity.
About information on wait events
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 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” on page 35.*
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
The Block Contention entity, in the Statistics tab, displays information that is derived from the V$WAITSTAT table.
About a block contention over time
The Over Time view, in the Statistics tab, displays a graph for the selected time period. The Over Time view displays the following graph:
■ Block Contention Waits vs. Time Waited
It displays the total number of block contentions vs. the total time waited. You cannot associate a block contention with any other entity.
About information on block contentions
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.
About a latch over time
The Over Time view, in the Statistics tab, displays graphs for the selected time period. The Over Time view displays the following graphs:
■ Wait Times (Sum.)
It displays the total time Oracle spent waiting for the latch to become available.
■ Latch Miss Ratio
It displays the number of requests to get a latch which were unsuccessful. It includes Willing to Wait Miss Ratio and No Wait Miss Ratio.
■ Latch Misses
It displays the total number of latch misses. It includes the number of Willing to Wait Misses and No Wait Misses. You cannot associate a latch with any other entity.
About information on latches
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.
Table 10-5 Latch information in the Association area
Column Description
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 Ratio 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 Ratio 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
The Statistics entity, in the Statistics tab, displays information that is derived from the V$SYSSTAT table.
About statistics over time
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:
■ Statistics
Displays the value of the statistic.
You cannot associate statistics with any other entity.
About statistics information
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. Some statistics are classified more than once. For example, Buffer is Pinned Count is classified as both Cache and SQL.
About the Database Time Statistics entity
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.
About database time statistics over time
The Over Time view, in the Statistics tab, displays a graph for the selected time period. The Over Time view displays the following graph:
■ Times Waited (Sum.)
Displays the total time waited.
You cannot associate database time statistics with any other entity.
About database time statistics information
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
The Operating System Statistics (Oracle 10g and later) entity, in the Statistics tab, displays information that is derived from the V$OSSTAT table.
About operating system statistics
The Overview, in the Statistics tab, displays processor-related and paging-related statistics for the selected time period.
Note: 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 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.
How the Statistics tab can help you identify performance problems
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
■ Examining top 10 wait events
■ Examining latches
■ About examining logical I/Os
■ Examining physical I/Os
■ Examining memory
■ Examining parse times
Examining top 10 wait events
We should begin our analysis with the Instance overview. This view displays the Top 10 Wait Events in a pie chart.
Note: The pie chart will not display what are considered to be idle events.
See “Examining latches” on page 176. To examine top 10 wait events
1 In the Time Frame list, choose the period of time you want to analyze.
2 In the Instance list, choose the item you want to analyze.
3 In the Association area, click Wait Events to observe the wait events that are associated with the selected instance and their wait duration.
Examining latches
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
1 In the Association area, click Latches from the Association controls. The Statistics tab displays the latches that are associated with this instance.
About examining logical I/Os
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.
Examining consistent gets
We can tune statements by examining the current number of consistent gets for a period of time. To examine consistent gets
1 In the Time Frame list, choose the period of time you want to analyze.
2 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.
To examine DB block gets
1 In the Time Frame list, choose the period of time you want to analyze.
2 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.
Examining physical I/Os
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
1 First examine logical I/Os.
See “Examining consistent gets” on page 176.
2 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.
Examining memory
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
1 In the Time Frame list, choose the period of time you want to analyze.
2 Open the statement you want to analyze in the Statistics tab. For our example, the Statistics tab shows data that was gathered over a 16-day period. All 32MB of the Java pool is free; all 8 MB of the large pool is free, and at least
15 MB of the shared pool is free.
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.
Examining parse times
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
1 In the Time Frame list, choose the period of time you want to analyze.
2 Open the statement you want to analyze in the Statistics tab.
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.