目前已知可以用命令设置表和索引的并行度
问题:如何查看这个并行度,因为管控需要,正常是不允许表或者索引有并行度的,oracle是设置完再dba_tables或者dba_indexes可以查看degree列,但是Ob这2个视图里的degree是不生效的
1 个赞
你的问题中没说明使用的租户类型，那我按照社区用户的mysql租户来回答你：
● mysql租户下表的并行度可以通过show create table或者通过内部视图来查
● mysql租户索引的并行度通过内部视图来查
举例：
MySQL [sql_collect]> create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.052 sec)
MySQL [sql_collect]> create index idx1 on t1(c1);
Query OK, 0 rows affected (0.443 sec)
MySQL [sql_collect]> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
KEY `idx1` (`c1`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)
MySQL [jingshun0804]> select d.database_name as database_name, t.database_id as database_id, t.table_name as table_name, t.table_id as table_id, t.dop as dop from oceanbase.__all_table_v2 t left JOIN oceanbase.__all_database d on t.database_id = d.database_id where d.database_name = 'jingshun0804' and t.table_name = 'ob_hist_sql_audit_sample';
+---------------+-------------+--------------------------+----------+-----+
| database_name | database_id | table_name | table_id | dop |
+---------------+-------------+--------------------------+----------+-----+
| jingshun0804 | 1206 | ob_hist_sql_audit_sample | 264815 | 1 |
+---------------+-------------+--------------------------+----------+-----+
MySQL [jingshun0804]> select d.database_name as database_name, t.database_id as database_id, t.table_name as table_name, t.table_id as table_id, sum(m.data_size) as total_data_size, t.dop as dop from oceanbase.__all_table_v2 t join oceanbase.__all_table_v2 i on t.table_id = i.data_table_id JOIN oceanbase.__all_database d on t.database_id = d.database_id join oceanbase.__all_tenant_meta_table m on m.table_id<<24>>24 = t.table_id where d.database_name = 'jingshun0804' and t.table_name = 'ob_hist_sql_audit_sample';
+---------------+-------------+--------------------------+----------+-----------------+-----+
| database_name | database_id | table_name | table_id | total_data_size | dop |
+---------------+-------------+--------------------------+----------+-----------------+-----+
| jingshun0804 | 1206 | ob_hist_sql_audit_sample | 264815 | 0 | 1 |
+---------------+-------------+--------------------------+----------+-----------------+-----+
MySQL [sql_collect]> alter table t1 parallel 4;
Query OK, 0 rows affected (0.021 sec)
MySQL [sql_collect]> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
KEY `idx1` (`c1`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARALLEL 4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
MySQL [oceanbase]> select d.database_name as database_name, t.database_id as database_id, t.table_name as table_name, t.table_id as table_id, REGEXP_REPLACE(i.table_name, '__idx_(\\d)*_', '') as index_name, i.dop as dop from oceanbase.__all_table_v2 t join oceanbase.__all_table_v2 i on t.table_id = i.data_table_id JOIN oceanbase.__all_database d on t.database_id = d.database_id where d.database_name = 'jingshun0804' and t.table_name = 't1';
+---------------+-------------+------------+----------+------------+-----+
| database_name | database_id | table_name | table_id | index_name | dop |
+---------------+-------------+------------+----------+------------+-----+
| jingshun0804 | 1206 | t1 | 264812 | idx1 | 2 |
+---------------+-------------+------------+----------+------------+-----+
2 个赞
我们计划用ORACLE企业版,但是目前还在协调安装中,只是研究文档发现这个需求
企业版oracle租户模式只是在设置并行度的语法上和mysql模式有所区别，查询并行度的话都可以通过sys租户下的内部视图来查，通过视图来查，查询语句都是一样的，可以参考上面第二条回复。
我们计划用MySQL版的，这个示例正好合适。
类似这种能不能放到官网文档里面,或者做个视图,否则不能遇到问题都社区交流,感觉很不方便
而且我在官网的视图介绍里面就没看到__all_table_v2这个系统表,你让我们使用者怎么学习呢