{"id":15820,"date":"2021-03-01T15:54:20","date_gmt":"2021-03-01T14:54:20","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/"},"modified":"2021-03-01T15:54:20","modified_gmt":"2021-03-01T14:54:20","slug":"oracle-blockchain-tables-commit-time","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/","title":{"rendered":"Oracle Blockchain Tables: COMMIT-Time"},"content":{"rendered":"

Oracle Blockchain Tables<\/a> are available now with Oracle 19.10. (see Connor’s Blog on it<\/a>), they are part of all editions and do not need any specific license. I.e. whenever we need to store data in a table, which should never be updated anymore and we have to ensure data cannot be tampererd, then blockchain tables should be considered as an option. As Oracle writes in the documentation that blockchain tables could e.g. be used for “audit trails”, I thought to test them by archiving unified audit trail data. Let me share my experience:<\/p>\n

First of all I setup a 19c-database so that it supports blockchain tables:<\/p>\n

– Installed 19.10.0.0.210119 (patch 32218454)
\n– Set COMAPTIBLE=19.10.0 and restarted the DB
\n– Installed patch 32431413<\/p>\n

REMARK: All tests I’ve done with 19c have been done with Oracle 21c on the Oracle Cloud as well to verify that results are not caused by the backport of blockchain tables to 19c.<\/p>\n

Creating the BLOCKCHAIN TABLE:<\/p>\n

Blockchain Tables do not support the Create Table as Select-syntax:<\/p>\n

\ncreate blockchain table uat_copy_blockchain2\nno drop until 0 days idle\nno delete until 31 days after insert\nhashing using \"sha2_512\" version v1\ntablespace audit_data\nas select * from unified_audit_trail;\n\nERROR at line 6:\nORA-05715: operation not allowed on the blockchain table\n<\/code><\/pre>\n
I.e. I have to pre-create the blockain table and insert with “insert… select”:<\/p>\n
\nCREATE blockchain TABLE uat_copy_blockchain \n   (\"AUDIT_TYPE\" VARCHAR2(64),\n\t\"SESSIONID\" NUMBER,\n\t\"PROXY_SESSIONID\" NUMBER,\n\t\"OS_USERNAME\" VARCHAR2(128),\n...\n\t\"DIRECT_PATH_NUM_COLUMNS_LOADED\" NUMBER,\n\t\"RLS_INFO\" CLOB,\n\t\"KSACL_USER_NAME\" VARCHAR2(128),\n\t\"KSACL_SERVICE_NAME\" VARCHAR2(512),\n\t\"KSACL_SOURCE_LOCATION\" VARCHAR2(48),\n\t\"PROTOCOL_SESSION_ID\" NUMBER,\n\t\"PROTOCOL_RETURN_CODE\" NUMBER,\n\t\"PROTOCOL_ACTION_NAME\" VARCHAR2(32),\n\t\"PROTOCOL_USERHOST\" VARCHAR2(128),\n\t\"PROTOCOL_MESSAGE\" VARCHAR2(4000)\n   )\nno drop until 0 days idle\nno delete until 31 days after insert\nhashing using \"sha2_512\" version v1\ntablespace audit_data;\n\nTable created.\n\n<\/code><\/pre>\n
Now load the data into the blockchain table:<\/p>\n
\nSQL> insert into uat_copy_blockchain\n  2  select * from unified_audit_trail;\n\n26526 rows created.\n\nElapsed: 00:00:07.24\nSQL> commit;\n\nCommit complete.\n\nElapsed: 00:00:43.26\n<\/code><\/pre>\n
Over 43 seconds for the COMMIT!!!<\/b><\/p>\n
The reason for the long COMMIT-time is that the blockchain (or better the row-chain of hashes for the 26526 rows) is actually built when committing. I.e. all blockchain related columns in the table are empty after the insert, before the commit:<\/p>\n
\nSQL> insert into uat_copy_blockchain\n  2  select * from unified_audit_trail;\n\n26526 rows created.\n\nSQL> select count(*) from uat_copy_blockchain\n  2  where ORABCTAB_INST_ID$ is NULL\n  3  and ORABCTAB_CHAIN_ID$ is NULL\n  4  and ORABCTAB_SEQ_NUM$ is NULL\n  5  and ORABCTAB_CREATION_TIME$ is NULL\n  6  and ORABCTAB_USER_NUMBER$ is NULL\n  7  and ORABCTAB_HASH$ is NULL\n  8  ;\n\n  COUNT(*)\n----------\n     26526\n<\/code><\/pre>\n
During the commit those hidden columns are updated:<\/p>\n
\nSQL> commit;\n\nCommit complete.\n\nSQL> select count(*) from uat_copy_blockchain\n  2  where ORABCTAB_INST_ID$ is NULL\n  3  or ORABCTAB_CHAIN_ID$ is NULL\n  4  or ORABCTAB_SEQ_NUM$ is NULL\n  5  or ORABCTAB_CREATION_TIME$ is NULL\n  6  or ORABCTAB_USER_NUMBER$ is NULL\n  7  or ORABCTAB_HASH$ is NULL\n  8  ;\n\n  COUNT(*)\n----------\n         0\n<\/code><\/pre>\n
When doing a SQL-Trace I can see the following recursive statements during the COMMIT:<\/p>\n
\nSQL ID: 6r4qu6xnvb3nt Plan Hash: 960301545\n\nupdate \"CBLEILE\".\"UAT_COPY_BLOCKCHAIN\" set orabctab_inst_id$ = :1,\n  orabctab_chain_id$ = :2, orabctab_seq_num$ = :3, orabctab_user_number$ = :4,\n   ORABCTAB_CREATION_TIME$ = :5\nwhere\n rowid = :lrid\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse    26526      0.56       0.55          0          0          0           0\nExecute  26526     10.81      12.21       3824       3395      49546       26526\nFetch        0      0.00       0.00          0          0          0           0\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    53052     11.38      12.76       3824       3395      49546       26526\n\n********************************************************************************\n\nSQL ID: 4hc26wpgb5tqr Plan Hash: 2019081831\n\nupdate sys.blockchain_table_chain$ set                    hashval_position =\n  :1, max_seq_number =:2\nwhere\n obj#=:3 and inst_id = :4 and chain_id = :5\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute  26526      9.29      10.12        512      26533      27822       26526\nFetch        0      0.00       0.00          0          0          0           0\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    26527      9.29      10.12        512      26533      27822       26526\n\n********************************************************************************\n\nSQL ID: 2t5ypzqub0g35 Plan Hash: 960301545\n\nupdate \"CBLEILE\".\"UAT_COPY_BLOCKCHAIN\" set orabctab_hash$ = :1\nwhere\n rowid = :lrid\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse    26526      0.58       0.57          0          0          0           0\nExecute  26526      6.79       7.27       1832       2896      46857       26526\nFetch        0      0.00       0.00          0          0          0           0\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    53052      7.37       7.85       1832       2896      46857       26526\n\n********************************************************************************\n\nSQL ID: bvggpqdp5u4uf Plan Hash: 1612174689\n\nselect max_seq_number, hashval_position\nfrom\n sys.blockchain_table_chain$ where obj#=:1 and                     inst_id =\n  :2 and chain_id = :3\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute  26527      5.34       5.51          0          0          0           0\nFetch    26527      0.75       0.72          0      53053          0       26526\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    53055      6.10       6.24          0      53053          0       26526\n\n********************************************************************************\n\nSQL ID: dktp4suj3mn0t Plan Hash: 4188997816\n\nSELECT  \"AUDIT_TYPE\",  \"SESSIONID\",  \"PROXY_SESSIONID\",  \"OS_USERNAME\",\n  \"USERHOST\",  \"TERMINAL\",  \"INSTANCE_ID\",  \"DBID\",  \"AUTHENTICATION_TYPE\",\n  \"DBUSERNAME\",  \"DBPROXY_USERNAME\",  \"EXTERNAL_USERID\",  \"GLOBAL_USERID\",\n  \"CLIENT_PROGRAM_NAME\",  \"DBLINK_INFO\",  \"XS_USER_NAME\",  \"XS_SESSIONID\",\n  \"ENTRY_ID\",  \"STATEMENT_ID\",  \"EVENT_TIMESTAMP\",  \"EVENT_TIMESTAMP_UTC\",\n  \"ACTION_NAME\",  \"RETURN_CODE\",  \"OS_PROCESS\",  \"TRANSACTION_ID\",  \"SCN\",\n  \"EXECUTION_ID\",  \"OBJECT_SCHEMA\",  \"OBJECT_NAME\",  \"SQL_TEXT\",  \"SQL_BINDS\",\n    \"APPLICATION_CONTEXTS\",  \"CLIENT_IDENTIFIER\",  \"NEW_SCHEMA\",  \"NEW_NAME\",\n   \"OBJECT_EDITION\",  \"SYSTEM_PRIVILEGE_USED\",  \"SYSTEM_PRIVILEGE\",\n  \"AUDIT_OPTION\",  \"OBJECT_PRIVILEGES\",  \"ROLE\",  \"TARGET_USER\",\n  \"EXCLUDED_USER\",  \"EXCLUDED_SCHEMA\",  \"EXCLUDED_OBJECT\",  \"CURRENT_USER\",\n  \"ADDITIONAL_INFO\",  \"UNIFIED_AUDIT_POLICIES\",  \"FGA_POLICY_NAME\",\n  \"XS_INACTIVITY_TIMEOUT\",  \"XS_ENTITY_TYPE\",  \"XS_TARGET_PRINCIPAL_NAME\",\n  \"XS_PROXY_USER_NAME\",  \"XS_DATASEC_POLICY_NAME\",  \"XS_SCHEMA_NAME\",\n  \"XS_CALLBACK_EVENT_TYPE\",  \"XS_PACKAGE_NAME\",  \"XS_PROCEDURE_NAME\",\n  \"XS_ENABLED_ROLE\",  \"XS_COOKIE\",  \"XS_NS_NAME\",  \"XS_NS_ATTRIBUTE\",\n  \"XS_NS_ATTRIBUTE_OLD_VAL\",  \"XS_NS_ATTRIBUTE_NEW_VAL\",  \"DV_ACTION_CODE\",\n  \"DV_ACTION_NAME\",  \"DV_EXTENDED_ACTION_CODE\",  \"DV_GRANTEE\",\n  \"DV_RETURN_CODE\",  \"DV_ACTION_OBJECT_NAME\",  \"DV_RULE_SET_NAME\",\n  \"DV_COMMENT\",  \"DV_FACTOR_CONTEXT\",  \"DV_OBJECT_STATUS\",  \"OLS_POLICY_NAME\",\n    \"OLS_GRANTEE\",  \"OLS_MAX_READ_LABEL\",  \"OLS_MAX_WRITE_LABEL\",\n  \"OLS_MIN_WRITE_LABEL\",  \"OLS_PRIVILEGES_GRANTED\",  \"OLS_PROGRAM_UNIT_NAME\",\n   \"OLS_PRIVILEGES_USED\",  \"OLS_STRING_LABEL\",  \"OLS_LABEL_COMPONENT_TYPE\",\n  \"OLS_LABEL_COMPONENT_NAME\",  \"OLS_PARENT_GROUP_NAME\",  \"OLS_OLD_VALUE\",\n  \"OLS_NEW_VALUE\",  \"RMAN_SESSION_RECID\",  \"RMAN_SESSION_STAMP\",\n  \"RMAN_OPERATION\",  \"RMAN_OBJECT_TYPE\",  \"RMAN_DEVICE_TYPE\",\n  \"DP_TEXT_PARAMETERS1\",  \"DP_BOOLEAN_PARAMETERS1\",\n  \"DIRECT_PATH_NUM_COLUMNS_LOADED\",  \"RLS_INFO\",  \"KSACL_USER_NAME\",\n  \"KSACL_SERVICE_NAME\",  \"KSACL_SOURCE_LOCATION\",  \"PROTOCOL_SESSION_ID\",\n  \"PROTOCOL_RETURN_CODE\",  \"PROTOCOL_ACTION_NAME\",  \"PROTOCOL_USERHOST\",\n  \"PROTOCOL_MESSAGE\",  \"ORABCTAB_INST_ID$\",  \"ORABCTAB_CHAIN_ID$\",\n  \"ORABCTAB_SEQ_NUM$\",  \"ORABCTAB_CREATION_TIME$\",  \"ORABCTAB_USER_NUMBER$\",\n  \"ORABCTAB_HASH$\",  \"ORABCTAB_SIGNATURE$\",  \"ORABCTAB_SIGNATURE_ALG$\",\n  \"ORABCTAB_SIGNATURE_CERT$\"\nfrom\n \"CBLEILE\".\"UAT_COPY_BLOCKCHAIN\" where rowid = :lrid\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute  26526      3.85       3.84          0          0          0           0\nFetch    26526      1.31       1.31          0      28120          0       26526\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    53053      5.17       5.15          0      28120          0       26526\n\n********************************************************************************\n\nSQL ID: fcq6kngm4b3m5 Plan Hash: 4188997816\n\nSELECT  \"ORABCTAB_INST_ID$\",  \"ORABCTAB_CHAIN_ID$\",  \"ORABCTAB_SEQ_NUM$\",\n  \"ORABCTAB_CREATION_TIME$\",  \"ORABCTAB_USER_NUMBER$\",  \"ORABCTAB_HASH$\",\n  \"ORABCTAB_SIGNATURE$\",  \"ORABCTAB_SIGNATURE_ALG$\",\n  \"ORABCTAB_SIGNATURE_CERT$\",  \"ORABCTAB_SPARE$\"\nfrom\n \"CBLEILE\".\"UAT_COPY_BLOCKCHAIN\" where rowid = :lrid\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute  26526      3.04       3.05          0          0          0           0\nFetch    26526      0.41       0.39          0      26526          0       26526\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    53053      3.45       3.45          0      26526          0       26526\n\n********************************************************************************\n\nSQL ID: fcq6kngm4b3m5 Plan Hash: 4188997816\n\nSELECT  \"ORABCTAB_INST_ID$\",  \"ORABCTAB_CHAIN_ID$\",  \"ORABCTAB_SEQ_NUM$\",\n  \"ORABCTAB_CREATION_TIME$\",  \"ORABCTAB_USER_NUMBER$\",  \"ORABCTAB_HASH$\",\n  \"ORABCTAB_SIGNATURE$\",  \"ORABCTAB_SIGNATURE_ALG$\",\n  \"ORABCTAB_SIGNATURE_CERT$\",  \"ORABCTAB_SPARE$\"\nfrom\n \"CBLEILE\".\"UAT_COPY_BLOCKCHAIN\" where rowid = :lrid\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute  26526      3.04       3.05          0          0          0           0\nFetch    26526      0.41       0.39          0      26526          0       26526\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal    53053      3.45       3.45          0      26526          0       26526\n<\/code><\/pre>\n
I.e. for every row inserted in the transaction, several recursive statements have to be executed to compute and update the inserted rows to link them together through the hash chain.<\/p>\n
That raises the question if I should take care of PCTFREE when creating the blockchain table to avoid row migration (often wrongly called row chaining).<\/p>\n
As with normal tables, blockchain tables have a default of 10% for PCTFREE:<\/p>\n
\nSQL> select pct_free from tabs where table_name='UAT_COPY_BLOCKCHAIN';\n\n  PCT_FREE\n----------\n        10\n<\/code><\/pre>\n
Do we actually have migrated rows after the commit?<\/p>\n
\nSQL> @?\/rdbms\/admin\/utlchain\n\nTable created.\n\nSQL> analyze table uat_copy_blockchain list chained rows;\n\nTable analyzed.\n\nSQL> select count(*) from chained_rows;\n\n  COUNT(*)\n----------\n      7298\n\nSQL> select count(distinct dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid)) blocks_with_rows\n  2  from uat_copy_blockchain;\n\nBLOCKS_WITH_ROWS\n----------------\n\t    1084\n<\/code><\/pre>\n
So it makes sense to adjust the PCTFREE. In my case best would be something like 25-30%, because the blockchain date makes around 23% of the average row length:<\/p>\n
\nSQL> select sum(avg_col_len) from user_tab_cols where table_name='UAT_COPY_BLOCKCHAIN';\n\nSUM(AVG_COL_LEN)\n----------------\n\t     401\n\nSQL> select sum(avg_col_len) from user_tab_cols where table_name='UAT_COPY_BLOCKCHAIN'\n  2  and column_name like 'ORABCTAB%';\n\nSUM(AVG_COL_LEN)\n----------------\n\t      92\n\nSQL> select (92\/401)*100 from dual;\n\n(92\/401)*100\n------------\n  22.9426434\n<\/code><\/pre>\n
I could reduce the commit-time by 5 secs by adjusting the PCTFREE to 30.<\/p>\n
But coming back to the commit-time issue:<\/p>\n
This can easily be tested by just chekcing how much the commit-time increases when more data is loaded per transaction. Here the test done on 21c on the Oracle Cloud:<\/p>\n
\nSQL> create blockchain table test_block_chain (a number, b varchar2(100), c varchar2(100))\n  2  no drop until 0 days idle\n  3  no delete until 31 days after insert\n  4  hashing using \"sha2_512\" version v1;\n\nTable created.\n\nSQL> set timing on\nSQL> insert into test_block_chain select object_id, object_type, object_name from all_objects where rownum < 1000;\n\n999 rows created.\n\nSQL> commit;\n\nCommit complete.\n\nElapsed: 00:00:00.82\nSQL> insert into test_block_chain select object_id, object_type, object_name from all_objects where rownum < 2000;\n\n1999 rows created.\n\nSQL> commit;\n\nCommit complete.\n\nElapsed: 00:00:01.56\nSQL> insert into test_block_chain select object_id, object_type, object_name from all_objects where rownum < 4000;\n\n3999 rows created.\n\nSQL> commit;\n\nCommit complete.\n\nElapsed: 00:00:03.03\nSQL> insert into test_block_chain select object_id, object_type, object_name from all_objects where rownum < 8000;\n\n7999 rows created.\n\nSQL> commit;\n\nCommit complete.\n\nElapsed: 00:00:06.38\nSQL> insert into test_block_chain select object_id, object_type, object_name from all_objects where rownum < 16000;\n\n15999 rows created.\n\nSQL> commit;\n\nCommit complete.\n\nElapsed: 00:00:11.71\n<\/code><\/pre>\n
I.e. the more data inserted, the longer the commit-times. The times go up almost linearly with the amount of data inserted per transaction.<\/p>\n
Can we gain something here by doing things in parallel? A commit-statement cannot be parallelized, but you may of course split your e.g. 24000 rows insert into 2 x 12000 rows inserts and run them in parallel and commit them at the same time. I created 2 simple scripts for that:<\/p>\n
\noracle@cbl:\/home\/oracle\/ [DB0111 (CDB$ROOT)] cat load_bct.bash \n#!\/bin\/bash\n\nROWS_TO_LOAD=$1\n\nsqlplus -S cbleile\/${MY_PASSWD}@pdb1 <<EOF\ninsert into test_block_chain select object_id, object_type, object_name from all_objects where rownum <= $ROWS_TO_LOAD ;\n-- alter session set events '10046 trace name context forever, level 12';\nset timing on\ncommit;\n-- alter session set events '10046 trace name context off';\nexit\nEOF\n\nexit 0\n\noracle@cbl:\/home\/oracle\/ [DB0111 (CDB$ROOT)] cat load_bct_parallel.bash \n#!\/bin\/bash\n\nPARALLELISM=$1\nLOAD_ROWS=$2\n\nfor i in $(seq ${PARALLELISM})\ndo\n  .\/load_bct.bash $LOAD_ROWS &\ndone\nwait\n\nexit 0\n<\/code><\/pre>\n
Loading 4000 Rows in a single job:<\/p>\n
\noracle@cbl:\/home\/oracle\/ [DB0111 (CDB$ROOT)] .\/load_bct_parallel.bash 1 4000\n\n4000 rows created.\n\n\nCommit complete.\n\nElapsed: 00:00:03.56\n<\/code><\/pre>\n
Loading 4000 Rows in 2 jobs, which run in parallel and each loading 2000 rows:<\/p>\n
\noracle@cbl:\/home\/oracle\/ [DB0111 (CDB$ROOT)] .\/load_bct_parallel.bash 2 2000\n\n2000 rows created.\n\n\n2000 rows created.\n\n\nCommit complete.\n\nElapsed: 00:00:17.87\n\nCommit complete.\n\nElapsed: 00:00:18.10\n<\/code><\/pre>\n
That doesn’t scale at all. Enabling SQL-Trace for the 2 jobs in parallel showed this:<\/p>\n
\nSQL ID: catcycjs3ddry Plan Hash: 3098282860\n\nupdate sys.blockchain_table_chain$ set                    hashval_position =\n  :1, max_seq_number =:2\nwhere\n obj#=:3 and inst_id = :4 and chain_id = :5                  and epoch# = :6\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute   2000      8.41       8.58          0    1759772       2088        2000\nFetch        0      0.00       0.00          0          0          0           0\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal     2001      8.41       8.58          0    1759772       2088        2000\n\nMisses in library cache during parse: 0\nOptimizer mode: CHOOSE\nParsing user id: SYS   (recursive depth: 1)\nNumber of plan statistics captured: 1\n\nRows (1st) Rows (avg) Rows (max)  Row Source Operation\n---------- ---------- ----------  ---------------------------------------------------\n         0          0          0  UPDATE  BLOCKCHAIN_TABLE_CHAIN$ (cr=2 pr=0 pw=0 time=103 us starts=1)\n         1          1          1   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED BLOCKCHAIN_TABLE_CHAIN$ PARTITION: ROW LOCATION ROW LOCATION (cr=2 pr=0 pw=0 time=25 us starts=1 cost=1 size=1067 card=1)\n         1          1          1    INDEX RANGE SCAN BLOCKCHAIN_TABLE_CHAIN$_IDX (cr=1 pr=0 pw=0 time=9 us starts=1 cost=1 size=0 card=1)(object id 11132)\n\n\nElapsed times include waiting on following events:\n  Event waited on                             Times   Max. Wait  Total Waited\n  ----------------------------------------   Waited  ----------  ------------\n  buffer busy waits                             108        0.00          0.00\n  latch: cache buffers chains                     1        0.00          0.00\n********************************************************************************\n\nSQL ID: fh1yz4801af27 Plan Hash: 1612174689\n\nselect max_seq_number, hashval_position\nfrom\n sys.blockchain_table_chain$ where obj#=:1 and                     inst_id =\n  :2 and chain_id = :3 and epoch# = :4\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute   2000      0.55       0.55          0          0          0           0\nFetch     2000      7.39       7.52          0    1758556          0        2000\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal     4001      7.95       8.08          0    1758556          0        2000\n\nMisses in library cache during parse: 0\nOptimizer mode: CHOOSE\nParsing user id: SYS   (recursive depth: 1)\nNumber of plan statistics captured: 1\n\nRows (1st) Rows (avg) Rows (max)  Row Source Operation\n---------- ---------- ----------  ---------------------------------------------------\n         1          1          1  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED BLOCKCHAIN_TABLE_CHAIN$ PARTITION: ROW LOCATION ROW LOCATION (cr=2 pr=0 pw=0 time=49 us starts=1 cost=1 size=1067 card=1)\n         1          1          1   INDEX RANGE SCAN BLOCKCHAIN_TABLE_CHAIN$_IDX (cr=1 pr=0 pw=0 time=10 us starts=1 cost=1 size=0 card=1)(object id 11132)\n\n\nElapsed times include waiting on following events:\n  Event waited on                             Times   Max. Wait  Total Waited\n  ----------------------------------------   Waited  ----------  ------------\n  buffer busy waits                              80        0.00          0.00\n  latch: cache buffers chains                     1        0.00          0.00\n<\/code><\/pre>\n
The single job for above 2 statements contained the following:<\/p>\n
\nSQL ID: catcycjs3ddry Plan Hash: 3098282860\n\nupdate sys.blockchain_table_chain$ set                    hashval_position =\n  :1, max_seq_number =:2\nwhere\n obj#=:3 and inst_id = :4 and chain_id = :5                  and epoch# = :6\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute   4000      1.76       1.85          0       8001       4140        4000\nFetch        0      0.00       0.00          0          0          0           0\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal     4001      1.76       1.85          0       8001       4140        4000\n\nMisses in library cache during parse: 0\nOptimizer mode: CHOOSE\nParsing user id: SYS   (recursive depth: 1)\nNumber of plan statistics captured: 1\n\nRows (1st) Rows (avg) Rows (max)  Row Source Operation\n---------- ---------- ----------  ---------------------------------------------------\n         0          0          0  UPDATE  BLOCKCHAIN_TABLE_CHAIN$ (cr=2 pr=0 pw=0 time=102 us starts=1)\n         1          1          1   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED BLOCKCHAIN_TABLE_CHAIN$ PARTITION: ROW LOCATION ROW LOCATION (cr=2 pr=0 pw=0 time=26 us starts=1 cost=1 size=1067 card=1)\n         1          1          1    INDEX RANGE SCAN BLOCKCHAIN_TABLE_CHAIN$_IDX (cr=1 pr=0 pw=0 time=12 us starts=1 cost=1 size=0 card=1)(object id 11132)\n\n********************************************************************************\n\nSQL ID: fh1yz4801af27 Plan Hash: 1612174689\n\nselect max_seq_number, hashval_position\nfrom\n sys.blockchain_table_chain$ where obj#=:1 and                     inst_id =\n  :2 and chain_id = :3 and epoch# = :4\n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute   4000      1.09       1.09          0          0          0           0\nFetch     4000      0.06       0.06          0       8000          0        4000\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal     8001      1.15       1.16          0       8000          0        4000\n\nMisses in library cache during parse: 0\nOptimizer mode: CHOOSE\nParsing user id: SYS   (recursive depth: 1)\nNumber of plan statistics captured: 1\n\nRows (1st) Rows (avg) Rows (max)  Row Source Operation\n---------- ---------- ----------  ---------------------------------------------------\n         1          1          1  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED BLOCKCHAIN_TABLE_CHAIN$ PARTITION: ROW LOCATION ROW LOCATION (cr=2 pr=0 pw=0 time=49 us starts=1 cost=1 size=1067 card=1)\n         1          1          1   INDEX RANGE SCAN BLOCKCHAIN_TABLE_CHAIN$_IDX (cr=1 pr=0 pw=0 time=10 us starts=1 cost=1 size=0 card=1)(object id 11132)\n<\/code><\/pre>\n
I.e. there’s a massive difference in logical IOs and I could see in the trace that the SQLs became slower with each execution.<\/p>\n
Summary:<\/b> Blockchain Tables are a great technology, but as with any other technology you should know its limitations. There is an overhead when committing and inserting into such tables in parallel sesssions does currently not scale when committing. If you test blockchain tables then I do recommend to review your PCT-FREE-setting of the blockchain table to avoid row migration.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle Blockchain Tables are available now with Oracle 19.10. (see Connor’s Blog on it), they are part of all editions and do not need any specific license. I.e. whenever we need to store data in a table, which should never be updated anymore and we have to ensure data cannot be tampererd, then blockchain tables […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[2224,2225,2264,492,96],"type_dbi":[],"class_list":["post-15820","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-blockchain","tag-blockchain-table","tag-blockchain-tables","tag-commit","tag-oracle"],"acf":[],"yoast_head":"\nOracle Blockchain Tables: COMMIT-Time - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Blockchain Tables: COMMIT-Time\" \/>\n<meta property=\"og:description\" content=\"Oracle Blockchain Tables are available now with Oracle 19.10. (see Connor’s Blog on it), they are part of all editions and do not need any specific license. I.e. whenever we need to store data in a table, which should never be updated anymore and we have to ensure data cannot be tampererd, then blockchain tables […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-03-01T14:54:20+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Oracle Blockchain Tables: COMMIT-Time\",\"datePublished\":\"2021-03-01T14:54:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\"},\"wordCount\":616,\"commentCount\":0,\"keywords\":[\"blockchain\",\"Blockchain Table\",\"blockchain tables\",\"Commit\",\"Oracle\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\",\"name\":\"Oracle Blockchain Tables: COMMIT-Time - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-03-01T14:54:20+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Blockchain Tables: COMMIT-Time\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\/\/www.dbi-services.com\",\"https:\/\/x.com\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle Blockchain Tables: COMMIT-Time - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Blockchain Tables: COMMIT-Time","og_description":"Oracle Blockchain Tables are available now with Oracle 19.10. (see Connor’s Blog on it), they are part of all editions and do not need any specific license. I.e. whenever we need to store data in a table, which should never be updated anymore and we have to ensure data cannot be tampererd, then blockchain tables […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/","og_site_name":"dbi Blog","article_published_time":"2021-03-01T14:54:20+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Oracle Blockchain Tables: COMMIT-Time","datePublished":"2021-03-01T14:54:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/"},"wordCount":616,"commentCount":0,"keywords":["blockchain","Blockchain Table","blockchain tables","Commit","Oracle"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/","name":"Oracle Blockchain Tables: COMMIT-Time - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-03-01T14:54:20+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-blockchain-tables-commit-time\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle Blockchain Tables: COMMIT-Time"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15820","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15820"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15820\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15820"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}