While running a 10046 trace against a replicat session, I saw UPDATE statements against the checkpoint table. While this isn’t unexpected, as I had configured the replicat to use a checkpoint table, I was curious to see how often it did. in other words, is it every 3 seconds, or every 100 rows, etc.<\/p>\n
I wasn’t surprised, as it is never that simple, to find it somewhat random. To verify, I ran a test with several thousand INSERTs per minute against two tables, ORDERS and ORDER_DETAILS. I then extracted the CURSOR # in the 10046 trace for each statement, and ran the following awk against the trace file.<\/p>\n
\r\nexpressdb1:oracle:express1:\/u01\/app\/oracle\/diag\/rdbms\/express\/express1\/trace# cat a.awk\r\n{\r\n if ($0 ~ \"EXEC #47821729564056\" || $0 ~ \"EXEC #47821729706488\") {\r\n j++\r\n found=1\r\n }\r\n else if($0 ~ \"XCTEND rlbk=0, rd_only=0\" && found=1) {\r\n split($0,t,\",\")\r\n split(t[3],s,\"=\")\r\n PROCESSED++\r\n if (last > 0 && (PROCESSED <= 10 || PROCESSED >= 100)) {\r\n printf(\"Inserted %i rows in %.1f seconds before checkpointing\\n\",j,(s[2]-last) \/ 1000000)\r\n }\r\n j=0\r\n last=s[2]\r\n }\r\n}\r\nexpressdb1:oracle:express1:\/u01\/app\/oracle\/diag\/rdbms\/express\/express1\/trace# awk -f a.awk express1_ora_23158.trc\r\nInserted 701 rows in 1.3 seconds before checkpointing\r\nInserted 236 rows in 2.9 seconds before checkpointing\r\nInserted 399 rows in 10.1 seconds before checkpointing\r\nInserted 22 rows in 1.7 seconds before checkpointing\r\nInserted 75 rows in 3.3 seconds before checkpointing\r\nInserted 458 rows in 4.1 seconds before checkpointing\r\nInserted 107 rows in 1.2 seconds before checkpointing\r\nInserted 339 rows in 7.2 seconds before checkpointing\r\nInserted 164 rows in 5.0 seconds before checkpointing\r\nInserted 263 rows in 5.4 seconds before checkpointing\r\nInserted 89 rows in 3.1 seconds before checkpointing\r\nInserted 121 rows in 5.5 seconds before checkpointing\r\nInserted 160 rows in 2.0 seconds before checkpointing\r\nInserted 363 rows in 4.3 seconds before checkpointing\r\nInserted 107 rows in 13.6 seconds before checkpointing\r\nInserted 303 rows in 1.8 seconds before checkpointing\r\nInserted 120 rows in 2.4 seconds before checkpointing\r\nInserted 118 rows in 1.3 seconds before checkpointing\r\nInserted 204 rows in 2.3 seconds before checkpointing\r\nInserted 333 rows in 6.1 seconds before checkpointing\r\nInserted 64 rows in 4.3 seconds before checkpointing\r\nInserted 198 rows in 9.9 seconds before checkpointing\r\nInserted 6 rows in 7.0 seconds before checkpointing\r\nexpressdb1:oracle:express1:\/u01\/app\/oracle\/diag\/rdbms\/express\/express1\/trace#\r\n<\/pre>\nWhen I did the math, I found the time between checkpoints was as low as about a second, and as high as 13 seconds. The median and average was about two seconds. The INSERT execution counts between checkpoints, as you can see above, are all over the map.<\/p>\n
No real information, but I thought it was interesting.<\/p>\n","protected":false},"excerpt":{"rendered":"
While running a 10046 trace against a replicat session, I saw UPDATE statements against the checkpoint table. While this isn’t unexpected, as I had configured the replicat to use a checkpoint table, I was curious to see how often it…<\/p>\n