Link to home
Start Free TrialLog in
Avatar of ooiewe
ooiewe

asked on

Table is slow on update. How to monitor and resolve?

Table A is having initrans 120 (and its associated indexes), pctfree 20. While performing concurrent updates on this table from our application, we are consistently seeing that after 1000+- updates, the updates will get slower by 3000%, and not too long after that, the time taken for each updates will be back to normal.

The update is on the PK column(index unique scan).
The table size is 3000 records.
Average row length is 1000+-.

I don't think i can do anything to improve the sql. Considering setting the freelists for the table, but would it help in this case as it is update, not insert? What is the optimal value for initrans for 120 concurrent updates on this table? Does it have anything to do with the archiver archiving redo log (we don't multiplex redo log)? We are not doing batch commit, as this is an OLTP app. Log buffer is sized at 1M. Would parallelism on this table helps (take note that currently we are talking about 3000 records)? How do i monitor the contention on a particular table, as statspack only give me overall database performance stat?

Please ask me if you still need any info.
We are running Oracle 8.1.7.1 on UNIX.

Beginner "DBA"

(Since i am asking so many questions, please treat this as a sharing session rather than points earning attempt, appreciate and thanks for your effort in advance)
Avatar of Wadhwa
Wadhwa


Hi,

As your update slows and again regain its pace. It looks to me that you may have a locking problem on this table.
Just check whem your update is slow , the table is in which lock mode. Is it waiting for some other resouce to free.

As you are running oracle 8.1.7 you can get easily locking information from DBA_waiters ,dba_blockers etc.

Also your update may slow if you have rollback segment contention.

You update may slow if you have bitmap indexes on the table

You set inittrans 120 which i think is  too much.
Increasing the inittran parameter will allow multiple transactions access the same BLOCK in a table during high DML activities. Also remember that each additional INITTRAN requires extra 23 bytes in the block header

You also asked about archiver archiving redo log
For that you can check alert log and find out switch time of one log to another. also you can find here wheather archiver is waiting or not.


Parallism can give you better result if you have multiple CPU's. In 8i you can enable automatic parallism by setting parameter in init parameter file.



You can query v$waitstat or v$session_Wait to determine contention.

Hope this helps
Sam..
HI
the slow update due to some one lock table or 1 record in table or some one open form to update record and go without close these form so first disconnect all user then apply your update also this slow due to index on table and primay key so if u disable them so the update be very speed
---rmz--
Hi,


This is intelligent speculation.  

From things we know --   this is kind of stream of consciousness  (as I am currently dealing with a -3 hour timezone change and an extreme lack of sleep!)

1)   Your avg rowsize is apx 3K   (not including overhead)
2)   Figure blocksize range on most platforms of 2K to 32K... I am going to guess the standard 8K
3)   Initrans of of 120K on table  (I am concluding the initrans is not on the index.)
4)  You are updating the column of the primary key.


From this --  I am going to bet the problem is two fold,  and it is not contention -- It is space management & index!

Problem # 1
1)   By definition,  according to good nomalization & rdbms practice --  you do not update the primary key.
    A primary key is an immutable feature of a table row.    There is a side effect of this.   --  remember  PK in oracle is a constraint enforced by a uinque index.   (Think about the facts what happen when you uodate columns that are indexed -- the index effectivity degrades signigicantly.
2)   the initrans relates to locking the rows in a block.   assuming most platforms using a 32K block, with 3K rowsize -- that is a requirement of an initrans around 10 --  this is assuming that the row goes into the rdbms at apx the average rowize..       initrans  takes 23 bytes each instance --   120 *23 bytes =  2760 bytes  (  a little larger than 2K reserved for initrans per block...    
    Now logically break this down --  figuring about 120bytes for block overhead --  assuming PCTFREE & PCTUSED at oracle default   (10/40 --   PCTFREE at 10% is apx 800 bytes less space available after the initrans reservation )      This means in an 8K block  that apx 1.5  rows per block.    120 locking reservations is way toooooooooo  much!!!  
The end result  with all those DML updates --  row chaining and migration.  which means multiple phyical block reads to retrieve the same row.
This is something you are going to need to look at.

Okay --   Now,  you are looking up on the primary key --  updating the primary key --  which in essence in changing the index.     Remember -- leaf rows are discarded with updates --  the btree level is increasing with the splits, and space is not reclaimed.   But your lookup is based on the primary key --- an index which is expidentally degrading ...


I am going to bet -- if you look at your table and you index after the process is complete
you are going to find that the index tree is really degraded --  maybe 3 or 4 levels deep with a 20% or greater deleted leaf factor.


You are probably going to find that you have a number of chained/migrated rows in you base table.


The corrections would be:
1)  Adjust your INITRANS down to a level where you would expect the average rows per block
2)  adjust your  PCTFREE higher  on your base table to accound for differences in the way the average row changes oin your DML
3)   Adjust you index  PCTFREE higher  --  also rebuild your indexes after the process has completed.
    (assuming you are not going to be able to change the process so as not to updates the PK columns)


Things  that you need to query:
1)   Analyse the table pre & post process for chained rows -- this is going to give you a good indication for resetting PCTFREE
ANALYZE TABLE xxxxx LIST CHAINED ROWS;     (assuming you have setup the chained rows tables)
then compare the count of chained rows to the count of the rows in the table
ie:  SELECTcount( *) FROM chained_rows where table_name = 'xxxxx';

2)  Analyze the index per & post Process for the the two factors   --   BTREE level & deleted_leaf_rows
   I am betting the process is going to show that the btree is greated/equal to three  and that the deleted_leafs  are greater than 20%
remember the same applies to the index --  if degrades too much -- multiple block reads (which is aging out other rows of the db_cache) to read the appropriate table rowid...
You might want to look at the V$sysstat for fetch by chained row as well  (pre & post process)
ie:   select * from v$sysstat where upper(name) like '%FETCH%';

I am betting you are going to see it increase significantly!

Are you using the CBO?   You are using 8.1.7 --  I am wondering the state of DML monitoring on the table  --
ie: is the table set for Monitoring --   alter table xxxxxx  monitoring;

I have just looked back at the other comments --  Wadhwa  is making some good points...  forgive me if I had repeated some...

I hope this isn't too unclear -- I realize it is a great deal of in depth storage info  & index info ...

I will be happy to clarify after I get some sleep...  ;)

hope this helps,
dBalaski

Avatar of ooiewe

ASKER

dbalaski, hope u have had a nice sleep

First of all, there are a few misunderstanding here due to my description of the problem.

The table size is 3000 records.
1) Your avg rowsize is apx 3K (not including overhead)

After analyze the table,

AVG_ROW_LEN      91
NUM_ROWS      2555

The average row length is 91 including overhead. The 3000 is actually the estimated number of records
for the table. In fact, it's only 2555 rows after all the updates.


2) Figure blocksize range on most platforms of 2K to 32K... I am going to guess the standard 8K

Yes, the db_block_size is 8192 (8K)


Table A is having initrans 120 (and its associated indexes)
3) Initrans of of 120K on table (I am concluding the initrans is not on the index.)

The initrans for the table and the primary key index are both set to 120.


The update is on the PK column(index unique scan).
4) You are updating the column of the primary key.

OK, this is really a big misunderstanding. Anyway, appreciate dbalaski for the indepth explaination on the updates on candidate keys. The update is actually on non candidate keys based on the condition specified on candidate keys. As such, the explain plan will be of INDEX UNIQUE SCAN on the primary key index.


1) Adjust your INITRANS down to a level where you would expect the average rows per block

This doesn't seem to be applicable anymore as the average row length is just 91, instead of 3K. Still, INITRANS of 120 is a bit too high, which i am still waiting for more feedback.

2) adjust your PCTFREE higher on your base table to accound for differences in the way the average
row changes oin your DML

Currently, the PCTFREE is set at 20, which i didn't mentioned in my earlier post.

3) Adjust you index PCTFREE higher -- also rebuild your indexes after the process has completed.
(assuming you are not going to be able to change the process so as not to updates the PK columns)
1) Analyse the table pre & post process for chained rows -- this is going to give you a good indication
for resetting PCTFREE
ANALYZE TABLE xxxxx LIST CHAINED ROWS; (assuming you have setup the chained rows tables)
then compare the count of chained rows to the count of the rows in the table
ie: SELECTcount( *) FROM chained_rows where table_name = 'xxxxx';
2) Analyze the index per & post Process for the the two factors -- BTREE level & deleted_leaf_rows

I assume these are not applicable anymore as the update is not on the primary key columns.

ie: is the table set for Monitoring -- alter table xxxxxx monitoring;

I am not familiar with "alter table xxxxxx monitoring;", will look at it while waiting for more from you guys.


Just check whem your update is slow , the table is in which lock mode. Is it waiting for some other
resouce to free.
As you are running oracle 8.1.7 you can get easily locking information from DBA_waiters ,dba_blockers
etc.

This is just a simple update involving one table. What are the columns that of our interest from DBA_waiters ,dba_blockers tables?


Also your update may slow if you have rollback segment contention.

I believe we have sufficient rollback segments (>20), each sized at 1M.

You update may slow if you have bitmap indexes on the table

No bitmap index

You also asked about archiver archiving redo log
For that you can check alert log and find out switch time of one log to another. also you can find here
wheather archiver is waiting or not.

The log is being switched every one minute during peak transaction. How to see if the archiver is waiting for something?

Parallism can give you better result if you have multiple CPU's. In 8i you can enable automatic parallism
by setting parameter in init parameter file.

I doubt it will give any performance enhancement as the overhead in distributing/gathering the load/result for CPUs is higher than the time saved from the parallel processing, when we are talking about 3000 records, non-partitioned table.


I have identified the problem table, but don't know how exactly to go about finding the bottleneck, and appearantly the table lock and waiting for resource seems to be the area to explore, as well as space management and index. Correct me if i am wrong anywhere. Thanks again.
Avatar of Mark Geerlings
You may not be able to easily change the table or application design, but I question the wisdom of attempting 120 simultaneous updates of a table with only 2500-3000 rows.

The "initrans" setting of 120 also looks way too high to me.  Have you tried that at a much lower setting?
At the most you would be able to have 50 update locks on a block, and that would be if by coincidence 50 people managed to be updating 50 separate records that reside in one particular block.   Seems unlike, reduce the inittrans to 20 or less.  You will get another 20 records stored in each block just by doing that.  We only use the defaults, and don't usually have problem.
Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Please click your Member Profile, expand View Question History to navigate and update all your open and locked questions.
ASKER CERTIFIED SOLUTION
Avatar of Moondancer
Moondancer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial