sga_max_size and db_writer_processes - tuning and setting
Hi Community,
I have 11 databases running 11.2.0.4 with patch 181016.
An application 'test' utility shows better performance on two databases compared to the other 9 databases.
I have noted two differences that might cause this discrepency.
1. sga_max_size
2. db_writer_processes.
| Database/Parameter | DB8 | DB9 | DB1 | DB5 |
|---|---|---|---|---|
| sga_max_size | 33504100352 | 33504100352 | 68719476736 | 68719476736 |
| sga_target | 0 | 0 | 0 | 0 |
| db_writer_processes | 4 | 4 | 25 | 25 |
| memory_max_target | 68719476736 | 68719476736 | 68719476736 | 68719476736 |
| memory_max_size | 51539607552 | 51539607552 | 51539607552 | 51539607552 |
I'm concerned about the difference of sga_max_size when the sga_target is "0" (zero), which therefore suggests that the database would determine the sga_max_size.
The DB8 and DB9 show "better" performance with the "tester" applications, that DB1 and DB5 (DB2, DB3, DB4, DB6, DB7, DB10 and DB11 - have identical settings here).
1