Oracle-崖山UPDATE和CR块一致读性能测试

最开始本来在做崖山RAC和Oracle RAC无事务情况下跨节点全表扫描性能对比测试节点1 select /* full(tab) */ count(*) from tab; 节点2 select /* full(tab) */ count(*) from tab;测试发现崖山YAC跨节点全表扫描第一次查询巨慢相比Oracle慢了20倍崖山一直在等gc cr requestOracle无此问题我把测试结果反馈给崖山内部人士回复说问题确实存在已经安排排期紧急优化中我在进行测试的时候没有做事务操作但是崖山一直在等gc cr request也就是说在等远端节点构造cr块都没进行事务操作但是一直在等构造cr块这里应该是崖山cache fusion机制还不完善因为出现了cr块等待所以我就想干脆做个Oracle-崖山UPDATE和CR块一致读性能测试(单机YAC cache fusion目前正在优化中等后续拿到优化版本再做测试)Oracle和崖山设置logfile 2GBOracle19c SQL select group#,bytes/1024/1024/1024 size_gb from v$log; GROUP# SIZE_GB ---------- ---------- 4 2 5 2 6 2 崖山23.5.1 SQL select id,name,block_size*block_count/1024/1024/1024 size_gb from v$logfile; ID NAME SIZE_GB ------------ ---------------------------------------------------------------- ----------- 4 /data/yashan/yasdb_data/db-1-1/dbfiles/redo5 2 5 /data/yashan/yasdb_data/db-1-1/dbfiles/redo6 2 6 /data/yashan/yasdb_data/db-1-1/dbfiles/redo7 2 3 rows fetched.崖山23.5.1虚拟机session1 SQL select count(*) from t; COUNT(*) --------------------- 44537344 1 row fetched. Elapsed: 00:00:00.493 SQL update t set owner where object_id44648; 22685696 rows affected. Elapsed: 00:00:32.359 session2 SQL select count(*) from t; COUNT(*) --------------------- 44537344 1 row fetched. Elapsed: 00:00:08.733 SQL select count(*) from t; COUNT(*) --------------------- 44537344 1 row fetched. Elapsed: 00:00:08.671Oracle19c虚拟机session1 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:00.79 SQL update t set owner where object_id44648; 22685696 rows updated. Elapsed: 00:01:50.32 session2 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:34.11 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:28.92 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:28.13崖山(虚拟机)UPDATE耗时32秒Oracle(虚拟机)UPDATE耗时1分50秒崖山(虚拟机)CR块一致读耗时8.6秒Oracle(虚拟机)CR块一致读耗时28秒崖山把Oracle暴捶了一顿后面我又在Oracle19c物理机上进行了测试session1 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:00.72 SQL update t set owner where object_id44648; 22685696 rows updated. Elapsed: 00:00:42.47 session2 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:17.63 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:17.23 SQL select count(*) from t; COUNT(*) ---------- 44537344 Elapsed: 00:00:16.95崖山(虚拟机)UPDATE耗时32秒Oracle(物理机)UPDATE耗时42秒崖山(虚拟机)CR块一致读耗时8.6秒Oracle(物理机)CR块一致读耗时17秒为什么崖山比Oracle快这么多? 这就需要对比一下Oracle和崖山耗费的UNDO崖山耗费了1148MB UNDOINST_ID SID UNDO_BLOCKS UNDO_SIZE_MB USERNAME PROGRAM STATUS ----------- -------- ------------ ------------ -------------------- -------------------------------------------------- --------- 1 35 147002 1148.45313 SCOTT /data/yashan/yasdb_home/23.5.1.100/bin/yasql INACTIVEOracle耗费了2002MB UNDOINST_ID SID UNDO_BLOCKS UNDO_RECORDS UNDO_SIZE_MB USERNAME PROGRAM STATUS ---------- ---------- ----------- ------------ ------------ ---------- --------------- -------- 1 492 256342 22685696 2002.671875 SCOTT sqlplus.exe INACTIVE同样的UPDATE语句Oracle比崖山多耗费了接近1倍的UNDO难怪崖山UPDATE和CR块一致读比Oracle快那么多总结崖山UPDATE性能比Oracle快3倍CR块一致读比Oracle块3倍原因在于崖山UNDO量产生比Oracle少接近1倍单机版崖山性能确实牛逼(国产DB最强)希望崖山早日修复YAC存在的性能问题