Results 1 to 6 of 6

Thread: tuning problem

  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Red face Unanswered: tuning problem

    I installed database oracle 8.1.7.4.1 windows 2000. Database stores the phone calls and everything was fine till inserting call rate got max at 4 pm. After that i found a few trace files with ORA-00060 error deadlock detected and database shutdown by itself. I restared it but still have this error. No doubt that is a tuning problem(we have database with the same schema but different server and database parameters), some initial database parameters setup incorrectly but what and how big they should be.
    system has following parameters:
    4 GB RAM
    8 CPUs
    shared pool 50M
    buffer cache 760M
    large pool 0
    java pool 19M
    total SGA 838 M
    sort area size 5M
    RBS 10GB
    processes 171
    db_block_size 4096
    Any suggestion or ideas to explain the problem?
    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Deadlock does not occur because the parameter you have mentioned are not set properly, But if 2 or more users are waiting for the lock on the data which has already locked by one of them.

    So try to see the trace files and get the users who had dead lock detected in their session.

    Then use TopSessions, or any other oem tool to see the locking information on the sessions you have seslected.

    You can also use logminer to see the activity that caused deadlock. or

    You can start auditing on the selected users and check exactly what causes deadlock

    after detecting the actions which causes deadlock, Try to resolve it.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Based upon what specific evidence did you reach the conclusion that
    "No doubt that is a tuning problem"?
    If you choose to continue to chase wild geese,
    you won't get close to solving the actual problem.
    DEADLOCKS are the result of poor application design;
    not lack of resources.
    I have never, ever seen Oracle shut itself down due to ORA-00060s.
    Exactly over which resource(s) did the deadlock(s) occur?
    I suggest your company hire a real DBA who knows Oracle
    rather than waste your time & theirs chasing this problem.

  4. #4
    Join Date
    Dec 2003
    Posts
    3

    tuning problem

    2 Hings thank you i'll try.

    2anacedent thanks for your friendly advice.
    I wrote "No doubt that is a tuning problem" because we have exactly the same system with the same applications (not written by me) and it's working just fine. Inserting rate to the same table is higher then i have now.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You should be able to find trc files in your UDUMP directory that
    have the deadlock in them ... As mentioned earlier, this is because
    of application design where 2 sessions are competing for rows that
    the other has a lock on ... inside the trace files you will find (close to
    the top) information like:

    Session 136: obj - rowid = 00007FE0 - AAAH/gAAiAAAByOAA0

    This shows the object and rowid of that is locked ...
    00007FE0 is the object_id in hex ... Convert it to decimal as follows:

    Create a function that you can call .... Thru sqlplus

    create function HEXTODEC (Hexnum in CHAR)
    RETURN NUMBER IS
    X NUMBER;
    Digits NUMBER;
    Result NUMBER := 0;
    Current_Digit CHAR(1);
    Current_Digit_Dec NUMBER;
    begin
    Digits := LENGTH(Hexnum);
    for X in 1..Digits loop
    Current_Digit := UPPER(SUBSTR(Hexnum, X, 1));
    if Current_Digit in ('A','B','C','D','E','F') then
    Current_Digit_Dec := ASCII(Current_Digit) - ASCII('A') + 10;
    else
    Current_Digit_Dec := TO_NUMBER(Current_Digit);
    end if;
    Result := (Result * 16) + Current_Digit_Dec;
    end loop;
    return Result;
    end;
    /

    2. Execute the function: Thru sqlplus

    define x = '00007FE0'

    column hex_key new_value hex_key_value noprint
    select hextodec('&x') hex_key from dual;

    select object_name from all_objects where object_id = '&hex_key_value';

    undefine x;

    This will show the object that is locked ... You might try to increase
    the freelist and initrans of the table and indexes to try to reduce the
    locking ...

    HTH
    Gregg

  6. #6
    Join Date
    Dec 2003
    Posts
    3
    thank you Gregg for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •