本文记录一些作为DBA或数据库开发角色所要了解的Sql语句和关于Oracle的知识技巧

这个地方有非常专业详细的Oracle调优可能用到的Sql

实用SQL合集

查看一些基础参数

1
2
3
4
5
6
7
8
9
10
11
-- 查看最大允许的连接数
show parameter processes;

-- 查看当前连接数(加上ACTIVE是当前并发的连接)
select count(*) from v$session where status='ACTIVE';

-- 查看数据库用户
select * from all_users;

-- 查看数据库版本
select banner from sys.v_$version;

查询表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- oracle中表空间相当于物理存储的位置, 每个用户都会指定一个表空间
-- 当表空间不足时会带来很多问题, 配置足够的表空间非常重要
select a.tablespace_name,
a.bytes / 1024 / 1024 "sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;

挂载新的表空间

1
2
-- 将该dbf文件挂载到当前数据库,以200M为步长自动扩展最大8G
alter database datafile 'D:\NEW_TABLE_SPACE.dbf' autoextend on next 200m maxsize 8192m

查询当前执行的事务或SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 在排查事务执行慢的原因时非常有用, 可以看到当前执行的sql处于何种等待事件
SELECT s.sid, s.serial#, s.event,
a.sql_text, a.sql_fulltext,
s.username, s.status, s.machine,
s.terminal, s.program, a.executions,
s.sql_id, p.spid, a.direct_writes
FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s
LEFT JOIN v$sqlarea a
ON s.sql_id = a.sql_id
INNER JOIN v$process p
ON s.paddr = p.addr

-- 正在执行的SQL
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value

干掉一个死锁的SQL会话

1
2
3
4
5
-- alter ssytem kill session 'sid,serial#';
-- 这个比较常用, 解决掉某个被锁住的会话, 下面这句话可以查询死锁会话
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

侦查一下有哪些人在删数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 使用Hist视图查询最近三天执行过哪些删除语句
-- dba_hist_sqltext的command_type字段有以下常见的几种类型
-- 2 insert; 3 select; 6 update; 7 delete;
-- 42 alter session; 44 commit; 47 begin...end; 48 SET TRANSACTION;
-- 49 alter system; 85 truncate table;
SELECT c.username,
a.program,
b.sql_text,
b.command_type,
a.sample_time
FROM dba_hist_active_sess_history a
JOIN dba_hist_sqltext b
ON a.sql_id = b.sql_id
JOIN dba_users c
ON a.user_id = c.user_id
WHERE a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;

如何删库跑路

Oracle中并没有提供类似Mysql中的drop database语句, 删起库来很不方便, 只能自己动手拼接字符串来删库跑路了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 这些语句分别从user_tables ... user_triggers 等表中
-- 选择出数据库的表,序列,存储过程,触发器等, 拼接drop实现获取删除语句
select 'drop table ' || table_name ||' cascade constraints PURGE;'
||chr(13)||chr(10)
from user_tables;

select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10)
from user_sequences;

select 'drop procedure ' || object_name||';'||chr(13)||chr(10)
from user_objects where object_type='PROCEDURE';

select 'drop TRIGGER "' ||sys_context('USERENV','CURRENT_USER')||'"."'
|| trigger_name ||'";' ||CHR(13) ||CHR(10)
from user_triggers;

性能调优

Oracle数据库的调优是一门很深的学问, 此处先记录一些非常简单有效的优化方法, 高端的调优方法还是得找专业DBA人员.
在定位问题时, 除了查询v$xxx表获得信息外, 生成分析AWR性能报告也是个好办法, 这里有一个关于AWR报告如何分析的文章

进程数、最大连接数配置

上面记录了查看最大连接数的办法show parameter processes, 修改的办法自然是用alter system语句了

1
2
-- 最大并发连接设置为300
alter system set processes = 300 scope = spfile;

SGA/PGA配置

SGA/PGA是oracle内存结构的重要部分, 配置的参数直接影响了oracle可用的内存大小, 相关的各种内存池的大小, 这里有一个关于oracle内存结构的文章.

  • SGA大小: 对于一个纯数据库系统, 应该分配80%的总内存, 剩下的给操作系统即可, 数据库+应用的系统适当减少SGA大小
  • PGA大小: 一般的OLTP应用, 分配SGA的20%即可, 如果有很多大查询, 适当增加PGA的大小, 但不要超过SGA的50%
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 进入sqlplus命令行
sqlplus sys/password as sysdba

-- 查看数据库参数
show parameter sga;
show parameter pga;
show parameter workarea;

-- 修改SGA(System Global Area)
alter system set sga_max_size=4096M scope=spfile;
alter system set sga_target=4096M scope=spfile;

shutdown immediate;
startup;

-- 修改PGA(Program Global Area)
alter system set workarea_size_policy=auto scope=both;
alter system set pga_aggregate_target=512m scope=both;

-- 10g后db_cache_size自动管理, 非专业人士不要乱改
alter system set db_cache_size=1024M scope=spfile sid='数据库SID';

-- 查看字典缓冲区的使用率, 低于90%的增加shared_pool_size
select (sum(gets-getmisses-usage-fixed))/sum(gets)
"Data dictionary cache" from v$rowcache;
alter system set SHARED_POOL_SIZE = 64M;

SQL和表结构优化

除了数据库本身的配置, 表的设计和SQL本身也是影响执行效率的决定性因素. 大部分情况下, 数据库的瓶颈都是人为造成的, 但大多也都归咎于硬件太差/数据量过大/网络不好等因素, 但在甩锅的时候, 也应该考虑一下这些可能提升性能的地方是否都已经做到了.

  • 索引
    • 成也索引, 败也索引, 以牺牲少量的写入性能换取读取性能的极大提高, SQL优化利器
    • 索引的类型有B-Tree索引, 位图索引, 函数索引等等. 选择索引的类型/字段要视业务而定, 这里有一些通用的原则
  • 并行
    • 开启并行: alter session enable parallel dml;
    • 并行查询(table可选, n为并行度): select /+PARALLEL([table], n)/ * from table
  • 避免低效的写法
    • 大部分情况下, insert时大量values批量插入比执行n条insert语句快好几个数量级
    • merge into比 insert into … select快, 比 update 快一个数量级
    • select的字段中嵌套select非常危险
    • in不一定比exists更快, in只支持1000个以下
    • union/union all可以代替两次select, union有时候还能代替去重
    • 存储过程一般比较快, 但不到万不得已不要用, 调试和维护的成本太高
    • truncate比delete更快
    • 越底层的库/框架越快, Mybatis没有JDBC快, Dapper没有ADO.NET快, ADO.NET没有ODP.NET快, 至于EntityFramework只能呵呵了
  • 物理存储上分库分区分表
    • 范围分区/Hash分区/复合分区
    • 读写分离
  • 表设计
    • CHAR比VARCHAR更快, VARCHAR比*LOB更快, 视实际情况选择合适的字段
    • 只增不删或很少删的表可以使用nologing, 配合/+append/高水位线插入提升效率, 可以使用在table或insert语句上, 这里有详述
    • 表结构尽量符合第三范式减少不必要的冗余

SQL的优化又是可以写一整本书的事情了, 这里列举的几个有的是Oracle特有的, 有的是所有RDBMS通用的, 此文不再深入讨论.