I/O View (Oracle Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Performance Analyst Statistics

In addition to a Home page, the I/O category of Oracle Performance Analyst includes the following tabbed pages:

Home

The I/O Home page includes the following sections:

Key Systems Analysis Pane

When complaints begin to surface about your database's performance, oftentimes the root cause can be traced to one or more issues with I/O. The thing to keep in mind when you begin to monitor the I/O of your database is that you are actually reviewing the success of your physical design model.

All the physical storage characteristics and placements, the table and index designs, and the speed with which it all works are on display when I/O is monitored. Because a database's main index of performance is measured by how fast I/O needs are satisfied, it is your responsibility to quickly interrogate Oracle to determine if a reported database slowdown is I/O related.

Physical I/O

Physical I/O consists of Oracle going to disk to gather or write data. The database writer (DBWR) and log writer (LGWR) processes typically perform all the I/O work in the database. You can also use other processes like the checkpoint and archive processes (CKPT and ARCH). IDERA Performance Analyst shows three key indicators of physical I/O. The table below describes these indicators:

Indicator Description

Physical Reads

Physical Reads is the total number of physical reads performed on all datafiles since the last refresh.

Physical Writes

Physical Writes is the total number of times the DBWR process has performed writes to various database datafiles since the last refresh

Redo Writes

Oracle's redo logs are hotbeds of I/O activity in databases that store heavily modified data. Redo log files are used to perform database recovery in the event of a system crash. Redo logs are written to in a cyclical fashion - each log file is filled up before Oracle moves on to the next file. The redo writes statistic reflects the total number of redo writes by the LGWR process since the last refresh

Metrics

The table below describes metrics for Physical I/O statistics:

Statistic Metrics

Physical Reads

Large numbers of physical reads could reflect a too small data/buffer cache. The Buffer Cache Hit Ratio is a better indicator of overall logical vs. physical I/O.

Physical Writes

Wait events related to I/O activity are good indicators of physical I/O problems. These events include db file parallel write and db file single write.

Correction

Doing the following can negate large numbers of continuous physical reads:

  • Increasing the size of the data/buffer cache.
  • Pinning often-referenced objects in memory by using the KEEP buffer pool.
  • Placing heavily scanned objects in larger blocksize tablespaces (16-32KB). For Oracle9i or later.
  • Tune SQL statements for better efficiency.

Logical I/O

Logical I/O refers to data access performed in memory. The database writer (DBWR) and log writer (LGWR) processes typically perform all the I/O work in the database. You can also use other processes like the checkpoint and archive processes (CKPT and ARCH). IDERA Performance Analyst shows three key indicators of logical I/O. The table below describes these indicators:

Indicator Description

Logical Reads

Logical Reads is the total number of db block gets and consistent gets (data read from memory) since the last refresh.

Logical Changes

Logical Changes is the total number of changes that were made to all blocks in the SGA that were part of an update or delete operation. These changes generate redo log entries and are permanent if the transaction is committed. The number of logical changes is an approximate indication of total database work.

Consistent Reads

Consistent Reads is the total number of times a consistent read was requested for a database block. Such a read is performed from Oracle’s rollback segments.

Metrics

Regarding raw logical I/O counts, no hard-core metrics exist. However, because physical I/O takes longer to complete than logical (memory) I/O, you should minimize physical read operations when possible. The Buffer Cache Hit Ratio is a better indicator of overall logical vs. physical I/O.

Correction

While logical I/O is still up to 1,400 times faster than physical disk access, it would be wise to investigate the top logical I/O process using Performance Analyst and see what SQL it is executing. If one process on the system is consuming between 25-50% of the overall amount, their SQL might require tuning.

Bottleneck Analysis Pane

When an Oracle database is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting can mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. You can use Bottleneck Analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Bottleneck analysis is a valid method of measuring performance because it helps you track where a database has been spending its time. If heavy table scan activity has been dragging a database’s performance down, you can use bottleneck analysis to confirm the actual root cause. Once one or more wait events or other bottlenecks have been pinpointed as possible performance vampires, you can discover a fair amount of detail about which sessions and objects are causing the problem.

For wait event analysis to be properly carried out, it is imperative that the timed_statistics initialization parameter be set to TRUE. By default this parameter is set to FALSE, which disallows the collection of wait times for each wait event defined to the Oracle engine. For one to really understand the impact of wait events on database performance, you need to not only discover what the database is or has been waiting on, but the durations of the waits. Having both allows a complete picture to be formed regarding the magnitude of wait-initiated performance degradations. Almost all Oracle experts now agree that allowing the collection of timing statistics adds little if anything to database overhead, so setting timed_statistics to TRUE should not be a worry. The Performance Analyst I/O home page identifies the top database and log file waits that can cause decreased performance.

The Bottleneck Analysis section includes:

Hottest Database Files

The Hottest Database Files section lists the most active database files as indicated by physical I/O activity.

Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Understanding the storage-level hot spots of a database is beneficial for a couple of reasons. First, you can get a feel for overworked physical disks by viewing I/O statistics at the tablespace and datafile levels. If a particular disk or set of disks is under too much strain, you can relocate the tablespaces to other less-used devices or create new tablespaces on different disks and move hot objects to them (assuming, of course, you have extra disks available). Second, if you have used standard DBA practice and placed indexes in their own tablespace, you can view the I/O statistics for that tablespace and see if the indexes are actually being used.

Correction

Here are some areas to consider when viewing Hottest Database Files: Seeing much activity in the SYSTEM tablespace and datafiles can indicate recursive calls (space management, etc.). The use of locally-managed tablespaces can help with space management as it relates to data dictionary references. Temporary tablespaces (devoted to sort activity) showing higher volumes of physical I/O could indicate a problem with excessive disk sorts. You might want to quickly review all the physical I/O for each drive/file system and get a feel for the overworked disks on your server. If you have under utilized disk drives (with their own controllers), consider relocating some tablespaces that exhibit high I/O characteristics to those drives.

Top Database File Waits

When viewing wait statistics, there are many levels of detail. The first level is the system view, which provides a global, cumulative snapshot of all the waits that have occurred on a system. The Top Database File Waits section filters all system wait events to present waits that are associated with database file activity. It ranks the top events by the count of total waits.

Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Correction

Oracle documentation contains a listing and description of every current wait event defined in Oracle. DBAs unfamiliar with what each event represents should keep this listing close by as they examine wait-based event metrics. The most common database file wait is a db file scattered read, which indicates table scan activity. Small table scans are normally not a problem as Oracle can access data from small tables by quickly caching and scanning them. Scans of large tables can be confirmed by examining the I/O access pattern metrics on the Performance Analyst I/O home page. Information is also available on which sessions are causing the large table scans. Finally, one thing to watch is not only the actual waits, but also the wait time in seconds. If no wait time is observed with each wait type, then performance is likely not endangered by the event.

Top Log File Waits

When viewing wait statistics, there are many levels of detail. The first level is the system view, which provides a global, cumulative snapshot of all the waits that have occurred on a system. This section filters all system wait events to present waits that are associated with log file activity. It ranks the top events by the count of total waits.

Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Home > Bottleneck Analysis Pane

Correction

Oracle documentation contains a listing and description of every current wait event defined in Oracle. DBAs unfamiliar with what each event represents should keep this listing close by as they examine wait-based event metrics. The most common database file wait is a log file parallel write, which indicates how quickly Oracle can flush the log buffer. Excessive wait times associated with this event count indicate a bottleneck at the log buffer level. Finally, one thing to watch is not only the actual waits, but also the wait time in seconds. If no wait time is observed with each wait type, then performance is likely not endangered by the event.

SQL Analysis Pane

Much of a database's overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end user response times. SQL statements that use much memory can also cause a problem in a database.

Before you can identify problem SQL in your database, you have to ask the question, “What is bad SQL?” What criteria do you use when you begin the hunt for problem SQL in your critical systems? Even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every Oracle professional's satisfaction. The SQL Analysis for I/O shows what SQL statements have consumed the largest percentages of physical and logical I/O, sort activity, and rows processed. The table below lists some general criteria you can use when evaluating the output from various database monitors or personal diagnostic scripts:

Criteria Description

Overall Response (Elapsed) Time

The amount of time the query took to parse, execute, and fetch the data needed to satisfy the query. It should not include the network time needed to make the round trip from the requesting client workstation to the database server. This statistic is available in Oracle9i or later.

CPU Time

The amount of CPU time the query took to parse, execute, and fetch the data needed to satisfy the query.

Physical I/O

This is often used as the major statistic in terms of identifying good vs. bad SQL, this is a measure of how many disk reads the query caused to satisfy the user's request. While you certainly want to control disk I/O where possible, it is important that you not focus solely on physical I/O as the single benchmark of inefficient SQL. Make no mistake, disk access is slower than memory access and also consumes processing time making the physical to logical transition, but you need to look at the entire I/O picture of a SQL statement, which includes looking at a statements' logical I/O as well.

Logical I/O

This is a measure of how many memory reads the query took to satisfy the user's request. The goal of tuning I/O for a query should be to examine both logical and physical I/O, and use appropriate mechanisms to keep both to a minimum.

Repetition

This is a measure of how often the query has been executed. A problem in this area is not as easy to spot as the others unless you know your application well. A query that takes a fraction of a second to execute may still be causing a headache on your system if it has executed erroneously (for example, a query that executes in a runaway PL/SQL loop) over and over again.

There are other criteria that you can examine like sort activity or access plan statistics (that show items like Cartesian joins and the like), but more often than not, these measures are reflected in the criteria listed above. Fortunately, Oracle records all the above measures (some only in 9i), which makes tracking the SQL that has been submitted against an Oracle database much easier.

Metrics

When you begin to look for inefficient SQL in a database, there are two primary questions you want answered: What HAS been the worst SQL that has historically been run in my database? What IS the worst SQL that is running right now in my database? When troubleshooting a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It could be that the query is in an inefficient PL/SQL loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.

There is the possibility that the SQL statement just is not tuned well. To determine that, you can go further into Performance Analyst's Top SQL view and, if you have IDERA SQL Tuner installed, you can port the SQL over to SQL Tuner to better optimize the statement.

I/O Access Patterns - I/O

There are many different routes that Oracle can take to access various objects contained in a database. The I/O Access Patterns section summarizes the methods Oracle is using to satisfy end-user requests for data.

Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Home > SQL Analysis Pane

Metrics

Long table scans are typically an activity to avoid as they have the capability to cause needless physical and logical I/O as well as flood the buffer cache with seldom referenced blocks of data. You can discover which sessions have been causing the most large table scans. The table fetch continued row statistic is indicative of chained/migrated row I/O. Such activity is not desired because chained/migrated row access can cause twice the I/O needed to access a table. Oracle must pick do two or more I/Os to read a chained/migrated row in a table. You can discover which sessions have been accessing tables with chained/migrated rows.

Workload Analysis Pane

It is not uncommon for one or two users to cause the majority of runtime problems that plague a database. The problem could be a runaway process, an untuned batch procedure, or other user-initiated operation. The Top I/O Hogs table shows the top sessions that have caused the most physical I/O on the system (both reads and writes).

Metrics

If you see one or two users who have caused more than 25-50% of the total memory usage, go further into the sessions to see the activities they are performing.

I/O Tablespace Tab

Physical I/O consists of Oracle going to disk to gather or write data. Logical I/O refers to data access performed in memory. The database writer (DBWR) and log writer (LGWR) processes typically perform all the I/O work in the database. Other processes like the checkpoint and archive processes (CKPT and ARCH) may also be used.

The Tablespace I/O tab displays details concerning the physical I/O activity at the tablespace level. The table below lists the information available on this tab:

Column Description

Tablespace Name

The name of the tablespace.

Physical Reads

The cumulative number of physical reads.

Physical Writes

The cumulative number of physical writes.

Physical Block Reads

The cumulative number of physical block reads.

Physical Block Writes

The cumulative number of physical block writes.

Physical Read Time

The time spent reading from the tablespace (in hundredths of seconds).

Physical Write Time

The time spent writing to the tablespace (in hundredths of seconds).


Metrics

Generally, you want to see much more logical I/O activity than physical I/O, at least with respect to reads, although this in and of itself is a guarantee of good I/O performance. Seeing logical and physical reads keeping pace with one another is a sure sign that the Oracle SGA's buffer cache is sized too small or that needless, large table scans are occurring, which cause blocks of data to be continually read in and flushed out of the buffer cache. Other telltale signs of trouble brewing are large amounts of activity visible in user's TEMP tablespaces. The normal interpretation of such a thing is that a large number of disk sorts are taking place (perhaps because the Init.ora/spfile parameter SORT_AREA_SIZE may be set too small).

Datafile I/O Tab

Physical I/O consists of Oracle going to disk to gather or write data. Logical I/O refers to data access performed in memory. The database writer (DBWR) and log writer (LGWR) processes typically perform all the I/O work in the database against the physical datafile used to hold information The checkpoint and archive processes (CKPT and ARCH), also perform I/O work in the database.

The Datafile I/O tab displays details concerning the physical I/O activity at the datafile level. The table below lists the information available on this tab:

Column Description

DataFile Name

The name of the datafile.

Tablespace Name

The name of the tablespace.

Physical Reads

The cumulative number of physical reads.

Physical Writes

The cumulative number of physical writes.

Block Reads

The cumulative number of physical block reads.

Block Writes

The cumulative number of physical block writes.

Read Time

The time spent reading from the tablespace (in hundredths of seconds).

Write Time

The time spent writing to the tablespace (in hundredths of seconds).


Metrics

Generally, you will want to see much more logical I/O activity than physical I/O, at least with respect to reads, although this in and of itself is a guarantee of good I/O performance. Seeing logical and physical reads keeping pace with one another is a sure sign that the Oracle SGA's buffer cache is sized too small or that needless, large table scans are occurring, which cause blocks of data to be continually read in and flushed out of the buffer cache. Other telltale signs of trouble brewing are large amounts of activity visible in user's TEMP tablespaces. The normal interpretation of such a thing is that a large number of disk sorts are taking place (perhaps because the Init.ora/spfile parameter SORT_AREA_SIZE may be set too small). This view also allows you to see the ‘hot’ files in terms of physical I/O activity. Seeing too much activity on one drive/file system may indicate a need for better file striping.

Rollback I/O Tab

The Rollback I/O tab includes the following sections:

For more information, see Home.

Rollback I/O

To undo changes made to the Oracle database from within a transaction, Oracle writes data to individual rollback segments. Oracle also uses these segments to maintain read consistency for multiple users of data that is being modified. Because Oracle reads from and writes data to rollback segments, they can become very hot areas for I/O.

The Rollback I/O section presents everything necessary to view and troubleshoot rollback problems.The table below lists the information available in this section:

Column Description

Name

The name of the rollback segment.

Size

The size of the rollback segment in KBs.

Shrinks

The number of times the rollback segment has decreased in size.

Extents

The number of times he rollback segment has increased in size.

Gets

The number of header gets (the segment has been used).

Waits

The number of header waits.

Writes

The number of bytes written to the rollback segment.

Active Trans

Indicates whether the rollback segment is active (non zero) or not (zero value).

Status

Indicates the status of the rollback segment, with the two main results being OFFLINE (a segment is offline and unavailable for transactions) and ONLINE (a rollback segment is online and available for transactional use).

High Water Mark

The largest size that the rollback segment has ever grown to.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Rollback I/O Tab

Metrics

To properly tune rollback I/O, you must first make sure that you have enough segments to accommodate the workload of the database. Constantly seeing a count of active rollback segments equal to or near the number of rollbacks defined for the database is an indicator that you should create more. An overall rollback contention ratio of 1% or greater is an indicator of too few rollbacks. Seeing wait counts greater than zero for each rollback segment is further evidence that you should create more rollback segments. Oracle9i provides the UNDO tablespace to automatically generate and eliminate the ‘correct’ number of rollback segments for a system given a certain workload. After ensuring that enough rollback segments exist in the database, you should then turn your attention to the question of sizing. Dynamic rollback extension can take a toll on performance when rollback segments are consistently enlarged to accommodate heavy transaction loads. Seeing rollback segments undergoing numerous extends and shrinks (as Oracle returns a segment back to its OPTIMAL setting), as well as rollback segments having current or high-water mark sizes greater than their OPTIMAL setting usually is a good indicator that they should be permanently enlarged. Again, Oracle9i’s automatic undo management can assist in this process.

Active Rollback Details

Rollback or undo segments are used to guarantee transactional integrity. When a transaction has not been committed or rolled back, a rollback segment will be in use with live transactional data. The Active Rollback Details section displays information concerning active rollback segments. The table below lists the information available in this section:

Column Description

SID

The system ID of the session using the rollback segment.

Username

The account/schema name of the session.

Rollback

The rollback segment name containing the session’s transaction.

Start Time

The time the transaction was initiated.

Blocks Used

The number of rollback blocks used by the transaction.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Rollback I/O Tab

Metrics

Seeing transactions with dated start times may indicate transactions that are the source of lock contention.

Session Rollback Activity

For all connected sessions, Oracle maintains statistics regarding each session’s counts of rollbacks and commits. The Session Rollback Activity section displays a transactional summary/history that helps identify sessions with excessive rollback activity. The table below lists the information available in this section:

Column Description

SID

The system ID of the session.

Username

The account/schema name of the session.

Machine Name

The machine name where the session logged on.

Logon Time

The time the session logged into Oracle.

User Rollbacks

The number of times the user manually issued the ROLLBACK statement or an error occurred during the user’s transaction.

Transactions Rolled Back

The number of transactions successfully rolled back.

User Commits

The number of times a user transaction was committed.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Rollback I/O Tab

Metrics

Seeing high numbers of transactions rolled back might indicate an application error or other transactional problem.

DBWR/LGWR Tab

The DBWR/LGWR tab includes the following sections:


Database Writer Detail

The database writer process (DBWR) handles the flow of information from Oracle's physical datafiles to and from the various memory structures in the system global area (SGA). On platforms that support it, you can configure and use multiple DBWR processes. The log writer (LGWR) process manages the information contained in Oracle's online redo log files and redo log buffer area. The table below lists the information available in this section:

Column Description

Statistic

The specific metric for the database writer.

Amount

The value for the statistic.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > DBWR/LGWR Tab

Metrics

Of all the statistics presented for the DBWR process, the summed dirty queue length statistic deserves attention. Non-zero values typically indicate buffers left in the write queue after a write request and may indicate that the DBWR process is falling behind.

Log Writer Detail

The database writer process (DBWR) handles the flow of information from Oracle's physical datafiles to and from the various memory structures in the system global area (SGA). On platforms that support it, you can configure and use multiple DBWR processes. The log writer (LGWR) process manages the information contained in Oracle's online redo log files and redo log buffer area. The table below lists the information available in this section:

Column Description

Statistic

The specific metric for the log writer.

Amount

The value for the statistic.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > DBWR/LGWR Tab

Metrics

For the LGWR process, non-zero values seen for the redo log space requests and redo log space wait time statistics could be a cause for concern. Redo log space requests reflect the number of times a user process waited for space in the redo log buffer, while the redo log space wait time presents the total time waited in milliseconds. Both could indicate the presence of contention in the redo log buffer. Possible remedies include increasing the log_buffer size in the SGA.

Daily Archive Log Summary (Last 7 Days)

To allow for point-in-time recovery, Oracle writes copies of redo log information to disk. When a database is running in archive log mode, a DBA can (with proper backup techniques in place) recovery nicely from a database error and roll forward to almost any point in time needed, as long as the proper archive logs are in place.

The I/O needed to write these archive logs is handled by Oracle's ARCH process. The Daily Archive Log Summary shows the number of archive logs written each day for the past seven days.

Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > DBWR/LGWR Tab

Metrics

Heavy redo log activity can lead to many archive files being written out to disk. Batch jobs have the potential to move very fast - sometimes so fast that the online redo logs wrap back around before they have a chance to be archived. Messages of this nature will oftentimes show up in the Oracle alert log. If this is the case, you many want to think about increasing the size of the online redo log files, or increase the number of redo logs in general. Seeing archive files written more than one per hour or half-hour may indicate a too small redo size (or above average data modification load). If archive log protection is a concern (in other words, you do not want to lose an archive file that may be needed for recovery) and you are using Oracle8, you can now take advantage of the feature to write archive files to more than one destination on disk. It also allows multiple ARCH processes to now be invoked. Investigate the use of the Init.ora parameters log_archive_dest_n and log_archive_max_processes. Always remember one thing with respect to archive files and running Oracle in archive log mode: running out of archive file space on the server is a sure fire way to halt all activity in a database. Make sure you have plenty of free space available on your archive drives. Also, implement a purge procedure for older archives in conjunction with a good backup routine.

Redo Wastage

Oracle's redo logs are hotbeds of I/O activity in databases that store heavily modified data. Redo log files are used to perform database recovery in the event of a system crash. Redo logs are written to in a cyclical fashion - each log file is filled up before Oracle moves on to the next file. The Redo Wastage section shows how many bytes were wasted by redo log buffers being written before they were full.

Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > DBWR/LGWR Tab

Metrics

Viewing a high percentage of wasted bytes to overall redo bytes can help you identify if redo wastage is a problem on your system.

Correction

Sometimes heavy redo wastage occurs when the log_checkpoint_interval parameter is set too high. If you think this is the case for your system, then Edit the Init.ora file for the database. Change the amount of log_checkpoint_interval to a lower value. Cycle the Oracle server when possible to allow the new value to take effect. Monitor new value to see if performance improves.

Table I/O Tab

I/O activity at the table level has historically been difficult to get with Oracle. Oracle9i and later contain better dictionary elements to help get a clearer picture of table I/O, but earlier versions of Oracle allow an estimation only. The table below lists the information available in the Estimated Physical I/O by table on this tab:

Column Description

Owner Name

The owner of the table

Table Name

The name of the table or table partition.

Estimated Disk Reads

The estimated number of physical and physical direct reads for the table.


Metrics

Viewing high numbers of disk reads for a table could indicate excessive table scan activity. Such a table may benefit from better indexing or from being placed into a larger blocksize tablespace (Oracle9i and later).

Sort Activity Tab

The Sort Activity tab includes the following sections:

Session Sort Detail

Excessive sort activity can degrade a user's or overall database performance. When a sort operation occurs, Oracle attempts to perform the sort in a memory space, assigned by the DBA, which exists at the operating system level. If the sort is too large to be contained within this space, it will continue the sort on disk - specifically, in the user's assigned temporary tablespace.

The Session Sort Detail section displays historical sort statistics for every logged on session. The table below lists the information available in this section:

Column Description

SID

The unique identified given the session by Oracle.

Username

The user account a session is using.

Machine Name

The machine name where the session logged on.

Logon Time

The time the session logged into Oracle.

Disk Sorts

The cumulative number of disk sorts for the session.

Memory Sorts

The cumulative number of memory sorts for the session.

Row Sorted

The cumulative number of rows sorted by the session.

Disk/Memory Sort Ratio

The percentage of times the session had to perform a disk sort.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Sort Activity Tab

Metrics

Techniques to include in your overall performance strategy are those that relate to minimizing the amount of sort activity overall and specifically sort activity that takes place on disk. A good place to start is by understanding things that cause sorts in the first place. A list of sort-related commands and SQL-related options include CREATE INDEX, ALTER INDEX … REBUILD, DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, IN, and NOT IN, as well as certain unindexed joins and certain correlated subqueries All of these SQL commands have the potential to create a sort. As a DBA, you probably will not know which queries will have their sorts performed entirely in memory and which ones will be forced to go to disk. There are times you simply cannot stop disk sort activity (such as in data warehousing environments). That being the case, you should ensure the following are true in your database: Your users' TEMPORARY TABLESPACE assignment is not the SYSTEM tablespace, which is the default assignment. The TEMPORARY TABLESPACE assigned to your users is placed on a fast disk. The TEMPORARY TABLESPACE has the tablespace parameter TEMPORARY assigned to it, which allows sort activity to be performed in a more efficient manner. If your overall memory sort ratio falls below 90%, you may want to increase the parameters devoted to memory sorts - sort_area_size and sort_area_retained_size. Keep in mind that Individual users might have the ability to alter their own sessions and increase their sort_area_size assignments! As a DBA, you may want to restrict users that have the ALTER SESSION privilege. In addition to increasing the amount of memory devoted to sorting, you should also hunt down inefficient SQL that cause needless sorts. For example, UNION ALL does not cause a sort whereas UNION does in an SQL query (to eliminate duplicate rows). DISTINCT oftentimes is coded inappropriately (especially by folks transferring from Microsoft Access, which used to use DISTINCT for nearly every SELECT query).

Current Disk Sorts

Excessive sort activity – especially disk sorts - can degrade a user's or overall database performance. When a sort operation occurs, Oracle attempts to perform the sort in a memory space, assigned by the DBA, which exists at the operating system level. If the sort is too large to be contained within this space, it will continue the sort on disk - specifically, in the user's assigned TEMPORARY TABLESPACE.

The Current Disk Sorts section displays sort statistics for every session currently performing a disk sort. The table below lists the information available in this section:

Column Description

SQL Text

The SQL statement causing the disk sort.

SID

The unique identified given the session by Oracle.

Username

The user account a session is using.

Machine Name

The machine name where the session logged on.

Tablespace

The tablespace containing the sort segments used by the sort.

Extents

The number of extents being used for the sort.

Blocks

The number of blocks being used for the sort.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Sort Activity Tab

Metrics

Techniques to include in your overall performance strategy are those that relate to minimizing the amount of sort activity overall and specifically sort activity that takes place on disk. A good place to start is by understanding things that cause sorts in the first place. A list of sort-related commands and SQL-related options include CREATE INDEX, ALTER INDEX … REBUILD, DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, IN, and NOT IN, as well as certain unindexed joins and certain correlated subqueries All of these SQL commands have the potential to create a sort. As a DBA, you probably will not know which queries will have their sorts performed entirely in memory and which ones will be forced to go to disk. There are times you simply cannot stop disk sort activity (such as in data warehousing environments). That being the case, you should ensure the following are true in your database: Your users' TEMPORARY TABLESPACE assignment is not the SYSTEM tablespace, which is the default assignment. The TEMPORARY TABLESPACE assigned to your users is placed on a fast disk. The TEMPORARY TABLESPACE has the tablespace parameter TEMPORARY assigned to it, which allows sort activity to be performed in a more efficient manner. If your overall memory sort ratio falls below 90%, you may want to increase the parameters devoted to memory sorts - sort_area_size and sort_area_retained_size. Keep in mind that Individual users might have the ability to alter their own sessions and increase their sort_area_size assignments! As a DBA, you may want to restrict users that have the ALTER SESSION privilege. In addition to increasing the amount of memory devoted to sorting, you should also hunt down inefficient SQL that cause needless sorts. For example, UNION ALL does not cause a sort whereas UNION does in an SQL query (to eliminate duplicate rows). DISTINCT oftentimes is coded inappropriately (especially by folks transferring from Microsoft Access, which used to use DISTINCT for nearly every SELECT query).

Job Queue Activity Tab

The Job Queue Activity tab includes the following sections:

Job Queue Summary

Oracle provides a way for Oracle developers to submit and run jobs through the job queue feature of the Oracle database. The table below lists the information available on this section:

Column Description

Total Jobs

The number of jobs defined to the database.

Broken

The number of jobs broken (disabled).

Failures

The number of job failures.

Running

The number of jobs currently running.

Problem Jobs

Oracle provides a way for Oracle developers to submit and run jobs through the job queue feature of the Oracle database. Sometimes a job may fail or become disabled (broken). The table below lists the information available in this section:

Column Description

Job ID

The job number for the job.

Login User

The user account used to run the job.

Last Date Successfully Run

The date/time of when the job last successfully ran.

Broken

Indicates if the job is disabled or not.

Failures

The number of failures the job has experienced.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Job Queue Activity Tab

Running Jobs Detail

Oracle provides a way for Oracle developers to submit and run jobs through the job queue feature of the Oracle database. The Running Jobs Detail section provides information about currently running jobs. The table below lists the information available in this section:

Column Description

SID

The unique identified given the session by Oracle.

Job ID

The job number for the job.

Start Date/Time

Indicates when the job started.

Submitted by

Indicates the session that submitted the job.

Run As

Indicates which account is used to run the job.

Parse As

Indicates which account is used to parse the job.

Next Run Date/Time

Indicates when the job will next run.

Job Contents

Indicates the actual command used by the job.


Location

Oracle Performance Analyst Statistics > I/O View (Oracle Performance Analyst) > Job Queue Activity Tab

Access Pattern Detail Tab

When a session submits a set of SQL or a transaction, there are many different combinations of methods the Oracle kernel will use to satisfy the query or transaction. The Access Pattern Detail tab displays all currently connected sessions, their demographic information, and a count of the various different methods that Oracle uses to handle incoming requests. The table below lists the information available on this tab:

Column Description

SID

The unique identified given the session by Oracle.

Username

The user account a session is using.

Machine Name

The machine name where the session logged on.

Logon Time

The time the session logged into Oracle.

Large Table Scans

The cumulative count of ‘large’ table scans with ‘large’ normally being defined as any table over five blocks.

Small Table Scans

The cumulative count of ‘small’ table scans with ‘small’ normally being defined as any table under five blocks.

Table Scan ROWID Ranges

The cumulative count of table scans where the table in question has the CACHE property enabled.

Table Scan Cache Partitions

The cumulative count for partitions that have the CACHE property enabled.

Table Scan Direct Reads

The cumulative count of table scans performed with Oracle bypassing the buffer cache (data read only once - straight from disk and not from both disk and cache).

Chained Row Accesses

The cumulative count of the number of times a chained/migrated row was accessed.

Index by ROWID

The cumulative count of the number of rows fetched from a table by ROWID (normally index access).

Index Scans

The cumulative count of fast full index scans.

Index Scan ROWID Ranges

The cumulative count of fast full index scans with ROWID endpoints specified.

Index Scan Direct Read

The cumulative count of fast full index scans using direct read (data read only once - straight from disk and not from both disk and cache).

Retrieved from "http://docwiki.embarcadero.com/DBArtisan/170/e/index.php?title=I/O_View_(Oracle_Performance_Analyst)&oldid=5952"