too many locks slowing the performance
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: too many locks slowing the performance

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    too many locks slowing the performance

    hi all,

    I have recently switched over my database to oracle 9i from 8i. I am finding deletes to be lot slower in oracle 9i as compared to that in oracle 8i. i use TOAD to monitor the database and there i could see that while deleting the parent records oracle is locking all the child tables as there are numerous ROW-S(SS)& ROW-X(SX)on these child tables. What could be going wrong and how can i make the deletes faster?

    The other problem is occuring while inserting almost 1,00,000 records into an user schema. This inserts which are done in one schema is slowing down every other users' performance. Once the jdbc process which inserts into the user data is started then other users face problems across the network even with their select statements. I tried to run export utility from my terminal to export an user data while the insert was running on a different user terminal and i found that the export had slowed down tremendously because of the inserts that's taking place in a different user schema. why would an insert statement running in one user schema slow down the performance of other users? I guess the oracle memory needs to be tuned for this huge inserts through jdbc. Pls tell me what could be going wrong and how to trace and solve the problem. The database server has 1GB of RAM and the present memory allocation to the oracle parameters are given below:-

    db_cache_size = 318767104
    java_pool_size = 33554432
    large_pool_size = 25165824
    log_buffer = 10485760
    sga_max_size = 791224400
    shared_pool_size= 318767104
    sort_area_size = 524288


    thanks for all ur time and response.

    Parijat Paul

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    locks do not slows performance

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: too many locks slowing the performance

    Originally posted by parijat67
    while deleting the parent records oracle is locking all the child tables
    Sounds to me like referential constraints with no index on the FK in the child table.

  4. #4
    Join Date
    Dec 2001
    Posts
    120
    i read somewhere that in oracle 9i we dont have to create indices on FKs. Is this correct or else i will have to create indices for FKs.

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    don't read somewhere, read documentation:
    http://download-west.oracle.com/docs...integ.htm#8565
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #6
    Join Date
    Dec 2001
    Posts
    120
    i have checked my tables and have found that each of the FKs already have an index on them. While checking my datatbase i found that "redo log space requests" is increasing when the huge inserts are started. at present the log buffer size is set to 10M and there are 4 redo log groups of 100M each. Each of these groups have only one memeber in them. The database is running in no archive log mode. The insert speed is very slow.. 10,000 records per hour appx.
    ---------------------------------------------
    Select name, value from v$sysstat
    Where name = 'redo log space requests';

    Redo Log Space Requests = 89

    ---------------------------------------------

    could the disk speed be a problem?? your suggestions plz.

    thanks

    parijat paul

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    DML Triggers on the table?
    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"

  8. #8
    Join Date
    Dec 2001
    Posts
    120
    None of the DML triggers are on the tables they are on the views.

    Parijat Paul

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by parijat67
    None of the DML triggers are on the tables they are on the views.

    Parijat Paul
    Not sure what are to talking here.

    Do u mean triggers ( DML ) on views??
    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"

  10. #10
    Join Date
    Dec 2001
    Posts
    120
    we have one view per table and on each of the views we have an instead of triggers which handles the DMLs. So there are no triggers on the tables they are all at the view level.

    Parijat

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