| Performance Issue (merged 2) [message #526267] |
Mon, 10 October 2011 06:50  |
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
|
Member |
|
|
Hi All,
I am new in performance tunning issue, need your help in that ..
Facing database performance issue while loading data into the dataware database.
I am attaching the AWR report with this mail as well, please go through the same and let me know if need any more info ...
As of now all default setting is configured at the database end ...
Regards
|
|
|
|
| Performance Issue [message #526268 is a reply to message #526267] |
Mon, 10 October 2011 06:50  |
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
|
Member |
|
|
Hi All,
I am new in performance tunning issue, need your help in that ..
Facing database performance issue while loading data into the dataware database.
I am attaching the AWR report with this mail as well, please go through the same and let me know if need any more info ...
As of now all default setting is configured at the database end ...
Regards
[Updated on: Mon, 10 October 2011 07:01] Report message to a moderator |
|
|
|
| Re: Performance Issue [message #526274 is a reply to message #526268] |
Mon, 10 October 2011 07:12 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
If it's a specific process that's going slow then an oracle trace of the session running that process is generally more useful than an awr report.
|
|
|
|
| Re: Performance Issue [message #526275 is a reply to message #526274] |
Mon, 10 October 2011 07:19 |
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
|
Member |
|
|
No .. the overall database performance having performance isssu.. so i have attached AWR.
Even those the load as below :-
top - 18:17:59 up 37 days, 7:09, 5 users, load average: 10.09, 5.76, 4.43
Tasks: 642 total, 1 running, 641 sleeping, 0 stopped, 0 zombie
Mem: 16628888k total, 15170984k used, 1457904k free, 29468k buffers
Swap: 17434828k total, 1720076k used, 15714752k free, 5446476k cached
and the server having below processor :-
When looking at the physical processors ...
grep 'physical id' /proc/cpuinfo | sort | uniq | wc -l
2
When looking at the virtual processers ...
grep ^processor /proc/cpuinfo | wc -l
16
|
|
|
|
|
|
| Re: Performance Issue [message #526334 is a reply to message #526268] |
Mon, 10 October 2011 10:23 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, here we go:
1. All tuning is about reducing the amount of DB Time needed to perform a certain amount of work.
2. DB Time is divided into working time and waiting time. You should attack which ever of these is the greatest.
3. The "Time Model Statistics" section of your AWR report shows that working time, reported as DB CPU, is only 5.6% of DB Time. This is tiny. Therefore, you must attack the waiting time,
4. Your worst "Wait Class" is Commit, which is also your worst wait event: log file sync. This cost you 23137 seconds of DB Time.
5. How do you reduce this wait event? You could restructure your code so that you commit less frequently; you could set the COMMIT_WRITE instance parameter to 'BATCH,NOWAIT' (this is probably the quick fix); you could reduce the LOG_BUFFER parameter to the minimum possible (try 3M, it is on default at the moment).
6. Then get another AWR report, and move on to what ever wait event is then the worst.
7. And once you get the DB CPU figure to over 50% of DB Time, start tuning the SQL to reduce the working time needed.
Database tuning is just a set routine. ANyone can do it.
|
|
|
|
|
|
| Re: Performance Issue [message #526346 is a reply to message #526341] |
Mon, 10 October 2011 11:21 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, Leonid, you are right: I hadn't looked at the number of commits, only how long they were taking in total. Disc I/O could well be the answer, particularly as slow commit processing can also cause excessive enqueue waits due to delays in releasing locks and other resources.
@crossed_sonu, can you check the I/O throughput? Run something like this:
var al number;
var mi number;
var mm number;
exec dbms_resource_manager.calibrate_io(max_iops=>:mi,max_mbps=>:mm,actual_latency=>:al)
print al
print mi
print mm
[update: commit_write='batch,nowait' might still be a big help]
[Updated on: Mon, 10 October 2011 11:23] Report message to a moderator |
|
|
|
|
|
| Re: Performance Issue [message #526350 is a reply to message #526341] |
Mon, 10 October 2011 12:05 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I was just looking at your SGA setup.
During the report, your large pool varied between 48M and 1056M.
Your DB cache varied between 64M and 1072M.
These are huge changes, possibly related to the parallel build of those bitmap indexes
that BS noticed.
What was going on during the time frame of that report? Was it a typical workload?
|
|
|
|
|
|
|
|
|
|
| Re: Performance Issue [message #526362 is a reply to message #526357] |
Mon, 10 October 2011 13:16 |
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
|
Member |
|
|
Please find below the details of your question :-
1.The project is in the initial phase so the initial load is going on .. i means in that all of the table are truncated and doing the load from the start.Apart from that the incremental load is also happen every day at night.
3. As of now we are loading approx billions of data.
4. through Informatica(ETL jobs) we are doing the load in the database.
Please let me know if require any more info ...
Apart from that i have added 3 more log groups with size of 200mb.
Regards,
Vimlendu
|
|
|
|
| Re: Performance Issue [message #526368 is a reply to message #526362] |
Mon, 10 October 2011 14:00 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Vimlendu, I gave you specific ideas regarding two instance parameters. Leonid suggested you test I/O and I told you how to do it. Cookie suggested tracing some of the slow sessions. BS suggested that the issue might be rebuilding bitmap indexes (which does seem to be a pretty silly thing to do while loading data). So that is lots of ideas.
And all you have done is reconfigure your redo log.
Why do you ask for advice if you intend to ignore it?
|
|
|
|
|
|
| Re: Performance Issue [message #526467 is a reply to message #526368] |
Tue, 11 October 2011 04:02 |
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
|
Member |
|
|
Hi John,
Sorry for the late reply .. i have also tried to change the parameter for MEMORY_TARGET to 8GB, but it is giving me the error ... "ORA-27102 out of memory"
I suspect that the problem is the shmmax and/or the shmall kernel parameters of the SUSE LINUX setup. I have checked through much documentation but nothing is very specific in calculating the parameter values for a very large system.
When looking at the physical processors ...
grep 'physical id' /proc/cpuinfo | sort | uniq | wc -l
2
When looking at the virtual processers ...
grep ^processor /proc/cpuinfo | wc -l
16
When looking at the sh values :-
kernel.shmmax = 4294967295
kernel.shmall = 268435456
uname -a
Linux mbhibisrv01.maxbupa.com 2.6.18-194.el5PAE #1 SMP Mon Mar 29 20:19:03 EDT 2010 i686 i686 i386 GNU/Linux
ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073741824
min seg size (bytes) = 1
Please sugguest how to procees ..
Regards,
Vimlendu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
