Oracle进程连接数过多时的Statspack分析报告

Database InfoFri Apr 22 2011 10:48:33 GMT+0800 (China Standard Time)DB IDInstanceReleaseRACHost4190444978hotel9.2.0.4.0NOlocalhost.lo Elapsed:25.37 (min)1,522.2 (sec)DB Time:435.35 (min)26,120.87 (sec)Cache:128 MBBlock Size:8,192 bytesTransactions:0.99 per second 

Performance SummaryPhysical Reads:15,821/sec MB per second:123.6 MB/sec Physical Writes:57/sec MB per second:0.45 MB/sec Single-block Reads:4,058.08/sec Avg wait:0.02 ms Multi-block Reads:1,194.9/sec Avg wait:0.08 ms Tablespace Reads:5,254/sec Writes:43/sec  

Top 5 EventsEventPercentage of Total Timed Eventsfree buffer waits87.70%CPU time4.90%latch free4.21%buffer busy waits.84%write complete waits.63% 

Tablespace I/O StatsTablespaceRead/sAv Rd(ms)Blks/RdWrites/sRead%% Total IOTBS_INTODATA4,841 03.2 36 99%92.06%UNDOTBS1363 01 2 99%6.89%TBS_INDEX35 0.11 1 96%0.69% 

Load ProfileLogical reads:159,761/s Parses:83.26/s Physical reads:15,821/s Hard parses:0.56/s Physical writes:57/s Transactions:0.99/s Rollback per transaction:0.93% Buffer Nowait:99.98% 

1 Recommendations:Your database has relatively high logical I/O at 159,761 reads per second. Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity. CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking), using faster CPUs or adding more CPUs to your system. 

Instance EfficiencyBuffer Hit:90.11% In-memory Sort:99.95% Library Hit:99.65% Latch Hit:99.96% Memory Usage:94.11% Memory for SQL:52.48% 

1 Recommendations:Your shared pool maybe filled with non-reusable SQL with 94.11% memory usage. The Oracle shared pool contains Oracle′s library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. You can check thedba_hist_librarycache table in Oracle10g to see your historical library cache RAM usage. 

SQL StatisticsClick here to see all SQL data 

Wait EventsEventWaitsWait Time (s)Avg Wait (ms)Waits/txnfree buffer waits24,10122,90895116.0latch free50,5311,0992233.6buffer busy waits51,511219434.3write complete waits1671649810.1enqueue7016423370.0db file scattered read1,818,87314401,210.2db file sequential read6,177,21010004,109.9log file sync2,00628141.3control file parallel write49113270.3log buffer space1311080.0

5 Recommendations:You have high free buffer wait time of 951 milliseconds. Free buffer waits commonly happen when your application is insert intensive (among many other factors), and Oracle requests a new block from the freelist. With a free buffer wait, Oracle requests RAM heap space for the new block but no space is available within the data buffer cache region. The remedy is tuning your data buffer cache which might include using faster disks, rebuilding with larger blocksizes, tuning your DBWR process, segregating hot tables into separate data buffers (using the multiple blocksize feature), optimizing your SQL to reduce data block requests (using highly-selective function-based indexes or materialized views) or by increasing the speed of your back-end disks. You can also optimize your db_cache_size and moving the hot objects to high-speed solid state disks .You have high latch free waits of 33.6 per transaction. The latch free wait occurs when the process is waiting for a latch held by another process. Check the later section for the specific latch waits. Latch free waits are usually due to SQL without bind variables, but buffer chains and redo generation can also cause them.You have excessive buffer busy waits with 34.3 per transaction. Buffer busy waits are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition. Using super-fast SSD can dramatically reduce wait times for other reads and in some cases lessen the buffer busy waits.You have excessive enqueue wait times at 2337 milliseconds. 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. Ensure that you are using locally-managed tables (if you see enqueue ST waits) and review your settings for INITRANS and MAXTRANS n(if you have enqueue TX waits). If you see enqueue TX waits, check for DML locks and ensure that all foreign keys are indexed.You have a high value for log file sync waits at 1.3 per transaction. Check to ensure that your application does frequent commits and consider moving your redo log files to the Write Accelerator. Also consider increasing your log_buffer size. 

Instance Activity StatsStatisticTotalper Secondper TransSQL*Net roundtrips to/from client343,186225.5228.3consistent gets243,022,988159,673.5161,691.9consistent gets - examination31,874,25220,942.421,207.1db block changes155,779102.4103.7execute count131,75686.687.7parse count (hard)8480.60.6parse count (total)126,72983.384.3physical reads24,080,76315,821.816,021.8physical reads direct24,42616.116.3physical writes88,25058.058.7physical writes direct26,13117.217.4redo writes2,2991.51.5sorts (disk)520.00.0sorts (memory)103,74568.269.0table fetch continued row8,064,7125,298.85,365.7table scans (long tables)5,3283.53.5table scans (short tables)26,25117.317.5workarea executions - onepass900.10.1

10 Recommendations:You have high network activity with 225.5 SQL*Net roundtrips to/from client per second, which is a high amount of traffic. Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit from bulk collection by using PL/SQL "forall" or "bulk collect" operators.You have 20,942.4 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce logical I/O, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.You have high update activity with 102.4 db block changes per second. The DB block changes are a rough indication of total database work. This statistic indicates (on a per-transaction level) the rate at which buffers are being dirtied and you may want to optimize your database writer (DBWR) process. You can determine which sessions and SQL statements have the highest db block changes by querying the v$session and v$sessatst views.You have high disk reads with 15,821.8 per second. Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage. You can monitor your physical disk reads by hour of the day using AWR to see when the database has the highest disk activity.You have high disk write activity with 58.0 per second. You should drill-down and identify the sessionsthat are performing the disk writes as they can cause locking contention within Oracle. Also investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process. In addition, you can dramatically improve your disk write speed by moving the high-write datafiles to a WriteAccelerator.You have 52 disk sorts during this period. Disk sorts are very expensive and increasing your PGA(sort_area_size or pga_aggregate_target) may allow you to perform these sorts in RAM.You have 8,064,712 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.You have 3.5 long table full-table scans per second. This might indicate missing indexes, and you can run plan9i.sql to identify the specific tables and investigate the SQL to see if an index scan might result in faster execution. If your large table full table scans are legitimate, look at optimizing yourdb_file_multiblock_read_count parameter.You have high small table full-table scans, at 17.3 per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD or the WriteAccelerator will significantly increase the speed of small-table full-table scans.You have excessive onepass PGA workarea executions with 90 non-optimal executions during this elapsed period. It is better to have "workarea executions - optimal" and you might consider optimizing your pga_aggregate_target parameter. 

Latch ActivityLatchGet Requests% Get Miss% NoWait MissWait Time (s)cache buffers lru chain193,02420.93.178library cache1,941,4030.21.58multiblock read objects4,863,6180.423session allocation125,1030.11simulator lru latch2,008,8140.13.120

2 Recommendations:You have a high value for cache buffer LRU chain waits with 20.9% get miss, and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer. Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE.You have high library cache waits with 0.2% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep. 

Buffer Pool AdvisoryCurrent:1,211,332,687 disk reads Optimized:150,412,688 disk reads Improvement:87.58% fewer The Oracle buffer cache advisory utility indicates 1,211,332,687 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 150,412,688, a 87.58% decrease. 

Init.ora Parameters ParameterValue db_block_size8,192 db_cache_size128MB db_file_multiblock_read_count16 hash_join_enabledtrue log_archive_starttrue pga_aggregate_target48MB query_rewrite_enabledfalse shared_pool_size128MB sort_area_size512KB _optimizer_cost_modelchoose session_cached_cursors50 cursor_sharingexact 

4 Recommendations:Your db_cache_size is 128MB, and this may be too small to fully cache your working set of frequently referenced tables and indexes. To see if an increase is right for you, see the v$db_cache_advice utility.You are not using your KEEP pool to cache frequently referenced tables and indexes. This may causeunnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this processusing scripts.Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.You have not enabled Materialized Views and Function-based indexes, which are very powerful features that require you to set query_rewrite_integrity and query_rewrite_enabled.