Re: Operations in huge transaction slow down suddenly. Why?

From: Bernhard Graeuler <fuerspam_at_gmx.de>
Date: Sun, 19 Dec 2004 12:53:41 +0100
Message-ID: <32l8e6F3lt1g4U1@individual.net>

joel-garry_at_home.com <joel-garry_at_home.com> schrieb:
> Some things to idly wonder about:

Sorry, I should have written that in the last posting, but forgot it because of a lack of time.

I moved your following question upward, because I'm interested in the answers:
> Are you using DMT? Perhaps there is some contention and thrashing
> going on as indices and tables get more segments.
Yes. Tablespace USERS is dictionary managed. The tables that where mostly used during the import and where the slowdown happens are in the only one locally managed tablespace called MDP553. Should we try moving the tables to a dictinary managed tablespace? What is "better" DMT or LMT?

> Which version of Oracle and platform?

Platform:

    Hardware:

        System: PC,
        Processor: 2x 750MHz
        Memory:    1GB
        HDD1:      20GB (System Drive C:)
        HDD2:      150GB (2 Partitions 100GB/50GB, both containing Oracle
datafiles
                          and the Oracle installation. Complete listing
which files
                          are where could be given later.)
                          Each drive has free space, so this could not be

the problem.

    Software:

        Windows 2000 SP 4
        Oracle 8i Enterprise Edition V. 8.1.7.4.1
        Oracle OLEDB Provider is updated to version 8.1.7.3.2
        No further important software installed

CBO?
    Don't know, I'm not the DB Admin.

> How many indices on the table?

    On the two tables that were used mostly before slowdown happens:

        1.: 9 and one primary key (144 records processed, no problem)
        2.: 9 and one primary key (about 22000 records processed, no

problem)

    Table where slowdown happens:

        8 on single columns
        1 one multiple columns
        1 primary key on a single column
    Table where conflicts were written into
        6 on single columns
        1 primary key on a single column

> Are you using a journalled file system?
NTFS, so, yes it is.

> Tried oraperf.com?

No, not yet, thanks for the hint!

I hope my answers would help solving the problem. Thank you!

    Bernhard Gräuler Received on Sun Dec 19 2004 - 05:53:41 CST