Sergey -- Thanks for the question regarding "OLTP table structure", version 10.2.0

You Asked

Hi Tom,

our customer has a biggest fact table about 30 Mio rows,
230 columns, 99 indexes and 68 foreign keys on it.
Pure OLTP environment.
DB performance is not very good.
AWRRPT shows sometimes until 4 seconds per insert.
Enqueue Type section has first line: "TX-Transaction (row lock contention)",
and excessive number of indexes was my first assumption.
At least, TOAD (during health-check) considers more, that 6 indexes is a red event, as well, as Redundant Indexes (same leading columns). We have a thousands of them.
This table has a max amount of physical and logical reads as well,
about 50% of total.
Could you comment this solution please?
Is it reasonable to do a fine tuning without structure changes?

Thanks in advance.
Regards,
Sergey

and we said...

there is nothing anyone could say here right now.

we don't know how you use the table.
we don't know what you do.
we don't know what "50% of total" is, or even if it is meaningful.
we don't know what your goals are.


TX-Transaction (row lock contention) - that indicates the application locks rows in one session and makes the other sessions wait for it.

for example, run this:

drop table t;
create table t ( x int primary key );
insert into t values ( 1 );
pause
select sid, event, seconds_in_wait
  from v$session_wait
 where sid in (select sid from v$session where username=user);

now, when that pauses, go into another session and insert 1 again. it'll block.

wait a bit, hit enter in the first session and you'll see:

ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x int primary key );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> pause

ops$tkyte%ORA10GR2> select sid, event, seconds_in_wait
  2    from v$session_wait
  3   where sid in (select sid from v$session where username=user);

       SID EVENT
---------- ----------------------------------------------------------------
SECONDS_IN_WAIT
---------------
       146 SQL*Net message to client
              0

       150 enq: TX - row lock contention
              6

so, I have a small table, with few columns, and 1 index.

yet, I waited 6 seconds in this case while trying to insert.

Because - my application did that.

There is nothing you could do at the database level for this one (my example), short of dropping the unique constraint (but that would not be very smart).

Here is my suggestion:

99% of all performance gains will be realized by studying the application, understanding what it needs to do (NOT what it does mind you, what it NEEDS to do) and ensuring that it does what it needs to do in a multi-user, scalable, efficient manner.

And if a new physical structure is the outcome of that - so be it, but that will come out of the analysis of the application.


Not all indexes are redundant if they share the same leading edge, eg:

create index i1 on t(a,b,c);
create index i2 on t(a,b,d);

I1 and I2 are not redundant, but:

create index i3 on (a,b);

i3 is redundant with both i1 and i2



sorry, no silver bullets here - I suggest going back to the application itself, knowing what it SHOULD do (not HOW it currently does it, know what it NEEDS to do first)

Reviews

OLTP table structure   January 24, 2008 - 1pm Central time zone Bookmark | Bottom | Top Reviewer: A reader from Germany Tom, many thanks for your explanation. Best regards, Sergey Is the application generating sequence numbers?   January 30, 2008 - 8pm Central time zone Bookmark | Bottom | Top Reviewer: David Penington from Melbourne, Australia One thing I'd be wondering is whether the application is trying to generate a sequence of transaction numbers with no gaps, using a unique index to enforce this. THere are various ways people do that. The worst I've seen is where they add one to the highest existing sequence number, insert a row, do lots of other work then commit (perhaps even waiting for user I/O during this transaction). Other sessions add one to the highest existing sequence number, get a unique index insert failure, and try the next higher number. These sessions wait until the first session commits before they get the error. Works fine for a single user. Terribly non-scalable, and positively disasterous if they wait for non-database I/O before committing. ITL relation with TX lock   October 11, 2009 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  Hi Tom, could lack of ITL leads to enq:TX row lock contention A public thread on TOAD website mentioned this: http://www.toadworld.com/Experts/GuyHarrisonsImprovingOraclePerformance/ResolvingOracleContention/Fe b2008DealingwithLockContention/tabid/304/Default.aspx I am confused, as I guess in 10g lack of ITL will have its own wait name not TX row lock contention. Could you please explain this? Thanks, AM

---

Write a Review