Urgent Production problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Urgent Production problem

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    I have stayed away because there's not enough information to adequately answer the question.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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
  •  



Click Here to Expand Forum to Full Width