 |
Elapsed Time
Description
This is the amount of total elapsed time that has been used for all executions of this SQL cursor. This is a useful metric in tuning SQL. This time number includes both CPU and wait time. Wait time may include time spent waiting for I/O, or a particular Oracle wait event such as the shared pool latch. Further investigation on how to best tune a particular SQL statement can be done by using the Explain Plan drilldown, or if you are on NT, the Tune SQL Statement drilldown as well. Data Source:
select elapsed_time from v$sql
User Action:
If the SQL cursor is performing poorly, most likely the elapsed time will be high. Determine how much of the elapsed time is CPU or wait related. Tune the statement accordingly.
Top SQL
This chart provides the ability to identify and show the details of the top
25 (default) resource consuming SQL statements in the shared pool's
library cache. The default count of returned rows (25) and SQL sort
metric can be changed as required. In addition, there are 25 filters that
can be applied to allow a highly customized view of the SQL cache.
For instance, you could create a Top SQL chart that shows the top 50
SQL statements performing more than 1000 disk reads by a particular
user against a particular table or view, etc. Up to 10 filters can be
applied simultaneously in the same query.
Once the list of top resource consuming SQL statements has been
retrieved you can double click on one to get a detailed list of statistics
and full text for a particular statement. Further investigation on how to
best tune a particular SQL statement can be done by using the Explain
Plan drilldown, or if you are on NT, the Tune SQL Statement drilldown
as well.
Improving SQL performance is usually the most effective way for
improving application performance. In identifying statements with the
most potential to improve performance, you may want to consider the
statements with the highest buffer gets or physical reads. Buffer gets,
or logical reads, is one of the contributors to CPU time, as it requires
locating and retrieving buffers from the cache. Looking at poor
performing SQL statements by physical reads gives you an overview of
the number of read requests that resulted in access to datafiles on disk
for each statement.
Another factor worth considering is the frequency at which the
statement is executed. A statement with the a high number of buffer
gets or physical reads may only be executed once a day, far less than
another statement with a lower number of buffer gets that is executed
many thousands of times a day.
There are numerous reasons why a query would result in excessive
buffer gets; for example, a full table scan is being performed.
To help further diagnose a potential problem, a number of drilldown
charts are available including:
PDF created with pdfFactory Pro trial version
www.pdffactory.com
Tables Referenced by this Query: Lists tables
referenced by this query as well as various optimizer
statistics.
Top SQL (Physical Reads): Presents SQL sorted by
Physical Reads.
Top SQL (Buffer Gets): Presents SQL sorted by Buffer
Gets.
Top SQL (Sorts): Presents SQL sorted by Sorts.
Tuning these statements will result in less CPU time for the
application. Execution count is also important when determining which
SQL statement should be tuned first. The statement with the highest
buffer get count may be executed once a day, far less often than
another statement in the list. Tuning the SQL statement with the
highest count may not produce as much of an improvement for the
application or system in this case as tuning the statement that is
executed much more frequently.
This chart will identify the SQL statements that are performing the
most buffer gets. There could be many reasons why a query is
performing these buffer gets, with full table scans being a common
culprit. To help further diagnose the cause of the problem there are
number of drilldown charts available. The SQL shown on this chart is
limited to a fixed number of characters. To see which tables this query
references and to check on the validity statistics used by the
optimizer, use the Tables Referenced by this Query Chart. To see SQL
sorted by Physical Reads, use the Top SQL (Physical Reads) Chart.
Further investigation on how to best tune a particular SQL statement
can be done by selecting any one of SQL statements listed and using
the Explain Plan drilldown, or if you are on NT, the Tune SQL
Statement drilldown as well.
To determine which session, program or application is responsible for
executing a particular SQL statement, you can use the Sessions
Currently Executing this SQL Statment drilldown. This will return a list
of sessions that are currently executing the selected SQL statement. If
there are no sessions executing the SQL statement at current
moment, no data will be returned.
This chart includes the following data items:
SQL Text
PDF created with pdfFactory Pro trial version
www.pdffactory.com
Disk Reads Per Execution
Buffer Gets Per Execution
Disk Reads
Buffer gets
Executions
Buffer Gets Per Row
Buffer Cache Hit Ratio
Sorts
Shareable Memory
Rows Processed
Parse Calls Per Execution
Parse Calls
Loads
Persistent Memory
Runtime Memory
Loaded Versions
Open Versions
Users Opening
Users Executing
PDF created with pdfFactory Pro trial version
www.pdffactory.com
Invalidations
Child Number
Optimizer Mode
Parsing User Id
Parsing Schema Id
Kept Versions
Address
Module
Module Hash
Action
Action Hash
Serializable Aborts
Outline Category
CPU Time
Elapsed Time
PDF created with pdfFactory Pro trial version
www.pdffactory.com
京公网安备 11010802041100号