-
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
-
locks do not slows performance
-
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.
-
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.
-
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
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
-
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"
-
None of the DML triggers are on the tables they are on the views.
Parijat Paul
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|