Home » RDBMS Server » Performance Tuning » Performance Issue (merged 2) (Oracle DB,11.2.0.1.0,Linux 2.6.18-194.el5PAE)
Performance Issue (merged 2) [message #526267] Mon, 10 October 2011 06:50 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #526319 is a reply to message #526275] Mon, 10 October 2011 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
system seems to be busy creating bitmap indexes in parallel.

why is this being done?

how often does this occur?
Re: Performance Issue [message #526334 is a reply to message #526268] Mon, 10 October 2011 10:23 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #526341 is a reply to message #526334] Mon, 10 October 2011 11:11 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
There are ONLY 0,5 commits per second. Do you suggest REALLY to reduce this number???

I think, crussed_sonu should check redo I/O, because one redo write takes ~ 0,5 sec.

Regarding waits on "enq: CR - block range reuse ckpt". It could be a bug.
Re: Performance Issue [message #526346 is a reply to message #526341] Mon, 10 October 2011 11:21 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #526347 is a reply to message #526341] Mon, 10 October 2011 11:32 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Sorry, 0,05 sec.
Re: Performance Issue [message #526350 is a reply to message #526341] Mon, 10 October 2011 12:05 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #526356 is a reply to message #526319] Mon, 10 October 2011 12:50 Go to previous messageGo to next message
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
Member
Basically we are doing it whenever load is happening in the database(dataware).
Re: Performance Issue [message #526357 is a reply to message #526356] Mon, 10 October 2011 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Basically we are doing it whenever load is happening in the database(dataware).
Please provide details that more fully describe "it".

What is being loaded?
How much is being loaded?
what software is being used to load the data?

loading data can be resource intensive

Re: Performance Issue [message #526358 is a reply to message #526356] Mon, 10 October 2011 13:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Do you intend to try any of the suggestions that have been made?
Re: Performance Issue [message #526362 is a reply to message #526357] Mon, 10 October 2011 13:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #526389 is a reply to message #526368] Mon, 10 October 2011 15:52 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You were right with unsufficient memory setup. Parameter setting memory_target=2GB can be too small for DWH. I would suggest to increase it.
Re: Performance Issue [message #526467 is a reply to message #526368] Tue, 11 October 2011 04:02 Go to previous message
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
Previous Topic: performance problem (Hit Ratio)
Next Topic: Slower DB response [Oracle 10g on Solaris 5.10]
Goto Forum:
  


Current Time: Thu Mar 28 09:44:37 CDT 2024