December 2007

December 2007
Ask DBSpecialists: ‘Data file init write’ wait event
You’re invited to submit your Oracle-related questions to us at askdbspecialists11@dbspecialists.com. Please include your name and telephone number in your email.This month’s question comes to us from Allison from York, UK: During data loads we are seeing lots of waits for event ‘Data file init write’.  This wait event is not in the 10gR2 documentation, what does it mean and how can we eliminate it?

Ian G. Jones of the Database Specialists team responds:

The ‘Data file init write’ wait event occurs when Oracle auto extends a datafile and subsequently formats the newly created space within the datafile.  This wait event is new in 10gR2 and is not yet in the Oracle documentation.  You can see and measure the effect of this by resizing a datafile while monitoring the V$SESSION_EVENT view.

 

As an example, you could run the following PL/SQL block, changing the datafile and size to something on your own system, to force the file to auto extend.

BEGIN
EXECUTE IMMEDIATE
‘CREATE TABLE BEGIN_STATS AS ‘||
‘SELECT * FROM V$SESSION_EVENT ‘||
‘WHERE SID IN (SELECT SID FROM V$MYSTAT)’;
EXECUTE IMMEDIATE
‘ALTER DATABASE DATAFILE ‘||
””||’/u01/oradata/h1/users01.dbf’||””||’ RESIZE 1000M’;
EXECUTE IMMEDIATE
‘CREATE TABLE END_STATS AS ‘||
‘SELECT * FROM V$SESSION_EVENT ‘||
‘WHERE SID IN (SELECT SID FROM V$MYSTAT)’;
END;

 

You could then monitor and see the session wait events using sqlplus and the following SQL statement.

 

SELECT

E.EVENT,
E.TOTAL_WAITS – NVL(B.TOTAL_WAITS,0) “TOTAL WAITS”,      

ROUND((E.TIME_WAITED-NVL(B.TIME_WAITED,0))/100,2) “WAITTIME(S)”
FROM BEGIN_STATS B, END_STATS E
WHERE B.EVENT (+) = E.EVENT
ORDER BY 3 DESC;

EVENT                         TOTAL WAITS  WAIT TIME(S)
—————————-  ———–  ————-
Data file init write                 1252          25.19
Control file parallel write             3           0.06
Events in waitclass Other              12           0.01

 

 

This example uses traditional data files but the same wait event will occur when the database is extending Oracle Managed Files (OMF) on top of Automatic Storage Management (ASM).

Under normal operation, even when heavily using the ‘autoextend’ feature, this wait event will be a small proportion of the total wait time.  This is because actually using the blocks i.e. populating them with data requires significantly more work than simply initializing them in the first place.

 

So, if this is a dominant wait event for your loads it implies that your loads trigger major datafile extension.  One way in which they could do this would be to create new segments with large storage allocations or perform direct path loads into tablespaces that do not have sufficient free space above the respective segment high-water marks.  To eliminate this wait event during your loads you simply need to pre-allocate the required space prior to your loads.

 

Adding Knowledge: Taking your own snapshot
James F. Koopmann

 

In the previous section that explained the ‘data file init write’ wait event there is a very important concept that we as DBAs should all be aware of. In essence a mini-snapshot was taken to show the actual wait events that occurred over a period of time. This can be done for any V$ view such as V$SESSION_EVENT above or calculated set of values such as the buffer cache percentage.

 

The steps to do this are always the same:

  1. Create or truncate and insert statistics or values into the beginning holding table
  2. Run a workload through the system. This could obviously already be running
  3. Create or truncate and insert statistics or values into the ending holding table
  4. Issue a query that joins the beginning table to the ending table while subtracting beginning statistics from ending statistics to get a true value for the interval

 

As another example of this, we could capture and report on system level statistics through the following DDL & DML

 

  1. CREATE TABLE beg_system_event AS SELECT * FROM v$system_event
  2. Assume the system is currently under workload. I will often replace this step with a sleep command that waits before going onto the next step
  3. CREATE TABLE end_system_event AS SELECT * FROM v$system_event
  4. Join the two tables:

 

 

Having a set of scripts that are pre-written to do this type of work for your favorite statistics can quickly help you pinpoint performance problems. We all know that Oracle’s statistics accumulate over time and that is one reason Oracle snapshots were born along with additional tools like ASH. Unfortunately we can’t always wait for a snapshot to be generated, reduce the snapshot interval, or the new performance tools aren’t always available.

SELECT b.event,
(e.total_waits      – b.total_waits) total_waits,
(e.total_timeouts – b.total_timeouts) total_timeouts,
(e.time_waited    – b.time_waited) time_waited
FROM beg_system_event b, system_event                                   WHERE b.event = e.event;

Oracle on Linux package requirements
James F. Koopmann

 

I don’t really know why this always seems to be an issue with Oracle installations. But if you have ever installed Oracle on Linux/Unix you probably have come to that part of the documentation where it talks about required packages for installation. I too have been baffled by not only finding WHAT packages to install but have also had difficulties INSTALLING them as well.

 

I recently installed Oracle 11g on CentOS-5, and as always, wondered if my “complete” installation actually had all required packages. Oracle is actually very good about giving a list of the packages that are required in their documentation and we can actually check for their install status by using the “rpm -q” command.

 

For instance if we wanted to check for the compat-libstdc++ package we would issue the following at the command line:

rpm -q compat-libstdc++

 

Unfortunately we might quickly get the following result showing that the package is not installed:

package compat-libstdc++ is not installed

 

We then might jump to the conclusion that issuing the following command to install the package:

yum -y install compat-libstdc++

 

Unfortunately the above command proves un-fruitful as this package, along with others, is no longer available. In order to get Oracle to install properly we do need some package that is comparable to the one listed. To remedy the situation and find those packages we can actually use the following command to check for packages that are LIKE the ones we need:

yum list available “compat*”

 

And will generate a list much like the following:
Available Packages
compat-db.i386                         4.2.52-5.1           base
compat-gcc-34.i386                   3.4.6-4                base
compat-gcc-34-c++.i386             3.4.6-4                base
compat-gcc-34-g77.i386             3.4.6-4                base
compat-glibc.i386                   1:2.3.4-2.26           base
compat-glibc-headers.i386      1:2.3.4-2.26           base
compat-libf2c-34.i386                3.4.6-4                base
compat-libgcc-296.i386                 2.96-138           base
compat-libstdc++-296.i386            2.96-138           base
compat-libstdc++-33.i386          3.2.3-61               base
compat-openldap.i386          2.3.27_2.2.29-5         base
compat-readline43.i386              4.3-3                   base
compat-slang.i386                  1.4.9-27.2.2            base

 

 

We can then proceed to install the appropriate package(s) with commands such as:

yum -y install compat-libstdc++-296.i386

 

Note: this column is in no ways meant to be a complete set of instructions for installing Oracle 11g on CentOS-5. For this, please consult the Oracle documentation or pull in an experienced professional.

Conference Roundup
RMOUG Training Days

The Rocky Mountain Oracle Users Group puts on a wonderful two-day conference every year in downtown Denver. Most of the Database Specialists, Inc. team will be in attendance and would enjoy seeing you there. Save the dates of February 13 & 14, 2008 and come say hello. Visit the RMOUG site for more details at http://www.rmoug.org.

 

NoCOUG Winter Conference

The Northern California Oracle Users Group will hold its Winter Conference on February 19, 2008. The conference will be at the Oracle Conference Center in Redwood Shores, and there are still opportunities for you to speak. Please share the knowledge. Get updates at http://www.nocoug.org.

Call Database Specialists, Inc. when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Visit our website for no-cost resources, white papers, conference presentations and handy scripts. We can help increase your uptime, improve performance, minimize risk, and reduce

costs.

 

Sincerely,

 
David Wollf
Database Specialists, Inc.

Leave a Reply

Your email address will not be published. Required fields are marked *