Hi all,

Oracle 8.1.7.3.0/ Win2K.

We have been experiencing real big problem recently in our production database. In less than 15 days, 3 times I had to bounce the database, which had never happened before. What happens is that system becomes dead slow and users are not able to open any forms in the application. They get hour glass constantly. Then, I need to bounce the database and then system starts behaving properly.

Today, I took the snapshot of some of the tables before bouncing the database such as v$session, v$session_wait, v$process, v$lock tables.

In v$session_wait, it shows 40 records with "latch free" event. Pl. see below.

Code:
EVENT	SID	P1TEXT	P1	P1RAW	P2TEXT	P2	P2RAW	P3TEXT	P3	P3RAW	PROGRAM	MODULE	OSUSER
latch free	5	address	20808456	013D8308	number	88	00000058	tries	1444	000005A4	ORACLE.EXE		SYSTEM
latch free	14	address	20808456	013D8308	number	88	00000058	tries	1430	00000596	vppdc.exe	VPXDBA	SYSTEM
latch free	46	address	20808456	013D8308	number	88	00000058	tries	1251	000004E3			SYSTEM
latch free	47	address	20808456	013D8308	number	88	00000058	tries	1488	000005D0			Oracle
latch free	54	address	20808456	013D8308	number	88	00000058	tries	1488	000005D0			Oracle
latch free	60	address	20808456	013D8308	number	88	00000058	tries	1433	00000599		FNDRSRUN	SYSTEM
latch free	75	address	20808456	013D8308	number	88	00000058	tries	1356	0000054C			SYSTEM
latch free	76	address	20808456	013D8308	number	88	00000058	tries	1511	000005E7	vppdc.exe	VPXDBA	SYSTEM
latch free	90	address	751556156	2CCBD63C	number	66	00000042	tries	24	00000018	dllhost.exe		CCIPComExecute
latch free	93	address	20808456	013D8308	number	88	00000058	tries	1013	000003F5		FNDRSRUN	SYSTEM
latch free	111	address	20808456	013D8308	number	88	00000058	tries	713	000002C9			SYSTEM
latch free	112	address	20808456	013D8308	number	88	00000058	tries	1419	0000058B			SYSTEM
latch free	119	address	20808456	013D8308	number	88	00000058	tries	614	00000266			SYSTEM
latch free	121	address	20808456	013D8308	number	88	00000058	tries	1570	00000622			SYSTEM
latch free	124	address	20808456	013D8308	number	88	00000058	tries	1022	000003FE			Oracle
latch free	125	address	20808456	013D8308	number	88	00000058	tries	1472	000005C0	vppdc.exe	VPXDBA	SYSTEM
latch free	134	address	20808456	013D8308	number	88	00000058	tries	426	000001AA	JDBC Thin Client	oracle.apps.per.selfservice.common.server.Common	SYSTEM
latch free	135	address	20808456	013D8308	number	88	00000058	tries	504	000001F8	JDBC Thin Client	oracle.apps.fnd.wf.worklist.server.WorklistAM	SYSTEM
latch free	137	address	20808456	013D8308	number	88	00000058	tries	1529	000005F9	vppdc.exe	VPXDBA	SYSTEM
latch free	138	address	751556156	2CCBD63C	number	66	00000042	tries	24	00000018	dllhost.exe		CCIPComExecute
latch free	142	address	20808456	013D8308	number	88	00000058	tries	501	000001F5	JDBC Thin Client	oracle.apps.per.selfservice.common.server.Common	SYSTEM
latch free	144	address	20808456	013D8308	number	88	00000058	tries	1514	000005EA			SYSTEM
latch free	146	address	20808456	013D8308	number	88	00000058	tries	1533	000005FD		FNDRSRUN	SYSTEM
latch free	147	address	20808456	013D8308	number	88	00000058	tries	1424	00000590			SYSTEM
latch free	151	address	20808456	013D8308	number	88	00000058	tries	214	000000D6	vppdc.exe	VPXDBA	SYSTEM
latch free	153	address	20808456	013D8308	number	88	00000058	tries	846	0000034E			SYSTEM
latch free	154	address	20808456	013D8308	number	88	00000058	tries	375	00000177			SYSTEM
latch free	158	address	20808456	013D8308	number	88	00000058	tries	1525	000005F5			Oracle
latch free	161	address	20808456	013D8308	number	88	00000058	tries	934	000003A6			SYSTEM
latch free	164	address	20808456	013D8308	number	88	00000058	tries	678	000002A6			SYSTEM
latch free	172	address	20808456	013D8308	number	88	00000058	tries	1092	00000444			SYSTEM
latch free	176	address	20808456	013D8308	number	88	00000058	tries	1341	0000053D			SYSTEM
latch free	179	address	20808456	013D8308	number	88	00000058	tries	1044	00000414			SYSTEM
latch free	180	address	20808456	013D8308	number	88	00000058	tries	1554	00000612	JDBC Thin Client	oracle.apps.fnd.wf.worklist.server.WorklistAM	SYSTEM
latch free	183	address	20808456	013D8308	number	88	00000058	tries	1567	0000061F	JDBC Thin Client	oracle.apps.fnd.wf.worklist.server.WorklistAM	SYSTEM
latch free	184	address	20808456	013D8308	number	88	00000058	tries	1547	0000060B	vppdc.exe	VPXDBA	SYSTEM
latch free	189	address	20808456	013D8308	number	88	00000058	tries	1252	000004E4		FNDRSRUN	SYSTEM
latch free	191	address	20808456	013D8308	number	88	00000058	tries	1509	000005E5			Oracle
latch free	192	address	20808456	013D8308	number	88	00000058	tries	258	00000102			Oracle
latch free	193	address	20808456	013D8308	number	88	00000058	tries	886	00000376			Oracle
Now, if you look at value of P2, which is 88. This is a 'sort extent pool' latch.

I want to know whether this would have caused the system slow down.

Recently, there is an increase in number of application/database users. But, I think that our SGA and buffer size are large enough.

SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE = 1572864
TEMP Tablespace properties:
-----------------------------
Type: LMT, TEMPORARY
Allocation: 10M uniform
Size: 2GB
Datafile= 1 datafile


I am listing below list of some of the init.ora parameters for your suggesions.

db_block_size = 8192
db_block_buffers = 75000
db_block_lru_latches = 12

shared_pool_size = 471859200
SHARED_POOL_RESERVED_SIZE = 47185920
LOG_CHECKPOINT_INTERVAL = 100000
processes = 200
optimizer_mode = CHOOSE
SESSION_CACHED_CURSORS = 200
# New 11i parameters
_sqlexec_progression_cost = 0
parallel_max_servers = 16 # 4 times number of CPUs on database server node = 4*4 = 16
_complex_view_merging = true
_fast_full_scan_enabled = false
_like_with_bind_as_equality = true
_optimizer_mode_force = true
_or_expand_nvl_predicate = true
_ordered_nested_loop = true
_push_join_predicate = true
_push_join_union_view = true
_sort_elimination_cost_ratio = 5
_table_scan_cost_plus_one = true
_use_column_stats_for_function = true
aq_tm_processes = 1
optimizer_max_permutations=2000 # Default was 80000
_new_initial_join_orders = TRUE
_optimizer_undo_changes = false
_shared_pool_reserved_min_alloc = 4100
always_semi_join = NESTED_LOOPS #DO NOT SET db_block_checksum = TRUE
dml_locks = 1000
enqueue_resources = 5000
hash_area_size = 3145728 #SORT_AREA_SIZE*2
java_pool_size = 10485760
job_queue_interval = 90
log_buffer = 10485760
log_checkpoint_timeout = 72000
log_checkpoints_to_alert = TRUE
max_dump_file_size = UNLIMITED
max_enabled_roles = 100
OPEN_CURSORS = 500
SORT_AREA_SIZE = 1572864
SORT_AREA_RETAINED_SIZE = 1572864
compatible = 8.1.7
db_file_multiblock_read_count = 8

Thanks in advance.