Home » RDBMS Server » Performance Tuning » Oracle Database 11g 11.2.0.4.0 running slow (Oracle Database 11g 11.2.0.4.0)
icon11.gif  Oracle Database 11g 11.2.0.4.0 running slow [message #630703] Sat, 03 January 2015 05:07 Go to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

I recently upgraded my database from oracle 10g to oracle 11g 11.2.0.4.0. Now I am facing a lot of problem in terms of performance. A lot of wait events and locks are occuring. Reports that were working fine now running very slow. I am unable to understand the problem. I am attaching AWR report. Anyone please advise me what should i do
I am using dual 12 core Intel® Xeon® Processor E5-2695 v2 (30M Cache, 2.40 GHz) with 128Gb ram.
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630706 is a reply to message #630703] Sat, 03 January 2015 05:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Can you upload a few AWR reports that cover just an hour or two, at times when the database was particularly busy or when performance was particularly poor? A report covering a week is not of much use. And please upload in HTML format, not converted to PDF.

Just from a quick look, it would seem that you need to consider your online redo log configuration and your memory usage, and then of course look at the SQL. The more focused AWR reports will confirm this.




[Updated on: Sat, 03 January 2015 05:38]

Report message to a moderator

Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630708 is a reply to message #630706] Sat, 03 January 2015 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Foe the two points John mentioned have a look at:
1/ v$archived_log view and increase the size of the redo log so that you have no more than 15-20 switches per hour
2/ "Advisory Statistics" section of your report and adjust the size of memory component to the performances you need matching with the memory you can afford to the database instance.

Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630714 is a reply to message #630708] Sat, 03 January 2015 09:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I found this online.

I was looking because of this:

Background Wait Events

db file async I/O submit 2,793,919 0 2,205,724 789 0.30 88.29


I am not sure if it is of any value, but it is an obvious thing to check since it seems to match your situation well.

If and when you get around to doing SQL Tuning (I would advise to follow John's and Michel's advice first as much as possible), then here is the promotional chapter of my book on SQL Tuning. I have attached the free promotional chapter and the free scripts from the book. This is the complete first chapter and it will teach you about the role of Cardinality in query plan generation and give you some tools to make tuning a little easier. This chapter and the scripts are free and you do NOT need to buy the book in order to use them and share them with others so please do so. If you do decide to get the book make sure to use the coupon codes noted in the back of the promotional chapter document so you can get it for 1/2 price. I see a few people buying from the noted website, but then forgetting to use the coupon which is a shame.

Kevin

Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630744 is a reply to message #630714] Mon, 05 January 2015 01:34 Go to previous messageGo to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

Thanks for your support
I am attaching 3 AWR reports from 9Am to 12PM. I am attaching it in PDF format as HTML format is not allowed by this orafaq.
I also increased the number of redo files. Now i have 9 redo files each with 2Gb size
First AWR report 9am-10am
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630745 is a reply to message #630744] Mon, 05 January 2015 01:35 Go to previous messageGo to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

Second AWR report from 10Am-11Am
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630746 is a reply to message #630745] Mon, 05 January 2015 01:36 Go to previous messageGo to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

Third AWr report from 11Am-12Pm
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630750 is a reply to message #630744] Mon, 05 January 2015 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am attaching it in PDF format as HTML format is not allowed by this orafaq.


Just change the extension to .TXT and post the html files.

Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630756 is a reply to message #630744] Mon, 05 January 2015 03:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for the separate reports, but please upload them as html as MC said. Or you can zip them, and again add a different suffix if necessary. I will not look at another PDF file.
The reports confirm my initial suggestions, and also show that the pattern of activity is fairly even, rather than figures being distorted by averaging over a long time. As your DB CPU time plus PL/SQL Exec time is only about 55% of DB TIme, it is probably worth doing some basic instance and database tuning before attacking the SQL.
Your worst wait event is free buffer wait, and the memory advisors recommend more buffer cache. I would
alter system set memory_max_target=80g scope=spfile;
alter system set memory_target=70g scope=spfile; 
alter system set db_writer_processes=12 scope=spfile;

and restart.
Your next wait event is "log file switch (private strand flush incomplete. This is because you are log switching on average about every 4 minutes, so the peak rate is probably much higher. I would create new logfile groups of 4GB and drop the 2GB groups.
These changes should save you perhaps 35% of your DB Time: let's see what the reports look like after that.
And while that is running, what was your reasoning behind setting those _underscore parameters to cripple the cursor sharing mechanism? Have you considered using parallel processing?
Note that the "enq KO - fast object checkpoint" is caused by the changes in behaviour for serial direct reads, we can address that (and parallel query) next if necessary. However, you will probably need to identify the individual SQLs that are under performing and tune them individually.
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630763 is a reply to message #630756] Mon, 05 January 2015 03:45 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Looks to me like you're struggling because everything is waiting behind dbwr. I've seen this sort of thing before.

However...why 12 writer processes? That's a hell of a lot - surely making sure async io is working properly is the first port of call?

Check the OS is good for async and investigate disk_asynch_io and filesystemio_options values. The defaults (as I recall) actually disable async IO on linux.


Additionally check the availability of hugepages on the host and strongly consider manual memory management. Otherwise you might well thrash the host to death managing the memory.
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630788 is a reply to message #630763] Mon, 05 January 2015 07:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I suggested 12 DBWRs because by default OP will have 6, and doubling this seemed reasonable given the possibility that the cache isn't being cleaned fast enough and there is plenty of spare CPU capacity. However, you may well be correct about the async I/O. The only Linux system I have right now is an Amazon EC2 with RedHat 5.9 and DB 12.1.0.1. By default, the aio libraries are linked in but not enabled:
apexres1> select  FILETYPE_NAME, ASYNCH_IO from  v$iostat_file;

FILETYPE_NAME                ASYNCH_IO
---------------------------- ---------
Other                        ASYNC_OFF
Control File                 ASYNC_OFF
Log File                     ASYNC_OFF
Archive Log                  ASYNC_OFF
Data File Backup             ASYNC_OFF
Data File Incremental Backup ASYNC_OFF
Archive Log Backup           ASYNC_OFF
Data File Copy               ASYNC_OFF
Flashback Log                ASYNC_OFF
Data Pump Dump File          ASYNC_OFF
External Table               ASYNC_OFF
Data File                    ASYNC_OFF
Temp File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
after I set filesystemio_options=setall and restarted, it looks like this:
apexres1> select  FILETYPE_NAME, ASYNCH_IO from  v$iostat_file;

FILETYPE_NAME                ASYNCH_IO
---------------------------- ---------
Other                        ASYNC_OFF
Control File                 ASYNC_OFF
Log File                     ASYNC_OFF
Archive Log                  ASYNC_OFF
Data File Backup             ASYNC_OFF
Data File Incremental Backup ASYNC_OFF
Archive Log Backup           ASYNC_OFF
Data File Copy               ASYNC_OFF
Flashback Log                ASYNC_OFF
Data Pump Dump File          ASYNC_OFF
External Table               ASYNC_OFF
Data File                    ASYNC_ON
Temp File                    ASYNC_ON
Data File                    ASYNC_ON
Data File                    ASYNC_ON
Data File                    ASYNC_ON
which is what I would want to see.

@OP, so that is another thing to check.


Re: Oracle Database 11g 11.2.0.4.0 running slow [message #631032 is a reply to message #630788] Thu, 08 January 2015 02:06 Go to previous messageGo to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

Thanks John, after i made your below mentioned changes now database is running smooth but few minor I/O wait events are still there. I also set filesystemio_options=setall but results are the same as before making this change. Below is the out put. My OS is redhat 5.7 64bit
I am also attaching 3 AWR reports after making all your below mentioned changes. Please change the extension of files from TXT to HTML

 select  FILETYPE_NAME, ASYNCH_IO from  v$iostat_file;

FILETYPE_NAME                ASYNCH_IO
---------------------------- ---------
Other                        ASYNC_OFF
Control File                 ASYNC_OFF
Log File                     ASYNC_OFF
Archive Log                  ASYNC_OFF
Data File Backup             ASYNC_OFF
Data File Incremental Backup ASYNC_OFF
Archive Log Backup           ASYNC_OFF
Data File Copy               ASYNC_OFF
Flashback Log                ASYNC_OFF
Data Pump Dump File          ASYNC_OFF
Data File                    ASYNC_OFF
Temp File                    ASYNC_OFF
Data File                    ASYNC_OFF
Temp File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF
Data File                    ASYNC_OFF

Re: Oracle Database 11g 11.2.0.4.0 running slow [message #631033 is a reply to message #631032] Thu, 08 January 2015 02:08 Go to previous messageGo to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

2nd AWR Report
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #631034 is a reply to message #631033] Thu, 08 January 2015 02:09 Go to previous messageGo to next message
DevilzAdvocate
Messages: 9
Registered: January 2015
Location: Pakistan
Junior Member

3rd AWR report
Re: Oracle Database 11g 11.2.0.4.0 running slow [message #632942 is a reply to message #631034] Sat, 07 February 2015 17:02 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hi,

did you solve already your problem or do you need still any advice?

Kind regards
Leonid
Previous Topic: update statement takes too long
Next Topic: Updating text string to mask address characters
Goto Forum:
  


Current Time: Thu Mar 28 05:46:35 CDT 2024