FPSBASE.ADM_SYSMETRICS_HIST

>

> FPSBASE.WIP_WAFER_HIST_LOOP

View FPSADMIN.ADM_SYSMETRICS_HIST

Shows important database system metrics for the last 100 days, as available in DBA_HIST_SYSMETRIC_SYMMARY, in a row-based format that is best suited to in-depth (statistical) analyses to track down performance issues. Comments about each column: ACTIVE_PARALLEL_SESSIONS: The number of active parallel sessions. ACTIVE_SERIAL_SESSIONS: The number of active serial sessions. AVG_ACTIVE_SESSIONS: The mean number of active sessions, both serial and parallel. AVG_SYNC_BLCK_R_LATENCY: The mean latency (in ms) of synchronous single-block reads. Synchronous single-block reads are a reasonably accurate way of assessing the performance of the storage subsystem. High latencies are typically caused by a high I/O request load. Excessively high CPU load can also cause the latencies to increase. BCKGRD_CHKPTS_PER_SEC: The number of background checkpoints per second. A checkpoint is the writing by the DBWR process of all modified buffers in the SGA buffer cache to the database data files. Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks, as well as the control files. Checkpoints occur after every redo log switch and also at intervals specified by initialization parameters. BRANCH_NODE_SPLITS_PER_TXN: The number of branch node splits per transaction. It is the number of times an index branch block was split because of the insertion of an additional value. BUFFER_CACHE_HIT_RATIO: The percentage of times the data block requested by the query is already/still in memory. Effective use of the buffer cache can greatly reduce the I/O load on the database. If the buffer cache is too small, frequently accessed data will be flushed from the buffer cache too quickly which forces the information to be re-fetched from disk. Since disk access is much slower than memory access, application performance will suffer. In addition, the extra burden imposed on the I/O subsystem could introduce a bottleneck at one or more devices that would further degrade performance. CELL_PHYS_IO_ICONN_BYTES: The amount of bytes for disk-to-database-host interconnect traffic. CONS_R_CHG_PER_TXN: The number of times per transaction a user process has applied rollback entries to perform a consistent read on the block. Oracle always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time. CONS_R_GETS_PER_TXN: The number of times per transaction a consistent read was requested for a block. CPU_USG_PER_TXN: The CPU usage (in ms) per transaction. Note that the default unit in DBA_HIST_SYSMETRIC_SUMMARY is centiseconds but this view converts it to milliseconds. CR_BLCK_CREATED_PER_TXN: The number of current blocks per transaction cloned to create consistent read (CR) blocks. Read consistency is used when competing processes are reading and updating the data concurrently. CR_UNDO_RECS_PER_TXN: The number of undo records applied for consistent read per transaction. Read consistency is used when competing processes are reading and updating the data concurrently and undo blocks are used to retain the prior image of the rows. CURR_LOGONS_COUNT: The current number of logons. CURR_OPEN_CURS_COUNT: The current number of opened cursors. CURR_OS_LOAD: The current number of processes running. CURS_CACHE_HIT_RATIO: The ratio of the number of times an open cursor was found to the number of times a cursor was sought. This is influenced by the SESSION_CACHED_CURSORS parameter. The SESSION_CACHED_CURSORS parameter is used to reduce the amount of parsing with SQL statements that use host variables. DBWR_CHKPTS_PER_SEC: The number of times per second the DBWR was asked to scan the cache and write all blocks marked for a checkpoint. The database writer process writes the contents of buffers to datafiles. The DBWR processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. When a buffer in the database buffer cache is modified, it is marked dirty. The primary job of the DBWR process is to keep the buffer cache clean by writing dirty buffers to disk. As user processes dirty buffers, the number of free buffers diminishes. If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are not able to find free buffers. DBWR manages the buffer cache so that user processes can always find free buffers. DB_BLCK_CHG_PER_TXN: The total number of changes per transaction that were part of an update or delete operation that were made to all blocks in the SGA. DB_BLCK_GETS_PER_TXN: The number of times per transaction a current block was requested from the RAM data buffer. DB_CPU_TIME_RATIO: The CPU-to-DB time ratio. DB_WAIT_TIME_RATIO: The wait-to-DB time ratio. DISK_SORT_PER_TXN: The number of sorts going to disk per transactions for the sample period. For best performance, most sorts should occur in memory because sorts to disks are expensive to perform. If the sort area is too small, extra sort runs will be required during the sort operation. This increases CPU and I/O resource consumption. ENQUEUE_REQS_PER_TXN: The total number of table or row locks acquired per transaction. ENQUEUE_TIMEOUTS_PER_TXN: The total number of table and row locks (acquired and converted) per second that time out before they could complete. ENQUEUE_WAITS_PER_TXN: The total number of waits per transaction that occurred during an enqueue convert or get because the enqueue get was deferred. It''s important to remember when you take a look at the STATS$ENQUEUESTAT table that enqueue waits are a normal part of Oracle processing. It is only when you see an excessive amount of enqueue waits for specific processes that you need to be concerned in the tuning process. Oracle locks protect shared resources and allow access to those resources via a queuing mechanism. A large amount of time spent waiting for enqueue events can be caused by various problems, such as waiting for individual row locks or waiting for exclusive locks on a table. EXECS_PER_TXN: The number of executes per second. EXECS_WITHOUT_PARSE_RATIO: The percentage of statement executions that do not require a corresponding parse. A perfect system would parse all statements once and then execute the parsed statement over and over without reparsing. This ratio provides an indication as to how often the application is parsing statements as compared to their overall execution rate. A higher number is better. FULL_IDX_SCANS_PER_TXN: The number of index fast-full scans execution plans per second. HARD_PARSE_COUNT_PER_TXN: The number of hard parses per second during this sample period. A hard parse occurs when a SQL statement has to be loaded into the shared pool. In this case, the Oracle Server has to allocate memory in the shared pool and parse the statement. A soft parse is recorded when the Oracle Server checks the shared pool for a SQL statement and finds a version of the statement that it can reuse. HOST_CPU_UTIL: The percentage of CPU being used on the host. IO_MB_PER_SEC: The total I/O throughput of the database for both reads and writes in megabytes per second. A very high value indicates that the database is generating a significant volume of I/O data. IO_REQS_PER_SEC: The total number of I/O requests of the database for both reads and writes in megabytes per second. LEAF_NODE_SPLITS_PER_TXN: The number of times per transaction an index leaf node was split because of the insertion of an additional value. It is an important measure of the DML activity on an index and excessive DML can leave an index in a sub-optimal structure, necessitating a rebuild for optimal index performance. LIBRARY_CACHE_HIT_RATIO: The percentage of entries in the library cache that were parsed more than once (reloads) over the lifetime of the instance. Since you never know in advance how many SQL statements need to be cached, the Oracle DBA must set SHARED_POOL_SIZE large enough to prevent excessive re-parsing of SQL. LOGICAL_R_PER_TXN: The number of logical reads per transaction. The value of this statistic is zero if there have not been any write or update transactions committed or rolled back during the last sample period. If the bulk of the activity to the database is read only, the corresponding per second data item of the same name will be a better indicator of current performance. LOGONS_PER_TXN: The number of logons per transaction. LONG_TAB_SCANS_PER_TXN: The number of long table scans per transaction during sample period. A table is considered long if the table is not cached and if its high-water mark is greater than 5 blocks. MEMORY_SORTS_RATIO: The percentage of sorts (from ORDER BY clauses or index building) that are done to disk vs. in-memory. Disk sorts are done in the TEMP tablespace, which is hundreds of times slower than a RAM sort. The in-memory sorts are controlled by sort_area_size or by pga_aggregate_target. NETW_TRAFFIC_VOL_PER_SEC: The network traffic volume (in bytes) per second. OPEN_CURS_PER_TXN: The total number of cursors opened per transaction. One problem in performance is caused by multiple cursors with bind variables that open the cursor and execute it many times. PARSE_FAIL_COUNT_PER_TXN: The total number of parse failures per transaction. PGA_CACHE_HIT_RATIO: The total number of bytes processed in the PGA versus the total number of bytes processed plus extra bytes read/written in extra passes. PHYS_R_DRCT_PER_TXN: The number of direct physical reads per transaction. Direct reads are analogous to direct reads, done by bypassing the OS JFS cache. PHYS_R_PER_TXN: The number of disk reads per transaction. When a user performs a SQL query, Oracle tries to retrieve the data from the database buffer cache (memory) first, then goes to disk if it is not in memory already. Reading data blocks from disk is much more expensive than reading the data blocks from memory. The goal with Oracle should always be to maximize memory utilization. PHYS_R_TOT_BYTES_PER_SEC: Total amount of disk reads (in bytes) per second. PHYS_W_DRCT_PER_TXN: The number of direct physical disk writes per second. You can speed up disk write in several ways, most notably the segregation of high-write datafiles to a small data buffer, and by using solid-state-disk (SSD) for high disk write files. PHYS_W_PER_TXN: The number of disk reads per transaction. PHYS_W_TOT_BYTES_PER_SEC: Total amount of disk writes (in bytes) per second. PQ_QC_SESSION_COUNT: The number of parallel query sessions. PQ_SLAVE_SESSION_COUNT: The number of slave sessions for parallelized queries. PX_OPS_DOWNGR_PER_SEC: The number of parallel execution operations per second downgraded (i.e. degree of parallelism reduced or serialized) due to the adaptive multiuser algorithm or the depletion of available parallel execution servers. PX_OPS_NOT_DOWNGR_PER_SEC: The number of parallel execution operations per second not downgraded (i.e. degree of parallelism not reduced) due to the adaptive multiuser algorithm or the depletion of available parallel execution servers. QUERIES_PARALLELIZED_PER_SEC: The number of SQL queries parallelized per second. RECURSIVE_CALLS_PER_TXN: The number of recursive calls, per second. Sometimes, to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. REDO_ALLOC_HIT_RATIO: The percentage of times users did not have to wait for the log writer to free space in the redo log buffer. Redo log entries contain a record of changes that have been made to the database block buffers. The log writer (LGWR) process writes redo log entries from the log buffer to a redo log file. The log buffer should be sized so that space is available in the log buffer for new entries, even when access to the redo log is heavy. When the log buffer is undersized, user process will be delayed as they wait for the LGWR to free space in the redo log buffer. REDO_GENERATED_PER_TXN: The amount of redo, in bytes, generated per transaction during this sample period. The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER or DROP operations. Redo entries are used for database recovery, if necessary. REDO_W_PER_TXN: The number of redo write operations per second during this sample period. RESP_TIME_PER_TXN: The time spent (in ms) in database operations per transaction. It is derived from the total time that user calls spend in the database (DB time) and the number of commits and rollbacks performed. A change in this value indicates that either the workload has changed or that the database is ability to process the workload has changed because of either resource constraints or contention. Note that the default unit in DBA_HIST_SYSMETRIC_SUMMARY is centiseconds but this view converts it to milliseconds. ROWS_PER_SORT: The average number of rows per sort for all types of sorts performed. ROW_CACHE_HIT_RATIO: The ratio of hits to the total number of bytes processed in the related the total number of bytes processed plus extra bytes read/written in extra passes. Oracle recommends that if the free memory is close to zero and either the library cache hit ratio is less than 0.95 or the row cache hit ratio is less than 0.95, then the SHARED_POOL_SIZE parameter should be increased until the ratios stop improving. SESSION_COUNT: The total number of session. SHARED_POOL_FREE_PCT: The percentage of the Shared Pool that is currently marked as free. SOFT_PARSE_RATIO: The ratio of soft parses (SQL is already in library cache) to hard parses (SQL must be parsed, validated, and an execution plan formed). The library cache (as set by shared_pool_size) serves to minimize hard parses. Excessive hard parsing could be due to a time shared_pool_size or because of SQL with embedded literal values. SQL_SRVC_RESP_TIME: The time (in ms) for each call. Note that the default unit in DBA_HIST_SYSMETRIC_SUMMARY is centiseconds but this view converts it to milliseconds. TEMP_SPACE_USED: The total amount of bytes used in the TEMP tablespace. TOT_IDX_SCANS_PER_TXN: The total number of index scans per transaction. Physical disk speed is an important factor in weighing these costs. As disk access speed increases, the costs of a full-table scan versus single block reads can become negligible. TOT_PARSE_COUNT_PER_TXN: The total number of parses per second, both hard and soft. TOT_PGA_ALLOC: Total amount of bytes allocated to the PGA. TOT_PGA_SQL_WORKAREAS: Total amount of bytes of PGA used by SQL work areas. With manual configuration of the SGA, it is possible that compiled SQL statements frequently age out of the shared pool because of its inadequate size. This can increase the frequency of hard parses, leading to reduced performance. When automatic SGA management is enabled, the internal tuning algorithm monitors the performance of the workload, increasing the shared pool if it determines the increase will reduce the number of parses required. TOT_TAB_SCANS_PER_TXN: The number of long and short table scans per transaction. TXNS_PER_LOGON: The number of transactions per logon. USR_CALLS_PER_TXN: The number of user calls per transaction. USR_COMMITS_PCT: The percentage of user commits to total user transactions. USR_RBCK_PCT: The percentage of user rollbacks to total user transations. USR_RBCK_UNDO_RECS_PER_TXN: The number of undo records applied to user-requested rollback changes per transaction. USR_TXN_PER_SEC: The number of user transactions per second. Abbreviations used in the column names: ALLOC - allocated/allocation BCKGRD - background BLCK - block(s) CHKPTS - checkpoints CHG - changes CONS - consistent CR - consistent read CURR - current CURS - cursor(s) DB - database DBWR - database writer DOWNGR - downgraded DRCT - direct EXECS - executions IDX - index ICONN - interconnect LIM - limit NETW - network PCT - percentage PHYS - physical PGA - parallel global area PQ - parallel query PX - parallel execution QC - query coordinator R - read(s) RBCK - rollbacks RECS - records REQS - requests RESP - response SRVC - service SYNC - synchronous TAB - table TOT - total TXN - transaction USG - usage USR - user UTIL - utilization VOL - volume W - write(s) More details about the metrics can be found in the Oracle Database Concepts guide.