ORA-4031 Troubleshooting Tool ???
- by Takeyoshi Sasaki
ORA-4031 ???????????????????? SGA ????????????(??????)??????????????????????????????????????????????????? ORA-4031 ??????????????????? ORA-4031 Troubleshooting Tool ???????????
ORA-4031 Troubleshooting Tool ??
ORA-4031 Troubleshooting Tool ? ORA-4031 ????????? ORA-4031 ???????????????????????????????????WEB????????????????????????????????My Oracle Support ??????????????????????? ORA-4031 ??????????????????????????????ORA-4031 ??????????????????????????????
ORA-4031 Troubleshooting Tool ????????
My Oracle Support ?????? Diagnostic Tools Catalog ?? ORA-4031 Troubleshooting Tool ???????????????? ORA-4031 Troubleshooting Tool ???????????????
ORA-4031 Troubleshooting Tool ?????
???2???????????????
ORA-4031 ?????????????????????????????ORA-4031 Troubleshooting Tool ????????????????????????????????????????ORA-4031 ????????????????????
???????????????
ORA-4031????????????
????????????? ORA-4031 ?????? AWR????????????????????????????????????????????????????????????
????·???????·????????????
ORA-4031 ??????????
????????? SR ??????????????????????????
[ADR] ????·??????·??????????
[10g ???] AWR?????(STATSPACK????)????
?????????? ORA-4031 ????????????????????????????????????????????? ORA-4031 ?????????????? ORA-4031 ??????1?1??????????? ORA-4031 Troubleshooting Tool ???????????(??????????????)????????
ORA-4031 ?????????
????????????????????????????????????????????????
??????????????????????????????????????1)High Session_Cached_Cursor Setting Causing Excessive Consumption of Shared Pool???SESSION_CACHED_CURSOR ??????????????????????????????????????????????2)Insufficient SGA Free Memory at StartupThis issue could occur if in the init.ora parameters of your Alert log, (shared_pool_size + large_pool_size + java_pool_size + db_keep_cache_size + streams_pool_size + db_cache_size) / sga_target is greater than 90%.????????????????????????? ?????????????? shared_pool_size, large_pool_size, java_pool_size, db_keep_cache_size, streams_pool_size, db_cache_size ????? /sga_target ??? 90% ??????????????????sga_target ??? memory_target ??????????????????????????????????????????????????????????????????? shared_pool_size ???????????????????????????????????????????????????????????????????????(?????????)? sga_target ?????????????????????????????????????????????????????????????
?????????????????????????????????????????????1)In your Alert log,* Look for parameters under "System parameters with non-default values:". If session_cached_cursor * 2000 / shared_pool_size is greater than 10%, then session_cached_cursors are consuming significant shared_pool_size.??? ????????????? "System parameters with non-default values:" ???? session_cached_cursor * 2000 ??? shared_pool_size ? 10% ????????????????????????
???2)
In your Alert log, SGA Utilization (Sum of
shared_pool_size, large_pool_size, java_pool_size,
db_keep_cache_size, streams_pool_size and db_cache_size over
sga_target) is 99%, which might be too high. ??? shared_pool_size, large_pool_size, java_pool_size,
db_keep_cache_size, streams_pool_size and db_cache_size ???? sga_target ? 99% ????????????????????
?????????????????????????????? My Oracle Support ??????????????????????????1)Decrease the parameter SESSION_CACHED_CURSORSSESSION_CACHED_CURSORS ???????????????????????2)Reduce the minimum values for the dynamic SGA components to allow memory manager to make changes as neededSGA ?????????????????(???)?????????????????? 2????????????????????? ORA-4031 Trobuleshooting Tool ?????????????????????????????????????????
ORA-4031 Troubleshooting Tool ?????? ORA-4031 ??????????????????????ORA-4031 Troubleshooting Tool ???????????????????????????????????????????????????????ORA-4031 ????????????????????????????ORA-4031 ??????????????? ORA-4031 Troubleshooting Tool ?????????