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.
No comments:
Post a Comment