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

Database Info		Fri Apr 22 2011 10:48:33 GMT+0800 (China Standard Time)
DB ID	Instance	Release	RAC	Host
4190444978	hotel	9.2.0.4.0	NO	localhost.lo
Elapsed:	25.37 (min)	1,522.2 (sec)
DB Time:	435.35 (min)	26,120.87 (sec)
Cache:	128 MB
Block Size:	8,192 bytes
Transactions:	0.99 per second

Performance Summary
Physical 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 Events
Event	Percentage of Total Timed Events
free buffer waits	87.70%
CPU time	4.90%
latch free	4.21%
buffer busy waits	.84%
write complete waits	.63%

Tablespace I/O Stats
Tablespace	Read/s	Av Rd(ms)	Blks/Rd	Writes/s	Read%	% Total IO
TBS_INTODATA	4,841	0	3.2	36	99%	92.06%
UNDOTBS1	363	0	1	2	99%	6.89%
TBS_INDEX	35	0.1	1	1	96%	0.69%

Load Profile
Logical 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 ( or ), using  or adding  to your system.

Instance Efficiency
Buffer 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  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 the table in Oracle10g to see your historical library cache RAM usage.

SQL Statistics

Wait Events
Event	Waits	Wait Time (s)	Avg Wait (ms)	Waits/txn
free buffer waits	24,101	22,908	951	16.0
latch free	50,531	1,099	22	33.6
buffer busy waits	51,511	219	4	34.3
write complete waits	167	164	981	0.1
enqueue	70	164	2337	0.0
db file scattered read	1,818,873	144	0	1,210.2
db file sequential read	6,177,210	100	0	4,109.9
log file sync	2,006	28	14	1.3
control file parallel write	491	13	27	0.3
log buffer space	13	1	108	0.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 . 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 , rebuilding with , , segregating hot tables into separate data buffers (using the  feature),  to reduce data block requests (using highly-selective  or ) 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 .

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.  are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index  parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management () in the tablespace definition. Using super-fast 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  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 . Also consider .

Instance Activity Stats
Statistic	Total	per Second	per Trans
SQL*Net roundtrips to/from client	343,186	225.5	228.3
consistent gets	243,022,988	159,673.5	161,691.9
consistent gets - examination	31,874,252	20,942.4	21,207.1
db block changes	155,779	102.4	103.7
execute count	131,756	86.6	87.7
parse count (hard)	848	0.6	0.6
parse count (total)	126,729	83.3	84.3
physical reads	24,080,763	15,821.8	16,021.8
physical reads direct	24,426	16.1	16.3
physical writes	88,250	58.0	58.7
physical writes direct	26,131	17.2	17.4
redo writes	2,299	1.5	1.5
sorts (disk)	52	0.0	0.0
sorts (memory)	103,745	68.2	69.0
table fetch continued row	8,064,712	5,298.8	5,365.7
table scans (long tables)	5,328	3.5	3.5
table scans (short tables)	26,251	17.3	17.5
workarea executions - onepass	90	0.1	0.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 "" 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  will result in a flatter index tree structure.

You have high update activity with 102.4 db block changes per second. The  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  (DBWR) process. You can determine which sessions and SQL statements have the  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 storage. You can  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 that are performing the disk writes as they can cause locking contention within Oracle. Also investigate moving your high-write datafiles to a  to improve the speed of the . In addition, you can dramatically improve your disk write speed by moving the high-write datafiles to a .

You have 52 disk sorts during this period. Disk sorts are very expensive and (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  might result in faster execution. If your large table full table scans are legitimate, look at optimizing your parameter.

You have high small table full-table scans, at 17.3 per second. Verify that your  is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to or the will significantly increase the speed of .

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  parameter.

Latch Activity
Latch	Get Requests	% Get Miss	% NoWait Miss	Wait Time (s)
cache buffers lru chain	193,024	20.9	3.1	78
library cache	1,941,403	0.2	1.5	8
multiblock read objects	4,863,618	0.4		23
session allocation	125,103	0.1		1
simulator lru latch	2,008,814	0.1	3.1	20

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 Advisory
Current:	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
Parameter	Value
db_block_size	8,192
db_cache_size	128MB
db_file_multiblock_read_count	16
hash_join_enabled	true
log_archive_start	true
pga_aggregate_target	48MB
query_rewrite_enabled	false
shared_pool_size	128MB
sort_area_size	512KB
_optimizer_cost_model	choose
session_cached_cursors	50
cursor_sharing	exact

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 .

You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause. 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 process.

Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of  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 .