Page History
...
For more information on configuring permissions and roles see the Precise Administration Guide.
How information is collected
Precise for Oracle collects and displays performance data that enables quick and accurate performance monitoring and analysis. To understand the information displayed in the various graphs, tables, and views, it is important to understand how information is collected.
Precise for Oracle Collector sampling methods
The Precise for Oracle Collector is a non-Oracle process that can collect data from both the server's operating system and the Oracle instance. This is important because both the server's operating system and the Oracle instance contain data necessary for the solution of a performance problem, but neither is aware of the other's internal state.
For example, the Oracle instance may have data indicating that a specific process is waiting in the operating system, but it does not have data indicating why. Only the servers operating system has data indicating that the process is waiting for CPU, memory, and so on.
A product that can support all phases of tuning from the entire system down to individual applications and objects must meet the following criteria:■ It
- It must be able to collect data from both the server’s operating system and the Oracle database system.
...
- It must be fully independent of the Oracle system that it monitors to provide a true picture of Oracle activity.
...
- It must be able to achieve high sampling rates. Precise for Oracle meets all of these criteria.
Non-intrusive monitoring of the Oracle instance
Most monitors collect data on user activity and system status intrusively by issuing queries to the Oracle X$ and V$ dynamic performance views. These queries consume Oracle resources and tend to be neglected by Oracle when system load is heavy and collecting data on Oracle is most important.
Precise for Oracle's Collector gathers data non-intrusively by sampling data in the System Global Area (SGA) of the
Oracle instance. This makes monitoring the Oracle instance independent of the Oracle system.
How Precise for Oracle monitors availability
The Precise for Oracle Collector monitors the Oracle instance. If the instance status (Available/Unavailable) has changed, a message code is sent by the Collector and causes the current availability of the instance saved in the PMDB to be changed. In addition, the availability of the instance is saved, over time, in the PMDB. During a planned downtime period, the instance availability is shown as it was available during this period.
Note: If the Precise for Oracle Collector is down, the instance availability status reported is as Unknown.
High sampling rates
The Collector can be configured to sample data using a high sampling rate. This enables the Collector to collect data on entities that are executed for only very short periods of time.
High resource consumption can be the result of an entity (such as a program or statement) with heavy resource demands running for an extended period of time (long duration, low frequency). It can also be the result of an entity with modest resource demands running often for a short period of time (short duration, high frequency). Identifying the latter entity as the cause of a performance problem can be difficult if the Oracle instance is sampled using a low sampling rate. If execution duration is short enough, complete executions of the problematic entity can take place between samples, and data on these executions will not be included in the overall picture of system performance.
Wait States
Fundamental to the operation of Precise for Oracle is the capturing of wait state information to identify resource consumption. The Precise for Oracle Collector, using its unique collection technology, samples every Oracle session and SQL statement, and at each sample point determines the Oracle wait event. It also correlates the session information with the corresponding operating system process information to capture additional wait states. In fact, it employs a two-Tier categorization: wait state and wait sub-state. It defines the status of sessions currently connected to Oracle by these states. By aggregating this wait state information over time, Precise for Oracle can give an accurate time-based, resource consumption profile for every statement, table, session, program, and so on, up to the instance level.
This wait state information forms the basis of the resource consumption graphs, particularly in the Dashboard, Current, and Activity tabs.
Precise for Oracle displays the following resource consumption graphs:■ Overall
- Overall activity
...
- In Oracle
...
- I/O wait
Overall activity
The Overall Activity resource consumption graphs give the total time that a session or a set of sessions is connected to Oracle. During that time, the session may be actively executing statements within Oracle, or it may be idle. Overall Activity can help answer the question: Is the application spending its time in Oracle or elsewhere?
Overall Activity is divided into the following states:■ Request
- Request Wait
...
- . The session is idle. The underlying Oracle wait state is typically an SQL*Net message from the client or pipe_get, and the client is performance-related and not Oracle-related activity.
...
- In Oracle (Sum.)
...
- . The total time sessions spent actively executing statements and PL/SQL within Oracle. This is further broken down into In Oracle states in the In Oracle resource consumption graphs.
...
- Parallel Query Sync
...
- . The time that a parallel query coordinator and parallel query slave sessions spent waiting for other slaves to complete an operation. It is not shown as time spent actively executing In Oracle because the parallel query sessions are effectively idle during this time.
...
- Miscellaneous. The time not accounted for by one of the other states. It is usually due to session initialization or termination. For example, a dead session not yet cleaned up by Oracle PMON may have some time in this state.
In Oracle
The In Oracle resource consumption graphs give a breakdown of how Oracle is spending its time executing statements and PL/SQL. The wait states with the longest duration indicate the bottlenecks within Oracle. Reducing the duration of these wait states will improve the response time or alternatively increase the throughput.
Precise for Oracle employs a two-level hierarchy of In Oracle wait states. Each In Oracle state has one or more In Oracle sub-states. In turn, sub-states have one or more Oracle or operating system wait states. States and sub-states relate to each other, as described in the following table. As a consequence, Precise for Oracle displays two types of In Oracle resource consumption graphs: either In Oracle state or In Oracle sub-state.
The following table lists the existing In Oracle states.
Table 2-4 In 4 In Oracle states
In Oracle State Icon In Oracle
Sub-State Description Typical Oracle Events
Using CPU
Using CPU The session is using CPU
executing SQL. N/A
I/O Wait Direct I/O Wait The session is waiting on direct
I/O. direct path read; direct path write
I/O Wait
Scattered Scattered I/O Wait The session is waiting on scattered I/O. db file scattered read
I/O Wait Sequential I/O Wait The session is waiting on sequential I/O. db file sequential read;
control file sequential read
I/O Wait
Other I/O Wait The session is waiting on another type of I/O. db file parallel write; log file parallel write; control file parallel write; asynch disk I/O
I/O Wait Note: The I/O sub-states Direct I/O Wait, Scattered I/O Wait, Sequential I/O Wait, and Other
I/O Wait only apply to Oracle files, objects, and storage entities. For other entities, the sub-state is simply shown as I/O Wait.
Application Lock Wait
Row Lock Wait The session is waiting on row locks. Enq: TX - row lock contention (Oracle 10g)
Application Lock Wait Table Lock Wait The session is waiting on table locks. Enq: TM - contention (Oracle
10g)
Internal Lock Wait
Other Other Lock Wait The session is waiting on other Oracle locks, such as streams, latches, or internal locks. STREAMS events; enqueue; other enq: events; latch activity; latch free; latch: events
Table 2-4 In Oracle states
In Oracle State Icon In Oracle
Sub-State Description Typical Oracle Events
Application Lock Wait Note: Enqueue may appear in Row Lock Wait and Table Lock Wait, depending on the values of the P1, P2, and P3 parameters. The Collector captures these parameters and assigns the wait state accordingly. This can be seen in the Current and Activity tabs. However, the Collect Instance Statistics PMDB process does not capture the parameters so that in the Statistics tab, the wait is always shown as Other Lock Wait.
Operating System
Waits
CPU CPU Wait The session is on the operating system run queue waiting for CPU. N/A
Operating System
Waits Memory Wait The session is waiting on a memory operation, such as page-in or page-out. This sub-state may also include memory-mapped I/O. N/A
Operating System
Waits
Other Other Host Wait The session is waiting on another type of OS operation, such as sorting. N/A
RAC/OPS/Other Waits RAC/OPS Wait The session is waiting on RAC or
OPS synchronization. DFS events; DLM events; gc events; gcs events; ges events; global events;
RAC/OPS/Other Waits
Other Other Wait The session is waiting on another, less common lock event. N/A
Commit & Checkpoint
Waits Rollback Segment
Wait The session is waiting on a rollback segment operation. alter rbs offline; undo segment events
Commit & Checkpoint
Waits
Redo Log Buffer Wait The session is waiting on a redo log buffer operation. log buffer space; log file syn
Commit & Checkpoint
Waits Log Switch and Clear
Wait The session is waiting for a log switch and clear operation to occur. checkpoint events; log file switch events
Oracle Server Wait
Parallel Parallel Query Server
Wait The session is waiting for a parallel query server to become available. PX create server; PX server shutdown; parallel query; create server; parallel query; dequeue wait
Oracle Server Wait Parallel Query Sync. Wait The session is waiting for a parallel query server to get synchronized with another parallel query server. Other PX events; other parallel query events
Table 2-4 In Oracle states
In Oracle State Icon In Oracle
Sub-State Description Typical Oracle Events
Oracle Server Wait
Resource Resource Mgr Wait The session is waiting for a resource to become available (only if the database resource manager is enabled). resmgr events
Oracle Server Wait [ Multi-Threaded Server
Wait The session is waiting for a
multi-threaded server to become available. alter system set dispatcher; alter system set mts_dispatcher
Oracle Server Wait
Background Background Process
Wait The session is waiting for an Oracle background process, such as DBWR or LGWR. LGWR events
Client Request Wait Request wait Request wait is generally considered as an idle event as the Oracle server process is waiting for the client process. Client messages
Communication Wait
Oracle Oracle/Oracle
Communication Wait The session is waiting for another Oracle instance, usually as a result of a DBLINK event or a standby database. SQL*Net break/reset to DBLINK; SQL*Net message from DBLINK; SQL*Net message to DBLINK; SQL*Net more data from DBLINK; SQL*Net more
data to DBLINK
Communication Wait Oracle/Client
Communication Wait The session is waiting for data sent to the client, usually as a result of a selected statement, or for data sent from a client, usually as a result of bind variables. SQL*Net break/reset to client; SQL*Net message to client; SQL*Net more data from client; SQL*Net more data to client
Internal Lock Wait
Shared Shared Pool Wait The session is waiting for a shared pool operation. library cache load lock; library cache lock; library cache pin
Internal Lock Wait Buffer Wait The session is waiting for a buffer cache operation. buffer busy; buffer busy wait;
buffer latch
I/O wait
I/O Wait resource consumption graphs are shown for Oracle files, objects, and storage entities. They give a breakdown of the following I/O wait into:■ Direct
- Direct I/O Wait
...
- Scattered I/O Wait
...
- Sequential I/O Wait
...
- Other I/O Wait
Modified In Oracle graphs are shown for Oracle objects such as tables and indexes. In addition to the listed I/O
sub-states, Precise for Oracle records the following sub-states for an object:■ Buffer
- Buffer Wait
...
- Table Lock Wait
...
- Row Lock Wait
The other In Oracle wait states, such as Using CPU, do not apply to an object.
See “In Oracle” on page 36.
Statement identifier vs. Oracle hash value
SQL statements in Precise for Oracle are assigned a numeric identifier (a 20-digit number that is divided into 4 sections, with dot separators) or a user-defined name. The numeric identifier is similar to, but not the same as, the hash value that Oracle assigns to a SQL statement. When Precise for Oracle calculates the numeric identifier, it ignores white space and case, unlike Oracle. It also takes into account whether Collapse Statements is switched on, in which case string literals are replaced with place holders or pseudo-bind variables.
Executions vs. opens and fetches
Precise for Oracle employs the following methods to determine how many times a statement has been executed.■ At
- At each sample point, the Precise for Oracle Collector examines the equivalent area of the SGA that V$SQL maps on to and captures the number of executions of the statement that Oracle has recorded. (It also captures buffer gets and rows processed.
Note: Precise for Oracle only increments the counters when the statement has finished executing similar to V$SQL.
...
- At each sample point, the Precise for Oracle Collector examines whether the session is opening or fetching from a cursor, that is executing a statement. Oracle does not provide enough information to allow the Collector to determine whether the session is opening (executing) the statement or fetching results, and therefore it counts both. This potential over-reading of the actual execution count may be offset by the fact that any sampling technology may miss some executions, particularly for fast-running statements.
Where possible, Precise for Oracle displays the number of executions that Oracle has recorded because it is more accurate. However, V$SQL only contains statistics about the statement; it does not tell you which session ran it. Therefore, it is not possible to accurately divide the number of executions between the sessions. This means that in the user interface, wherever you drill down in the context of a session (and this includes program and user), you will see opens and fetches. Where you drill down directly from the instance level to a statement, you will see executions. The Current tab, for example, displays opens and fetches because you always view a statement in the context of a session, whereas the Activity tab may display either executions or opens and fetches, depending on your navigation path. Other derived values, such as average response time, are only shown if the number of executions is available.
In Oracle time vs. Duration time
In Oracle time indicates the period that the entity consumed within the monitored box, not including IDLE time (sleep, waiting for user requests, etc.).
Duration time is the "wall clock time", including idle and non-idle consumption periods. The following calculations are used:
Table 2-5 Calculations5 Calculations
Calculations Description
In Oracle time (Avg.) In Oracle time divided by the amount of executions.
Duration time (Avg.) Duration time divided by the amount of executions or sessions (the parameter depends on the entity that is used).
The following is an explanation based on an example statement using a parallel query.
When a session runs a statement for a table with parallel degree of n (4 degrees for example), the Oracle engine creates n new sessions to serve it (these sessions are called: "Parallel Server Agents"). Each of the parallel server agents runs as an individual entity and analyzes part of the requested data, while the original session (the one that you logged in with) is called the coordinator session. The last goal is to receive all the information from the parallel server agents and summarize (sort/filter/calculate/join) it. Usually the coordinator session will not perform an "In Oracle"
operation. In fact it is waiting "All the time" when the parallel server agents are working. These parallel server agents can perform I/O / Locks / RAC operations - like any other regular session. These sessions (4 in our example) have the same session identifiers as the coordinator session. Therefore in the collector's aggregation phase all the sessions are aggregate into one row. For example, if a specific statement runs for 1 hour (wall clock time) you can see in the Activity tab that the statement consumed 4 hours "In Oracle" time. You can also find the parallel degree of the statement within the Activity tab.
Oracle statistics usage within Precise for Oracle
Precise for Oracle captures standard Oracle statistics that you can see in the V$SESSTAT table. However, the Collector reads the values in a low-overhead fashion directly from the SGA memory that the V$ tables map onto. Where necessary, the statistics are divided between the sessions executing each SQL statement and then aggregated to the user, program, and instance levels. You will see these statistics in the Current and Activity tabs, displayed as name-value pairs. Each statistic represents a different counter and has its own units. See Oracle’s documentation for more information on these statistics.
Precise Interpoint - how resource consumption of packaged applications is examined
Precise Interpoint is the only tool that helps the ERP administrator identify the exact cause of problems in ERP
applications.
To tune any application, you need to identify specific users and programs and be able to review their activities and resource consumption in relation to the statements that they execute. In this respect, packaged application tuning poses several problems.
First, ERP applications identify themselves to Oracle through only one user ID or a few user IDs, and by only a few program names. When faced with a problematic statement, you have no way of identifying the user who ran the statement, or the transaction from which the statement was executed.
Second, the statements executed by ERP transactions can rarely be changed. This means that your tuning can only focus on the objects referenced by these statements.
Precise Interpoint provides solutions to ERP application tuning restrictions by allowing you to display explicit information on the activities and resource consumption of your packaged application components. This lets you identify the ERP users, transactions, reports, and so on, that consume the most Oracle resources.
You can look at the information about a packaged application work type (a type of working process executed by an internal packaged application) collected during a historical time frame. The collected information tells you about the application work type, its resource consumption, and the activities it performed in Oracle.
After using Precise Interpoint to detect the biggest resource consumers, you can drill down to the statements that they executed and the objects they accessed. After ascertaining the objects referenced by these statements, you can use the What-If tab to tune them, thus consequently reducing the response times of your statement and improving the performance of your packaged applications.
Currently, Precise Interpoint supports the technologies Oracle Applications, SAP, PeopleSoft, and Siebel.
Precise Interpoint replaces some of the entities that appear in the base Precise for Oracle product with entities that are relevant to the ERP technology.
The following table shows how Precise Interpoint maps the entities in each ERP application.
You can find information about how to install Oracle Applications, SAP, PeopleSoft, and Siebel Tier Collectors in the
Precise Installation Guide.
Table 2-6 Mapping 6 Mapping of Precise Interpoint entities per technology
Technology Entity without Interpoint
Installed Entity with Interpoint Installed
Oracle Applications User User
Oracle Applications Program Form
Table 2-6 Mapping of Precise Interpoint entities per technology
Technology Entity without Interpoint
Installed Entity with Interpoint Installed
Oracle Applications Module Application
Oracle Applications Action Request
Oracle Applications Work Type: OA Batch, OA Interactive, OA Self-Service, OA CRM, or Other
SAP User User
SAP Program SAP Transaction
SAP Module Function
SAP Action Screen
SAP Work Type: SAP Dialog, SAP Batch, SAP Spool, or SAP Lock/Enqueue
PeopleSoft User User
PeopleSoft Machine Machine
PeopleSoft Work Type: PS Interactive, PS Batch, or Other
Siebel Program Siebel Views
Siebel Module Siebel Business Objects
Siebel Action Siebel Applications
Siebel Work Type: Interactive, Task Batch, Task Background, or
Task Interactive
Precise for Storage, Oracle version - how resource consumption of storage devices is examined
Precise for Storage, Oracle version supports EMC Symmetrix and Clariion, and Hitachi HDS. It offers a unique method to identify I/O problems in your attached storage and reports I/O wait time instead of just I/O counters. This lets you easily identify the resource that is actually being waited for and tune it instead of guessing by rules of thumb. Furthermore, you can associate the storage device with the applications and statements that are waiting on I/O.
You may look at a device, for example, with a relatively high throughput and I/O rate. However, does this mean that the device causes performance problems in the Oracle applications? Even though the device seems to be very active, it does not necessarily follow that the applications are waiting for I/O on this device.
It becomes even harder to decide whether I/O problems are occurring according to the activity parameters for the device in the Oracle application. Oracle uses mechanisms like sequential read to read in advance blocks required by applications before they actually demand them and background write to write data to files using background processes. These mechanisms may increase the volume of activity on a device without causing I/O problems.
Because Precise for Storage, Oracle version, reports I/O wait time and not just I/O counters, you are able to find out if the applications are actually waiting for the device.
Performance problems caused by high I/O wait time may be tuned on the following levels:■ Oracle
- Oracle files
...
- . After finding Oracle files for which I/O wait time is high, you can improve I/O wait time using different techniques. For example, you can partition a table residing in the file, separate tables residing in the file into different files, or decrease the number of extents making up the file.
...
- Devices. After finding devices for which I/O wait time is high, you can improve I/O wait time using different techniques. For example, you can move a heavily used Oracle file to a less active device, change the configuration, and relocate devices with high waiting time to different physical devices or balance the volume of the disk directors.
It is important to understand how Precise for Oracle can aid you with this latter activity. Precise for Oracle provides two different views of device performance. The Activity tab shows the time waited on devices used by the currently selected instance: you use this tab to find the performance bottlenecks within your application. The Objects tab shows performance counters for all devices, not just those used by the current instance. This gives you a view of the overall performance of the storage arrays and lets you choose less busy devices to move your Oracle files to.
Launching Precise for Oracle from Precise StartPoint
Precise is a Web-based application. You can access the Precise user interface using the Internet Explorer browser, version 6.0 or later. The syntax of the Precise URL address is: http://<server>:<port>, where <server> refers to the Precise FocalPoint server and <port> refers to the port number used by the GUI Web server. By default, the port number is 20790 (20760 for J2EE). For example: http://beanotown:20790.
This URL provides secure access to the StartPoint using authorized roles. From here, you can launch all Precise products. It gives you a quick overview of the status of your applications and access to the AdminPoint, where you can perform various management tasks (see the Precise Administration Guide for details).
Note: You must have local administrator privileges on the server where the StartPoint is running.
To launch your product using Precise StartPoint1 Type
- Type the address of the StartPoint user interface into the Address bar of your browser and click Enter. The
...
- Precise login page opens. The login page provides secure access to Precise and to your specific product.
...
- Specify your authorized role name and password. By default, both role name and password are admin. For more information about role names, see the Precise Administration Guide.
...
- Click Login. The StartPoint page opens. This is the Precise home page.
...
- On the Product Selection bar, from the drop-down list, select the product you want to launch.
Precise. Performance intelligence from click to storage. Learn more > >
...