{"id":89504,"date":"2021-01-11T17:32:54","date_gmt":"2021-01-11T17:32:54","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89504"},"modified":"2021-07-14T13:06:50","modified_gmt":"2021-07-14T13:06:50","slug":"oracle-archived-redo-size-research","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-archived-redo-size-research\/","title":{"rendered":"Oracle archived redo size \u2013 the research"},"content":{"rendered":"

In an earlier article<\/a>, I explained why you might see log file switches taking place much more frequently than expected, with the archived<\/em><\/strong> redo files being much smaller than the online<\/em><\/strong> redo log files.<\/p>\n

When I do presentations about the odd effects that can appear in a fairly ordinary Oracle system, I\u2019m often asked what I did to work out what was happening under the covers. In this article, I will describe a few of the steps I took to investigate this aspect of the logging and archiving mechanism. It\u2019s not rocket science, and most of the time, all I ever do is use methods that have been available for decades.<\/p>\n

cpu_count<\/h2>\n

I started with a review of some default parameter settings and the resulting allocation of public and private redo strands. Somewhere along the line, I pointed out that with \u201cmy cpu_count of 48\u201d<\/em> I got 3 public redo strands.<\/p>\n

I don\u2019t actually have a laptop with 48 CPUs \u2013 I have a laptop with a single core and 4 CPUs that can do double-threading to look like 8 CPUs, and I was running 19c in a virtual machine configured with 4 CPUs. I set the cpu_count<\/code> parameter to 48 and restarted the instance \u2013 which still told me I had only 4 CPUs until I also set the parameter _disable_cpu_check<\/code> to true<\/em>. Now, after startup, I see the following:<\/p>\n

SQL> show parameter cpu<\/pre>\n
\"CPU<\/p>\n
Given that I was going to mess with parameters a lot, I switched from a binary spfile<\/em> to a pure text pfile<\/em>, (create pfile from spfile<\/code> command) and ended up issuing the following pair of commands fairly frequently:<\/p>\n
shutdown transactional<\/p>\n
startup pfile=\u2019.\/initor19.ora\u2019<\/pre>\n
That should tell you how I decided that processes <\/em><\/strong>defaults to 80 * cpu_count<\/em><\/strong> + 40 and found that the manual was slightly wrong in its statement about the default value of sessions<\/em><\/strong> (actually 1.5 * processes<\/em><\/strong> + 24) but correct in its statement about transactions<\/em><\/strong> (1.1 * sessions<\/em><\/strong>). I kept restarting the instance after changing the cpu_count<\/em><\/strong> in my pfile<\/em> to see how the other parameters changed.<\/p>\n
Redo Strands and In-Memory Undo (IMU)<\/h2>\n
In the previous article, I mentioned the existence of x$<\/code> structures but used v$latch_children<\/code> to find out more about in-memory undo and redo strands. If you want access to the memory for redo strands and in-memory undo, you can query x$kcrfstrand<\/code> (redo strands) and x$ktifp<\/code> (in-memory undo) while connected as sys<\/em><\/strong>. First, the redo strands:<\/p>\n
select\r\n        ptr_kcrf_pvt_strand,\r\n        first_buf_kcrfa,\r\n        last_buf_kcrfa,\r\n        total_bufs_kcrfa,\r\n        strand_size_kcrfa,\r\n        indx\r\nfrom\r\n        x$kcrfstrand\r\n\/<\/pre>\n
<\/p>\n
This output shows 3 (= 48\/16) public redo strands using 7,856,128 bytes of memory each, and 7 private strands using 132,096 bytes of memory each. The latter was surprising since the number of redo allocation latches<\/em><\/strong> told me I should have 644 private strands. Possibly there\u2019s some memory effect that stopped this from happening, or it\u2019s an initial allocation that grows dynamically on demand. Still, I\u2019ve actually been able to disable private redo strands completely by setting an \u201cunlucky\u201d size for the log_buffer<\/code> parameter which left me with no private strands on startup and resulted in the system activity statistics incrementing the \u201cIMU pool not allocated\u201d<\/em> and \u201cIMU- failed to get a private strand\u201d<\/em> statistics on every transaction.<\/p>\n
Now the in-memory undo:<\/p>\n
select\r\n        ktifpno,\r\n        ktifpxcb                                tx_addr,\r\n        ktifpupb                                undo_start,\r\n        ktifpupc                                undo_cur,\r\n        to_number(ktifpupc,'XXXXXXXXXXXXXXXX') -\r\n                to_number(ktifpupb,'XXXXXXXXXXXXXXXX')  undo_usage,\r\n        ktifprpb                                redo_start,\r\n        ktifprpc                                redo_cur,\r\n        to_number(ktifprpc,'XXXXXXXXXXXXXXXX') -\r\n                to_number(ktifprpb,'XXXXXXXXXXXXXXXX')  redo_usage,\r\n        ktifptxflg\r\nfrom\r\n        x$ktifp\r\n\/<\/pre>\n
I\u2019ve selected all the rows from this structure (but deleted most of them) to show that Oracle had allocated all 644 even though it had only allocated a few of the private redo strands. If I wanted to query this structure for troubleshooting purposes, I\u2019d probably add the predicate: where tx_addr != hextoraw('00')<\/code> to limit the output to just the strands currently in use.<\/p>\n
<\/p>\n
The log buffer and the log file<\/h2>\n
The next step was checking the connection between the log buffer \/ public strands and the log file. I had set the log_buffer<\/code> parameter to 60MB with 3 public strands and a log file of 65MB and then made a single process do a lot of work. After seeing the effect on the size of the archived redo log file, I made the initial hypothesis that the three strands had been mapped to 3 areas in the log file. What could I do to confirm this hypothesis?<\/p>\n
When I had my 3 strands of 20MB, the first 3 rows of x$kcrfstrand<\/code> looked like this:<\/p>\n
<\/p>\n
Notice the addresses given in the first_buf_kcrfa<\/code> and the gaps between them:<\/p>\n