Does Table storage settings affect performance

[oralearner]

Hi,

I have a stored procedure that loads 300,000 rows in table1.
The loading logic is
1)insert all 300,000 rows (first 10 columns only)
2)commit
3)perform some lookups and update remaining 5 columns using BULK features.

I run the same procedure on two different tables on different schemas (on the same database) there is a lot of difference in performance.

Storage settings for Table1
-------------------------

Schema1 Schema2
-----------------------------------
Inital Extent 512 KB 128 MB
Next Extent 640 KB 128 MB
Table Size 128 MB 128 MB
PCT Increase 0 0
No of Extents 205 1
Execution Time 6 Minutes 3 Hours

Can anyone help me understand this ?

 

[sbix]

Well normally the table storage parameters do affect the performance.
An insufficent INITIAL and NEXT value could create a lot of segment which could not be contiguos.
This is a penalty in reading but also in writing, cause each new extent has some header info which are more work for the DB.
So probably you have to look around for a different cause.
An explanation for this strange difference could be some extents of table in Schema 1 are on different disks so probably you have a bigger parallelism

 

[oralearner]

Any pointers to look around for a different cause ?

 

[sbix]

select tablespace_name from dba_tables where table_name = 'YOUR_TABLE';
select file_name from dba_data_files where tablespace_name='RESULT_OF_PREVIOUS_QUERY';

Do this for both the table
Hopely for schema 1 you will have several files, placed on different disks
Another hint could be, are there any constraints on the table in which you are inserting, some primary key constraints?
If so, where are located the indexes for both the schema?
Which are the indexes allocation parameter for both the schema.

 

[dbtoo2001]

Perhaps you should analyze the table before the update (assuming you have optimizer mode other than RULE.)

 

[sbix]

Analyze this table could be a good idea, then check for row chaining or row migration I suspect the massive update creates a big problem of row migration.
Get a look also to the pctfree parameter.
Are you running Oracle 9.2 with tablespaces of different block size?