Re: Performance Brainstorming

From: David Osborne <davidosborne_at_comcast.net>
Date: 27 Nov 2002 09:01:52 -0800
Message-ID: <193f1e93.0211270901.2ed1dccd@posting.google.com>

davidosborne_at_comcast.net (David Osborne) wrote in message news:<193f1e93.0211250729.a16590a_at_posting.google.com>...
> I have been struggling with a performance issue for a few months now
> and was hoping to get some direction from the best!
>
> The table involved has 75 million rows, and is non-partitioned. Each
> night we add about a million rows and it takes several hours (4+).
> Our goal is under two hours (is this reasonable?) The table and
> indexes are on different tablespaces; which are on different disks and
> controllers (although I'm hearing that this is no longer an issue).
>
> I have divided the load up into 16 seaprate jobs (using DBMS_JOB) and
> set the freelists on the table and indexes to 16.
>
> When the job is running (all 16), TOP shows that the CPU is 0-3% idle
> and the IO wait varies from about 5% to 20% so it appears that we are
> fully utilizing the server. The server is running SunOS 5.6 and has 4
> GB of memory. The DBA has alloted about 1.1GB to this Oracle
> instance.
>
> One idea is to load the new rows into a new table and then insert them
> with one "insert into big_table select * from new_small_table" with
> the append hint. The big table has pctfree at 10%.
>
> Thanks,
> David

I changed our process to load a staging table with pctfree set to 1. It took one hour to do some business logic and load this staging table with one million rows; which is great! However, the bulk insert of one million rows into the main table took 5 hours. I used:

INSERT /*+ APPEND */ INTO big_table
SELECT * FROM big_table_stg;

Big_table is defined as follows:

X_ID             NUMBER                       NOT NULL,
X_KEY            NUMBER                       NOT NULL,
ATYPE            VARCHAR2(1),
ADATE            DATE                         NOT NULL,
ADM              NUMBER(6,5),
ADA              NUMBER(6,5),
NG_FLG           VARCHAR2(1),
ADT_FLG          VARCHAR2(1)

Other big_table info:
PCTUSED 40
PCTFREE 20
INITRANS 1
MAXTRANS 255
FREELISTS 16
FREELIST GROUPS 1
NOLOGGING
NOCACHE
PARALLEL ( DEGREE 4 INSTANCES 1 )
Size: The tablespace is LMT with 100M extents. The table is 3.6GB with 36 extents. Number of rows is 70,247,110.

There is a primary key on X_ID and ADATE with the index on a LMT with 100M extents. Info:
NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255

PCTINCREASE      0
FREELISTS        16

FREELIST GROUPS 1
NOPARALLEL
Size is 1.8GB with 18 extents.
70,117,916 distinct keys.

There is a non-unique index on X_ID (for the foreign key). It is on a different LMT with 100M extents. This index's info: NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
FREELISTS 16
FREELIST GROUPS 1
PARALLEL ( DEGREE 4 INSTANCES 1 );
Size is 1.4GB with 14 extents.
1,332,880 distinct keys.

Constraints:

 CONSTRAINT CK_ATYPE CHECK (ATYPE IS NULL OR ATYPE IN ('X','Y','Z')));  CONSTRAINT FK_BIG_TABLE FOREIGN KEY (X_ID) REFERENCES ISP (X_ID)    ON DELETE CASCADE Next thoughts:

1. Change primary key index to be PARALLEL 4,1.
2. Change pctfree to be 10 on big_table.
3. Drop indexes before loading and recreate them when complete?

Thanks,
David Received on Wed Nov 27 2002 - 11:01:52 CST