应用服务器是同一台Weblogic，连接池中开的连接数是160，之前一直在9i的环境下正常，现在用10g for Linux和10g for Windows 2003进行测试：
1) 一直在使用的9i的环境，已经使用多年，一切正常
环境：Windows 2003（32位） + Oracle 9.2.0.1
参数设置如下：
aq_tm_processes
1
background_dump_dest
D:\oracle\admin\ORA9\bdump
compatible
9.2.0.0.0
control_files
D:\oracle\oradata\ORA9\CONTROL01.
core_dump_dest
D:\oracle\admin\ORA9\cdump
db_block_size
4096
db_cache_size
771751936
db_domain
db_file_multiblock_read_count8
db_keep_cache_size
268435456
db_name
ORA9
dispatchers
(PROTOCOL=TCP) (SERVICE=ORA9XDB)
fast_start_mttr_target
300
hash_join_enabled
FALSE
instance_name
ORA9
java_pool_size
16777216
job_queue_processes
12
large_pool_size
25165824
log_buffer
2097152
open_cursors
4000
parallel_automatic_tuningTRUE
pga_aggregate_target
524288000
processes
2500
query_rewrite_enabled
FALSE
remote_login_passwordfile EXCLUSIVE
sessions
2755
sga_max_size
1302930712
shared_pool_size
134217728
sort_area_size
524288
star_transformation_enabled FALSE
timed_statistics
TRUE
undo_management
AUTO
undo_retention
3600
undo_tablespace
UNDOTBS1
user_dump_dest
D:\oracle\admin\ORA9\udump
workarea_size_policy
AUTO
Statuspack中的部分内容：
Load Profile
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Redo size:
5,103,114.69
9,895.03
Logical reads:
164,364.02
318.70
Block changes:
44,109.08
85.53
Physical reads:
44.77
0.09
Physical writes:
701.97
1.36
User calls:
15,114.28
29.31
Parses:
925.36
1.79
Hard parses:
0.00
0.00
Sorts:
739.15
1.43
Logons:
0.05
0.00
Executes:
30,393.96
58.93
Transactions:
515.72
% Blocks changed per Read: 26.84Recursive Call %:70.80
Rollback per transaction %:0.16 Rows per Sort: 1.72
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %:100.00
BufferHit %: 99.97In-memory Sort %:100.00
Library Hit %:100.04Soft Parse %:100.00
Execute to Parse %: 96.96 Latch Hit %: 99.43
Parse CPU to Parse Elapsd %: 92.01 % Non-Parse CPU: 99.33
Shared Pool StatisticsBegin End
------------
Memory Usage %: 51.57 51.56
% SQL with executions>1: 81.46 81.63
% Memory for SQL w/exec>1: 78.28 78.45
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0
7,403
0
%PGA%Auto %Man
PGA AggrAuto PGA PGA MemW/A PGAW/AW/AW/A Global Mem
Target(M) Target(M)Alloc(M) Used(M)MemMemMemBound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 500 351191.60.0 .0 .0 .0 25,600
E 500 354185.50.0 .0 .0 .0 25,600
-------------------------------------------------------------
2)搭建的Linux测试环境，在此环境下测试没有出现异常。
环境如下：Red hat AS 4 Update 7（32位） + Oracle 10.2.0.1.0
参数设置如下：
Parameter Name Begin value End value (if different)
audit_file_dest /app/oracle/admin/ora92/adump
background_dump_dest /app/oracle/admin/ora92/bdump
compatible
10.2.0.1.0
control_files
/app/oracle/oradata/ora92/control01.ctl, /app/oracle/oradata/ora92/control02.ctl, /app/oracle/oradata/ora92/control03.ctl
core_dump_dest
/app/oracle/admin/ora92/cdump
db_block_size
8192
db_cache_size
419430400
db_domain
db_file_multiblock_read_count8
db_keep_cache_size134217728
db_name ora92
db_recovery_file_dest/app/oracle/flash_recovery_area
db_recovery_file_dest_size2147483648
dispatchers (PROTOCOL=TCP)(SERVICE=ora92XDB)
java_pool_size
16777216
job_queue_processes 10
large_pool_size 33554432
log_buffer
15316992
nls_language SIMPLIFIED CHINESE
nls_territory
CHINA
open_cursors 2000
pga_aggregate_target 419430400
processes
1500
remote_login_passwordfileEXCLUSIVE
sessions
1655
sga_target
1241513984
shared_pool_size 134217728
undo_management AUTO
undo_retention
3600
undo_tablespace UNDOTBS1
user_dump_dest
/app/oracle/admin/ora92/udump
workarea_size_policy AUTO
AWR中的部分内容：
Load Profile
Per SecondPer Transaction
Redo size: 2,562,624.78 10,165.12
Logical reads: 71,298.98282.82
Block changes: 20,522.6481.41
Physical reads:29.050.12
Physical writes:159.020.63
User calls:7,381.69 29.28
Parses:5,086.08 20.17
Hard parses:1.23 0.00
Sorts: 486.471.93
Logons:0.07 0.00
Executes: 15,235.3360.43
Transactions: 252.10
Cache Sizes
Begin End
Buffer Cache: 752M816MStd Block Size: 8K
Shared Pool Size: 240M176MLog Buffer: 15,192K
Shared Pool Statistics
BeginEnd
Memory Usage %: 65.8191.15
% SQL with executions>1: 87.3080.04
% Memory for SQL w/exec>1:87.6390.44
PGA Aggr Summary
PGA Cache Hit %W/A MB Processed ExtraW/A MB Read/Written
100.00 3,612
0
PGA Aggr Target Stats
PGA Aggr Target(M) Auto PGA Target(M) PGA Mem Alloc(M)W/A PGA Used(M)%PGA W/A Mem %Auto W/A Mem %Man W/A MemGlobal Mem Bound(K)
B 40025
846.02 0.000.00 0.000.0081,920
E 40025
1,347.220.000.00 0.000.0081,920
3)搭建的Windows 2003测试环境，在此环境下一直报ORA-04030的错。
环境如下：Windows 2003（32位） + Oracle 10.2.0.1.0
参数设置与2)中的完全相同，但在测试过程中一直报错：
ORA-04030: 在尝试分配 8389132 字节 (pga heap,redo read buffer) 时进程内存不足
疑问：
1. 应用服务器是同一台Weblogic，连接池中开的连接数是160。为何9i和10g相比PGA的使用相差如此之大？是否因为某个参数的DEFAULT值在10g中发生了变化而引起的？比如说SESSION_CACHED_CURSORS。
2. 为何采用同样的设置参数在10g for linux上正常，而在10g for Windows 2003上就会出错呢？
|
