Changing The Number Of Oracle Database 12c Log Writers
In an Oracle Database 12c instance you will likely see multiple log writer (LGWR) background processes. When you first start the Oracle instance you will likely see a parent and two redo workers. This is a very big deal and something many of us have been waiting for - for many years!
While I'm excited about the change, if I can't control the number of LGWRs I could easily find myself once again constrained by the lack of LGWRs!
So, my question is how do I manipulate the number of LGWRs from the default. And what is the default based on? It's these types of questions that led me on this quest. I hope you enjoy the read!
Serialization Is Death
Multiple LGWRs is great news because serialization is death to computing performance. Think of it like this. A computer program is essentially lines of code and each line of code takes a little bit of time to execute. A CPU can only process N lines of code per second. This means every serial executing program has a maximum through capability. With a single log writer (LGWR) background process the amount of redo that can be processed is similarly constrained.
An Example Of Serialization Throughput Limitation
Suppose a CPU can process 1000 instructions per millisecond. Also, assume through some research a DBA determined it takes the LGWR 10 instructions to process 10 KB of redo. (I know DBAs who have taken the time to figure this stuff out.) Given these two pieces of data, how many KB of redo can the CPU theoretically process per second?
? KB of redo/sec = (1000 inst / 1 ms)*(10 KB redo / 10 instr)*(1000 ms / 1 sec)* (1 MB / 1000 KB) = 1000 KB redo/sec
This is a best case scenario. As you can see, any sequential process can become a bottleneck. One solution to this problem is to parallelize.
Note: Back in April of 2010 I posted a series of articles about parallelism. If you are interested in this topic, I highly recommend you READ THE POSTS.
Very Cool! Multiple 12c LGWRs... But Still A Limit?
Since serialization is death... and parallelism is life, I was really excited when I saw on my 12c Oracle instance by default it had two redo workers in addition to the "parent" log writer. On my Oracle version 184.108.40.206.2.0 Linux machine this is what I see:
$ ps -eaf|grep prod40 | grep ora_lg oracle 54964 1 0 14:37 ? 00:00:00 ora_lgwr_prod40 oracle 54968 1 0 14:37 ? 00:00:00 ora_lg00_prod40 oracle 54972 1 0 14:37 ? 00:00:00 ora_lg01_prod40
This is important. While this is good news, unless Oracle or I have the ability to change and increase the number of LGWR redo workers, at some point the two redo workers, will become saturated bringing us back to the same serial LGWR process situation. So, I want and need some control.
Going Back To Only One LGWR
Interestingly, starting in Oracle Database version 220.127.116.11.2.0 there is an instance parameter _use_single_log_writer. I was able to REDUCE the number LGWRs to only one by setting the instance parameter _use_single_log_writer=TRUE. But that's the wrong direction I want to go!
More Redo Workers: "CPU" Instance Parameters
I tried a variety of CPU related instance parameters with no success. Always two redo workers.
More Redo Workers: Set Event...
Using my OSM script listeventcodes.sql I scanned the Oracle events (not wait events) but was unable to find any related Oracle events. Bummer...
More Redo Workers: More Physical CPUs Needed?
While talking to some DBAs about this, one of them mentioned they heard Oracle sets the number of 12c log writers is based on the number of physical CPUs. Not the number CPU cores but the number of physical CPUs. On a Solaris box with 2 physical CPUs (verified using the command, psrinfo -pv) upon startup there was still on two redo workers.
$ psrinfo -p 2 $ psrinfo -pv The physical processor has 1 virtual processor (0) UltraSPARC-III (portid 0 impl 0x14 ver 0x3e clock 900 MHz) The physical processor has 1 virtual processor (1) UltraSPARC-III (portid 1 impl 0x14 ver 0x3e clock 900 MHz)
More Redo Workers: Adaptive Behavior?
Looking closely at the Solaris LGWR trace file I repeatedly saw this:
Created 2 redo writer workers (2 groups of 1 each) kcrfw_slave_adaptive_updatemode: scalable->single group0=375 all=384 delay=144 r w=7940 *** 2014-12-08 11:33:39.201 Adaptive scalable LGWR disabling workers kcrfw_slave_adaptive_updatemode: single->scalable redorate=562 switch=23 *** 2014-12-08 15:54:10.972 Adaptive scalable LGWR enabling workers kcrfw_slave_adaptive_updatemode: scalable->single group0=1377 all=1408 delay=113 rw=6251 *** 2014-12-08 22:01:42.176 Adaptive scalable LGWR disabling workers
It looks to me like Oracle has programed in some sweeeeet logic to adapt the numbers of redo workers based the redo load.
So I created six Oracle sessions that simply inserted rows into a table and ran all six at the same time. But it made no difference in the number of redo workers. No increase or decrease or anything! I let this dml load run for around five minutes. Perhaps that wasn't long enough, the load was not what Oracle was looking for or something else. But the number of redo workers always remained at two.
Summary & Conclusions
It appears at instance startup the default number of Oracle Database 12c redo workers is two. It also appears that Oracle has either already built or is building the ability for Oracle to adapt to changing redo activity by enabling and disabling redo workers. Perhaps the number of physical CPUs (not CPU cores but physical CPUs) plays a part in this algorithm.
While this was not my research objective, I did discover a way to set the number of redo workers back to the traditional single LGWR background process.
While I enjoyed doing the research for this article, it was disappointing that I was unable to influence Oracle to increase the number of redo workers. I sure hope Oracle either gives me control or the adaptive behavior actually works. If not, two redo workers won't be enough for many Oracle systems.
All the best in your Oracle performance endeavors!
Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.
Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
|Parsing Performance: Going Beyond Cursor Sharing Using Bind Variables||How To See Unseen Activity Using ASH And SQL*Net Message From Client||Why Tuning Oracle Database Works And Modeling It|