db_writer_processes

I wrote the first part of this example in 2012.

 

The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'cpu_count'

SQL> /

 

VALUE

------------------------------

1

 

SQL>

 

The database used the default value for db_writer_processes, which Oracle had calculated as 1:

 

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'db_writer_processes'

  4  /

 

VALUE                          ISDEFAULT

------------------------------ ---------

1                              TRUE

 

SQL>

 

(However, since I first wrote this, I have found documentation suggesting that db_writer_processes always defaulted to 1 in Oracle 9 and took no notice of cpu_count.)

 

I looked for this process in the operating system as follows (I had to squash the ps output a bit to make it fit the screen):

 

UNIX > ps -ef|grep dbw|grep TEST9

oracle 250424 1 0.0 02:07:18 ?? 0:00.87 ora_dbw0_TEST9

UNIX >

 

I found an Oracle 11 database on a Solaris server with cpu_count set to 16:

 

SQL> select value from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE

------------------------------

16

 

SQL>

 

It also used the default value for db_writer_processes, which Oracle had calculated as 2:

 

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'db_writer_processes'

  4  /

 

VALUE                          ISDEFAULT

------------------------------ ---------

2                              TRUE

 

SQL>

 

When I found these processes in the operating system, I saw that Oracle had given them consecutive numbers i.e. dbw0 and dbw1 (the ps output was squashed again to fit the screen):

 

UNIX > ps -ef|grep dbw|grep PROD11

oracle 12230 1 0 19:42:36 ? 1:24 ora_dbw0_PROD11

oracle 12232 1 0 19:42:36 ? 1:21 ora_dbw1_PROD11

UNIX >

 

If you don’t have enough database writer processes, you can apparently have problems with free buffer waits. I searched all our production databases and could only find two with any of these at all. This was one of them:

 

  1  select time_waited from v$system_event

  2* where event = 'free buffer waits'

SQL> /

 

TIME_WAITED

-----------

71

 

SQL>

 

As this figure is given in hundredths of a second, I decided to do nothing about it.

 

I created an Oracle 11.2.0.4 database in 2015 and set db_writer_processes to 36:

 

SQL> alter system set db_writer_processes = 36

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database (this is not shown). When I looked for the database writer processes in the operating system, I saw that Oracle had called them dbw0 through dbw9 then dbwa through dbwz:

 

UNIX > ps -ef|grep dbw|grep BECHEDV1

oracle 50594 50565 0 17:27:03 ? 0:00 ora_dbwh_BECHEDV1

oracle 50583 50565 0 17:27:02 ? 0:00 ora_dbw6_BECHEDV1

oracle 50578 50565 0 17:27:02 ? 0:00 ora_dbw1_BECHEDV1

oracle 50611 50565 0 17:27:03 ? 0:00 ora_dbww_BECHEDV1

oracle 50589 50565 0 17:27:02 ? 0:00 ora_dbwc_BECHEDV1

oracle 50593 50565 0 17:27:03 ? 0:00 ora_dbwg_BECHEDV1

oracle 50600 50565 0 17:27:03 ? 0:00 ora_dbwn_BECHEDV1

oracle 50615 50565 0 17:27:03 ? 0:00 ora_dbwz_BECHEDV1

oracle 50612 50565 0 17:27:03 ? 0:00 ora_dbwx_BECHEDV1

oracle 50582 50565 0 17:27:02 ? 0:00 ora_dbw5_BECHEDV1

oracle 50580 50565 0 17:27:02 ? 0:00 ora_dbw3_BECHEDV1

oracle 50608 50565 0 17:27:03 ? 0:00 ora_dbwt_BECHEDV1

oracle 50588 50565 0 17:27:02 ? 0:00 ora_dbwb_BECHEDV1

oracle 50610 50565 0 17:27:03 ? 0:00 ora_dbwv_BECHEDV1

oracle 50579 50565 0 17:27:02 ? 0:00 ora_dbw2_BECHEDV1

oracle 50602 50565 0 17:27:03 ? 0:00 ora_dbwp_BECHEDV1

oracle 50595 50565 0 17:27:03 ? 0:00 ora_dbwi_BECHEDV1

oracle 50614 50565 0 17:27:03 ? 0:00 ora_dbwy_BECHEDV1

oracle 50598 50565 0 17:27:03 ? 0:00 ora_dbwl_BECHEDV1

oracle 50609 50565 0 17:27:03 ? 0:00 ora_dbwu_BECHEDV1

oracle 50590 50565 0 17:27:03 ? 0:00 ora_dbwd_BECHEDV1

oracle 50581 50565 0 17:27:02 ? 0:00 ora_dbw4_BECHEDV1

oracle 50586 50565 0 17:27:02 ? 0:00 ora_dbw9_BECHEDV1

oracle 50596 50565 0 17:27:03 ? 0:00 ora_dbwj_BECHEDV1

oracle 50584 50565 0 17:27:02 ? 0:00 ora_dbw7_BECHEDV1

oracle 50599 50565 0 17:27:03 ? 0:00 ora_dbwm_BECHEDV1

oracle 50604 50565 0 17:27:03 ? 0:00 ora_dbwr_BECHEDV1

oracle 50587 50565 0 17:27:02 ? 0:00 ora_dbwa_BECHEDV1

oracle 50585 50565 0 17:27:02 ? 0:00 ora_dbw8_BECHEDV1

oracle 50591 50565 0 17:27:03 ? 0:00 ora_dbwe_BECHEDV1

oracle 50603 50565 0 17:27:03 ? 0:00 ora_dbwq_BECHEDV1

oracle 50601 50565 0 17:27:03 ? 0:00 ora_dbwo_BECHEDV1

oracle 50606 50565 0 17:27:03 ? 0:00 ora_dbws_BECHEDV1

oracle 50597 50565 0 17:27:03 ? 0:00 ora_dbwk_BECHEDV1

oracle 50592 50565 0 17:27:03 ? 0:00 ora_dbwf_BECHEDV1

oracle 50577 50565 0 17:27:02 ? 0:00 ora_dbw0_BECHEDV1

UNIX >

 

I read somewhere that the maximum value for db_writer_processes is 36 but Oracle allowed me to change it to 37:

 

SQL> alter system set db_writer_processes = 37

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again:

 

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  521936896 bytes

Fixed Size                  2252448 bytes

Variable Size             306184544 bytes

Database Buffers          205520896 bytes

Redo Buffers                7979008 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

UNIX >

 

However, when I checked in the operating system, there were still only 36 database writer processes:

 

UNIX >  ps -ef|grep dbw|grep BECHEDV1|wc -l

      36

UNIX >

 

… and there was a message in the alert log telling me that db_writer_processes had been adjusted:

 

NOTE: db_writer_processes has been changed from 37 to  36ue to NUMA requirements.

SQL*Loader and ORA-01480

This was tested on Oracle 11.2.0.2.7 on Solaris. I created a SQL*Loader control file with a table name which was more than 30 characters long: 

ORCL > cat andrew.ctl

load data

infile 'andrew.dat'

into table andrew7890123456789012345678901

fields terminated by ','

(col1, col2, col3)

ORCL >

Then when I tried to use SQL*Loader, I got an ORA-01480: 

ORCL > sqlldr / control=andrew.ctl

 

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Feb 7 16:06:23 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

SQL*Loader-704: Internal error: ulmtsyn: OCIStmtExecute(tabhp) [1480]

ORA-01480: trailing null missing from STR bind value

ORCL >

I reduced the length of the table name to 30 characters and the error went away.

How to See the Version Number of an Oracle Client

If you want to find out the version number of an Oracle client from Windows, open a Command Prompt, type tnsping and press Enter / Return. No parameters are required. You will see the Oracle version number (10.2.0.4.0 in this case) and whether it is 32-bit or 64-bit. As usual, click on the image to enlarge it and bring it into focus:

Another option, suggested by Laurent Schneider in the first comment at the end, is to use sqlplus -v:

Tru64 > sqlplus -v

SQL*Plus: Release 9.2.0.7.0 - Production

Tru64 >

H:\>sqlplus -v

SQL*Plus: Release 10.2.0.4.0 - Production

H:\> 

Solaris > sqlplus -v

SQL*Plus: Release 11.2.0.2.0 Production 

Solaris >

ORA-01931 Again

In the previous post, I said that you could not grant unlimited tablespace to a role in Oracle 11.1.0.6.0. Today I discovered something else so I have repeated the example below, this time on Solaris:

ORACLE 11.1 > sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 14 16:06:15 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role role1;

Role created.

SQL> grant unlimited tablespace to role1;

grant unlimited tablespace to role1

*

ERROR at line 1:

ORA-01931: cannot grant UNLIMITED TABLESPACE to a role

SQL>

I had a look at the Oracle 11.1 documentation and this appears to be intentional:

ORA-01931: cannot grant string to a role

Cause: UNLIMITED TABLESPACE, REFERENCES, INDEX, SYSDBA or SYSOPER privilege cannot be granted to a role.

Action: Grant privilege directly to the user.

In Oracle 11.2.0.1.0, this is no longer the case:

ORACLE 11.2 > sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 14 16:08:53 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role role1;

Role created.

SQL> grant unlimited tablespace to role1;

Grant succeeded.

SQL> select privilege from dba_sys_privs

  2  where grantee = 'ROLE1';

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

SQL>

I had a look at the Oracle 11.2 documentation and again, this appears to be intentional:

ORA-01931: cannot grant string to a role

Cause: REFERENCES, INDEX, SYSDBA, SYSOPER or SYSASM privilege could not be granted to a role.

Action: Grant privilege directly to the user.

AVERAGE_WAIT

The AVERAGE_WAIT column in V$SYSTEM_EVENT records how long Oracle has had to wait (on average) for a given event. The value is in hundredths of a second:

 

SQL> l

  1  select event, average_wait

  2  from v$system_event

  3  where event in

  4  ('db file sequential read',

  5*  'db file scattered read')

SQL> /

 

EVENT                          AVERAGE_WAIT

------------------------------ ------------

db file sequential read                1.67

db file scattered read                 5.16

 

SQL>

 

The example above was run on an Oracle 11.2 test database running on Solaris with datafiles on a Celerra filer. The first line relates to single block reads and the second to multiblock reads. Here are the same values from an Oracle 11.2 production database, also running on Solaris but with datafiles on dedicated disks:

 

SQL> l

  1  select event, average_wait

  2  from v$system_event

  3  where event in

  4  ('db file sequential read',

  5*  'db file scattered read')

SQL> /

 

EVENT                          AVERAGE_WAIT

------------------------------ ------------

db file sequential read                  .2

db file scattered read                  .58

 

SQL>

Finally, here are the figures from an Oracle 10.2.0.1.0 database running on Windows XP SP3 on a PC I assembled myself:

SQL> l
  1  select event, average_wait
  2  from v$system_event
  3  where event in
  4  ('db file sequential read',
  5*  'db file scattered read')
SQL> /

EVENT                          AVERAGE_WAIT
------------------------------ ------------
db file sequential read                6,49
db file scattered read                 5,88

SQL>

 

CPU_COUNT

According to Oracle’s own documentation for 10g release 1:

 

On most platforms, Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.

 

The following test was done on an Oracle 9 database running on Tru64:

 

Tru64 > psrinfo -n

number of processors on system = 1

Tru64 > psrinfo -v

Status of processor 0 as of: 12/02/11 15:19:04

  Processor has been on-line since 02/19/2011 17:29:11

  The alpha EV6.7 (21264A) processor operates at 618 MHz,

  has a cache size of 2097152 bytes,

  and has an alpha internal floating point processor.

 

Tru64 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Dec 2 15:19:28 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> col value format a10

SQL> col isdefault format a9

SQL> select value, isdefault from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE      ISDEFAULT

---------- ---------

1          TRUE

 

SQL>

 

The following test was done on an Oracle 9 database running on Linux:

 

Linux > cat /proc/cpuinfo | grep -i 'processor' | wc -l

2

Linux > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Dec 2 15:40:12 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

 

SQL> col value format a10

SQL> col isdefault format a9

SQL> select value, isdefault from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE      ISDEFAULT

---------- ---------

2          TRUE

 

SQL>

 

The following test was done on an Oracle 10 database running on Solaris:

 

Solaris > psrinfo -p -v

The physical processor has 2 virtual processors (0 16)

  UltraSPARC-IV+ (portid 0 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (2 18)

  UltraSPARC-IV+ (portid 2 impl 0x19 ver 0x22 clock 1500 MHz)

Solaris > sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 2 15:52:40 2011

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL> col isdefault format a9

SQL> col value format a5

SQL> select value, isdefault from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE ISDEFAULT

----- ---------

4     TRUE

 

SQL>

 

So it looks as if it works as intended. I went to an Oracle seminar recently, where they said that cpu_count works differently in 11g release 2. Once I have investigated, I will cover this in a future post.

ORA-00209

I was asked to move a database from a Linux server to a Solaris server today. Nobody knows who created the source database. The last update to the parameter file was made in 2006. The person who did it used one of those sample init.ora files and made the necessary changes for the database in question. However, for some reason he (or she, as we have had a couple of female DBA’s where I work in the past) duplicated a large part of the parameter file so it contained 2 copies of several parameters! To avoid confusion, the parameter file for the target database only has one copy of each parameter. However, the spare parameters in the source database never caused a problem. Oracle simply used the value from the last copy of any given parameter. So , if your parameter file includes the following:

 

open_cursors=100

open_cursors=200

 

When you open the database, it will look like this:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'open_cursors'

SQL> /

 

VALUE

------------------------------

200

 

SQL>

 

It even seems to be able to handle the situation where the first parameter is wrong. So if your parameter file includes the following:

 

db_block_size=4096

db_block_size=8192

 

When you open the database, it will look like this:

 

SQL> select value from v$parameter

  2  where name = 'db_block_size';

 

VALUE

------------------------------

8192

 

SQL>

 

Obviously, if you put the incorrect parameter last, the database will not open. So, if your parameter file includes the following:

 

db_block_size=8192

db_block_size=4096

 

Your database will not open:

 

SQL> startup

ORACLE instance started.

 

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

ORA-00209: control file blocksize mismatch, check

alert log for more info

 

SQL>

SQL*Plus on UNIX and ps -ef

This example was tested on an Oracle 10 database running on Solaris.

Imagine you have a UNIX user called smithj. He logs into the system database user as follows:

TEST10 > whoami

smithj

TEST10 > sqlplus system/secret

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 26 15:13:23 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

However, while he is doing this, another user, e.g. bloggsf, can use the ps command to see the password for the system database user as follows:

TEST10 > whoami

bloggsf

TEST10 > ps -ef|grep sqlplus|grep -v grep

smithj 4324 29829 0 15:13:23 pts/12 0:00 sqlplus system/secret
TEST10 >

To stop this happening, smithj should login to the database with the username and wait for Oracle to prompt him for the password:

TEST10 > sqlplus system

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 26 16:12:37 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

Then bloggsf can see that smithj has logged in to the system user but cannot see the password:

TEST10 > ps -ef|grep sqlplus|grep -v grep

smithj 21306 20953 0 16:12:37 pts/12 0:00 sqlplus system

TEST10 >