-
Urgent Production problem
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.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Forms applications against the database? Is someone using Form Builder/doing forms development against the database? That can lock up transactions in a heartbeat. Disconnect the FB connection, and transactions go back to normal.
-
Hi stecal,
Nobody is doing this kind of task.
Any help please...
Thanks,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Hi all,
I thought there will be many takers for this one-off performance related thread.
I am waiting for expert suggestions from Oracle Guru like Jeff, Pando, Sambavan....
Thanks,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
I have stayed away because there's not enough information to adequately answer the question.
Jeff Hunter
-
Originally posted by marist89
I have stayed away because there's not enough information to adequately answer the question.
Cool Jeff!
Could you please tell me which additional info. you need?
I had taken snapshots of some the tables mentioned above. I then had to bounce the database. So, I cannot give any dynamic information now.
Thanks,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|