Re: IOT, memory and transaction time

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 18 Apr 2007 12:19:12 +0200
Message-ID: <58m9khF2hddk6U1@mid.individual.net>

On 18.04.2007 11:59, deja_at_2bytes.co.uk wrote:
> On Apr 18, 10:36 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> .
>> post
>> top
>> not
>> do
>> Please
>>
>> On 18.04.2007 10:51, d..._at_2bytes.co.uk wrote:
>>
>>> ok, I've discovered that it is the Update of the Dummy table that is
>>> the problem. It doesn't cause a problem in SQL Server but seems to be
>>> a massive problem in Oracle. Changing the Update statement to "Update
>>> DUMMY Set Col1 = 1" without the where clause eliminates the problem
>>> but obviously does not fulfill the requirement. Why does the where
>>> clause "...where @lastRead = (select max(versionNo) from Table1 where
>>> id = @id and versionNo <= @currentTransNum)" cause such a major
>>> problem with a seemingly endlessly increasing transaction time?
>>> What is a better way of structuring this SQL for Oracle?
>>> The client code already assumes a rowcount based on an update
>>> statement so I cannot change to a read statement, it still needs to be
>>> an update  (so I am told, though I have not seen the client app code
>>> myself)
>> Frankly, I am more and more confused.  You cannot change the application
>> and you do not have access to the code but you can change SQL?  Also,
>> your changed logic of using those tables does not interfere with the
>> supposedly fixed app logic?
>>
>> Maybe it's easier to start over with a list of requirements and fixed
>> interfaces and a description of what you want to do.  At least I do not
>> see your problem clear enough that I would dare to come up with suggestions.
>>
>> Regards
>>
>>         robert

>
> Yep I can change the SQL but the app is written in LISP and is too
> unwieldy with not enough expertise available for enough time to change
> it. I have had fairly minor changes made to the LISP code to deal with
> this new logic.
>
> It seems to be a fairly simple problem now - the old system did an
> update of the old versions in Table1 WHERE id = @id and deleted = -1
> and versionNo =@lastRead - worked fine.
> The new system does an Update of the dummy table where @lastRead =
> (select max(versionNo) from Table1 where id = @id and versionNo <=
> @currentTransNum) and gets progressively slower after every
> transaction as the table fills.
>
> This is where I expected it to use the secondary index. I have not
> been able to trace yet what it is actually doing as we do not have
> Oracle expertise available at the moment but certainly SQL Server
> works fine with this new SQL. The obvious difference is that SQL
> Server uses dynamic memory allocation whereas Oracle, in our tests,
> only had about 180MB allocated. We are going to do another test with
> more memory but is the SQL optimized? Should it be re-written? As I
> say, in our test, we were adding 20,000 records to 2 separate tables
> in each transaction, so the tables were growing quite fast.

I don't know where you get that from but you might have to manually set / tune storage parameters for Oracle - depending on the version.

> Can you confirm to me how to set Auto Update Statistics on?

There's a ton of things to explain about Oracle but you do not even mention your version. There is nothing like auto update statistics in Oracle. If you're on 10g you can fairly easy define a scheduled job that updates statistics once in a while. But be aware of a bug that caused not all stats to be updated properly (see metalink).

Frankly, I believe since you're so much in a hurry - you're in trouble.   If not today then at a later point in time where even more installations will be out there. I think at some point in time you have to do this properly - rather sooner than later. And I think you need to dig a bit deeper into Oracle - IMHO DB tuning requires some level of insight.

Regards

        robert Received on Wed Apr 18 2007 - 05:19:12 CDT