Thursday, February 05, 2009

Oracle Insert Faster, How?


When I would like to Insert Faster on Oracle database. 
Using High I/O, High CPU and High Memory, they're a great to do.

Anyway What I should to do on Database,  (SQL) ... on Table... on Index ???

About my issue...
I have many clients and many sessions, they connected and inserted data on (TABLE) Oracle in the same time.

*** Not Use CTAS***

In My Idea I use bigger block size ,nolgging, no archivelog, no indexing (Actually I use 3 indexes) and Increase Buffer Cache.

I can't not use "APPEND", Becasue In the same session i insert only one row.

INSERT INTO TABLE_NAME VALUES(...);

Example:

begin
for x in 1 ..100
loop
insert into t01 values(x, 100);
commit;
end loop;
end;
/
Elapsed: 00:00:00.16

begin
for x in 1 ..100
loop
insert /*+ append */ into t01 values(x, 100);
commit;
end loop;
end;
/
Elapsed: 00:00:01.15

Enough? I thought No! about idea... So, I'd like to know another idea else, So I posted to ask on Oracle Community.

Thank You many people to post their suggestion.

I would like to share their idea to other.  Perhaps that can help... other

Gerald Venzl suggests:

1) Bulking (if you want to make it fast, do bulking!) which reduces commits and commit time
-  In the same session i insert only one row, that can not...help
2) Enough ITL on the block (INITRANS)
3) Depending on the size of a row: Blocksize, PCTFREE
4) No Indexing
5) No integrity checks (Foreign keys, not null constraints)
6) Buffer cache (if it's to small you'll early end up with I/O)
7) And Partitioning

Gerald's Suggestion great.

Filipe Martins 's Idea helpful with his real experience, he suggests good idea from his experience to create table with partitioning table.
He told i should create table with range and list ... 

Assume, I have clients (C1, C2, C3, ...,Cn) to connect and insert data, So create table with range partition on (Client)
So: 
On C1 clinet .. insert into table_name partition (Partition_C1) values() ;
On C2 clinet .. insert into table_name partition (Partition_C2) values() ;
.
.
On Cn clinet .. insert into table_name partition (Partition_Cn) values() ;

good!

TongucY help me what i should to do before... anyway , that's make SQL TRACE event 10046 level 8. and then use tkprof to check.

Arie told i should to know... It's inserted from other table or one row...

Finally, Joe Goodman suggests:

1. If Inserts are comming from many processes then contention might occur due to

1.1. Buffer Busy wait to to contention for update access to same table block. This
may be checked in v$segment_statistics. If so then smaller block sizes or 
higher PCTFREE may help reduce number of rows per block but using ASSM
will help as well.

- About smaller block size. I'n not sure about this idea, Becasue data is inserted ..., no update

1.2 If same process is inserting many rows using CTAS, or insert into... select 
from then parallel execution should speed the process along. 

-  In the same session i insert only one row, that can not...help

1.3 If indexes exist on the table then the problem may be cause by Buffer Busy 
Wait on the right hand block of the index also visible in v$segment_statistics.
If so then one may either use Reverse Key Indexes (if the index is not needed for
range scan access paths) or use Hash partitioned Global index (if you have a 
license for partitioning) or possibly both as these reduce contention on the right
had index block.

Many Ideas, I'm sure to improve performance insert faster... Thank You!

About my issue I'll start with new table:

- Partitioning Table, range ... perhaps list
- increase INITRANS...
- reduce number of Indexes
- try to no constraint
- increase buffer cache and check about variable on sql statement help reduce library cache size
- bigger block size ... actually not sure, I have to test before. 
- Perhaps use nologging (or no archivelog)
- finally, use SQL TRACE 10046 to trace about waited and ... tune!

...



2 comments:

Giorgio Sorbara said...

From my experience (16TB OLTP) my two cents

Partitioning Table, range ... perhaps list

Partitioning is a physical layout that can be used to reduce contention, but not sure if this helps with so little info. Hash partitioning might be helpful as well.

increase INITRANS...

It might help... it depends on how many concurrent processes you have on the block. Consider also the fact that Oracle adjusts the initrans itself if there is enough space on the block (pctfree). You can check contention on the segment stats view.

reduce number of Indexes

Good point... but there should not be any useless indexes ;)

try to no constraint

Good point as well only if there is no need for data quality. Do you trust entirely the source ?

increase buffer cache and check about variable on sql statement help reduce library cache size

Don't understand very much this point.
Buffer cache should not be a big issue if the instance is well tuned.
Variable? Be sure you use bind variables to reduce parse time.

bigger block size ... actually not sure, I have to test before.

I would go for a "correct" block size.
This is not a smart a$$ answer. Bigger block means more contention in SGA. Smaller block may not fit your record size.

Perhaps use nologging (or no archivelog)

You should use archive log.
I usually remove the logging only for indexes. But Oracle tracks down some portion of logging anyway.

finally, use SQL TRACE 10046 to trace about waited and ... tune!

10046, session wait, and segment stats view
I would be a little concerned aboout latches too... keep an eye on "cache buffer chain"

HTH
g

HunterX said...

Hi Giorgio Sorbara,

Thank You for Your Suggestion.

Increase buffer cache and check about variable on sql statement help reduce library cache size
Sorry make you confuse.
Actually If i tune anythings and can not help ... more. I'll add Physical memory and increase SGA Size.