chenjin如何学习ob

一起唠嗑
#1

陈进如何学习ob，讨论！

2 个赞
#2

加油:muscle:

1 个赞
#3

坚持

1 个赞
#6

表组与负载均衡
create database lab4lb;
use lab4lb;
create table np_tb1(c1 int,c2 int,c3 varchar(20),c4 varchar(100));
create table np_tb2(c1 int,c2 int,c3 varchar(20),c4 varchar(100));
create table hp_tb1(c1 int,c2 int,c3 varchar(20),c4 varchar(100)) partition by hash(c1) partitions 2;
create table hp_tb2(c1 int,c2 int,c3 varchar(20),c4 varchar(100)) partition by hash(c1) partitions 4;

CREATE TABLEGROUP tbg_hp SHARDING=‘PARTITION’;
ALTER TABLEGROUP tbg_hp ADD TABLE hp_tb1,hp_tb2;

查看 np_tb1 表组分布
SELECT TABLE_NAME, TABLE_ID, PARTITION_NAME, TABLET_ID, OBJECT_ID,LS_ID, ZONE, SVR_IP FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME=‘lab4lb’ AND ROLE=‘LEADER’ and table_name=‘np_tb1’ ORDER BY SVR_IP;

SELECT LS_ID, SVR_IP, SVR_PORT, ZONE, ROLE FROM oceanbase.DBA_OB_LS_LOCATIONS ORDER BY LS_ID,ZONE;

关闭自动均衡
ALTER SYSTEM SET enable_rebalance=false;
ALTER SYSTEM SET enable_transfer=true;
show parameters like ‘%rebalan%’;
show parameters like ‘%enable_transfer%’;

500092为np_tb1的tablet_id，1005为另外一个zone的ls id，

ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500092, OBJECT_ID = 500092 TO LS 1005;

1 个赞
#7

坚持

1 个赞
#8

感谢！

#9

create database lab4spm;
use lab4spm;
create table tb1(c1 int,
c2 int,
c3 varchar(20),
c4 varchar(100));

1、
select sql_id,query_sql from oceanbase.gv$ob_sql_audit where query_sql like ‘%select * from tb1 where c2<10%’\G

SELECT LAST_ACTIVE_TIME,SQL_ID, STATEMENT, PLAN_HASH, AVG_EXE_USEC,EXECUTIONS, EVOLUTION, EVO_EXECUTIONS, EVO_CPU_TIME
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE SQL_ID=‘1B92DFBF2E7D7A402FA5AEC813DFFBD4’\G

select DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(‘lab4spm’,‘1B92DFBF2E7D7A402FA5AEC813DFFBD4’,13388268709115914355,‘NO’,‘YES’) from dual;

2、
set global optimizer_use_sql_plan_baselines=on;
set global optimizer_capture_sql_plan_baselines=on;

obclient -h192.168.36.76 -P2883 -uroot@obmysql#obcp_exam -p’OBexampas123%^&’ -c -A -D oceanbase

3、

建立新会话，执行
select * from tb1 where c2<10;

SELECT SQL_ID, QUERY_SQL, STATEMENT, PLAN_HASH, AVG_EXE_USEC, EXECUTIONS,
EVOLUTION, EVO_EXECUTIONS, EVO_CPU_TIME FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE SQL_ID=‘1B92DFBF2E7D7A402FA5AEC813DFFBD4’\G;

为表tb1添加一个索引
create index ix1 on tb1(c2);

再次执行SQL，并确认SPM已开启计划演进
select * from tb1 where c2<10;
SELECT SQL_ID, QUERY_SQL, STATEMENT, PLAN_HASH, AVG_EXE_USEC, EXECUTIONS,
EVOLUTION, EVO_EXECUTIONS, EVO_CPU_TIME FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE SQL_ID=‘1B92DFBF2E7D7A402FA5AEC813DFFBD4’\G;

使用dbms_spm系统包，提前结束演进，将新执行计划添加为基线计划
CALL DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE(‘lab4spm’, ‘sqlid’,‘plan_hash_value’);

1 个赞
#10

多实践！

2 个赞