征信报送系统数据库部分调优指南

此文档用于征信报送系统实施过程中数据库部分调优的

1.前言

东华征信综合报送系统基于二代征信系统数据采集规范开发，系统按照统一管理，分散

处理的原则，提供数据采集、校验处理、汇总处理、总分校验、报送文件生成、报表生成、

反馈管理等一整套自动化功能。帮助金融机构降低人工干预程度，提高工作效率及质量。另

外，针对部分金融机构对于报送自动化需求不高的情况，我们也有单独的数据校验模块可供

选择，节约成本的同时保证报送数据的准确性。

报送系统具有大数据量、时效性强的特点，对后台数据库系统要求较高，属重资源用户。

因此，在实施、维护过程中，不可避免对数据库造成巨大压力，需要合理调优资源，以支撑

整个系统的正常运转。

本文以东华二代报送系统实施过程中相关经验总结而成，针对实施现场中常见问题展开

论述，作为实施人员数据库部分的简单指南。

2.数据库调优基本原理及内容

2.1 数据库调优的目标

一般来说，数据库调优的目的就是要让数据库运行得更快，响应的时间更快，吞吐量

更大。但是随着用户量的不断增加，以及应用程序复杂度的提升，数据库调优有了不同的目

标，因为用户在不同时间段访问服务器遇到的瓶颈不同，比如双十一促销的时候会带来大规

模的并发访问；还有用户在进行不同业务操作的时候，数据库的事务处理和 SQL 查询都会

有所不同。因此我们还需要更加精细的定位，去确定调优的目标。

通过用户的反馈去确定调优的目标：用户是服务对象，因此他们的反馈是最直接的。
通过日志分析：我们可以通过查看数据库日志和操作系统日志等方式找出异常情况，通

过它们来定位遇到的问题。

3.通过服务器资源使用监控：通过监控服务器的 CPU、内存、I/O 等使用情况，可以实时了

解服务器的性能使用，与历史情况进行对比。

数据库内部状况监控：在数据库的监控中，活动会话监控是一个重要的指标。通过它，

你可以清楚地了解数据库当前是否处于非常繁忙的状态，是否存在 SQL 堆积等。

2.2 数据调优的维度：

2.2.1选择适合的 DBMS

不同的DBMS有不同的特点，DBMS 的选择关系到了后面的整个设计过程，所以第一步

就是要选择适合的 DBMS。

2.2.2优化表设计

①表结构要尽量遵循第三范式的原则。这样可以让数据结构更加清晰规范，减少冗余字段，
同时也减少了在更新，插入和删除数据时等异常情况的发生。
②如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优化。
反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率。
③表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。一般来说，如果
字段可以采用数值类型就不要采用字符类型；字符长度要尽可能设计得短一些。

2.2.3. 优化逻辑查询

SQL 查询优化，可以分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变
SQL 语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查
询进行重写。
SQL 的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除
和嵌套连接消除等。
EXISTS 子查询和 IN 子查询的时候，会根据小表驱动大表的原则选择适合的子查询。
在 WHERE 子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。
eg：

SELECT ... FROM ... WHERE SUBSTRING(comment_text,1.3) = 'abc'
改写为
SELECT ... FROM ... WHERE comment_text LIKE 'abc%'

2.2.4优化物理查询

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过
计算代价模型对各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计
划。这个部分的核心是高效地建立索引，并通过这些索引来做各种优化。
索引创建需要注意的情况：
1.如果数据重复度高，就不需要创建索引。通常在重复度超过 10% 的情况下，可以不创建
这个字段的索引。比如性别这个字段（取值为男和女）。
2.要注意索引列的位置对索引使用的影响。比如我们在 WHERE 子句中对索引字段进行了表
达式的计算，会造成这个字段的索引失效。
3.要注意联合索引对索引使用的影响。我们在创建联合索引的时候会对多个字段创建索引，
这时索引的顺序就很重要了。比如我们对字段 x, y, z 创建了索引，那么顺序是 (x,y,z) 还是
(z,y,x)，在执行的时候就会存在差别。
4.要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，
索引多也就意味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时
候增加了筛选出索引的计算时间，影响评估的效率。
5.在物理查询优化阶段会根据数据表的索引情况和数据情况确定访问路径，这就决定了执行
SQL 时所需要消耗的资源，并决定这些查询所采用的路径：
①单表查询：对于单表扫描来说，我们可以全表扫描所有的数据，也可以局部扫描。
②两张表的连接：常用的连接方式包括了嵌套循环连接、HASH 连接和合并连接。
③多张表的连接：多张数据表进行连接的时候，顺序很重要，因为不同的连接路径查询的效
率不同，搜索空间也会不同。巨大的搜索空间会占用很多的资源，因此我们需要通过调整连
接顺序，将搜索空间调整在一个可接收的范围内。

2.2.5使用 Redis 或 Memcached 作为缓存

因为数据都是存放到数据库中，我们需要从数据库层中取出数据放到内存中进行业务逻
辑的操作，当用户量增大的时候，如果频繁地进行数据查询，会消耗数据库的很多资源。如
果我们将常用的数据直接放到内存中，就会大幅提升查询的效率。
键值存储数据库Redis 和 Memcached，它们都可以将数据存放到内存中。
两者相比Redis 支持持久化，可以让我们的数据保存在硬盘上，不过这样一来性能消耗
也会比较大。而 Memcached 仅仅是内存存储，不支持持久化。并且Redis支持的数据类型
比 Memcached 要多。

2.2.6库级优化

库级优化是站在数据库的维度上进行的优化策略，比如控制一个库中的数据表数量。或
是采用主从架构来优化读写策略。
如果读和写的业务量都很大，并且它们都在同一个数据库服务器中进行操作，那么数据
库的性能就会出现瓶颈，这时为了提升系统的性能，优化用户体验，我们可以采用读写分离
的方式降低主数据库的负载，比如用主数据库（master）完成写操作，用从数据库（slave）
完成读操作。

分库：

我们还可以对数据库分库分表。当数据量级达到亿级以上时，有时候我们需要把一个数

据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

垂直切分和水平切分：

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同

的服务器上，它的核心理念是专库专用。

垂直分表：将一个表按照字段分成多表，每个表存储其中一部分字段。

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的

服务器上。

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

采用垂直分表的形式，就是将一张数据表分拆成多张表，采用水平拆分的方式，就是将

单张数据量大的表按照某个属性维度分成不同的小表。分拆在提升数据库性能的同时，也会

增加维护和使用成本。

如果数据库中的数据表过多，可以采用垂直分库的方式，将关联的数据表部署在一个数

据库上。如果数据表中的列过多，可以采用垂直分表的方式，将数据表分拆成多张，把经常

一起使用的列放到同一张表里。

如果数据表中的数据达到了亿级以上，可以考虑水平分表，将大的数据表分拆成不同的

子表，每张表保持相同的表结构。比如你可以按照年份来划分，把不同年份的数据放到不同

的数据表中。 2017 年、 2018 年和 2019 年的数据就可以分别放到三张数据表中。

2.3 征信报送系统实施过程中数据库调优

在报送系统实施的过程中，系统的架构、产品的方案基本确定，数据库系统一般也由甲

方指定，实施工程师所做的主要工作就是在现有的环境上完成产品的搭建、二次开发、测试、

环境迁移、正式上线等过程。在此过程中，对数据库调优而言，主要包括以下几个方面：

1.测试数据库环境搭建（现场未提供数据库）及联通测试

2.产品脚本导入

3.ETL部分二次开发

4.产品功能测试

5.产品压力测试

6.数据库及产品参数适配

7.形成部署文档（含数据库配置要求）

8.正式生产环境迁移

9.正式生产环境测试

10.正式生产环境数据库适配

11 .正式上线

因此，本文按如下内容展开

1.数据库安装

2.数据库基本设置

3.数据库基本观测手段及举例

4.实施过程中基本调优方法

5.报送系统应用调优方法

6 .实施过程中常见故障及排除

3.数据库的安装

现场数据库多由甲方提供，基本不涉及我方安装。但部分现场测试环境，Mysql由我方
驻场工程师安装。Oracle环境大部分由甲方提供，不涉及安装部分。本章仅给出测试环境
mysql 5.7版本的安装方法。

关闭selinux 和 firewald
永久关闭selinux: vi /etc/sysconfig/selinux ----->SELINUX=disabled
临时关闭selinux: setenforce=0 , 查看 getenforce
2.添加mysql用户组和用户
groupadd mysql
useradd -r -g mysql mysql
3.解压压缩文件并创建符号链接
mkdir -p /xxx_app
cd /xxx_app
tar -zxvf mysql-5.7.23-linux-glibc2.12-i686.tar.gz
ln -s mysql-5.7.23-linux-glibc2.12-i686 mysql
cd /xxx_app/mysql
mkdir -p /xxx_app/mysql5723/data
mkdir -p /xxx_app/mysql5723/log
touch my.cnf
cd log
touch mysql-error.log
chown -R mysql:mysql /xxx_app
chgrp -R mysql /xxx_app

vi /xxx_app/mysql5723/my.cnf

[client]
port = 3306
socket = /xxx_app/mysql5723/mysql.sock
[mysql]
default-character-set=utf8mb
socket = /xxx_app/mysql5723/mysql.sock
[mysqld]
port = 3306
socket = /xxx_app/mysql5723/mysql.sock
basedir = /xxx_app/mysql5723/
datadir = /xxx_app/mysql5723/data
pid-file = /xxx_app/mysql5723/mysql.pid
user=mysql
bind-address = 0.0.0.
autocommit=

server-id = 1
skip-name-resolve

skip-grant-tables

max_connections = 10000
max_connect_errors = 600
max_allowed_packet = 100M
wait_timeout = 3600 #
interactive_timeout = 3600 #
read_buffer_size = 16M
read_rnd_buffer_size = 8M
join_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 128M
tmpdir = /tmp
default-time-zone='+8:00'
log_timestamps = SYSTEM
character_set_server=utf8mb
collation-server=utf8mb4_bin
character-set-client-handshake=FALSE
sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"

bulk_insert_buffer_size=100M
log_bin = mysql-bin
binlog_format = row
log_error = /xxx_app/mysql5723/log/mysql-error.log
slow_query_log = 1
slow_query_log_file = /xxx_app/mysql5723/log/slow.log
long_query_time = 300
log_queries_not_using_indexes = 1
lower_case_table_names = 1
expire_logs_days = 1

default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /xxx_app/mysql5723/data
innodb_data_file_path =
ibdata1:100M;ibdata2:100M;ibdata3:100M;ibdata4:100M:autoextend:max:10G
注意只能成 :max:10G 不能写成 :max:10GB
innodb_temp_data_file_path=ibtmp1:100M;ibtmp2:100M:autoextend:max:10G
innodb_page_size = 16384
extend to 32G
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 3
innodb_log_file_size = 4G

innodb_log_buffer_size = 128M
innodb_log_group_home_dir = /xxx_app/mysql5723/data/
innodb_undo_directory = /xxx_app/mysql5723/data/
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_flush_method=O_DIRECT

innodb_write_io_threads = 8
innodb_purge_threads = 5
innodb_page_cleaners = 5
innodb_io_capacity = 2000

relay_log = /xxx_app/mysql5723/data/relay.log
relay_log_recovery = 1
transaction_isolation=READ-COMMITTED
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_slave_updates = 1
sync_binlog = 1
gtid-mode = ON
enforce-gtid-consistency = ON
skip-slave-start = 1
sync_binlog=
innodb_flush_log_at_trx_commit=
plugin_load =
"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 500

4.进入support-files目录
cp mysql.server /etc/init.d
mv mysql.server mysqld_app
chkconfig --add mysqld_app
chkconfig mysqld_app on

vi mysqld_app
把下面的两个变量设为项目的路径

basedir = /xxx_app/mysql5723/

datadir = /xxx_app/mysql5723/data

5.初始化mysql
/xxx_app/mysql5723/bin/mysqld --defaults-file=/xxx_app/mysql5723/my.cnf --user=mysql
--initialize 初始化
tail /log/mysql-error.log 找到临时密码 xxxxxpassword

启动服务
service mysqld_app start
对新版本7 systemctl start mysqld_app
systemctl stop mysqld_app
7.更改临时密码
ps -ef | grep mysqld 可以看到服务正常启动
使用上面输出的sock文件
mysql -hlocalhost -uroot -p"xxxxxpassword" -P3306 -S /xxx_app/mysql5723/mysql.sock

alter user 'root'@'localhost' identified with mysql_native_password by 'test123';
flush privileges;
create user 'root'@'%' identified with mysql_native_password by 'test123';
grant all on . to 'root'@'%';
flush privileges;

mysql即可正常访问。

4.数据库的基本设置

4.1Oracle 数据库基本设置

4.1.1可用性检查

4.1.1.1 服务器进程检查

Linux系统下的Oracle是多进程结构，包括smon,pmon,checkpoint,arch等系统组件。

进程检查：
[oracle@redhat706 ~]$ ps -ef | grep ora_|grep -v grep
oracle 33132 1 0 16:28? 00:00:00 ora_w000_ORCL
oracle 85832 1 0 Aug29? 00:00:47 ora_pmon_ORCL
oracle 85840 1 0 Aug29? 00:02:32 ora_vktm_ORCL
oracle 85845 1 0 Aug29? 00:00:11 ora_gen0_ORCL
oracle 85847 1 0 Aug29? 00:00:25 ora_diag_ORCL
oracle 85849 1 0 Aug29? 00:00:11 ora_dbrm_ORCL
oracle 85851 1 0 Aug29? 00:00:14 ora_psp0_ORCL
oracle 85853 1 0 Aug29? 00:05:52 ora_dia0_ORCL
oracle 85855 1 0 Aug29? 00:00:12 ora_mman_ORCL
oracle 85857 1 0 Aug29? 00:00:29 ora_dbw0_ORCL
oracle 85859 1 0 Aug29? 00:00:20 ora_lgwr_ORCL
oracle 85861 1 0 Aug29? 00:02:21 ora_ckpt_ORCL
oracle 85863 1 0 Aug29? 00:00:31 ora_smon_ORCL
oracle 85865 1 0 Aug29? 00:00:06 ora_reco_ORCL
oracle 85867 1 0 Aug29? 00:07:07 ora_mmon_ORCL
oracle 85869 1 0 Aug29? 00:04:03 ora_mmnl_ORCL
oracle 85871 1 0 Aug29? 00:00:08 ora_d000_ORCL
oracle 85873 1 0 Aug29? 00:00:08 ora_s000_ORCL
oracle 85975 1 0 Aug29? 00:00:51 ora_cjq0_ORCL
oracle 86864 1 0 Aug29? 00:00:14 ora_smco_ORCL

ps -ef | grep smon
kill -9 pid of pmon

4.1.1.2 数据库状态检查

数据库状态检查：

[oracle@redhat706 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 1 16:37:23 2022

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from gv$instance;

STATUS

OPEN

如果是19C,还需要检查pdb状态

4.1.1.3 监听状态检查

监听状态检查：

[oracle@redhat706 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-SEP-2022 16:32:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.122.84)(PORT=1521)))
STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 09-AUG-2022 10:44:
Uptime 23 days 5 hr. 48 min. 9 sec
Trace Level support
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/redhat706/listener/alert/log.xml
Listener Trace File
/u01/app/oracle/diag/tnslsnr/redhat706/listener/trace/ora_32565_140737352795968.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.122.84)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.com" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "orclXDB.com" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

4.1.1.4 tns 连接检查

tns检查：

[oracle@redhat706 admin]$ tnsping orcl.com

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-SEP-2022 16:36:

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.122.84)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl.com)))
OK (0 msec)

到此为止，数据库处于可用状态，可以接受连接。

sqlplus test/password@192.168.1.1:1521/orcl 测试连接成功！

4.1.2数据库常用配置项检查

4.1.2.1 SGA 检查

sga_target=系统内存80%80%
sga_max_target=系统内存80%80%

4.1.2.2 PGA 检查

pga_aggregate_target= sga_target=系统内存80%20%

4.1.2.3 Process 检查

一般保持在 800 以上

11G:

session=process*1.5+

4.1.2.3 Logfile 检查

select * from v$log;
生产上至少保证 3 组，每个logfile 2G;

4.1.2.4 cpu 检查

生产上至少保证16 cores

4.1.2.5 表空间检查

select a.tablespace_name as "Tablespace Name",
round(a.bytes_alloc / 1024 / 1024) "Allocated (MB)",
round(nvl(b.bytes_free, 0) / 1024 / 1024) "Free (MB)",
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) "Used (MB)",
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Free",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Used",
round(maxbytes/1024 / 1024) "Max. Bytes (MB)"
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select
h.tablespace_name as tablespace_name,

round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used +
h.bytes_free)) * 100) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(f.maxbytes) / 1048576) max
from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 2;

确保产品表空间利用率60%以下

undo表空间 40G以上
temp表空间 40G 以上

4.2Mysql 数据库基本设置

2 .1可用性检查

4.2.1.1 服务器进程检查

[root@redhat706 ~]# ps -ef | grep mysqld
root 1871 1 0 Aug31? 00:00:00 /bin/sh
/usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/d
ata --pid-file=/usr/local/mysql/tmp/mysqld.pid
mysql 2808 1871 0 Aug31? 00:16:18 /usr/local/mysql/bin/mysqld
--basedir=/usr/local/mysql --datadir=/usr
/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql
--log-error=/usr/local/mysql/log/mysqld.log
--pid-file=/usr/local/mysql/tmp/mysqld.pid --socket=/tmp/mysql.sock --port=
root 63703 63567 0 15:23 pts/2 00:00:00 grep --color=auto mysqld

4. 2. 1. 2 监听端口检查

[root@redhat706 ~]# netstat -ntlpa | grep mysqld
tcp6 0 0 :::3306 ::😗 LISTEN
2808/mysqld
tcp6 0 0 127.0.0.1:3306 127.0.0.1:25761 ESTABLISHED
2808/mysqld

4. 2. 1. 3 服务器实例检查

mysql -hlocalhost -uroot -prootrootroot --socket=/tmp/mysql.sock --port=

使用tcp连接，模拟应用帐号连接

[root@redhat706 ~]# mysql -h127.0.0.1 -uroot -prootrootroot --port=3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.27-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4. 2. 1. 4 服务器运行日志检查

mysql> show variables like '%general_log_file%';
+------------------+-------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------+
| general_log_file | /usr/local/mysql/data/redhat706.log |
+------------------+-------------------------------------+

如果服务器启动不正常，检查运行日志，查找error

[root@redhat706 ~]# cat /usr/local/mysql/data/redhat706.log | tail -n 10 | more
2021 - 02 - 01T10:27:23.156790Z 11164 Connect rhzx2_w@10.10.122.83 on rhzx2 using
TCP/IP
2021 - 02 - 01T10:27:23.157857Z 11164 Query /* mysql-connector-java-5.1.21 ( Revision:
${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR
Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR
Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variab
le_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name =
'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_nam
e = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name =
'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time
_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name =
'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode'
OR
Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name
= 'init_connect'

2021 - 02 - 01T10:27:23.162118Z 11164 Query /* mysql-connector-java-5.1.21 ( Revision:
${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
2021 - 02 - 01T10:27:23.162602Z 11164 Query SHOW COLLATION
2021 - 02 - 01T10:27:23.177325Z 11164 Query SHOW CHARACTER SET
2021 - 02 - 01T10:27:23.178840Z 11164 Query SELECT @@session.autocommit
2021 - 02 - 01T10:27:23.179383Z 11164 Query SET NAMES utf8
2021 - 02 - 01T10:27:23.179802Z 11164 Query SET character_set_results = NULL
2021 - 02 - 01T10:27:23.180168Z 11164 Query SET autocommit=1
2021 - 02 - 01T10:27:23.196331Z 11164 Quit

4. 2. 1. 5 数据库表空间检查

select table_schema as "数据库",sum(table_rows) as "记录数
",sum(truncate(data_length/1024/1024, 2)) as "数据容量
(MB)",sum(truncate(index_length/1024/1024, 2)) as "索引容量(MB)"
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

4. 2. 1. 6 数据库连接数检查

show variables like '%connect%';

5.数据库的基本观测手段

5 .1Oracle 数据库基本观测手段

现场开发运维场景中，主要面临的情况就是锁表处理、SQL跑不出，下面主要介绍介绍一下

oracler提供的基本观测方法。

5 .1.1 线程类查询

查询数据库正在执行的sql

select a.program, b.spid, c.sql_text,c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;

查询数据库的锁

select /*+ rule */ lpad('--',decode(b.block,1,0,4))||s.username user_name,
b.type,o.owner||'.'||o.object_name object_name,
s.sid,s.serial#,decode(b.request,0,'blocked','waitting') status
from dba_objects o,v$session s,v$lock v,v$lock b
where v.id1=o.object_id and v.sid=s.sid
and v.sid=b.sid and (b.block=1 or b.request>0)
and v.type='TM' and v.sid=b.sid
order by b.id2,v.id1,user_name desc;

alter system kill session 'sid,serial#'; 杀掉阻塞进程即可

5 .1. 2 执行计划类查询

oracle的执行计划内容很多，一般包括路径选择，连接顺序选择许多方面。对现场而言，很
多SQL跑不出，很多是执行计划跑错。下面简单说明下如何查执行计划。

表连接选择
oracle 数据库一共有 nestloop join ,merge join,has join 三种表连接方式,我们在书写sql时，
要根据sql的特点综合运行这三种方式。

基本点：
nestloop 适用于返回极少数据集
merge join 适用于排序数据集连接
hash join 适用于大数据集

drop table test1;
drop table test2;

select * from dba_objects;

create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;

create index idx_test1_object_name on test1(object_name);
create index idx_test2_object_name on test2(object_name);

nest loop连接
explain plan for select /*+ use_nl(a b) */ *
from test1 a,test2 b
where a.object_name=b.object_name;