Home » RDBMS Server » Performance Tuning » Performance issue (Oracle 9.2.0.1.0)
Performance issue [message #338895] Wed, 06 August 2008 03:57 Go to next message
manoj12
Messages: 185
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 #338925 is a reply to message #338895] Wed, 06 August 2008 04:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Insert and update gets slower when you have indexes in place.

Check whether index need to be rebuild??

Check This Link Also:-

http://www.dba-oracle.com/art_9i_indexing.htm

Regards,
Rajat

[Updated on: Wed, 06 August 2008 04:57]

Report message to a moderator

Re: Performance issue [message #338997 is a reply to message #338925] Wed, 06 August 2008 07:23 Go to previous messageGo to next message
manoj12
Messages: 185
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 #339011 is a reply to message #338997] Wed, 06 August 2008 08:43 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Appreciate your help on the below

Regards
Re: Performance issue [message #339161 is a reply to message #339011] Wed, 06 August 2008 23:37 Go to previous messageGo to next message
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 #339229 is a reply to message #338895] Thu, 07 August 2008 02:13 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Your application is NOT using bind variables.

2. Do you have ONE index on (borrid,coarow,stmtid,coaid,user_id) or did you define 5 indexes (index for each column)?

3. Did you gather stats for the table with DBMS_STATS?

4. Post EXPLAIN.

[Updated on: Thu, 07 August 2008 02:14]

Report message to a moderator

Re: Performance issue [message #339345 is a reply to message #339229] Thu, 07 August 2008 05:39 Go to previous messageGo to next message
manoj12
Messages: 185
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 #339365 is a reply to message #339345] Thu, 07 August 2008 06:34 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Apptreciate your help on the below

Regards
Re: Performance issue [message #339389 is a reply to message #339365] Thu, 07 August 2008 07:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Are there any foreign key constraints dependant on this table?
Quote:

No Foreign Key


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 #339414 is a reply to message #339413] Thu, 07 August 2008 08:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How long does it take to do an update from SQL*Plus on the server?
Re: Performance issue [message #339419 is a reply to message #339414] Thu, 07 August 2008 08:25 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
Yes my application is using bind variables


Can you show us that ?

By the look of your first post, It doesn't.
Re: Performance issue [message #339426 is a reply to message #339419] Thu, 07 August 2008 08:39 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
By seeing your first post
This may be causing problem
trans_datetime = sysdate (taking from system every time) Very Happy

Secondly if you are insterting then index may not of that importance, but if you are updating index can help because your have to search row and then you have to update.

Thirdly if you are selecting 10% row or more than that from OLTP Database tables (large) having index than that may degrade your performance & In OLAP if its more than 15% of row selected....
Re: Performance issue [message #339433 is a reply to message #339426] Thu, 07 August 2008 08:56 Go to previous messageGo to next message
manoj12
Messages: 185
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 #339439 is a reply to message #339433] Thu, 07 August 2008 09:05 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Appreciate your help for the same?

Regards
Re: Performance issue [message #339483 is a reply to message #339439] Thu, 07 August 2008 10:32 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
As every thing seems to be good at your end then.

why update is taking 6.5 minutes?
If you are updating, index can help because your have to search row and then you have to update.

secondly is you index is fregmented?

Do you have index or not?


And if all the above thing is fine than...due to row migration and row chaining... you are suffering.

you Insert happening in 35 seconds... how many row you are inserting... if it is 35 row than performance is bad Surprised

can u take this sysdate in any variable and then u try to update? let see what is your result.
Re: Performance issue [message #339486 is a reply to message #339345] Thu, 07 August 2008 10:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
manoj12
Messages: 185
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 #339673 is a reply to message #339663] Fri, 08 August 2008 02:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

JRowbottom wrote on Thu, 07 August 2008 15:13
How long does it take to do an update from SQL*Plus on the server?


Re: Performance issue [message #339780 is a reply to message #339673] Fri, 08 August 2008 08:07 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Recently saw my post about "taking sysdate in variable" then realised manoj need date and time both.

Manoj can read below link to know about bulk insert and update
http://www.dba-oracle.com/oracle_tips_rittman_bulk%20binds_FORALL.htm

If you do have access from documentation and metalink then also you can read about bulk insert/update/delete from there.

Thanks to roshan dharmender... he will start replying from tomorrow.

[Updated on: Fri, 08 August 2008 08:09]

Report message to a moderator

Re: Performance issue [message #339798 is a reply to message #339780] Fri, 08 August 2008 09:19 Go to previous messageGo to next message
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 #339821 is a reply to message #338895] Fri, 08 August 2008 14:54 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

Hi,
what operaqting system are youy running on?

what RAM do you have in your system?
what size SGA do you have?

regards

Alan
Re: Performance issue [message #339904 is a reply to message #339821] Sat, 09 August 2008 10:13 Go to previous messageGo to next message
manoj12
Messages: 185
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



Re: Performance issue [message #339962 is a reply to message #339904] Sun, 10 August 2008 07:24 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF ( event 10046, level Cool of your update process.
Previous Topic: How to simplify this pivot query
Next Topic: shink blob column
Goto Forum:
  


Current Time: Sat Dec 03 01:15:34 CST 2016

Total time taken to generate the page: 0.09903 seconds