Oracle和MySql笔记（持续更新）

发表于 2020-02-18 更新于 2022-09-01 分类于开发工具

本文字数： 35k 阅读时长 ≈ 32 分钟

Oracle和MySql常用功能和指令笔记。

Oracle和MySql笔记

一. 常用SQL

1.1 Oracle

（1）表空间

-- 查看表和索引所占空间大小
select *
from user_segments 
where segment_type in ('TABLE' ,'INDEX')
order by bytes desc; 

-- 查询索引空间信息
select s.index_name, s.table_name, s.tablespace_name,
       s.initial_extent, s.next_extent
from user_indexes s
where s.index_name = 'PK_XX_XX';

select * from user_tables;
-- 查询表空间和用户
select * from dba_tables where owner='XX_XX';
select * from dba_tables where tablespace_name='XX_XX' order by owner;
-- 查询指定表空间信息
select file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files where tablespace_name='TB_XXX';
-- 查询剩余空间
select sum(bytes/1024/1024) sizeMB from dba_free_space z where z.tablespace_name='TB_XXX';
-- 扩容
alter database datafile '/home/oracle/database/oradata/XXXX.dbf' resize 500M;
-- 开启自动扩容
alter database datafile '/u01/app/oracle/12c/dbs/XX_XX.dbf' autoextend on;


SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM V$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

select h.tablespace_name tablespace_name,f.autoextensible,
               round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,
               round(sum(decode(f.autoextensible, 'YES',  f.maxbytes, 'NO', f.bytes)) / power(2, 30), 2) max_gb
          from gv$temp_space_header h, gv$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,f.autoextensible;
         
         
				 SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);


SELECT 
A.TABLESPACE_NAME, 
case when  A.MAXBYTES/power(1024,3)>1 THEN  ROUND(A.MAXBYTES/power(1024,3),4)||'GB'
ELSE ROUND(A.MAXBYTES/power(1024,2),4)||'MB' END "允许最大值",
case when  A.BYTES/power(1024,3)>1 THEN  ROUND(A.BYTES/power(1024,3),4)||'GB'
ELSE ROUND(A.BYTES/power(1024,2),4)||'MB' END "历史峰值", 
case when  B.BYTES_USED/power(1024,3)>1 THEN 
ROUND(B.BYTES_USED/power(1024,3),4)||'GB'
ELSE ROUND(B.BYTES_USED/power(1024,2),4)||'MB' END "已使用",   
round((B.BYTES_USED/A.BYTES)*100,2)||'%' "已使用/历史峰值",   
round((B.BYTES_USED/A.MAXBYTES)*100,2)||'%' "已使用/允许最大值" 
 FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES,SUM(F.MAXBYTES) MAXBYTES  FROM DBA_TEMP_FILES F   GROUP BY TABLESPACE_NAME) A,
 (SELECT P.TABLESPACE_NAME, SUM(P.BYTES_CACHED) BYTES_CACHED,SUM(P.BYTES_USED) BYTES_USED  FROM V$TEMP_EXTENT_POOL P  GROUP BY TABLESPACE_NAME  ) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

（2）表结构

-----------------------------------------表结构-----------------------------------------

select * from all_tab_comments where table_name = 'XXX';
select * from user_tab_columns where table_name = 'XX_XX_XX' AND COLUMN_NAME = 'XX_XX';

-- user_tab_cols 包含oracle创建的隐藏字段
SELECT column_name FROM user_tab_cols where table_name = upper('表名')

select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH 
from user_tab_columns 
where COLUMN_NAME = 'XX_XX'
ORDER BY DATA_LENGTH,TABLE_NAME,COLUMN_NAME,DATA_TYPE

-- 查看视图构造SQL
SELECT DBMS_METADATA.GET_DDL('VIEW','VIEW_NAME','USER') FROM DUAL;

-----------------------------------------索引-----------------------------------------

-- 查询表所创建索引
select * from user_indexes where table_name='XX_XX_XX';
-- 查询索引字段
select * from user_ind_columns where index_name=upper('pk_XX_XX');
-- 查询表约束，包括字段
SELECT * from user_cons_columns where table_name = 'XX_XX_XX';
-- 查询表约束
SELECT * FROM user_constraints WHERE table_name='XX_XX_XX' AND constraint_name = 'PK_XX_XX';
-- 查询视图状态
select status from user_objects where object_name = 'XX_XX';
-- 查询包含字段的表
select table_name, data_length from user_tab_columns where column_name = 'XX_XX';

-- 查看指定表名的索引结构
select uic.TABLE_NAME,uic.INDEX_NAME,uic.COLUMN_NAME,uic.COLUMN_POSITION 
from user_ind_columns uic
LEFT JOIN user_indexes ui
ON uic.INDEX_NAME = ui.INDEX_NAME
where ui.table_name IN('XX_XX_XX','XX_XX_XX')
ORDER BY uic.TABLE_NAME,uic.INDEX_NAME,uic.COLUMN_POSITION;

（3）性能问题排查

会话信息：

-- 查看活跃会话信息
SELECT * FROM V$SESSION WHERE USERNAME = 'XXX' AND STATUS = 'ACTIVE' ORDER BY OSUSER,SCHEMANAME;

select SID,EVENT,STATE,WAIT_TIME_MICRO,PREV_SQL_ID,OSUSER FROM gv$session where status = 'ACTIVE' AND OSUSER = 'xxxx';

select SID,EVENT,STATE,WAIT_TIME_MICRO,PREV_SQL_ID,USERNAME,OSUSER 
FROM gv$session where status = 'ACTIVE'
ORDER BY USERNAME,OSUSER,EVENT; 

SELECT SID,blocking_session FROM V$SESSION WHERE blocking_session is not NULL;

-- 查看阻塞SQL
select s.inst_id as inst,
       s.sid as blocked_sid,
       s.username as blocked_user,
       sa.sql_id as blocked_sql_id,
       trunc(s.p2/4294967296) as blocking_sid,
       b.username as blocking_user,
       b.sql_id as blocking_sql_id
from gv$session s
join gv$sqlarea sa
  on sa.hash_value = s.p1
join gv$session b
  on trunc(s.p2/4294967296)=b.sid
 and s.inst_id=b.inst_id
join gv$sqlarea sa2
  on b.sql_id=sa2.sql_id
where s.event is not null or s.event <> '';

等待事件：

-- 查询等待的事件
select inst_id,event,count(*) from gv$session_wait
where wait_class not like 'Idle'
group by inst_id, event order by 3 desc;
-- 查询等待事件在执行的SQL
SELECT b.inst_id,b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       sql_fulltext,
       b.machine 计算机名,
       b.EVENT,
       c.SQL_ID,
       c.CHILD_NUMBER
FROM gv$process a, gv$session b, gv$sql c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
and event like '%pin S wait on X%'
   and a.inst_id=1
   and b.inst_id=1
   and c.inst_id=1;

-- 查看指定等待事件信息
select * from DBA_HIST_ACTIVE_SESS_HISTORY where event LIKE '%mutex%';
select * from v$session_wait where event LIKE '%mutex%';

死锁：

-----------------------------------------------
-- 查看死锁是否存在
select username,lockwait,status,machine,program,sid from v$session where sid in
(select session_id from v$locked_object);

-- 查看死锁的语句
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

-- 查看trace文件日志路径
 select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
 
-- 查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

select sid,type,id1,id2 ,lmode,request,block from v$lock where type='TX';

执行计划：

--执行计划
EXPLAIN PLAN FOR SELECT ... FROM ....;
select * from table(dbms_xplan.display);

--根据SQL_ID查看真实执行计划
select * from table(dbms_xplan.display_cursor('7ac05552jb0v9',0));

SQL排查，包括硬解析，High Version Count，绑定变量等

-- 根据SQL_ID或SQL内容查看SQL信息
SELECT * from v$sql t where t.SQL_ID = '4r8184hs1fjsk';
SELECT * FROM v$sql_shared_cursor c where c.sql_id = '4r8184hs1fjsk';
SELECT * from v$sql t where t.sql_text like '%XXX%';

select sql_text, hash_value,address from v$sqlarea  where sql_id='d4rra0417m4hz';
select sql_text, hash_value,address from v$sqlarea 
where sql_text like '%SELECT XXX FROM XXX%';

-- 查看SQL对应子游标个数，为Y的字段表示为何不能共享，如bind_equiv_failure表示绑定变量字段类型与实际不匹配
-- ROLL_INVALID_MISMATCH则与dbms_stats的no_invalidate参数有关，一般大批量sql出现这种情况一般是因为自动收集统计信息导致的。
select * from v$sql_shared_cursor where address = '00000000AEC7BB48';
select * from v$sql_shared_cursor where sql_id='d4rra0417m4hz';

-- 查看SQL的绑定变量
select c.sql_id,c.sql_type_mismatch,c.bind_equiv_failure,b.position,b.datatype_string,b.last_captured
from v$sql_bind_capture b,v$sql_shared_cursor c
where c.sql_id='8tw2skzjj73k5'
and c.child_address=b.child_address;

-- bind_equiv_failure = 'Y'的SQL_ID集合
SELECT sql_id FROM (
select c.sql_id,c.sql_type_mismatch,c.bind_equiv_failure,b.position,b.datatype_string,b.last_captured
from v$sql_bind_capture b,v$sql_shared_cursor c
where c.bind_equiv_failure = 'Y'
and c.child_address=b.child_address) TEMP
GROUP BY sql_id;

-- 查看VERSION_COUNT比较高的SQL信息
SELECT S.SQL_ID, S.SQL_FULLTEXT,S.VERSION_COUNT ,S.LAST_LOAD_TIME FROM v$sqlarea S ORDER BY S.VERSION_COUNT DESC;

-- 查询SQL是否对绑定敏感，是否能根据不同绑定变量值选择执行计划
select SQL_ID,SQL_TEXT,IS_BIND_SENSITIVE,IS_BIND_AWARE,is_shareable from v$sql where sql_id='7ac05552jb0v9';

（4）其他

-- 绑定变量敏感等，与ACS相关参数
select * from v$parameter;
select * from v$parameter2 WHERE NAME LIKE '%binds%';
select * from v$system_parameter WHERE NAME LIKE '%binds%';
select * from v$system_parameter2 WHERE NAME LIKE '%binds%';
select * from v$spparameter WHERE NAME LIKE '%binds%';

-- 查看ACS相关配置
select name, value from v$parameter where name in ('optimizer_mode', 'optimizer_features_enable', 'optimizer_capture_sql_plan_baselines', 'cursor_sharing');

-- 需要用SYS
select a.ksppinm name, 
       b.ksppstvl value,
      b.ksppstdf  isdefault
from sys.x$ksppi a, sys.x$ksppcv b
where a.inst_id = userenv('Instance') 
  and b.inst_id = userenv('Instance') 
  and a.indx = b.indx 
  and a.ksppinm in ('_optim_peek_user_binds', '_optimizer_adaptive_cursor_sharing', '_optimizer_extended_cursor_sharing', '_optimizer_extended_cursor_sharing_rel')
order by translate(a.ksppinm, ' _', ' ');
-- _optimizer_adaptive_cursor_sharing=TRUE：
-- _optimizer_extended_cursor_sharing=UDO；
-- _optimizer_extended_cursor_sharing_rel=SIMPLE；

-- 清除缓存
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;
exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

select * from nls_database_parameters;
select * from nls_session_parameters;
select * from nls_instance_parameters;

alter system set nls_language='AMERICAN' scope=spfile; 
alter session set nls_language='AMERICAN';
alter session set nls_territory='AMERICA';

1.2 MySQL

--------------------------------系统--------------------------------
-- 显式系统状态
SHOW GLOBAL STATUS;

-- 查看数据库隔离级别
select @@tx_isolation;
select @@global.tx_isolation,@@tx_isolation;

-- 查询是否锁表
show open tables where in_use>0;

-- 查看innodb状态，打印死锁日志
show engine innodb status

-- 查看线程列表
show PROCESSLIST
-- 错误日志
select @@log_error;

-- 查询正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX
-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

SHOW VARIABLES LIKE 'AUTOCOMMIT'

-- 查看表信息
SHOW TABLE STATUS LIKE 'version'
--------------------------------表结构--------------------------------
SHOW FULL COLUMNS FROM XX_XX_XX;

-- 查看视图构造SQL和字段信息
SHOW CREATE VIEW VIEW_NAME;
DESC VIEW_NAME;

SELECT
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS 列类型,
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = 'XX_XX' and COLUMN_NAME = 'XX_XX'
ORDER BY
    CHARACTER_MAXIMUM_LENGTH,
		TABLE_NAME;
--------------------------------索引--------------------------------

-- 查看表所含索引信息
SHOW INDEX FROM XX_XX_XX;

SELECT a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
where TABLE_NAME = 'XX_XX_XX'
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name;


SELECT a.TABLE_NAME as 表名,a.index_name as 索引名,a.non_unique as 非唯一性,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS 字段
FROM information_schema.statistics a
where TABLE_NAME in ('XX_XX_XX')
  and index_name in ('idx_XX_XX')
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name,a.non_unique

二. 执行脚本

2.1 Oracle表结构调整

注意：省略了exist和notexist判断。

（1）字段调整

新增字段：

1 2	-- 添加字段 ALTER TABLE XXX ADD column_a varchar2(20) DEFAULT ' ' NOT NULL;

修改字段长度：

-- 修改字符串类型字段长度
ALTER TABLE XXX MODIFY (column_a varchar2(32) DEFAULT ' ');

-- 修改数字类型字段长度
-- 先判断字段是否存在，然后改名并创建新字段（不在意字段顺序，copy效率不高）
ALTER TABLE XXX RENAME COLUMN column_a TO column_a_old;
ALTER TABLE XXX ADD column_a number(19,4) DEFAULT 0 NOT NULL;
UPDATE XXX SET column_a = round(column_a_old, 4);
ALTER TABLE XXX DROP COLUMN column_a_old;

修改字段名：

1 2	--字段改名 ALTER TABLE XXX RENAME COLUMN column_a TO column_aa;

（2）索引调整

新增索引：

-- 新增普通索引
CREATE INDEX idx_XXX ON XXX(column_a ASC ,column_b ASC ,column_c ASC ,column_d ASC);
-- 新增唯一索引
CREATE UNIQUE INDEX idx_XXX ON XXX(column_a ASC ,column_b ASC);
-- 新增主键索引
ALTER TABLE XXX ADD CONSTRAINT pk_XXX PRIMARY KEY(id);

修改/删除索引：

-- 普通索引，增加或减少字段 / 修改顺序 等
DROP INDEX idx_XXX;
CREATE INDEX idx_XXX ON XXX(column_a ASC ,column_b ASC ,column_c ASC ,column_d ASC);

-- 主键索引
ALTER TABLE XXX DROP CONSTRAINT pk_XXX;
ALTER TABLE XXX ADD CONSTRAINT pk_XXX PRIMARY KEY(column_a, column_b, column_c, column_d);

-- 未知为主键索引还是唯一索引，主键索引有约束，而唯一索引只创建了索引
declare
	v_rowcount_delete_index integer;
	v_rowcount_delete_cons integer;
	v_rowcount_add integer;
begin
	select count(1) into v_rowcount_delete_index from USER_INDEXES where INDEX_NAME = upper('pk_XXX');
	select count(1) into v_rowcount_delete_cons from USER_CONSTRAINTS where CONSTRAINT_NAME = upper('pk_XXX');
	if v_rowcount_delete_cons = 1 then
		execute immediate 'ALTER TABLE XXX DROP CONSTRAINT pk_XXX';
	elsif v_rowcount_delete_index = 1 then
		execute immediate 'DROP INDEX pk_XXX';
	end if;

	select count(1) into v_rowcount_add from user_indexes where index_name = upper('pk_XXX');
  	if v_rowcount_add = 0 then
  		execute immediate 'ALTER TABLE XXX ADD CONSTRAINT pk_XXX PRIMARY KEY(column_a, column_b, column_c)';
  	end if;
end;

（3）表调整

新增表：
1
删除表：
1
2
-- 删除表
drop table XXX;

2.2 MySQL表结构调整

注意：省略了exist和notexist判断。

（1）字段调整

新增字段：

1 2	-- 添加字段 ALTER TABLE XXX ADD column_a varchar(20) DEFAULT '' NOT NULL;

修改字段长度：

-- 修改字符串类型字段长度，MySQL需要指定非空和默认值
ALTER TABLE XXX MODIFY column_a varchar(32) DEFAULT '' NOT NULL;

-- 修改数字类型字段长度
-- 先判断字段是否存在，然后改名并创建新字段（不在意字段顺序，copy效率不高）
ALTER TABLE XXX CHANGE column_a column_a_old decimal(19,12);
ALTER TABLE XXX ADD column_a decimal(19,4) DEFAULT 0.0 NOT NULL;
UPDATE XXX SET column_a = round(column_a_old, 4);
ALTER TABLE XXX DROP COLUMN column_a;

修改字段名：

1 2	-- 字段改名 ALTER TABLE XXX CHANGE column_a column_aa varchar(32) DEFAULT '' NOT NULL;

（2）索引调整

新增索引

-- 新增普通索引
CREATE INDEX idx_XXX_2 ON XXX(column_a ASC ,column_b ASC ,column_c ASC ,column_d ASC );
-- 新增唯一索引
CREATE UNIQUE INDEX idx_XXX_1 ON XXX(column_a ASC ,column_b ASC );
-- 新增主键索引
ALTER TABLE XXX ADD CONSTRAINT pk_XXX PRIMARY KEY(id);

修改/删除索引：

-- 普通索引，增加或减少字段 / 修改顺序 等
DROP INDEX idx_XXX ON XXX;
CREATE INDEX idx_XXX ON XXX(column_a ASC ,column_b ASC ,column_c ASC ,column_d ASC );

-- 主键索引
ALTER TABLE XXX DROP PRIMARY KEY;
ALTER TABLE XXX ADD PRIMARY KEY(column_a,column_b,column_c,column_d);

（3）表调整：

新增表
删除表：
1
2
-- 删除表
drop table XXX;

三. 常用功能

3.1 常用命令

# 用DBA用户进入SQLPLUS
$ sqlplus / as sysdba
# 关闭Oracle服务
SQL> shutdown immediate
# 启动Oracle服务
SQL> startup
# 退出SQLPLUS
SQL> exit


# 重启服务器，需要脚本
dbstart

监听：

# 进入到oracle的安装目录 
cd $ORACLE_HOME 
# 查看监听服务状态
$ lsnrctl status
# 启动监听服务
$ lsnrctl start
# 关闭
$ lsnrctl stop
# 重启
$ lsnrctl reload

监听文件 sqlnet.ora ：

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME= orcl)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.42)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app

3.1 数据库定时备份

Linux：暂无

Windows：新建一个.bat文件，Windows开启定时任务：【任务管理器】->【管理工具】->【任务计划程序中】

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump" --opt -u root --password=<密码> <数据库名> > E:\db_backup\scwl_%Ymd%.sql
@echo on

Navicat for MySQL：Navicat for MySQL 如何创建定时备份

3.2 Oracle查看临时表空间

SELECT 

A.TABLESPACE_NAME, 

case when  A.MAXBYTES/power(1024,3)>1 THEN  ROUND(A.MAXBYTES/power(1024,3),4)||'GB'

ELSE ROUND(A.MAXBYTES/power(1024,2),4)||'MB' END "允许最大值",

case when  A.BYTES/power(1024,3)>1 THEN  ROUND(A.BYTES/power(1024,3),4)||'GB'

ELSE ROUND(A.BYTES/power(1024,2),4)||'MB' END "历史峰值", 

case when  B.BYTES_USED/power(1024,3)>1 THEN 

ROUND(B.BYTES_USED/power(1024,3),4)||'GB'

ELSE ROUND(B.BYTES_USED/power(1024,2),4)||'MB' END "已使用",   

round((B.BYTES_USED/A.BYTES)*100,2)||'%' "已使用/历史峰值",   

round((B.BYTES_USED/A.MAXBYTES)*100,2)||'%' "已使用/允许最大值" 

 FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES,SUM(F.MAXBYTES) MAXBYTES  FROM DBA_TEMP_FILES F   GROUP BY TABLESPACE_NAME) A,

 (SELECT P.TABLESPACE_NAME, SUM(P.BYTES_CACHED) BYTES_CACHED,SUM(P.BYTES_USED) BYTES_USED  FROM V$TEMP_EXTENT_POOL P  GROUP BY TABLESPACE_NAME  ) B

 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

3.3 Oracle生成AWR报告

Linux按照sqlplus：Instant Client for Linux x86-64 (64-bit) (oracle.com)

下载basic和sqlplus的rpm包后，上传到tmp目录：

1
2
3

cd /tmp
rpm -ivh oracle-instantclientXXX-basic-XXX-1.x86_64.rpm
rpm -ivh oracle-instantclientXXX-sqlplus-XXX-1.x86_64.rpm

安装完毕后，生成的客户端目录在 /usr/lib/oracle/12.2/client64 ，在该目录下新建network目录，并编写tnsnames.ora文件：

mkdir network
vi tnsnames.ora

testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = testdb)
      (SERVER = DEDICATED)
    )
  )

修改完成后按下 esc + :wq 保存退出。

配置环境变量：

vi ~/.bash_profile

export  ORACLE_HOME=/usr/lib/oracle/12.2/client64
export  TNS_ADMIN=$ORACLE_HOME/network
export  LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/local/lib:$LD_LIBRARY_PATH:.
export  ORABIN=$ORACLE_HOME/bin
export  NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin:$ORABIN

source ~/.bash_profile

登陆sqlplus：

sqlplus sys@XXX.XXX.XXX.XXX:1521/orcl AS SYSDBA
sql> @/u01/app/oracle/12c/rdbms/admin/awrrpt.sql

Enter value of report_type
# 意思是生成报告的格式有两种，html和txt，这里选择html
Enter value of num_days
# 收集几天的报告信息，数字，可以输入1
Enter value of begin_snap
# 输入开始快照id，要根据日志打印的快照id范围来填

生成的 .lst 文件直接拷贝为html打开即可。

更多内容参考：Oracle-AWR报告。

3.4 Oracle查看临时表

临时表空间可以通过使用 V$TEMP_EXTENT_POOL 视图来实时监控。语句如下：

SELECT 

A.TABLESPACE_NAME, 

case when A.MAXBYTES/power(1024,3)>1 THEN ROUND(A.MAXBYTES/power(1024,3),4)||'GB'

ELSE ROUND(A.MAXBYTES/power(1024,2),4)||'MB' END "允许最大值",

case when A.BYTES/power(1024,3)>1 THEN ROUND(A.BYTES/power(1024,3),4)||'GB'

ELSE ROUND(A.BYTES/power(1024,2),4)||'MB' END "历史峰值", 

case when B.BYTES_USED/power(1024,3)>1 THEN 

ROUND(B.BYTES_USED/power(1024,3),4)||'GB'

ELSE ROUND(B.BYTES_USED/power(1024,2),4)||'MB' END "已使用",  

round((B.BYTES_USED/A.BYTES)*100,2)||'%' "已使用/历史峰值",  

round((B.BYTES_USED/A.MAXBYTES)*100,2)||'%' "已使用/允许最大值" 

 FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES,SUM(F.MAXBYTES) MAXBYTES FROM DBA_TEMP_FILES F  GROUP BY TABLESPACE_NAME) A,

 (SELECT P.TABLESPACE_NAME, SUM(P.BYTES_CACHED) BYTES_CACHED,SUM(P.BYTES_USED) BYTES_USED FROM V$TEMP_EXTENT_POOL P GROUP BY TABLESPACE_NAME ) B

 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

执行一张大表的排序查询，确认查询未完成时的、查询完成后，以及连接释放后的临时表使用情况。

1	SELECT * FROM XXX ORDER BY XX DESC;

3.5 Oracle内存管理

Oracle内存管理分类：

自动内存管理：AutomaticMemory Management，指Oracle自动的对SGA和PGA进行管理。启动自动内存管理，只需设置 MEMORY_TARGET 和 MEMORY_MAX_TARGET 即可。
- MEMORY_TARGET 用于设置目标内存大小，Oracle会尝试将内存稳定在该值。修改 MEMORY_TARGET 并不需要重启数据库。
- MEMORY_MAX_TARGET 用于设置最大允许的内存大小，Oracle以此来限制内存使用的最大值。修改该参数，需要重启数据库。
- 在修改以上两个值时需要特别注意，MEMORY_MAX_TARGET必须大于或者等于MEMORY_TARGET。
手动内存管理：
- 自动共享内存管理：Automatic Shared Memory Management，简称为ASMM。当启用自动共享内存管理时，Oracle会自动的调整SGA的各个组件的值。如果需要启动自动共享内存管理，需要将 SGA_TARGET 和 SGA_MAX_SIZE 设置为非0值，同时还需要将 MEMORY_TARGET 和 MEMORY_MAX_TARGET 设置为0，否则 MEMORY_TARGET 不为0，Oracle采用的是自动内存管理而不是自动共享内存管理。
  - SGA_TARGET用于设置共享内存目标大小，Oracle会努力维持共享内存在此目标值，如果你修改了该参数，你并不需要重启数据库。
  - SGA_MAX_SIZE用于设置最大允许的共享内存大小，Oracle以此来限制共享内存的最大值，如果你修改了该参数，你需要重启数据库。
  - 在修改以上两个值时需要注意，SGA_MAX_SIZE****必须大于或者等于SGA_TARGET。
- 手动共享内存管理：Manual Shared Memory Management，要手动管理共享内存，首先必须禁用自动内存管理和自动共享内存管理。因此 MEMORY_TARGET 和 SGA_TARGET 都必须设置为0。同时需要手工设置其他组件的值。
  - DB_CACHE_SIZE：缓冲区缓存，主要用于缓存数据，较大的缓存通常会减少磁盘的读写数量，因此缓冲区缓存的大小对性能影响较为明显，因此设置一个合理的缓冲区缓存尤为重要。
  - SHARED_POOL_SIZE：共享池，存储多种类型的数据，例如解析后的SQL，PL/SQL代码，数据字典，查询的结果集缓存等数据。因此在多用户环境下，较大的共享池对于性能提升也是非常有帮助的。
  - LARGE_POOL_SIZE：大池是一个可选组件。一般用于备份进程，并行执行等。
  - JAVA_POOL_SIZE：JAVA池，JAVA代码所需要的内存将从此分配。
  - STREAMS_POOL_SIZE：流池，存储缓冲队列消息的内存池。
- 自动PGA内存管理：Automatic PGA Memory Management。当使用自动PGA内存管理时，Oracle会自动的管理实例PGA的内存总量。
  - 可以通过设置初始化参数 PGA_AGGREGATE_TARGET 为非0值，来开启自动PGA内存管理。
  - Oracle会尝试确保分配给所有数据库服务器进程和后台进程的PGA内存总量不会超过这个目标，但实际使用时可能超过该设置。
  - 当我们使用自动PGA内存管理时，SQL工作区的大小是自动的，并且会忽略所有*_AREA_SIZE初始化参数
- 手动PGA内存管理：Manual PGA Memory Management。当自动内存管理被禁用并且 PGA_AGGREGATE_TARGET 被设置为0时，将启用手动PGA内存管理。
  - 使用手动PGA内存管理时，意味着你需要手工设置 _AREA_SIZE 初始化参数。
  - 注意：Oracle推荐使用自动PGA内存管理，不推荐使用手动PGA内存管理。

（1）自动内存管理

如何调整内存？

1
2
3

ALTER SYSTEM SETMEMORY_MAX_TARGET = 1000M SCOPE = SPFILE;

ALTER SYSTEM SET MEMORY_TARGET =1000MSCOPE= SPFILE;

参数：

SCOPE：指修改范围，分别是SPFILE，BOTH和MEMORY。
SPFILE：指修改服务器参数文件中的数据。
MEMORY：指修改内存中的数据，对于要重启数据库才生效的参数，该值不可用
BOTH：指同时修改服务器参数文件和内存中的数据。

什么情况下使用自动内存管理？

Oracle官方推荐SGA+PGA的内存总大小如果小于或等于4GB，建议使用自动内存管理。如果你的SGA+PGA大于4G也使用了自动内存管理，那么建议最好设置 SGA_TARGET 和 PGA_AGGREGATE_TARGET 的值。这些值将作为SGA和PGA的最小值。该设置主要是为了避免过大的内存抖动。

（2）自动共享内存管理

如何调整内存？

ALTER SYSTEM SET SGA_TARGET = 1000M SCOPE = SPFILE;

ALTER SYSTEM SET SGA_MAX_SIZE = 1000M SCOPE= SPFILE;

ALTER SYSTEM SET MEMORY_MAX_TARGET = 0 SCOPE = SPFILE;

ALTER SYSTEM SET MEMORY_TARGET = 0 SCOPE = SPFILE;

什么情况下使用自动共享内存管理？

Oracle官方推荐SGA+PGA的总大小大于4GB，建议使用自动共享内存管理。如果我们启用了自动共享内存管理，Oracle会自动的调整SGA各组件大小，一般我们并不需要干预。但如果我们知道各组件高峰期时这些值的使用量，那么我们也可以为这些组件设置指定值，这些值将作为组件的最小值。从而避免高峰期时不必要的内存调整。

（3）手动共享内存管理

什么情况下使用手动共享内存管理？

不推荐使用手动共享内存管理，首先你需要对内存的各参数的作用非常的了解。其次你必须对系统各阶段内存的使用情况非常了解。并且由于不同时期对各个组件内存使用的多少可能有较大的差异，这极大的增加了管理成本。

（4）PGA内存管理

如何分配内存？

不管是采用自动内存管理还是自动共享内存管理+自动PGA内存管理。在分配内存时，普遍的做法是分配机器总内存的50% ~ 75%。

例如：机器内存是128G，SGA+PGA合计会分配64G ~ 96G。需要注意的是50%~75%只是一个普遍值，但不是个绝对值。机器内存只有4G的情况下，分配50%是很有必要的，但是如果机器内存有512G，对于只部署数据库的机器来说分配75%仍然有大量的内存未使用。

SGA需要多大？PGA需要多大？这个并没有参考的指标，一般需要根据实际情况来分配，一般可以先确定PGA大小，然后剩余内存都分配给SGA。如果你的系统有大量的并发访问，那么PGA分配就需要比较多，而如果你的系统并发访问人数非常少。那么几百MB的PGA就可满足了。而剩下的内存则都可以分配给SGA。

3.6 手动SqlLoder

测试数据 detail.txt ：

1	123,xxx,xxx,xxx,xxx,0,0,5000,xxx,xxx

控制文件 detail.ctl ：

1
2
3

LOAD DATA INFILE 'detail.txt' APPEND
INTO TABLE XXX FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY x'05' Trailing Nullcols
(XXX, XXX, XXX, XXX char(4000), XXX, XXX, XXX, XXX, XXX, XXX)

执行：

1	$ sqlldr "用户名/密码@host:port/sid" control=/XXX/detail.ctl

3.7 MySQL升级版本

查看是否安装过MySQL：

1	rpm -qa\|grep -i mysql

查看安装目录：

1	whereis mysql

暂略。

四. 知识点记录

（1）MySQL中字符串与数值比较-小心隐式转换

select 123 > '';
select '1abc' = 1;

select -1 > '';
select 0 = '';

原因：当MySQL字段类型和传入条件数据类型不一致时，会进行隐形的数据类型转换（MySQL Implicit conversion）
若字符串是以数字开头，且全部都是数字，则转换为数字结果是整个字符串；部分是数字，则转换为数字结果是截止到第一个不是数字的字符为止。理解： varchar str = “123dafa”，转换为数字是123 。 SELECT ‘123dafa’+1 ; — 124 。
若字符串不是以数字开头，则转换为数字结果是 0 。 varchar str = “aabb33” ; 转换为数字是 0 。 SELECT ‘aabb33’+100 ; — 100 。
更多隐式转换规则摘录：
- 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
- 两个参数都是字符串，会按照字符串来比较，不做类型转换
- 两个参数都是整数，按照整数来比较，不做类型转换
- 十六进制的值和非数字做比较时，会被当做二进制串
- 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
- 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较；如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较；所有其他情况下，两个参数都会被转换为浮点数再进行比较。

（2）死锁问题

--复现死锁：

--查询1：
update XXX set column_a = 1 where column_b = 'xxx' and column_d = '20180430';
update XXX set column_a = 1 where column_b = 'xxx' and column_d = '20180531';

--查询2：
update XXX set column_a = 1 where column_b = 'xxx' and BUSS_DATE = '20180430';
update XXX set column_a = 1 where column_b = 'xxx' and BUSS_DATE = '20180531';

--执行查询1第一条SQL，不提交
--执行查询2第二条SQL，不提交
--执行查询1第二条SQL，不提交 -> 锁等待
--执行查询2第一条SQL，不提交 -> 死锁

--查看死锁是否存在
select username,lockwait,status,machine,program,sid from v$session where sid in
(select session_id from v$locked_object);

--查看死锁的语句
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

--查看trace文件日志路径：如/u01/app/oracle/diag/rdbms/orcl/orcl/trace
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

linux到上述目录下执行：grep "current SQL" *trc

找到记录死锁日志的追踪文件，找到发生死锁的SQL

trc：

===============================================================================

*** 2020-12-30T16:51:07.695140+08:00
Suspected Hangs in the System
                     Root       Chain Total               Hang               
  Hang Hang          Inst Root  #hung #hung  Hang   Hang  Resolution         
    ID Type Status   Num  Sess   Sess  Sess  Conf   Span  Action             
 ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
     5 HANG    VALID    1   230     2     2    LOW  LOCAL Terminate Process  
 
  Inst  Sess   Ser             Proc  Wait    Wait 
   Num    ID    Num      OSPID  Name Time(s) Event
  ----- ------ ----- --------- ----- ------- -----
      1    422 46567      2632    FG      94 enq: TX - row lock contention
      1    230 15556     20325    FG      96 SQL*Net message from client
 
 
                                                     IO           
 Total  Self-         Total  Total  Outlr  Outlr  Outlr           
  Hung  Rslvd  Rslvd   Wait WaitTm   Wait WaitTm   Wait           
  Sess  Hangs  Hangs  Count   Secs  Count   Secs  Count Wait Event
------ ------ ------ ------ ------ ------ ------ ------ -----------
     6      0      0    677   2930      8   2688      0 enq: TX - row lock contention
 

*** 2020-12-30T16:51:07.695294+08:00
HM: Short Stack of immediate waiter session ID 422, OSPID 2632 of hang ID 5
Short stack dump: 
ksedsts()+346<-ksdxfstk()+71<-ksdxcb()+912<-sspuser()+217<-__sighandler()<-semtimedop()+10<-skgpwwait()+200<-ksliwat()+2292<-kslwaitctx()+197<-ksqcmi()+8465<-ksqgtlctx()+4872<-ksqgelctx()+771<-ktuGetTxForXid()+241<-ktcwit1()+378<-kdddgb()+6484<-kdddel()+566<-kaudel()+107<-delrow()+1489<-qerdlFetch()+695<-delexe()+1119<-opiexe()+10904<-kpoal8()+2679<-opiodr()+1229<-ttcpip()+1257<-opitsk()+1940<-opiino()+941<-opiodr()+1229<-opidrv()+1021<-sou2o()+145<-opimai_real()+455<-ssthrdmain()+417<-main()+262<-__libc_start_main()+256
 
HM: current SQL: delete from XXX
			 where column_a = :p00 and column_b = 'Y' and column_c >= :p01 and column_c <= :p02
 
 
HM: Short Stack of root session ID 230, OSPID 20325 of hang ID 5
Short stack dump: 
ksedsts()+346<-ksdxfstk()+71<-ksdxcb()+912<-sspuser()+217<-__sighandler()<-read()+14<-nttfprd()+368<-nsbasic_brc()+432<-nioqrc()+6340<-opikndf2()+1071<-opitsk()+890<-opiino()+941<-opiodr()+1229<-opidrv()+1021<-sou2o()+145<-opimai_real()+455<-ssthrdmain()+417<-main()+262<-__libc_start_main()+256
 
HM: current SQL: none
 

*** 2020-12-30T16:51:09.761422+08:00
HM: Session with ID 422 serial # 46567 (FG) on single instance 1 is hung
    and is waiting on 'enq: TX - row lock contention' for 97 seconds.
    Session was previously waiting on 'SQL*Net message from client'.
    Final Blocker is Session ID 230 serial# 15556 on instance 1
     which is waiting on 'SQL*Net message from client' for 98 seconds
     p1: 'driver id'=0x54435000, p2: '#bytes'=0x1, p3: ''=0x0
 

*** 2020-12-30T16:51:58.864634+08:00
HM: Ignoring LOW confidence LOCAL Hang with ID=5
    Victim is (inst#:sess id:serial#): (1:230:15556) (OSPID:20325)
    involving 2 total sessions.  Ignore count is 1.
    Previous hang confidence was LOW.  Hang confidence policy is HIGH.
    Hang Ignore Reason: Hang resolution is disabled.
    Hang resolution scope is OFF.  This hang's scope is SESSION.

*** 2020-12-30T16:51:58.864731+08:00
HM: About to produce Verified Hang dump
 
Details for verified hangs up to hang ID=5 in trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dia0_11498_vfy_5.trc

*** 2020-12-30T16:52:06.880582+08:00
HM: Session with ID 230 with serial number 15556 is no longer hung

*** 2020-12-30T16:52:06.880681+08:00
 
HM: Session ID 422 serial# 46567 ospid 2632 on instance 1 in hang ID 5
    was considered hung but is now no longer hung
 
 
HM: Session ID 230 serial# 15556 ospid 20325 on instance 1 in hang ID 5
    was considered hung but is now no longer hung
 
HM: Session with ID 422 with serial number 46567 is no longer hung

*** 2020-12-30T16:52:45.959966+08:00
HM: Hang ID=3 detected at 12/30/2020 16:43:30 with victim:1/230/15556
    Evt:'SQL*Net message from client', SELF-RESOLVED after 0 matches (0) (4).

*** 2020-12-30T16:54:04.127084+08:00
HM: Session with ID 230 serial # 15556 (FG) on single instance 1 is hung
    and is waiting on 'SQL*Net message from client' for 96 seconds.
    Session was previously waiting on 'SQL*Net message to client'.
    Session ID 230 is blocking 1 session
 

*** 2020-12-30T16:54:04.127351+08:00
HM: Session with ID 612 serial # 43336 (FG) on single instance 1 is hung
    and is waiting on 'enq: TX - row lock contention' for 96 seconds.
    Session was previously waiting on 'SQL*Net message from client'.
    Final Blocker is Session ID 230 serial# 15556 on instance 1
     which is waiting on 'SQL*Net message from client' for 96 seconds
     p1: 'driver id'=0x54435000, p2: '#bytes'=0x1, p3: ''=0x0
 

*** 2020-12-30T16:54:04.127888+08:00
HM: Hung Sessions (local detect) - output local chains

（3）生成大批量测试数据

# Oracle
DECLARE
i INT;
BEGIN 
i:=0;
WHILE(i<32000) 
LOOP
    i:=i+1;
	INSERT INTO XXX("XXX_ID", "XXX_ID", "XXX_DATE", "XXX_NO", "XXX_CODE", "XXX", "XXX_PRICE", "USER_XXX", "XXX_TIME", "USER_XXX", "XXX_TIME") 
	SELECT 40000 + i, '100104000', '20210820', '4', sys_guid(), 'XXX', '1.032', '149100', '20210820.102314', '0', '0' from dual;
END LOOP; 
COMMIT;
END;

# MySQL
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET i=1;
WHILE i <= 3000 DO
    
INSERT INTO xxx(`xxx`, `xxx`) VALUES (replace(uuid(),"-",""), 1);

SET i=i+1;
END WHILE;
END 
 
call proc_batch_insert();

（4）求累计金额

MySQL：

SET @csum := 0;

select * from 
(SELECT account_date, val_price, (@csum := @csum + val_price) AS 累计利润
FROM (select account_date,sum(val_price) as val_price from XXX group by account_date) a) b where account_date >= 20180404 and account_date <= 20180405;

（5）数据库连接池设定大小

线程并非越多越好，举例：为什么一个只有4个线程的Nginx服务器可以超过，拥有100个进程的Apache服务器？

网站操作从文件读取静态内容放到网络上传输给请求的用户，这些操作具有等待时间，在服务器处理完请求前都会占用线程时间。
Apache为每个用户提供一个专用线程，采用阻塞式I/O。每次请求都要创建一个新的进程/线程，导致处理器要在进程间频繁进行上下文切换。
Nginx则采用事件驱动的方式，使用一个单线程的非阻塞式I/O来处理请求。即使是一台单核心的CPU也可以“同时”支持几十几百个线程，由OS时间切片实现，核心同时只能执行一个线程，OS切换上下文后再执行另一个线程的代码。CPU调度的规律是按顺序执行任务A和B总是比按时间切割执行的快，因为上下文切换是比较昂贵的操作，当线程数量远远超过核心数时，必然会因为增加线程而变慢。

对于数据库来说，还有其它的影响因素：CPU、磁盘、网络和内存。

数据库数据大部分存储在磁盘上，传统磁盘由旋转的金属板组成上面有读写头，同一时间只能在一处读写数据，所以会有寻址耗时和旋转成本。
网络也类似，当发送和接收缓冲区填满并停滞时，会产生阻塞现象。
在等待的这段时间，连接/查询/线程只是在磁盘上等待，OS在此期间可以通过执行另一个线程来更好的利用CPU资源。

所以由于线程在I/O上被阻塞，可以通过拥有比核心数量更多的线程来完成更多的工作。但具体要多多少？

数据库连接池：

如果一个服务，经常有1W个用户连接同时发送数据库请求，每秒约2W个交易。如果为每个请求都建立一个连接，这样的资源消耗是无法想象的。即使是连接池大小设为100也有些过大，我们更想要一个几十个连接的池子让任务排队等待执行。

PostgreSQL提供了一个公式来计算连接池的最佳吞吐量：

1	connections = ((core_count \ 2) + effective_spindle_count)

为了避免死锁，连接池大小计算公式，参数:

Tn：线程的最大数量。
Cm：单个线程同时持有的最大连接数。

1	pool size = Tn * (Cm — 1) + 1

例如，最多有3个线程，每个线程需要4个连接来执行任务，所需连接池=3 * (4 - 1) + 1 = 10，这是避免死锁的最小要求，但不一定是最佳。

网上有案例对Oracle数据库进行了压测，模拟9600个并发线程，每两次操作间休眠550ms。

初始时线程池大小为2048：每个请求在连接池平均等待33ms，获得连接后，执行SQL耗时77ms，CPU使用率维持在95%左右。
降低到1024后：等待连接时长基本不变，SQL执行耗时降低了。
降低到96后：平均等待时间降为1ms，执行耗时降为2ms。

大部分情况下，我们只需要一个小的连接池，从而让等待连接的线程饱和。

（6）Oracle v$sql 视图

V$SQL 列出了没有GROUP BY子句的共享SQL区域的统计数据，并为输入的原始SQL文本的每个子句包含一行。V$SQL中显示的统计数据通常在查询执行结束时更新。然而，对于长期运行的查询，它们每5秒更新一次。这使得我们可以很容易地看到仍在进行中的长期运行的SQL语句的影响。

Column	Datatype	Description
`SQL_TEXT`	`VARCHAR2(1000)`	当前游标的SQL文本的前1000个字符
`SQL_FULLTEXT`	`CLOB`	作为`CLOB`列显式的SQL语句的全文。可以使用此列检索SQL语句的全文，而不是与`V$SQL_TEXT`动态性能视图连接。
`SQL_ID`	`VARCHAR2(13)`	库缓存中父游标的SQL标识符
`SHARABLE_MEM`	`NUMBER`	子游标所使用的共享内存的数量（单位：字节）
`PERSISTENT_MEM`	`NUMBER`	在子游标的生命周期内使用的固定内存量 (单位：字节)
`RUNTIME_MEM`	`NUMBER`	在执行子游标时需要的固定内存量
`SORTS`	`NUMBER`	为子游标做的排序数
`LOADED_VERSIONS`	`NUMBER`	表示上下文堆是否被加载
`OPEN_VERSIONS`	`NUMBER`	指示子游标是否被锁定
`USERS_OPENING`	`NUMBER`	打开该语句的用户数
`FETCHES`	`NUMBER`	与SQL语句相关的fetch数
`EXECUTIONS`	`NUMBER`	自该对象被载入库缓存以来的执行次数。
`PX_SERVERS_EXECUTIONS`	`NUMBER`	并行eXecution服务器执行的总次数。当语句从未被并行执行时，该值为0。
`END_OF_FETCH_COUNT`	`NUMBER`	自从游标被载入库缓存以来被完全执行的次数。当游标被部分执行时，该统计值不会递增。根据定义，`END_OF_FETCH_COUNT`列的值应该小于或者等于`EXECUTIONS`列的值。
`USERS_EXECUTING`	`NUMBER`	执行该语句的用户数
`LOADS`	`NUMBER`	该对象被加载或重新加载的次数
`FIRST_LOAD_TIME`	`VARCHAR2(19)`	父类创建时间
`INVALIDATIONS`	`NUMBER`	该子游标已被废止的次数
`PARSE_CALLS`	`NUMBER`	该子游标的解析调用次数
`DISK_READS`	`NUMBER`	该子游标的磁盘读取次数
`DIRECT_WRITES`	`NUMBER`	该子游标的直接写入数
`BUFFER_GETS`	`NUMBER`	该子游标获得的缓冲区数量
`APPLICATION_WAIT_TIME`	`NUMBER`	应用程序等待时间（单位：微秒）
`CONCURRENCY_WAIT_TIME`	`NUMBER`	并发等待时间（单位：微秒）
`CLUSTER_WAIT_TIME`	`NUMBER`	集群等待时间（单位：微秒）
`USER_IO_WAIT_TIME`	`NUMBER`	用户I/O等待时间（单位：微秒）
`PLSQL_EXEC_TIME`	`NUMBER`	PL/SQL 执行时间（单位：微秒）
`JAVA_EXEC_TIME`	`NUMBER`	Java执行时间（单位：微秒）
`ROWS_PROCESSED`	`NUMBER`	解析后的SQL语句返回的总行数
`COMMAND_TYPE`	`NUMBER`	Oracle命令类型定义
`OPTIMIZER_MODE`	`VARCHAR2(10)`	执行SQL语句的模式
`OPTIMIZER_COST`	`NUMBER`	优化器给出的该查询的成本
`OPTIMIZER_ENV`	`RAW(691)`	优化器环境
`OPTIMIZER_ENV_HASH_VALUE`	`NUMBER`	优化器环境的哈希值
`PARSING_USER_ID`	`NUMBER`	最初建立这个子游标的用户ID。
`PARSING_SCHEMA_ID`	`NUMBER`	最初用于建立这个子游标的Schema ID
`PARSING_SCHEMA_NAME`	`VARCHAR2(30)`	最初建立这个子游标时使用的Schema名
`KEPT_VERSIONS`	`NUMBER`	表示这个子游标是否被标记为使用`DBMS_SHARED_POOL`包在缓存中保持固定。
`ADDRESS`	`RAW(4	8)`
`TYPE_CHK_HEAP`	`RAW(4)`	该子游标的类型检查堆的描述符
`HASH_VALUE`	`NUMBER`	库缓存中父语句的哈希值
`OLD_HASH_VALUE`	`NUMBER`	旧的SQL哈希值
`PLAN_HASH_VALUE`	`NUMBER`	该游标的SQL计划的数字表示。比较一个 `PLAN_HASH_VALUE` 和另一个 `PLAN_HASH_VALUE` 可以很容易地确定两个计划是否相同（而不是逐行比较这两个计划）
`CHILD_NUMBER`	`NUMBER`	该子游标的数量
`SERVICE`	`VARCHAR2(64)`	服务名
`SERVICE_HASH`	`NUMBER`	在`SERVICE`中列出名称的哈希值
`MODULE`	`VARCHAR2(64)`	包含在第一次解析SQL语句时正在执行的模块的名称，它是通过调用 `DBMS_APPLICATION_INFO`.`SET_MODULE` 设置的
`MODULE_HASH`	`NUMBER`	`MODULE` 列中所列模块的哈希值
`ACTION`	`VARCHAR2(64)`	包含在第一次解析SQL语句时正在执行的动作的名称，它是通过调用 `DBMS_APPLICATION_INFO`.`SET_ACTION` 设置的
`ACTION_HASH`	`NUMBER`	`ACTION` 列中列出的行动的哈希值
`SERIALIZABLE_ABORTS`	`NUMBER`	每个游标中，交易无法序列化，产生 `ORA-08177` 错误的次数
`OUTLINE_CATEGORY`	`VARCHAR2(64)`	If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank.
`CPU_TIME`	`NUMBER`	该游标用于解析、执行和获取的CPU时间（单位：微秒）
`ELAPSED_TIME`	`NUMBER`	该游标用于解析、执行和获取的时间（单位：微秒）
`OUTLINE_SID`	`NUMBER`	Outline session identifier
`CHILD_ADDRESS`	`RAW(4	8)`
`SQLTYPE`	`NUMBER`	表示该语句使用的SQL语言的版本
`REMOTE`	`VARCHAR2(1)`	指示游标是否被远程映射
`OBJECT_STATUS`	`VARCHAR2(19)`	游标的状态：`VALID` - 有效的，没有错误的授权。`VALID_AUTH_ERROR` - 有效的，有授权错误的授权。 `VALID_COMPILE_ERROR` - 有效的，有编译错误的授权。`VALID_UNAUTH` - 有效的，未授权。`INVALID_UNAUTH` - 无效的，未授权。`INVALID` - 无效的，未授权，但保留时间戳。
`LITERAL_HASH_VALUE`	`NUMBER`	当使用`CURSOR_SHARING` 时，用系统生成的绑定变量替换的字词的哈希值，并且要进行匹配。这不是SQL语句的哈希值。如果不使用 `CURSOR_SHARING`，那么这个值是0。
`LAST_LOAD_TIME`	`VARCHAR2(19)`	查询计划（堆 6）加载到库缓存的时间
`IS_OBSOLETE`	`VARCHAR2(1)`	表示游标是否已经过时（`Y`）或没有过时（`N`）。如果子游标的数量太多，就会发生这种情况
`CHILD_LATCH`	`NUMBER`	Child latch number that is protecting the cursor
`SQL_PROFILE`	`VARCHAR2(64)`	SQL 简介 profile
`PROGRAM_ID`	`NUMBER`	程序标识符
`PROGRAM_LINE#`	`NUMBER`	程序行号
`EXACT_MATCHING_SIGNATURE`	`NUMBER`	在规范化的SQL文本上计算的签名。规范化包括去除白色空间和所有非字面字符串的大写字母
`FORCE_MATCHING_SIGNATURE`	`NUMBER`	当`CURSOR_SHARING`参数设置为`FORCE`时使用的签名。
`LAST_ACTIVE_TIME`	`DATE`	查询计划最后被激活的时间
`BIND_DATA`	`RAW(2000)`	绑定数据

（7）判断两个时间段有交集

首先可以用排列组合列出所有时间段可能情况，用A，B表示目标时间段，用a，b，c表示时间所处的三块时间段区间，其中b是闭合。

列出当前时间段所有可能的组合：

A			B
a \|		b		\| c

开始	结束
a	a
a	b
a	c
b	a
b	b
b	c
c	a
c	b
c	c

首先排除c-a，c-b，b-a非法开始时间不能大于结束时间，然后排除a-a，c-c和目标时间段没有交集，所以只剩下a-b，a-c，b-b，b-c四种情况。

开始	结束
a	a
a	b
a	c
b	a
b	b
b	c
c	a
c	b
c	c

得到结果：

(startTime < A && endTime >= A) || (startTime >= A && startTime <= B && endTime >= A)

endTime >= A and startTime <= B

参考：

🔗 《Navicat for MySQL 如何创建定时备份》

🔗 《Oracle-V$SQL》

🔗 三分钟了解Oracle内存管理方式

🔗 Database Connections: Less is More