Insert hangs???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Insert hangs???

  1. #1
    Join Date
    Apr 2001
    Posts
    103

    Insert hangs???

    Hello Gurus,

    We are using a tool called Marketic one to process letters.
    In the beginning it inserts in a table. The insertion seems to hangs. It is been 2 hours it is doing nothing.
    I have looked for the locks there is no locks on the table.
    My database is in Archive log mode.
    DB version is oracle 8.1.7.4
    operating system is Win2000 advanced server
    Can somebody guide me where I have to look for to find a solution?

    Thanks
    Naeem

  2. #2
    Join Date
    Feb 2001
    Posts
    295
    Is just the INSERT hung? Can you do other operations on other sessions? If you have not enabled automatic archival, the entire instance will hang until you archive logs manually (although I don't think there is a good chance of that happening on a production database without being noticed before).

    Hard to guess without further details.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Do the Alert logs tell you anything ??

    Gregg

  4. #4
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Have you looked to see what the session is waiting for?

    Here are a couple of queries that may help (need to provide the sid)

    col minutes for 999.999
    select event, total_waits, (time_waited/100)/60 Minutes, average_wait, max_wait
    from v$session_event
    where sid = &sid
    AND event NOT LIKE 'SQL*Net%'
    /


    SELECT sw.sid, s.username, sw.event, sw.wait_time,
    sw.state, sw.seconds_in_wait SEC_IN_WAIT
    FROM v$session s, v$session_wait sw
    WHERE s.username IS NOT NULL
    AND sw.sid = s.sid
    AND sw.event NOT LIKE '%SQL*Net%'
    /


    HTH,
    Jodie

  5. #5
    Join Date
    Apr 2001
    Posts
    103
    Last time something was written in alert log was at 12.20 today afternoon
    here is the log

    Current log# 4 seq# 4163 mem# 0: H:\REDO\LOG4.ORA
    Thu Jul 03 12:23:27 2003
    ARC3: Beginning to archive log# 6 seq# 4162
    Thu Jul 03 12:24:42 2003
    ARC3: Completed archiving log# 6 seq# 4162

    There is one process called WIStorageManage takes lot of system resource. Does anybody knows what this process is?

    Only Insert hungs. I am able to do select on the tables.
    How can I find out there is a automatic archive is going on???

    Thanks
    Naeem

  6. #6
    Join Date
    Apr 2001
    Posts
    103
    Here is the reply for your second query

    SID USERNAME EVENT WAIT_TIME STATE SEC_IN_WAIT
    31 MOPROD db file scattered read -2 WAITED UNKNOWN TIME 80

    Does this say anything to you.
    Naeem

  7. #7
    Join Date
    Feb 2001
    Posts
    295
    How can I find out there is a automatic archive is going on???
    You can check it by two ways, basically:
    - Type "archive log list" on SQL*Plus when connect as sysdba
    - Your database will hang when a log switch try to overwrite an unarchived redo log.

    That's not your case. I'd bet it's a lock issue, but since you told us there's nothing and the database is running fine, you may check if it is something related to the application.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  8. #8
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    The insert is waiting on IO for a FULL table scan.

    The following query will tell you the file#, block# and blocks.

    select sid, event, P1text, p1, p2text, p2, p3text, p3
    from v$session_wait
    where sid=&sid
    and event not like '%SQL%' and
    event not like '%rdbms%';


    Then put the results in:

    select owner, segment_name, segment_type, tablespace_name
    from dba_extents
    where file_id=&fileid_in
    and &blockid_in between block_id and Block_id + Blocks -1;


    Jodie

  9. #9
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Also, the STATE = WAITED_UNKNOWN_TIME because timed_statistics is set to false. Set TIMED_STATISTICS=TRUE, and you'll get acurate timing.

    Jodie

  10. #10
    Join Date
    Apr 2001
    Posts
    103
    What should I do If my database hangs? Do I have to re-start the database?
    Naeem

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