DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Odd Database 'hang'

  1. 06-04-2004, 01:50 PM #1
    jhmartin
    jhmartin is offline Member
    Join Date
    Jun 2001
    Posts
    242

    Odd Database 'hang'

    I have a database with about 1500 users, say a1 through a1500. The tables are owned by the b1 user, with public synonyms on all of them, which are what the a1-a1500 users access constantly throughout the day.

    User b2 has a private synonym pointing to a table owned by b1. When user b2 attempts to drop their private synonym and create a new one with the same ddl, the database appears to hang for about 20 minutes (when under load). The database does not complete new connections (sqlplus does not return a prompt) and existing connections get no response.

    After the 20 minutes the database resumes and the DDL has completed.

    Any ideas what might cause this?
    Last edited by jhmartin; 06-04-2004 at 01:55 PM.
    Reply With Quote Reply With Quote
  2. 06-04-2004, 04:25 PM #2
    tamilselvan
    tamilselvan is offline Senior Advisor
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Are you using 8.1.6 ?

    Tamil
    Reply With Quote Reply With Quote
  3. 06-04-2004, 05:20 PM #3
    jhmartin
    jhmartin is offline Member
    Join Date
    Jun 2001
    Posts
    242
    8.1.7.4 64bit under solaris.
    Reply With Quote Reply With Quote
  4. 06-04-2004, 05:36 PM #4
    Axr2
    Axr2 is offline Senior Advisor
    Join Date
    Oct 2002
    Posts
    807
    Are you essentially saying that - during periods of high acitivity, the "drop synonym" and "create synonym" issued by b2 hangs for 20mts?! That's very strange.
    Reply With Quote Reply With Quote
  5. 06-04-2004, 06:37 PM #5
    jhmartin
    jhmartin is offline Member
    Join Date
    Jun 2001
    Posts
    242
    Yes, the combo of those two commands causes the entire db to hang during high activity. I don't really understand why, since the synonyms in question are private synonyms belonging to a user who only has one session open. It has happened the two times I've seen that operation attempted. That synonyms only has a few dependencies too. Very odd.

    Not that it necessairily matters, but the db is in MTS mode. Under low volume the operation is nearly instantaneous.
    Last edited by jhmartin; 06-04-2004 at 06:42 PM.
    Reply With Quote Reply With Quote
  6. 06-04-2004, 07:33 PM #6
    Axr2
    Axr2 is offline Senior Advisor
    Join Date
    Oct 2002
    Posts
    807
    What! At first, I thought the 'drop synonym' command was hanging.
    What you're saying is that the whole database hangs with a
    'drop synonym;'!! That's downright bizzare.

    Run statspack at appropriate intervals. Compare pre and post-hang reports. Look at your logs. I'd bet the problem lies elsewhere and has nothing to do with the synonym.

    How about posting what you're seeing. Set timing on. Show what's exactly happening. Can you duplicate it at will? (by simulating high-loads).
    Reply With Quote Reply With Quote
  7. 06-05-2004, 02:33 AM #7
    abhaysk's Avatar
    abhaysk
    abhaysk is offline Krishna
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    suerly problem lies with something else then drop private synonym.. (cant say though 100%)...

    But tell me what is the point in droping synonym and recreating them????
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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