| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: IOT, memory and transaction time
On 17.04.2007 15:56, deja_at_2bytes.co.uk wrote:
> On Apr 17, 11:34 am, d..._at_2bytes.co.uk wrote:
Why?
>>>> Reads have improved quite a lot but there is a problem with the
>>>> writes. Previously, when adding 40,000 records at a time, write times
>>>> were pretty consistent no matter how big the table (this transaction
>>>> used to actually involve 200 updates of the same table followed by
>>>> 40,000 inserts). With the new indexes, they get progressively worse
>>>> with every write plateauing at around 10 minutes!! (this new
>>>> transaction involves updating a dummy table with one record and one
>>>> column 200 times (using a where clause that references the other
>>>> table), followed by 40,000 inserts). The update part of the
>>>> transaction basically checks that no-one else has updated since you
>>>> read.
>>>> Why have write times changed so dramatically? Why do they get
>>>> progressively worse? If it was the update statement that references
>>>> the other table with a where clause, then why have Read times improved
>>>> (using pretty much the same clause)? So I figured it probably wasn't
>>>> the Update statement. So, looking at the Insert, why has this become
>>>> so different. The columns are the same, it is just that one is Index
>>>> Ordered and one isn't. The 40000 inserts will all have a col2 value
>>>> that is the same but is greater than any previous write of col2. The
>>>> col3 values may not be in order when written back - so is it just a
>>>> memory issue? If we allocate more memory to the Oracle instance will
>>>> that solve it? Does the memory have to be allocated to a specific area
>>>> or is the SGA sufficient?
>>> My first guess would be that because of the changed physical structure
>>> inserts are generally more complex and generate more IO.
>>> Kind regards
>>> robert
>> Thanks Robert,
>>
>> Here is the DDL...
>>
>> New DDL:
>> CREATE TABLE R_TABLE1 (
>> deleted INTEGER NOT NULL,
>> versionNo INTEGER NOT NULL,
>> Id char(32) NOT NULL,
>> Rel char(32) NOT NULL,
>> CONSTRAINT R_TABLE1_1 PRIMARY KEY(versionNo,Id, Rel),
>> FOREIGN KEY (deleted) REFERENCES T_TRANSACTIONS (TransNum),
>> FOREIGN KEY (versionNo) REFERENCES T_TRANSACTIONS (TransNum),
>> CHECK (versionNo >= deleted))
>> ORGANIZATION INDEX;
>>
>> CREATE INDEX R_TABLE1_2 ON R_TABLE1 (Id, versionNo);
>>
>> Old DDL:
>> CREATE TABLE R_TABLE1(
>> deleted INTEGER NOT NULL,
>> versionNo INTEGER NOT NULL,
>> Id char(32) NOT NULL,
>> Rel char(32) NOT NULL,
>> FOREIGN KEY (deleted) REFERENCES T_TRANSACTIONS (TransNum),
>> FOREIGN KEY (versionNo) REFERENCES T_TRANSACTIONS (TransNum),
>> CHECK (versionNo > deleted));
>>
>> CREATE UNIQUE INDEX R_TABLE1_1 ON R_TABLE1 (Id, deleted, Rel);
>> CREATE INDEX R_TABLE1_2 ON R_TABLE1 (versionNo, deleted);
>>
>> There are 2 basic reads on this table.
>> 1) the latest version of a record
>> 2) all the changes since the last read version
>>
>> In the old system this meant
>> 1) select * from r_table1 where deleted = -1 (because every time a
>> change was made, it would update the deleted column of the existing
>> version = the current transNum and then insert a new version with
>> deleted = -1)
>> AND
>> select * from r_table1 where deleted between 0 and current transNum
>> (just in case someone made changes between you starting your read and
>> ending it - consistent read of numerous tables)
That sounds strange. Oracle provides read consistency and SQL Server does a similar thing. The whole point of transactions is that you can apply multiple changes consistently. There are however differences how both products deal with concurrency; in Oracle readers don't get blocked while this may happen in SQL Server.
>> 2) select * from r_table1 where versionNo > @lastRead and versionNo <=
>> @currentTransNum
>> AND
>> select * from r_table1 where versionNo > currentTransNum and deleted >
>> @lastRead and deleted <= currentTrans (again for consistent read)
>>
>> In the new system it just involves one read for each because no update
>> takes place, a new version just supercedes the old version by virtue
>> of a bigger versionNo:
So you did not only change DDL but also the logic how data is inserted and dealt with. I was not aware of that.
>> 1) select * from r_table1 t1 where versionNo = (select max(versionNo)
>> from r_table1 t2 where t1.id = t2.id and t2.versionNo <=
>> @currentTransNum) and deleted = -1
Btw, is this in a stored procedure? Then a fixed plan might hit you.
Other than that it seems to me that this query would benefit from an clustered index on (versionNo, id, delete).
>> 2) select * from r_table1 t1 where versionNo = (select max(versionNo)
>> from r_table1 t2 where t1.id = t2.id and t2.versionNo > @lastRead and
>> t2.versionNo <= @currentTransNum)
same here
>> In the old system the write transaction used to do:
>> 1) Update R_TABLE1 Set deleted = versionNo, versionNo =
>> @currentTransNum where id = @id and deleted = -1 and versionNo =
>> @versionNo
>> ONCE FOR EACH value of Id
>> (If 1 record updated then no-one has updated since our read)
>> 2) Insert into R_TABLE1(deleted, versionNo, Id, Rel) Values (-1,
>> @currentTransNum, @id, @rel)
>> for as many relationship tuples as required.
>>
>> In the new system the write transaction does this:
>> 1) Updates DUMMY Set Col1 = 1 WHERE @versionNo = (SELECT
>> MAX(versionNo) from R_TABLE1 where id = @id and versionNo <
>> @currentTransNum)
>> (If 1 record updated then no-one has updated since our read)
>> 2) Same insert as old one above.
I'd rather design the system in a way that a new version requires exactly one insert - not more. The meaning of "Col1" is totally unclear to me.
>> As far as secondary index is concerned, the application goes out in an
>> Oracle and a SQL Server flavour. In SQL Server this additional index
>> seemed to improve performance being used in some of the subselects.
If it helps only on SQL Server then create it only on SQL Server. Oracle != SQL Server - as you might have guessed. :-)
>> The problem is, that the new version of our schema does not perform
>> like this under SQL Server, only under Oracle. Obviously in SQL Server
>> we use a CLUSTERED INDEX but as far as I am aware that is just the
>> same as an IOT.
>>
>> Thanks for any advice
>> Phil
> > Can anyone help with this? > > The Oracle results are not consistent with what we are seeing on SQL > Server.... > > Basically the results are like this: > > Old SQL Server vs New SQL Server : new SQL Server better on reads, > better on writes. All statistics are consistent. > Old Oracle vs New Oracle : new Oracle reads are better, new Oracle > writes start off better but quickly deteriorate to very poor results.
Oracle does not automatically update statistics. You may have to give it some data and update stats again. Watch the plans. If you are on 10g that is fairly easy. Otherwise in SQL Plus "set autotrace on".
> However both new Oracle and new SQL Server have had their schemas > changed in exactly the same way.
From a certain point of view this is impossible as SQL Server and Oracle are quite different. I'd first try to get the logic and schema of the table right and then tune it per DB.
> And in the tests Old Oracle and new Oracle were run on exactly the > same Oracle configuration while old SQL Server and new SQL Server were > run on exactly the same configuration. > > The evidence leads me to think that new schema requires more memory/ > space and the SQL Server configuration was set up in both cases with > enough whereas the Oracle configuration was set up with enough for the > old version but not enough for the new....... > > Is there another conclusion?
I don't have a conclusion to offer. I still feel that the problem is not yet understood fully.
Regards
robert Received on Tue Apr 17 2007 - 10:50:44 CDT
 |
 |