Commit or write ?
Akash Jain, April 29, 2019 - 7:12 am UTC
Hi,
By "Hey, please commit my stuff" , do you mean "Hey, please write redo buffer to online redo" ? Because , redo buffers are emptied to online redo log periodically even without commit. Am I correct ?
April 30, 2019 - 2:37 am UTC
Because , redo buffers are emptied to online redo log periodically even without commit
Yes, but that is asychronous to your session, whereas
"Hey, please commit my stuff"
is doing the same thing but also waiting for lgwr to come back say "yep, I can guarantee its done"
Follow Up
Geraldo Peralta, April 29, 2019 - 4:18 pm UTC
About what you wrote:
- I/O bandwith, ie, your storage cannot keep up with the rate. Here I am using a Exadata Storage.
- CPU - the lgwr needs CPU to work, so if the box is max'd out that's an issue. Increasing priority of LGWR is a work around. How Can I increase priority of LGWR?
- Commit rate too high (This is a expected behavior in the app because is a commit per request (we got lots of inserts) where there are two tables involved. )
CREATE TABLE X
ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 NOMAXVALUE NOMINVALUE CACHE 3000) NOT NULL,
TRACKID VARCHAR(36) NOT NULL, --THIS IS A GUID UNIQUE KEY
COLUMN 3,
...
COLUMN 8
TABLE 2
CREATE TABLE Y
ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 NOMAXVALUE NOMINVALUE CACHE 3000) NOT NULL,
X_ID NUMBER NOT NULL, --FK TO X TABLE
COLUMN 3,
...
COLUMN 6
- Too much redo (eg lots of indexes on tables etc)
There are two indexes on each table.
April 30, 2019 - 3:13 am UTC
I'll assume (because you didn't tell us) that there is index in ID in each table.
That index can be hash partitioned for example:
create unique index my_index on x(id)
global partition by hash(id) partitions 16;
Try that for X and Y and see how your waits/contention goes after that.
Because the values are now split over multiple segments, then queries like:
where ID = ...
are unaffected, but if you have queries like:
where ID > ...
where ID < ...
where ID between ...
then these will have problems (and I'd be questioning any design/code where you are doing range queries on ID columns)
Review
Geraldo Peralta, May 01, 2019 - 6:09 pm UTC
Yes, I do have index on each id column.
1. What does it mean partitions 16? Why start with 16?
2. Do I have to pay for partitioning license?
Thanks for the reply. It is very helpful.
May 02, 2019 - 6:42 am UTC
1. What does it mean partitions 16? Why start with 16?
You want to have a power of 2. I picked 16 out of thin air - you could try 4, 8 or 32 - it basically is a factor of how many concurrent sessions you expect to have
2. Do I have to pay for partitioning license?
Yes indeed.