Home » RDBMS Server » Performance Tuning » Performance issue (Oracle 9.2.0.1.0)
| Performance issue [message #338895] |
Wed, 06 August 2008 03:57  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
I have a web based application.When I am inserting a record from the application to the database the record is taking 35 seconds.But when I am doing some changes in the application there are two tables which are getting affected one table is coabalances and the other table is coaratios.Basically this application is making a query and then the query is getting fired through the database.The table coabalances and coaratios is having one indexes on 5 columns.Now Since inserts are taking 35 seconds and update is taking 6.5 minutes to save from the application to the database.Could you please help me as to what must be the cause?.Also these are the records which the control makes these update statements and are taking 6.5 minutes to update.May I know what must be the reason.Any help sir?
Now I wanted to do bulk updates in lesser amount of time
Update COABALANCES set value = '83712', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 10 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '91000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 10 and StmtId = 4 and coaid = 3 and user_id = 21;
Update coabalances set value = '1000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 11 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '1000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 11 and StmtId = 4 and coaid = 3 and user_id = 21;
Update coabalances set value = '1000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 15 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '1000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 15 and StmtId = 4 and coaid = 3 and user_id = 21;
Update coabalances set value = '1000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 16 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '1000', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 16 and StmtId = 4 and coaid = 3 and user_id = 21;
Update coabalances set value = '2134', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 17 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '1800', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 17 and StmtId = 4 and coaid = 3 and user_id = 21;
Update coabalances set value = '76765', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 21 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '3209', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 22 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7977', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 24 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '84712', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 12 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '43.7331387753024', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 13 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '4134', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 18 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '88846', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 19 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '79974', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 23 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '87951', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 25 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '76399', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 27 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '80591', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 29 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '8255', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 30 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7027', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 32 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 34 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 39 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 45 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 46 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 47 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 49 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 54 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 55 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '100', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 56 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 58 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7027', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 59 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '88846', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 61 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '76399', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 62 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '8255', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 63 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7027', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 64 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7521', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 65 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 66 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 67 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '8255', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 68 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7027', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 69 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7521', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 70 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 71 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6293', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 72 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '4192', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 73 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7027', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 74 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6139', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 82 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '13896', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 94 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '20035', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 95 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '7221', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 104 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '27256', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 105 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '10725', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 117 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '37981', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 118 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '2223', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 136 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '31687', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 137 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '5880', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 142 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '414', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 151 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '414', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 154 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 159 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '37981', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 160 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '10725', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 161 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '11652', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 162 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '1.58', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 163 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '2.54', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 164 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0.67', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 165 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 175 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '27256', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 177 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '6139', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 178 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '2609', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 179 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '4612', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 180 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '8748', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 181 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '17946', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 182 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '17015', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 183 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '12248', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 184 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '8491', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 185 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 186 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 187 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '2223', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 188 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 189 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '5405', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 190 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 191 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '414', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 192 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 193 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 194 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coabalances set value = '0', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 195 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '0.44', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 197 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '0.08', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 198 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '1.24', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 199 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '0.57', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 200 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '2.54', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 201 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '8.38', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 202 and StmtId = 3 and coaid = 3 and user_id = 21;
Update coaratios set value = '88846', trans_datetime = sysdate where BorrId = 3549 and CoaRow = 203 and StmtId = 3 and coaid = 3 and user_id = 21;
This table is having indexes on borrid,coarow,stmtid,coaid and user_id.Now I wanted to do bulk updates in lesser amount of time
Any help sir
[Updated on: Wed, 06 August 2008 04:10] Report message to a moderator |
|
|
|
|
|
| Re: Performance issue [message #338997 is a reply to message #338925] |
Wed, 06 August 2008 07:23  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Do I have to rebuild these indexes everyday to increase the performance of the application?
If suppose rebuilding the indexes is not able to increase the performance of my application then what I should to increase the performance of my application of the data which is going to the database?
Regards
|
|
|
|
|
|
| Re: Performance issue [message #339161 is a reply to message #339011] |
Wed, 06 August 2008 23:37 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Index rebuilding depends on how much data you are inserting and upadating daily on the table.
If it's going to be frequent then more frequently you have to rebuild.
one thing you should consider that when you are inserting and updating a table having indexes.The index entry is also updated.
i.e. if you have more index then you can be in problem in OLTP environment.
If you create indexes then DML becomes slower.And if not then Select become slower.
you have to decide whether you want faster select or faster DML.
Think about your Indexes in place??Do you really need them.
Regards,
Rajat
|
|
|
|
|
|
| Re: Performance issue [message #339345 is a reply to message #339229] |
Thu, 07 August 2008 05:39 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Your application is NOT using bind variables.
Yes my application is using bind variables
Do you have ONE index on (borrid,coarow,stmtid,coaid,user_id) or did you define 5 indexes (index for each column)?
No I have one composite index on 5 columnns which are used in filter condition
Did you gather stats for the table with DBMS_STATS?
Yes I did that
exec dbms_stats.gather_table_stats('usernname','table_name',estimate_percent =>15);
Post EXPLAIN.
I am posting the explain
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=30)
1 0 UPDATE OF 'COABALANCES'
2 1 INDEX (RANGE SCAN) OF 'IDX_COABALANCES_5FLDS' (NON-UNIQU
E) (Cost=1 Card=1 Bytes=30)
|
|
|
|
|
|
| Re: Performance issue [message #339389 is a reply to message #339365] |
Thu, 07 August 2008 07:21 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Dealing with a few points raised in previous posts:
---------------------------------------------------
Ignore the advice above about rebuilding indexes - it will provide no long or medium term benefits, and may well noticably slow down inserts and updates in the short term.
The existance of one index isn't going to make a noticable difference to the speed of your inserts/updates either, so don't worry about that.
The updates that you posted in the opening post are not using bind variable - they are using hardcoded literal values.
More information required
-------------------------
How long does it take to do an update from SQL*Plus on the server? The explain plan shows no problems, so this will determine if the problem is with the database/server or with the network.
Are there any foreign key constraints dependant on this table?
Are there any triggers that fire on insert or update?
|
|
|
|
| Re: Performance issue [message #339413 is a reply to message #339389] |
Thu, 07 August 2008 08:06 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Are there any foreign key constraints dependant on this table?
Are there any triggers that fire on insert or update?
| Quote: |
No Trigger.It is the application that is making updates when we do some changes in the form and after clicking on the 'SAVE TO DATABASE' button the entry is getting fired or updated into the database
|
[Updated on: Sat, 09 August 2008 01:12] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Performance issue [message #339433 is a reply to message #339426] |
Thu, 07 August 2008 08:56 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
I dont think sysdate would be a problem.Why do you think sysdate must be a problem.If I am runing this thing from my machine to update its takin 4 seconds in a loop
I also dont think there must be a network issue?Because If network issue would have caused then only why inserts are happening in 35 seconds.why update is taking 6.5 minutes?
Please clarify.Is there any performance hint that we can use in the query that would help us to increase the performance of an update statement?
Regards
[Updated on: Thu, 07 August 2008 08:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Performance issue [message #339486 is a reply to message #339345] |
Thu, 07 August 2008 10:40 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Index will help with selects. For all other DML, it needs overhead.
Rarely, indexes need to be rebuilt. Just recollect stats for tables and indexes after every major load.
From what you have posted, seems you are collecting stats only for tables.
Are you collecting stats for indexes? Else use CASCADE=>TRUE along with and try again.
[Updated on: Thu, 07 August 2008 10:40] Report message to a moderator |
|
|
|
| Re: Performance issue [message #339663 is a reply to message #339486] |
Fri, 08 August 2008 01:52 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear sir
Let me tell you that the tables which is getting affected is having 24 Laks datai.e coabalances is having 2402843 records and coaratios is having 147241 records and the record keeps on increasing.
After changing number from 1000 to 1200 in the application When I click on save to database button the time to update one record is taking more than 2 and half minutes.Why It is taking now 2 and the half minutes.Could you please tell me what is too be done to increase the performance of my application.I cannot purge the data also since its a very confidential data of the bank.?
[Updated on: Fri, 08 August 2008 01:53] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Performance issue [message #339798 is a reply to message #339780] |
Fri, 08 August 2008 09:19 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I was just looking at the data in your first post.
All the updates you show have the same values for BorrId, StmtId, Coaid and User_Id. How mnany distinct values are there for these fields.
Also, have you had a chance yet to find out how long it takes to do one of these updates directly on the server, so that any overhead caused by your application or the network is eliminated. This will tell us whether the problem is a database one or something else.
|
|
|
|
|
|
| Re: Performance issue [message #339904 is a reply to message #339821] |
Sat, 09 August 2008 10:13 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Well Let me tell you that my Ram size is 2GB.I have Windows 2003 server operating system.Two processors.These are the parameters I have set for my server
These are the parameters I have set in init.ora and after setting the parameters I have generated the statspack report.the ram size is 2 GB.
Existing database parameters :
Sga_max_size = 657 mb (will remain same )
Db_cache_size = recommended 170 mb ) -à as db cache hit ratio is only 45%
Shared_pool_size = 152 mb (will remain same) à as hit ratio is proper
Log_buffer = .5m (recommended 5 mb )
Java_pool_size = 152 mb (will remain same )
Large_pool_size = 8m (recommended 40mb)
PGA_AGGREGATE_TARGET = (RECOMMENDED 45MB ) à FOR TIME BEING
Regards
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 16:33:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
of your update process.