统计信息实战

[AntTech_LUTVQZ]

1. 环境准备
   #新建资源规格
   obclient -uroot@sys -h127.0.0.1 -P2881
   create resource unit u1_ora max_cpu=1,min_cpu=1,max_memory=‘2G’,min_memory=‘2G’,max_iops=128,max_disk_size=‘10G’,max_session_num=100;
   #新建资源池
   create resource pool pool_ora unit=‘u1_ora’,unit_num=1,zone_list=(‘zone1’,‘zone2’,‘zone3’);
   #新建 oracle 租户
   CREATE TENANT IF NOT EXISTS ob_pay charset=‘utf8mb4’, replica_num=1,zone_list=(‘zone1’,‘zone2’,‘zone3’), resource_pool_list=(‘pool_ora’) SET ob_tcp_invited_nodes=’%’, ob_compatibility_mode=‘oracle’;

#新建 tpcc 用户，授予 dba 权限
obclient -usys@ob_pay -h127.0.0.1 -P2881

create user tpcc identified by obce_test;
grant dba to tpcc;

obclient -utpcc@ob_pay -h127.0.0.1 -P2881 -p

#新建测试表
create table chap41 (c1 int primary key, c2 int, c3 varchar(3));

insert into chap41 select level, mod(level,10) ,‘001’ from dual connect by level
<=10000;
commit;

insert into chap41 values (99999,999,‘999’);
commit;

explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= ‘999’ \G

SELECT * FROM oceanbase.gv$tenant where tenant_name=‘ob_pay’;

select table_id, table_name from __all_virtual_table where tenant_id=1001 and table_name= ‘CHAP41’;

select row_count,data_size from __all_virtual_meta_table where table_id=1100611139453777;

alter system major freeze;

#等待 1-2 分钟
SELECT * FROM __all_zone WHERE name=‘merge_status’; --确认 merge_status 为‘IDLE‘状态

select row_count,data_size from __all_virtual_meta_table where table_id=1100611139453777;

±----------±----------+
| row_count | data_size |
±----------±----------+
| 10001 | 11868 |
| 10001 | 11868 |
| 10001 | 11868 |
±----------±----------+
3 rows in set (0.008 sec)

explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= ‘999’ \G

结论 1 ：
sys 租户中存放统计信息的数据字典表在合并后开始记录统计信息， 比较前后两者的 EST. ROWS 字段值，每个查询都发生了变化，说明优化器使用新的统计信息进行执行计划的估算

create index c2_c3 on chap41(c2,c3);
explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= ‘999’ \G

#手动统计字段 c3 的统计信息， 不包含直方图（histogram），再次比较以下 SQL 执行计划的输出; 统计前先检查现有表的统计信息

登陆 oracle 租户的 sys 用户

obclient -h127.0.0.1 -P2883 -usys@ob_pay#obce_test

select NUM_ROWS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN from all_tab_statistics where
table_name=‘CHAP41’;
±---------±-------------±----------±------------+
| NUM_ROWS | EMPTY_BLOCKS | AVG_SPACE | AVG_ROW_LEN |
±---------±-------------±----------±------------+
| NULL | NULL | NULL | NULL |
±---------±-------------±----------±------------+
1 row in set (0.253 sec)

select LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,LAST_ANALYZED from all_tab_col_statistics where table_name=‘CHAP41’;

select ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE,scope from all_tab_histograms where table_name=‘CHAP41’;

结论2：用户 schema 中，目前没有统计信息

#手动安装DBMS包，

1. dbms 的文件在 root 目录 dbms_stats 里，请先将文件中所有 sql 文件移动至 /home/admin/oceanbase/admin 文件夹下
2. 直连 observer，使用 root@sys 登陆，执行升级程序

obclient -uroot@sys -h127.0.0.1 -P2881
use oceanbase;

alter system set enable_upgrade_mode =true;
set ob_compatibility_mode=‘oracle’;
set ob_query_timeout = 300000000;
DELIMITER /
CREATE OR REPLACE PACKAGE “__DBMS_UPGRADE” IS
PROCEDURE UPGRADE(package_name VARCHAR2);
PROCEDURE UPGRADE_ALL;
END;
/
CREATE OR REPLACE PACKAGE BODY “__DBMS_UPGRADE” IS
PROCEDURE UPGRADE(package_name VARCHAR2);
PRAGMA INTERFACE(c, UPGRADE_SINGLE);
PROCEDURE UPGRADE_ALL;
PRAGMA INTERFACE(c, UPGRADE_ALL);
END;
/

DELIMITER ;
CALL “__DBMS_UPGRADE”.UPGRADE(‘DBMS_STATS’);
set ob_compatibility_mode=‘mysql’;
alter system set enable_upgrade_mode = false;

使用之前的 tpcc 会话
call dbms_stats.gather_table_stats(‘tpcc’,‘chap41’, method_opt=>‘for columns c3 size auto’);

登陆 oracle 租户的 sys 用户
select NUM_ROWS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN from all_tab_statistics where table_name=‘CHAP41’;

select LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS,LAST_ANALYZED from all_tab_col_statistics where table_name=‘CHAP41’;

select ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE,scope from all_tab_histograms where table_name=‘CHAP41’;

检查数据字典中的统计信息 –使用上面步骤的 SQL 命令
explain extended select * from chap41 where c2=1 \G
explain extended select * from chap41 where c2=999 \G
explain extended select * from chap41 where c3= ‘999’ \G