达梦V8大规模并行处理 MPP 主库和从库正常的情况下，不同场景的SQL执行情况

已于 2022-06-02 14:02:44 修改
阅读量1.5k 收藏 2
点赞数
分类专栏： 达梦数据库 文章标签： sql 哈希算法 数据库 database oracle
于 2022-06-01 11:58:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wty286313873/article/details/125079919
版权

结论

在这里插入图片描述

测试脚本


cat > b.sql <<EOF
drop table if exists t01;

drop table  if exists t01_hash;

create table t10 (id int);

create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);

insert into t10 values(1);

commit;

insert into t10_hash values (2001,'name2001');

commit;

create table t01 (id int);

create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);


insert into t01 values(2);

commit;

insert into t01_hash values (2001,'name2001');

commit;


begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
/

begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
/


select count(*) from t10;

select count(*) from t10_hash;

select count(*) from t01;

select count(*) from t01_hash;

EOF

cat > a.sql <<EOF
drop table if exists t01;

drop table  if exists t01_hash;

create table t01 (id int);

create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);


insert into t01 values(2);

commit;

insert into t01_hash values (2001,'name2001');

commit;

begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
/

begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
/




select count(*) from t01;

select count(*) from t01_hash;

EOF

primary，global模式

SQL> 
SQL> `a.sql
SQL> drop table if exists t01;
executed successfully
used time: 47.340(ms). Execute id is 193769.
SQL> drop table  if exists t01_hash;
executed successfully
used time: 51.277(ms). Execute id is 193770.
SQL> create table t01 (id int);
executed successfully
used time: 23.462(ms). Execute id is 193771.
SQL> create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
executed successfully
used time: 23.316(ms). Execute id is 193772.
SQL> insert into t01 values(2);
affect rows 1

used time: 3.495(ms). Execute id is 497143.
SQL> commit;
executed successfully
used time: 2.385(ms). Execute id is 497144.
SQL> insert into t01_hash values (2001,'name2001');
affect rows 1

used time: 0.443(ms). Execute id is 497146.
SQL> commit;
executed successfully
used time: 2.598(ms). Execute id is 497147.
SQL> begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
DMSQL executed successfully
used time: 00:00:01.838. Execute id is 502148.
SQL> begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
DMSQL executed successfully
used time: 00:00:01.742. Execute id is 507149.
SQL> select count(*) from t01;

LINEID     COUNT(*)            
---------- --------------------
1          5001

used time: 2.585(ms). Execute id is 507151.
SQL> select count(*) from t01_hash;

LINEID     COUNT(*)            
---------- --------------------
1          5001

used time: 2.423(ms). Execute id is 507153.
SQL>

primary，local模式

SQL> 
SQL> `b.sql
SQL> drop table if exists t01;
drop table if exists t01;
[-5027]:DDL operation is forbidden under mpp local mode.
used time: 0.279(ms). Execute id is 0.
SQL> drop table  if exists t01_hash;
drop table  if exists t01_hash;
[-5027]:DDL operation is forbidden under mpp local mode.
used time: 0.291(ms). Execute id is 0.
SQL> create table t10 (id int);
create table t10 (id int);
[-5027]:DDL operation is forbidden under mpp local mode.
used time: 0.337(ms). Execute id is 0.
SQL> create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
[-5027]:DDL operation is forbidden under mpp local mode.
used time: 0.201(ms). Execute id is 0.
SQL> insert into t10 values(1);
insert into t10 values(1);
[-2106]:Error in line: 1
Invalid table or view name [T10].
used time: 0.182(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.253(ms). Execute id is 132937.
SQL> insert into t10_hash values (2001,'name2001');
insert into t10_hash values (2001,'name2001');
[-2106]:Error in line: 1
Invalid table or view name [T10_HASH].
used time: 0.153(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.339(ms). Execute id is 132938.
SQL> create table t01 (id int);
create table t01 (id int);
[-2124]:Error in line: 1
Object [T01] already exists.
used time: 0.113(ms). Execute id is 0.
SQL> create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
[-2124]:Error in line: 1
Object [T01_HASH] already exists.
used time: 0.350(ms). Execute id is 0.
SQL> insert into t01 values(2);
affect rows 1

used time: 0.536(ms). Execute id is 132939.
SQL> commit;
executed successfully
used time: 1.730(ms). Execute id is 132940.
SQL> insert into t01_hash values (2001,'name2001');
affect rows 1

used time: 0.557(ms). Execute id is 132941.
SQL> commit;
executed successfully
used time: 1.716(ms). Execute id is 132942.
SQL> begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
DMSQL executed successfully
used time: 48.104(ms). Execute id is 132943.
SQL> begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
[-9515]:Data is not belong to this site on mpp local login.
used time: 0.498(ms). Execute id is 0.
SQL> select count(*) from t10;
select count(*) from t10;
[-2106]:Error in line: 1
Invalid table or view name [T10].
used time: 0.242(ms). Execute id is 0.
SQL> select count(*) from t10_hash;
select count(*) from t10_hash;
[-2106]:Error in line: 1
Invalid table or view name [T10_HASH].
used time: 0.154(ms). Execute id is 0.
SQL> select count(*) from t01;

LINEID     COUNT(*)            
---------- --------------------
1          6268

used time: 0.337(ms). Execute id is 132945.
SQL> select count(*) from t01_hash;

LINEID     COUNT(*)            
---------- --------------------
1          1252

used time: 0.248(ms). Execute id is 132946.
SQL>

standby，global模式

SQL> 
SQL> `b.sql
SQL> drop table if exists t01;
drop table if exists t01;
[-710]:Try to modify user database at standby mode.
used time: 0.866(ms). Execute id is 0.
SQL> drop table  if exists t01_hash;
drop table  if exists t01_hash;
[-710]:Try to modify user database at standby mode.
used time: 0.536(ms). Execute id is 0.
SQL> create table t10 (id int);
create table t10 (id int);
[-710]:Try to modify user database at standby mode.
used time: 0.300(ms). Execute id is 0.
SQL> create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
[-710]:Try to modify user database at standby mode.
used time: 0.467(ms). Execute id is 0.
SQL> insert into t10 values(1);
insert into t10 values(1);
[-2106]:Error in line: 1
Invalid table or view name [T10].
used time: 0.234(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.444(ms). Execute id is 81904.
SQL> insert into t10_hash values (2001,'name2001');
insert into t10_hash values (2001,'name2001');
[-2106]:Error in line: 1
Invalid table or view name [T10_HASH].
used time: 0.331(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 1.279(ms). Execute id is 81905.
SQL> create table t01 (id int);
create table t01 (id int);
[-710]:Try to modify user database at standby mode.
used time: 0.639(ms). Execute id is 0.
SQL> create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
[-710]:Try to modify user database at standby mode.
used time: 0.262(ms). Execute id is 0.
SQL> insert into t01 values(2);
insert into t01 values(2);
[-2018]:Error in line: 1
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.616(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.726(ms). Execute id is 81906.
SQL> insert into t01_hash values (2001,'name2001');
insert into t01_hash values (2001,'name2001');
[-2018]:Error in line: 1
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.441(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.435(ms). Execute id is 81907.
SQL> begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
[-2018]:Error in line: 3
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.466(ms). Execute id is 0.
SQL> begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
[-2018]:Error in line: 3
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.353(ms). Execute id is 0.
SQL> select count(*) from t10;
select count(*) from t10;
[-2106]:Error in line: 1
Invalid table or view name [T10].
used time: 0.405(ms). Execute id is 0.
SQL> select count(*) from t10_hash;
select count(*) from t10_hash;
[-2106]:Error in line: 1
Invalid table or view name [T10_HASH].
used time: 0.317(ms). Execute id is 0.
SQL> select count(*) from t01;

LINEID     COUNT(*)            
---------- --------------------
1          6268

used time: 1.063(ms). Execute id is 81908.
SQL> select count(*) from t01_hash;

LINEID     COUNT(*)            
---------- --------------------
1          1252

used time: 0.873(ms). Execute id is 81909.
SQL>

standby，local模式

SQL> 
SQL> `b.sql
SQL> drop table if exists t01;
drop table if exists t01;
[-710]:Try to modify user database at standby mode.
used time: 0.500(ms). Execute id is 0.
SQL> drop table  if exists t01_hash;
drop table  if exists t01_hash;
[-710]:Try to modify user database at standby mode.
used time: 0.255(ms). Execute id is 0.
SQL> create table t10 (id int);
create table t10 (id int);
[-710]:Try to modify user database at standby mode.
used time: 0.367(ms). Execute id is 0.
SQL> create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
create table t10_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
[-710]:Try to modify user database at standby mode.
used time: 0.280(ms). Execute id is 0.
SQL> insert into t10 values(1);
insert into t10 values(1);
[-2106]:Error in line: 1
Invalid table or view name [T10].
used time: 0.551(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.434(ms). Execute id is 83404.
SQL> insert into t10_hash values (2001,'name2001');
insert into t10_hash values (2001,'name2001');
[-2106]:Error in line: 1
Invalid table or view name [T10_HASH].
used time: 0.462(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.275(ms). Execute id is 83405.
SQL> create table t01 (id int);
create table t01 (id int);
[-710]:Try to modify user database at standby mode.
used time: 0.301(ms). Execute id is 0.
SQL> create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
[-710]:Try to modify user database at standby mode.
used time: 0.475(ms). Execute id is 0.
SQL> insert into t01 values(2);
insert into t01 values(2);
[-2018]:Error in line: 1
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.288(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.583(ms). Execute id is 83406.
SQL> insert into t01_hash values (2001,'name2001');
insert into t01_hash values (2001,'name2001');
[-2018]:Error in line: 1
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.316(ms). Execute id is 0.
SQL> commit;
executed successfully
used time: 0.302(ms). Execute id is 83407.
SQL> begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
[-2018]:Error in line: 3
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.350(ms). Execute id is 0.
SQL> begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
[-2018]:Error in line: 3
Try to insert/update/delete table table is not temporary or contains lob on standby mode.
used time: 0.393(ms). Execute id is 0.
SQL> select count(*) from t10;
select count(*) from t10;
[-2106]:Error in line: 1
Invalid table or view name [T10].
used time: 0.273(ms). Execute id is 0.
SQL> select count(*) from t10_hash;
select count(*) from t10_hash;
[-2106]:Error in line: 1
Invalid table or view name [T10_HASH].
used time: 0.224(ms). Execute id is 0.
SQL> select count(*) from t01;

LINEID     COUNT(*)            
---------- --------------------
1          6268

used time: 0.295(ms). Execute id is 83408.
SQL> select count(*) from t01_hash;

LINEID     COUNT(*)            
---------- --------------------
1          1252

used time: 0.256(ms). Execute id is 83409.
SQL>

达梦在线服务平台：https://eco.dameng.com

dmwydba
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
达梦数据库在linux上的主备简易搭建方式
whbravo的博客
02-28 800
1、主备/data都创建好（随机目录），然后chown -R dmdba:dinstall /data 2、主机/opt/dmdbms/bin下执行./dminit path=/data/ case_sensitive=n page_size=32 extent_size=32（初始化实例），然后/opt/dmdbms/bin下执行./dmserver /data/DAMENG/dm.ini提示s...
DM数据库-模拟实时主备故障
ycm_dn的博客
07-30 4416
一 测试主备服务器是否正常 1.1 验证主备库数据是否同步 **使用manager管理工具分别连接主备库 在主库上创建t_text_1表 ** create table t_text1 (id int,num int); 执行后，可以看到创建表成功，进行查询。 **连接备库 在备库上查询在主库中创建的t_text_1表，验证备库是否同步主库数据 ** select * from t_text_1; 提示查询成功 二 模拟故障 2.1 模拟确认监视器服务停止 **确认监视器停止 主库实例服务停止，无
【达梦】【主从同步】
最新发布
ilikewyb的博客
06-11 125
【代码】【达梦】【主从同步】
无效的表或视图
zhanghaisong_2015的博客
10-31 1787
无效的表或视图名
达梦读写分离集群服务名配置
qq_36436792的博客
07-26 2241
配置dm_svc.conf文件 [dmdba@DW_M ~]$ cat /etc/dm_svc.conf # 全局配置区 GRP1=(192.168.43.131:32141,192.168.43.132:32142) TIME_ZONE=(+480) #表示+8:00 时区 LOGIN_ENCRYPT=(0) DIRECT=(Y) # 服务配置区 [GRP1] TIME_ZONE=(+540) #表示+9:00 时区 #指定优先登录的服务器模式。0：优先连接 Primary 模式的库，Normal 模
达梦数据库常见问题汇总
算命de博客
05-14 8167
操作系统 银河麒麟 V10 数据库 DM8 企业版 VMware workstation 15 Pro 问题一 以图形界面 安装数据库软件时报错 现象描述 用图形界面形式安装数据库遇到报错，显示报错信息如下： [dmdba@localhostmnt]$ ./DMInstall.bin 解压安装程序.......... No protocol specified Exception in thread "main"org.eclipse.swt.SWTError: No more h...
达梦MPP 环境搭建
qq_17082877的博客
04-05 833
1 DM MPP 环境搭建 1.1 系统规划 配置一个两节点 MPP。如果有条件两个节点都配置两块网卡，一块接入内部网络交换模块，一块接入到外部交换机。两节点实例名分别为 EP01 和 EP02，相关的 IP、端口等规划见下表。 实例名 MAL_INST_HOST MAL_INST_PORT MAL_HOST MAL 端口 MPP_SEQNO EP01
达梦数据库的几种模式及状态说明
不定期分享数据库相关内容。
03-22 4523
数据库在Mount状态下，不能修改数据，不能访问表、视图等数据库对象，但可以执行修改归档配置、控制文件和修改数据库模式等操作，也可以执行一些不修改数据库内容的操作，比如查询动态视图或者一些只读的系统过程。数据库在Suspend状态下，可以访问数据库对象，甚至可以修改数据，但限制Redo日志刷盘， 一旦执行COMMIT等触发Redo日志刷盘的操作时，当前操作将被挂起。此参数可以动态修改。正常生成本地归档，但不发送实时归档（Realtime）、即时归档（Timely）和异步归档（Async）。
达梦数据库报错错误代码汇总
热门推荐
牛排煎黑椒的博客
09-21 3万+
达梦数据库报错代码查询汇总，错误代码
DM8备份恢复的简单使用
justforid的博客
03-18 598
DM8备份恢复的简单使用
达梦分区表的使用
不定期分享数据库相关内容。
05-23 1831
在大型的企业应用或企业级的数据库应用中，要处理的数据量通常达到TB级，对于这样的大型表执行全表扫描或者DML操作时，效率是非常低的。为了提高数据库在大数据量读写操作和查询时的效率，达梦数据库提供了对表和索引进行分区的技术，把表和索引等数据库对象中的数据分割成小的单位，分别存放在一个个单独的段中，用户对表的访问转化为对较小段的访问，以改善大型应用系统的性能。达梦数据库分区表主要包括范围分区、哈希分区和列表分区三种方式， 企业可以使用合适的分区方法，如日期（范围）、区域（列表），对大量数据进行分区。
达梦数据库状态和模式
cn_lyt的博客
11-17 5540
1. 达梦数据库状态 达梦8数据库包含OPEN、MOUNT、SUSPEND三大状态，默认启动为OPEN状态。 1.1 配置状态（MOUNT） 不允许访问数据库对象，只能进行控制文件维护、归档配置、数据库模式修改等操作 1.2 打开状态（OPEN） 不能进行控制文件维护、归档配置等操作，可以访问数据库对象，对外提供正常的数据库服务 1.3 挂起状态（SUSPEND） 与OPEN唯一的区别就是限制磁盘写入功能，...
达梦数据库使用查询语句报错-2106: 第1 行附近出现错误:无效的表或视图名[SYS_USER解决方案
guziqian123456的博客
04-28 4585
国产数据库，达梦，查询语句
DM8:达梦数据库系统中的错误码信息-dm8.1-1-88-20.06.24-123627-ENT
qq_37358909的博客
01-28 1万+
此达梦数据库版本为：dm8-1-88-20.06.24-123627-ENT SELECT * FROM SYS."V$ERR_INFO"; 错误码： 100 空结果集 101 字符串截断 102 在集函数中计算NULL值 103 无效的表名 104 删除0行记录 105 插入0行记录 106 更新0行记录 107 跨语句游标操作 108 回收权限时无相应权限 109 试图转换空字符串 110 编译没有结束 111 结果集数据获取完成 112 不支持UTF8 113 结果集缓存满 114 刻度截断 .
无效的表和视图名【V$DSC_EP_INFO】
xiaoke_2013的博客
02-16 912
无效的表和视图名【V$DSC_EP_INFO】
达梦数据守护读写分离搭建
zch_zch123的博客
07-15 1110
达梦数据库读写分离集群配置
达梦数据库-主备集群
fangyu_7362的博客
06-07 3128
主库修改数据产生的REDO日志, 通过实时归档机制, 在写入本地REDO之前发送给备库, 备库接收到REDO后重新应用REDO日志来保持与主库数据同步 当主库故障时, 备库再将所有REOD日志应用完成后, 就切换为主库对外提供服务 通过即时归档或实时归档所组成的主备系统 读写分离集群通过配置"一致性模式"保证主库和备库数据一致性 通过DM数据库的各种接口, 实现将读操作自动分流到备库 一致性模式: 该模式下, 当发送SELECT语
【达梦数据库DM8】DM8基本操作及DCA考试感悟分享
勤奋好学，天天向上
10-25 1523
本文主要介绍了本人关于DM8-DCA的一些学习感悟分享
达梦数据库分区索引知识汇总
qq_24343729的博客
11-27 1514
达梦数据库分区索引知识汇总
假如你是数据库市场专家，MPP数据库的市场占有率和hadoop相比，情况怎么样？
05-19
MPP数据库和Hadoop是两种不同类型的技术，但它们都用于大规模数据处理和分析。MPP数据库是一种关系型数据库技术，通常用于在线事务处理（OLTP）和在线分析处理（OLAP）。而Hadoop则是一种分布式计算框架，用于大数据存储和处理。 在市场占有率方面，MPP数据库在企业级数据处理和分析领域拥有较高的市场份额，而Hadoop则在大数据处理领域占据了主导地位。根据最新的市场调查数据显示，MPP数据库的市场占有率约为20％至30％，而Hadoop的市场占有率则超过50％。这表明，虽然MPP数据库在企业级数据处理和分析领域有较高的市场份额，但在大数据处理领域，Hadoop仍然是主流技术。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值