When we need to insert data(table) to database (NO RAC and RAC) faster...,
we should have disk i/o faster. that's a good idea (example: solid state or make raid 1+0)

INSERT INTO XXX VALUES(a,b,...);

Anyway What should we do on Oracle Database?

- insert nologging
- No Archivelog
- No index
- Use big block size(example 16k, 32k)
- Concern about redo log, if use logging

What about your idea to improve this case?

Views: 5084

Reply to This

Replies to This Discussion

Hi ,

im using "insert /*+ APPEND */" hint option and its good as comapre to others.

Faheem.
if i insert with sub query, append hint, that is good.

It's not good with INSERT INTO XXX 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
Hi Surachart,

Well there are many answers and my experiences have shown that it always depends on the use-cases.

What you can do to make inserts faster:

1) Bulking (if you want to make it fast, do bulking!) which reduces commits and commit time
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)

And the list goes on....

APPEND hint is also very useful on inserts because it's using DIRECT-PATH inserts which will append the data simply on the table. But be aware the unused space by the table will then not be filled up!

Also not forget paritioning!

Regards,

Gerald
Hi Surachart,

Gerald made an excellent exposition and I totally agree withg him.
From my personal experience I would recommend partitioning. But my experience is in the telecom world were we could easily make 20/30 million inserts per hour and some of these tables were partitioned by (day,hour) to improve insert speed, we had indexes and we could not use /*+append*/ because of backup restrictions.
If you need real insight or your specific case, may be you could post your full scenario.

Regards,
Filipe
I suggest before any guesses first you take an 10046 Level 8 SQL Trace and analyse it with tkprof to see where you spend the time with your inserts.

ps: If there are triggers they also might be a problem area.
Hi, do you have a column in the table to store the radius server id (i.ex server1, server2, ...)?
If so, is it indexed? Is it partitioned?
maybe you could consider partitioning the table according to the server. For instance
if table looks like this
c1, c2, radius_server_id, ...

You could use range or list partition by radious_server and the on each server, when you issue the insert statement you could specify the corresponding server id.

create table abc (c1 number,c2 number, radius_server varchar2(14)) partition by list (radius_server) (
PARTITION s1_server VALUES ('s1') ,
PARTITION s2_server VALUES ('s2') )
;

insert into abc partition (s2_server) values(1,2,'s2') ;
If the table is indexed by the server_id column, then using partitioning on that table and converting the index to local would produce even better results on insert.
Cheers,
Filipe
Thank You for your suggestion. that's a good idea.

Actually this table has kept about account, nas ip, etc... when users log on...
I have 4 indexes. I plan to recreate hash partition, But I think hash can not to improve.

Perhaps I might recreate with range(month) partition.

About list partition, I have no idea to choose about column... now.



Do you have any idea?
If your planning to rebuilt table and add partitions, i don't think hash would do the trick on this one...
I stand by adding a range or list partition using a column server_id and forcing each server to specify its own partition while doing the insert.

insert into abc partition (s2_server) values(1,2,'s2') ;
thank you.

And? sql statement, block size, init_trans, pct_free and freelists...

How do you think? Any Suggestion about them?


Surachart

RSS

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Why you Should Include Push Notifications in your Mobile App?

Push notifications are a display of your application’s icon and a message in the status bar of your device. We stress the importance of including push notifications in your mobile application for three major reasons:-

  1. Push Notifications allow direct marketing to your clients: When a customer downloads your application and enables push notification, it gives you direct…

Continue

Posted by Alvaro Dee on August 23, 2016 at 10:50pm

Amazon Virtual Assistant Services for Efficient e-store Management

Virtual assistant (VA) is a term that has been buzzing around in the business world for quite some time now. Essentially, a VA is skilled administrator who takes care of your technical/creative business support activities while working offsite. If you already have a designated eCommerce store on Amazon seller central or wish to start one, an Amazon VA will help you manage the store and update it regularly with utmost precision.

Amazon VA creates and updates the…

Continue

Posted by Alvaro Dee on August 23, 2016 at 2:56am

What to Expect When Investing in Data Cleansing Services?

Why invest in data cleansing services?

Imagine a scenario- You are maintaining huge databases containing information about your potential customers and you are all geared up with next direct mailing campaigns. After promotional mailers have been sent, you realize the fact that the major part of your database actually contained supplier information.

Such blunders can happen anytime and can…

Continue

Posted by Alvaro Dee on July 25, 2016 at 11:30pm

Avail Affordable and Specialized Android Games Development Services

Cellular gaming has become hugely popular in the last few years. With cellular gaming and amazing features of the Android operating system mixed together, the demand for Android games development services has increased drastically. Gaming becomes fun on Android based Smartphones with its great features getting the users hooked from the word go!

Android supports clarity…

Continue

Posted by Alvaro Dee on July 24, 2016 at 10:47pm

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service