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
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...
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!
...
Posts
