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.

ORA-12801 in utlrp

I tried to run a utlrp in an Oracle 11.1.0.6.0 database and saw the errors below:

ORA-12801: error signaled in parallel query server P035

ORA-00018: maximum number of sessions exceeded

ORA-06512: at "SYS.UTL_RECOMP", line 629

ORA-06512: at line 4

I checked the number of sessions in v$session but there were not very many:

SQL> select count(*) from v$session;

 

  COUNT(*)

----------

        32

 

SQL>

I checked the value of the sessions parameter and it was much higher:

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

87

 

SQL>

I took a look in the utlrp.sql and saw the following line:

@@utlprp.sql 0

I read somewhere that the parameter supplied to utlprp.sql specifies the number of parallel processes to use when doing the recompilations. If it is zero, the value is calculated as cpu_count x parallel_threads_per_cpu. I checked these two parameters:

SQL> l

  1  select name, value

  2  from v$parameter

  3  where name in

  4* ('cpu_count', 'parallel_threads_per_cpu')

SQL> /

 

NAME                           VALUE

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

cpu_count                      16

parallel_threads_per_cpu       2

 

SQL>

I traced my session and reran the utlrp.sql. When I looked in the trace file, I saw the following piece of SQL, which I have reformatted slightly:

WITH INST_CPUS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPUS

FROM GV$PARAMETER

WHERE NAME = 'cpu_count'),

INST_THREADS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPU_THREADS

FROM GV$PARAMETER

WHERE NAME = 'parallel_threads_per_cpu')

SELECT SUM((CASE WHEN CPUS <= 0 THEN 1 ELSE CPUS END)

* (CASE WHEN CPU_THREADS <= 0 THEN 1 ELSE CPU_THREADS END))

FROM INST_CPUS, INST_THREADS

WHERE INST_CPUS.INST_ID = INST_THREADS.INST_ID

So, clearly, cpu_count and parallel_threads_per_cpu are being multiplied together for some reason when you run a utlrp.sql. I changed the line above from:

@@utlprp.sql 0

to

@@utlprp.sql 1

... hoping that this would make the recompilations run one at a time. I ran the utlrp.sql again and it worked.

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.