Oracle 10g Top 5 Timed Events - backup timed events

In the TOP 5 Times Events, i have highlighted couple of events related to backup job. IMM OP wait event falls under the other category but related to the RMAN backup. The other one you see is Backup : sbtbackup falls under the administrative category also realted to the RMAN. These wait events will occur when there is RMAN backup running on the server.

imm op

Meaning: Waiting for an IMMEDIATE I/O request to a slave process to end.The event "imm op" may occur if I/O slaves are used,

that is, if parameters such as DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES are used.

BACKUP_TAPE_IO_SLAVES is set to TRUE in the case of a RMAN backup if dedicated I/O slave processes are to copy backup write

processes to tape and not the Oracle shadow processes. In this case, "imm op" waits only affect the backup runtime, but not the live system.

Backup: sbtbackup

Meaning: Wait situations during RMAN backup. The wait events that begin with "Backup: sbt" are associated with wait situations during a RMAN backup. This means they only affect the backup runtime.

One has to be careful with these events especially during the peak time, they should not be in the TOP 5. If they are found in the peak time, one has to look at scheduling the backup during the non peak time and also these will occur when RMAN is backing up the database directly to the TAPE instead to a disk. As backup is a heavy DISK and CPU intensive job, it has to be done during the non peak time only.

AWR Analysis - Oracle "Wait Class" from 10g

Top 5 Timed Events and Wait Class Categories from 10g

Oracle 10g includes many enhancements to the wait event interfaces. Among them the introduction of wait classes has made the life of the DBA a lot easier in identifying the problem. The color coding given to these wait classes helps the DBA's to identify and correlate the problem to a particular category. And also the same wait class concept has been used in AWR reports as well as in many screens of the enterprise manager for example in monitoring the TOP Activity under Performance TAB Of Enterprise Manager.

In Oracle 10g wait events are classified into categories which can help the DBA to more easily determine the likely cause of the problem. The categories are:
Administrative, Idle, Application , Network , Cluster Scheduler , Commit , System I/O , Concurrency , User I/O , Configuration , Other

DBA can identify from a distance by looking at the color of the activity in the enterprise manager.

The first diagram is the TOP Activity of Enterprise Manager showing the activity in colors and right side you can see the color coding against each category.The second diagram shows the AWR report TOP 5 Times Events and Wait Class Statistics.

Happy tuning.

AWR / Statspack Analysis

Problem : Performance and High CPU

Solution / Interpretation : The TOP Event log file sync This wait event is seen in most of the high transactional databases where more frequent commits happens. Let us look deep into the concept and then see how we can reduce that, definitely we cannot avoid it.

At commit time, process creates a redo record [ containing commit op codes] and copies that redo record in to log buffer. Then that process signals LGWR to write contents of log buffer. LGWR writes from log buffer to log file and signals user process back completing a commit. Commit is considered successful after LGWR write is successful.

Commit is not complete until LGWR writes log buffers including commit redo recodes to log files. In a nutshell, after posting LGWR to write, user or background processes waits for LGWR to signal back with 1 sec timeout. User process charges this wait time as ‘log file sync’ event.


LGWR is unable to complete writes fast enough for one of the below reasons..

1)Disk I/O performance to log files is not good enough.

2)LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve for CPU too. This will lead to slower response from LGWR, increasing ‘log file sync’ waits.

3)LGWR is unable to complete writes fast enough due to file system or unix buffer cache limitations.

4)LGWR is unable to post the processes fast enough, due to excessive commits. It is quite possible that there is no starvation for cpu or memory and I/O performance is decent enough. Still, if there are excessive commits, then LGWR has to perform many writes/semctl calls and this can increase ‘log file sync’ waits. This can also result in sharp increase in redo wastage’ statistics’.

This event is also the major contributor for overall CPU usage.

In the above scenario, we cannot avoid the COMMITS as the application requires it. What i have done is reduced the CPU contention so that LGWR does not wait for the lack of CPU.

The other aspect is to see where these online redo log files are created. We have made raw devices for these files so that the writing happened fast.

Also there is a misconception that increasing the redo log buffer will solve the problem , but this is not the case as if you the see fundamentals of log buffer flush is for every commit or when 1/3 is full or 1 MB of redo is generated. In this case for every commit there is redo buffer flush happenes so no need to have the redo buffer more than 3MB.

Also try to place the major hot tables and indexes separately on to multiple disks to increase the disk i/o.

Team ORAKHOJ
Get free AWR / Statspack Analysis by sending mail to tuning@orakhoj.com

AWR / Statspack Analysis

Problem : Performance and CPU





Solution : The 3rd TOP 5 Wait Event 'log file switch' indicates there is a problem with Checkpoint and Switching. It directly relates to a problem with the size of the online redo log files. According to the statpack we found that there are 70 + switches perfoemd by the database per hour and also a Delete Statement was found as the TOP CPU Consuming Statement, It clearly states that there is a bottleneck during the delete operation as much redo will be generated and because of small online redo log files, switched are happening frequently which in turn making the delete statement wait for log switch to perform. Also the delete statement is using the Index which is the cuase for the first 'db file sequential read ' wait event.

Happy Tuning
Team ORAKHOJ -- Get Free AWR / STATSPACK Analysis from ORAKHOJ tuning@orakhoj.com

Statspack/ AWR Analysis

Oracle Release 10.2.0.3
Problem CPU and Performance

Top 5 Wait Events

You can find 'db file sequential read' and 'read by other session' apart from CPU as TOP 5Events. Based on the 'db file sequential read' it is evident that the system is using most of the indexes.

Once we go to the other wait event 'read by other session' which is nothing but buffer busy waits where information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait; e.g. many sessions scanning the same index or performing full-table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

So to figure which object is causing the contention, you can look at which tablespace/datafile has got buffer waits and which object is falling under the Segments by Physical Reads and Logical Reads. Based out which we have identified an index which is the cause for this contention. The same has been rebuild on to a different tablespace to reduce the contention.

You can get free tuning advices from our DB experts by simply sending mail to tuning@orakhoj.com
-------------------------------------------------------------------------------

Statspack/ AWR Analysis

Database Version = 10.2.0.3



Problem : High CPU consumption.

Solution : Based on the above top 5 events one can cleary see that db file scattered read is high event after the CPU, which means that the application is performing full tablescans. Based on this one has to look at SQL ordered by CPU session of the report to find out which queries on performing full table scan. Based on the index recommendations the above problem has been sorted out.

Team ORAKHOJ

You can also get free tuning analysis from our database experts. Please send a mail to tuning@orakhoj.com

Free Statspack/AWR Analysis

Dear Oracle Professional,
Free Tuning Advisory Service has been started by ORAKHOJ- Exclusive Search Engine for Oracle Professionals. You can get free analysis & tuning recommendations from our tuning experts. Mail your AWR (html format) or Statspack reports to tuning@orakhoj.com. AWR / Statspack can be of RAC / Non RAC databases.
For best results, please collect AWR or Statspack report for 30 to 60 minutes duration.The information shared will be kept confidential including the mailer information.

Thank you for your patronage and keep using ORAKHOJ. - The ORAKHOJ Team"
www.orakhoj.com

Exclusive Search Importance

Search Engines have become very important for IT professionals day to day troubleshooting, Keeping in view the importance we have come across www.orakhoj.com with GOOGLE collaboration. This search engine will query the relevent oracle related websites which saves and lot of time and improves the efficiency of individuals. This site is admired and accessed by thousands of Oracle professionals from 90 countries across the globe. Visit the site and give your opinion on the same.www.orakhoj.com
Posted by Bharat at

Exclusive Search Importance

Search Engines have become very important for IT professionals day to day troubleshooting, Keeping in view the importance we have come across www.orakhoj.com with GOOGLE collaboration. This search engine will query the relevent oracle related websites which saves and lot of time and improves the efficiency of individuals. This site is admired and accessed by thousands of Oracle professionals from 90 countries across the globe. Visit the site and give your opinion on the same.
www.orakhoj.com