1.实例优化

1.1 参数管理

例如，一个密集交易型数据库服务器配置如下：cpu8核、内存256G、磁盘1T

1.2 性能监控工具

1.2.1 AWR

1.初始化awr包

--初始化生成 dbms_workload_repository 包
sp_init_awr_sys(1);

2.设置间隔时间

--设置多长时间生成一次快照,单位为分钟
call dbms_workload_repository.awr_set_interval(10);

3.手动生成快照

Dbms_workload_repository.create_snapshot();
Dbms_workload_repository.create_snapshot();

4.生成awr报告

#查看快照的编号
Select * from Sys.wrm$_snapshot;
#把 snapshot 的 id 为 1，2 生成 awr 报告
sys.awr_report_html(1,2,'/home/dmdba','awr.html');

1.2.2 会话监控

sysobjects 可以用来模糊查询数据字典

v$event_name,v$session_event,v$system_event,V$sessions

• 锁查询

select o.name,l.* from v$lock l,sysobjects o where l.table_id=o.id and blocked=1；

• 事务等待

select * from v$trx_wait; --正在等待
select * from V$SESSION_WAIT_HISTORY;  --等待历史
select * from GV$SESSION_WAIT_HISTORY  --全局等待历史

• sql监控

当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 时，显示系统最近 1000 条执行时间超过预定值的SQL 语 句 。默认监控超过000毫秒的sql语句，可通过SP_SET_LONG_TIME 系统函数修改，通过 SF_GET_LONG_TIME 系统函数查看当前值。

相关视图： 保存长sql

SQL> select * from v$dynamic_tables where name like '%LONG%';

行号     NAME                    ID          SCHNAME SYNONYMS
---------- ----------------------- ----------- ------- --------
1          V$LONG_EXEC_SQLS        433         SYS     V$LES
2          V$SYSTEM_LONG_EXEC_SQLS 434         SYS     V$SLES

相关参数： 控制长sql保存的条目

SQL> select para_name,para_value,para_type from v$dm_ini where para_name like '%LONG%';

行号     PARA_NAME                 PARA_VALUE PARA_TYPE
---------- ------------------------- ---------- ---------
1          LONG_EXEC_SQLS_CNT        1000       SYS  --长sql保留条数，控制保存在V$LONG_EXEC_SQLS中的条数
2          SYSTEM_LONG_EXEC_SQLS_CNT 20         SYS  --长sql保留条数，控制保存在V$SYSTEM_LONG_EXEC_SQLS中的条数
3          CTAB_WITH_LONG_ROW        0          SYS

相关函数： SF_GET_LONG_TIME、SP_SET_LONG_TIME

SQL> select * from v$ifun where name like '%LONG%';

行号     NAME             ID          ARG_NUM     HAS_RT_FACT IS_READONLY IS_MPP_BRO IS_MPP_FORBIDEN IS_MOUNT_ONLY IS_INCLUDE_SQL EXTRA_ATTR CLASS$       COMMENT$ IS_LPQ_FORBIDDEN
---------- ---------------- ----------- ----------- ----------- ----------- ---------- --------------- ------------- -------------- ---------- ------------ -------- ----------------
1          SF_GET_LONG_TIME 774         0           Y           Y           N          N               N             N              NULL       系统函数 NULL     N
2          SP_SET_LONG_TIME 773         1           Y           N           N          N               N             N              NULL       系统函数 NULL     N

• 大内存的sql动态视图

V$LARGE_MEM_SQLS
V$SYSTEM_LARGE_MEM_SQLS

2.执行计划

2.1 条件查询谓词的选择率

SQL> select para_name,para_value,para_type,description from v$dm_ini where para_name like '%RATE%';
4          SEL_RATE_EQU         0.025000   SESSION   Equal rate in selection
5          SEL_RATE_SINGLE      0.050000   SESSION   Single rate in selection

2.2 如何查看执行计划

**方法1：**通过explain查看

SQL> explain select * from sysobjects;

1   #NSET2: [1, 2768, 396] 
2     #PRJT2: [1, 2768, 396]; exp_num(17), is_atom(FALSE) 
3       #CSCN2: [1, 2768, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

方式2： 通过manager管理工具查看

2.3 执行计划包含哪些信息

• 一个执行计划由若干个计划节点组成，每个计划节点中包含操作符（CSN2）和它的代价（[0,2768,396]）等信息

• 代价由一个三元组组成**[代价、记录行数、字节数]**

• 代价的单位是毫秒，记录行数表示该计划节点输出的行数，字节数表示该计划节点输出的字节数，解读一下上个查询中的第三个节点：操作符CSCN2即全表扫描，代价估值是0ms，扫描的记录行数是2768行，输出字节数是396个

2.4 表的路径访问

CSCN2： CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表

SSEK2：Secondary index seek（定位），二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID 等信息去扫描表

CSEK2：聚集索引扫描只需要扫描索引，不需要扫描表

SSCN：secondary index scan，索引全扫描，不需要扫描表

CSEK2 : cluster index seek

FAGR：fast aggregation

SAGR：sort aggregation，流分组聚集，用于分组列没有索引只能走全表扫描的分组聚集

HAGR: hash分组聚集，用于分组列没有索引，只能走全表扫描的分组聚集

SORT:

3.sql优化

1.sql日志

--开启sql日志
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
--关闭sql日志
SP_SET_PARA_VALUE(1,'SVR_LOG',0);

2.查看执行计划

explain查看预估执行计划

查看实际的执行计划：

set autotrace trace
set autotrace traceonly

3.dumpln 从缓冲区中 dump 实际的执行计划

SELECT sqlstr,cache_item from v$cachepln where sqlstr like '%t_testlock%';
alter session set events 'immediate trace name plndump level 140260322830448, dump_file
''/tmp/sqlplan.txt''';

4.ET函数，需要打开monitor_sql_exec参数

ET 是达梦数据库内置的SQL性能优化分析工具，它可以统计指定会话ID执行的SQL的所有操作符的执行时间，对于分析优化SQL提供比较直观的数据依据，对于达梦数据库SQL优化，ET的使用是必须要掌握的技能之一。

达梦数据库ET功能默认是关闭，需要设置ini参数，enable_monitor=1、monitor_time=1和monitor_sql_exec=1

--查看ET相关参数及其值
select para_name,para_value,para_type from v$dm_ini where para_name in ('ENABLE_MONITOR','MONITOR_TIME','MONITOR_SQL_EXEC')
--配置相关参数开启ET功能
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1); #开启监视
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1); #监视SQL执行
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1); #监视时间
--使用ET分析sql执行效率,语句的执行号为3105，可以通过ET(3105)的方式调用ET
SELECT D.* FROM DMTEST.EMPLOYEES E, DMTEST.DEPT_EMP D WHERE D.EMP_NO = E.EMP_NO LIMIT 10;
ET(3105);

5.dbms_sqltune，需要打开monitor_sql_exec参数

SQL> set long 50000
SQL> select dbms_sqltune.report_sql_monitor(sql_exec_id=>4030);

4.表连接

4.1 嵌套循环连接

nest loop原理：

两层嵌套循环结构，有驱动表和被驱动表之分

选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二张表中的行。驱动表的行数就是循环的次数，这个很大程度上影响执行效率。

需要注意的问题：

选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。

大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。

使用场景:

驱动表有很多的过滤条件

表连接条件能使用索引

结果集比较小

4.2 哈希连接

hash join的特点：

一般没索引或用不上索引时会使用该连接方式

选择小的表或row source做hash表

只使用等值连接中的情形

原理：

使用较小的row source作为hash table和bitmap.而第二个row source被hashed，根据bitmap与第一row source生成的hash table相匹配，bitmap查找的速度极快。

hash连接比较消耗内存，如果系统有很多这种连接时，需要调整以下3个参数:

HJ_BUF_GLOBAL_SIZE/HJ_BUF_SIZE/HJ_BLK_SIZE 

4.3 归并排序连接

merge sort特点：

无驱动表之分，随机读很少

两个表都需要按照连接列排序，需要消耗大量的cpu和额外的内存

应用场景：

通常情况下，merge sort join需要消耗大量的cpu和内存，效率都不会太高，如果存在相关索引可以消除sort，那么cbo可能会考虑该连接方式。