Home » RDBMS Server » Performance Tuning » High insertion on the database | Tuning parameters (Oracle 10g, Linux 64)
| 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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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% ).
|
|
|
|
| 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: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
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  , devide LUN into piece size comfortable with our purpose.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 18:22:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
