OraFAQ Forum: Performance Tuning » High insertion on the database

Home » RDBMS Server » Performance Tuning » High insertion on the database | Tuning parameters (Oracle 10g, Linux 64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

High insertion on the database | Tuning parameters [message #622393]

Tue, 26 August 2014 02:03

abhoite
Messages: 3
Registered: August 2014
Location: Pune

Junior Member

Hi,

The database has a lot of inserts going on every day (more than 1 billion records) plus all other normal operations.

Please let me know how to and what configuration parameters can be used to tune a Oracle database?
Also, are there any monitoring parameters that can be monitored and used for measuring scalability of the database.

Thanks,
Amol

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #622399 is a reply to message #622393]

Tue, 26 August 2014 02:21

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Your question is too large to address in forum. You need to begin by studying basic database administration (such as the Oracle Uni OCP curriculum.) Of coure if you have any specific questions, please post them here.

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #622404 is a reply to message #622393]

Tue, 26 August 2014 02:36

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The first idea is to preallocate the space the table will need to keep all these inserts to prevent from dynamic extension of the table.

The second idea is to insert by array and not one by one (if this is possible for your application, it is not for an telecom application which logs each phone call, for instance).

The third idea is to limit the number of indexes and triggers on the table.

The fourth idea is to load the rows into a temp table and globally loads the temp table into the target one.

In the end, the possibilities depend on your application, you have to tell more about it.

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #622415 is a reply to message #622404]

Tue, 26 August 2014 04:42

abhoite
Messages: 3
Registered: August 2014
Location: Pune

Junior Member

Thanks a lot Michel.

Addition to above please let me know will below solution work to tune database?

1. Adding H/W(CPU/RAM) resources
2. Increasing size of redo log to avoid switching within the redo group.
3. Increasing SGA size.
4. Any instance level tuning parameters?

Also, are there any monitoring parameters that can be monitored and used for measuring scalability of the database.

Thanks,
Amol

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #622416 is a reply to message #622415]

Tue, 26 August 2014 04:47

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

(Assuming there is no obvious misconfiguration) First tune the requirements then tune the application design then tune the application code then tune the database then tune the OS then tune the HW.

AWR/Statspack reports will give you where you spend your time and where you wait.

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #622420 is a reply to message #622416]

Tue, 26 August 2014 05:08

abhoite
Messages: 3
Registered: August 2014
Location: Pune

Junior Member

Thanks a lot Michel Smile

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #622822 is a reply to message #622420]

Sun, 31 August 2014 05:27

d_seng
Messages: 78
Registered: November 2011
Location: UK

Member

Cannot help but mention that lack of bind variables is the #1 cause of latches, especially when using Java based app servers. You can verify that by looking at the dynamic views.

Others options could be setting INITRANS appropriately, creating reverse indexes, etc. to avoid hot blocks.

[Updated on: Sun, 31 August 2014 08:39]

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #623015 is a reply to message #622822]

Wed, 03 September 2014 06:39

irfankhan
Messages: 1
Registered: January 2008
Location: hyderabad

Junior Member

Hi,
For high transaction Databases Oracle always recommends to put redo log files on Raid 0+1 or Raid 1+0 mount point.
I advise you to crate a separate small size mount point with RAID 0+1 and move the redologs there.
Because the writing speed of Raid 0+1 is very fast compared to RAD 5 ( apprx more than 30% ).

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #623019 is a reply to message #623015]

Wed, 03 September 2014 06:45

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 26 August 2014 11:47

(Assuming there is no obvious misconfiguration) First tune the requirements then tune the application design then tune the application code then tune the database then tune the OS then tune the HW.

AWR/Statspack reports will give you where you spend your time and where you wait.

[Updated on: Wed, 03 September 2014 06:46]

Report message to a moderator

Re: High insertion on the database | Tuning parameters [message #623969 is a reply to message #623019]

Tue, 16 September 2014 23:01

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Quote:

Cannot help but mention that lack of bind variables is the #1 cause of latches, especially when using Java based app servers

Quote:

(Assuming there is no obvious misconfiguration) First tune the requirements then tune the application design then tune the application code then tune the database then tune the OS then tune the HW.

d_seng & MC are right!

You, "abhoite" should consider about 2 above recommendations.

First, you must know exactly you or your system face to what? 1 billion rows inserted continous day by day, this is the big event to every system. Your system must be good at response time to 1 billion continous inserting. And
- How does it face to this?
- Is response time good or bad? Example, 20 milion rows would be inserted into 2,3 tables at 2pm, does your customer do operation normally or not?

Second, return to question "How does the system work for the event?", you or your team should consider about:

- Application, exactly how does the sql transform to database? Does it use bind variable, does it call to database package? Application side over, what is it? Tomcat, OAS, Websphere, WebLogic, ...? Every application server got their advantages and disadvantages. Tuning Application server side is an important step before deploying. Every flow progressing in java which got one purpose are bonding right? Does it spend much CPU time? Does it appear 0.1ms, 1ms, 2ms after the next? Etc ...

- Database side, as many DBA work on, I realized the poor performance caused by poor design in both of database and application. Assume good design application, sql-transform, but make an imagine:
+ 10 parallel transactions do asynch insert 2 million into one heap-table using good bind-variable sql-transform by java, what happen? Lock TX, row-exclusive, cause latch cache buffer chained, ...
+ 10 parallel transactions do asynch insert 2 million into one heap-table using good bind-variable sql in database package, what happen? Lock TX, row-exclusive, cause latch cache buffer chained ...
+ However, 2 examples above will not cause hanged database, or cause down database by PMON-xxx required ..., the database may be hanged but will not be down. Now, continue think about 10 million rows will be updated into the temporary lock heap-table in which, has got 30 millions row inserted before. What's up? Does it lock continous? Yes, it will release as soon as possible by good sql, because of, maybe, update one column in 10 million rows. Good sql in package or using bulk collect technology, or something else help your system got acceptable respone time.
+ How about 100 select SQL statement appear when inserting/updating table from client/other application? Think of it. What does cause performance? Poor or Good? Indexes usable or unsable? 2 plans for each SQL statement execution or just only one at the same time?

+ How about Partitioned Table? Partition Index?
+ How about Global Temporary Table?
+ How about GG, Active Standby, TimesTen?
Consider them.

- Hardware configuration side: I do not think much about it, of course, rich will buy expesive, make a good simulation or design the best hardware system such as: Load balancer application; Enterprise machine server from IBM, Oracle, HP; Very good storage or SAN from Hitachi, Using licsence QFS, Veritas, ... the thrid clusterware, PowerVM IBM, Zone Solaris ... Many, many contract deal depend your company money investment. RAID 1, 5, 6, 10 or even 15 if exist Very Happy

, devide LUN into piece size comfortable with our purpose.

Report message to a moderator

Previous Topic:	Auto stats job window details
Next Topic:	sql_id of a sql not the same in different databases

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 08:34:19 CST 2025