SUSE Performance Issue - HELP!

[dcgsoftware]

We have a 10g environment in both Windows 2003 and in SUSE...we ran some very basic inserts/updates on 300K rows in a test table. Oracle on Windows 2003 completes the script in ~2 min (creating a table, inserting and then updating 300,000 rows) in our SUSE environment it takes almost 30 minutes. There is obviously some problem with the configuration, but we checked it against the Novell and Oracle recommended configuration and all of our settings are very similiar.

Has anyone experienced a situation where a simple script runs very quickly in a Windows environment and then in SUSE it just seems to lag. We basically boiled it down to testing this simple script as we have a different process that runs in 90 min in Win and takes almost 10 hrs in SUSE (but that is another story).

If anyone can provide some insight into some key areas within the SUSE config that could cause this type of issue I would really appreciate it. Just FYI the hardware is all consistent and we are running Oracle v10.2 in Win and v10.1 in the SUSE environment (we have no control and can't update the SUSE version as our client is running 10.1). Here are the results we get when running the script:

1) Loading 300,000 rows by 1 row
Win: 29 sec.
SuSE: 5 min 40 sec
2) Loading 300,000 rows by 3,000 rows (100 times):
Win: 6 sec
SuSE: Does Not Complete

Thanks very much to anyone that can help...Below is the script we are using to test:

CREATE TABLE fact
(
contract_id NUMBER NOT NULL,
allocation_date_id NUMBER NOT NULL
)
/

CREATE INDEX i_fds_contract_id ON fact
(contract_id ASC);
CREATE INDEX i_fds_allocation_date_id ON fact
(allocation_date_id ASC);

CREATE TABLE stat
(
stat_nm VARCHAR2(1000),
created_dt DATE
)
/

DECLARE
i int := 0;
BEGIN

INSERT INTO stat(stat_nm, created_dt)
VALUES('Started loading of records', SYSDATE);
COMMIT;

WHILE i < 300000 LOOP
INSERT INTO fact(contract_id, allocation_date_id)
VALUES(i, 300000-i);
COMMIT;
i := i + 1;
END LOOP;

INSERT INTO stat(stat_nm, created_dt)
VALUES('Finished loading of 300000 records', SYSDATE);
INSERT INTO stat(stat_nm, created_dt)
VALUES('Started inserting into facts', SYSDATE);
COMMIT;

i := 0;

WHILE i <= 100 LOOP
INSERT INTO fact(contract_id, allocation_date_id)
SELECT -allocation_date_id, -contract_id
FROM fact
WHERE contract_id <= i * 3000
AND contract_id > (i-1) * 3000
AND contract_id > 0;
i := i + 1;
COMMIT;
END LOOP;

INSERT INTO stat(stat_nm, created_dt)
VALUES('Finished 100 inserts into facts', SYSDATE);
INSERT INTO stat(stat_nm, created_dt)
VALUES('Started updating facts', SYSDATE);
COMMIT;

i := 0;

WHILE i <= 100 LOOP
UPDATE fact SET contract_id = contract_id - 1,
allocation_date_id = allocation_date_id + contract_id
WHERE contract_id+3000 <= i * 6000
AND contract_id+3000 > (i-1) * 6000;
COMMIT;
i := i + 1;
END LOOP;

INSERT INTO stat(stat_nm, created_dt)
VALUES('Finished updating facts', SYSDATE);
COMMIT;
END;
/

[davey23uk]

trace it, see where the time is taken

[Kamkan]

Hi,
Could you check whether any huge size is specified for Initial Extent in a tablespace where the table is getting created?