oracle基础和调优
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库原理和设计
内容
1
2
Oracle结构
PL/SQL
3
数据库优化
4
SQL优化
Oracle结构
• 逻辑结构
表 视图 存储过程 函数
索引 序列 触发器 包
segment extent block
Oracle结构
Oracle数据库
Database 表空间
Tablespaces
表空间
表空间
Objects
AWR
EM
数据库优化
功能模块切换 区 实例状态 区
告警区，调优诊断的关注区
数据库优化
• DB优化主要关注点
SGA 回滚段 内存 Lock 排序 资源竞争 磁盘
DB级
Latch
IO SQL级
Block
SQL SQL
OS级
OS OS
数据库优化
（1）SGA
library cache 计算公式： sum(pins-reloads)/sum(pins) 查询语句： select sum(pins-reloads)/sum(pins) from v$librarycache 一般要求： 应大于90% dictionary cache 计算公式： sum(gets-getmisses-usage-fixed)/sum(gets) 查询语句： select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache 一般要求： 应大于90% data buffer cache 计算公式： 1-(physical reads/(db block gets+consistent gets)) 查询语句： select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets') 一般要求： 应大于90% log buffer 计算公式： redo buffer allocation retries/redo entries 查询语句： select name,value from v$sysstat where name in ('redo buffer allocation retries','redo entries') 一般要求： 应小于1%
PL/SQL
（6）函数 1）内部函数： 1>字符函数 函数名称
ASCII(CHAR) CHR(N) CONCAT(CHAR1,CHAR2) LPAD('123',3,'51') LPAD('123',5,'51') LPAD('123',6,'51') 结果分别是什么？
功能描述
得到字符串的第一个字符的ASCII值 得到数值N指定的字符 连接两个字符串
系统级触发器： CREATE TRIGGER 触发器名 AFTER 系统级触发条件 ON DATABASE|SCHEMA BEGIN 处理语句; END 触发器名;
触发器中能用commit吗？
PL/SQL
（4）PL/SQL程序块
DECLARE 变量列表; BEGIN 处理语句; EXCEPTION WHEN 异常名称 THEN 异常处理语句; END;
关注一下 存储过程怎么传出游标
PL/SQL
（3）触发器 Oracle的触发器分为：系统级、表级、行级。 其中系统级主要用来实现数据库的管理和维护工 作，而其他两种主要用来实现企业的业务逻辑。
触发器
系 统 级 触 发 器
表 级 触 发 器
行 级 触 发 器
PL/SQL
表级触发器： CREATE TRIGGER 触发器名 BEFORE|AFTER INSERT| DELETE|UPDATE OF 列名 ON 表名 BEGIN 处理语句; END 触发器名; 行级触发器： CREATE TRIGGER触发器名 BEFORE|AFTERINSERT| DELETE|UPDATE OF 列名 ON 表名 FOR EACH ROW BEGIN 处理语句; END 触发器名;
123 51123 515123
LTRIM(CHAR) RTRIM(CHAR)
PL/SQL
2>日期函数 源自文库数名称
ADD_MONTHS(D,N) LAST_DAY(D)
函数功能
在D日期的基础上增加N个月 得到D日期所在月份最后一天的日期
MONTH_BETWEEN(D1,D2)
NEXT_DAY(D,CHAR) ROUNT(D,FMT) SYSDATE
数据库优化
（1）性能视图
V$sysstat：记录的是数据库系统的统计信息
V$session_wait：记录当前所有session的等待信息
V$process：记录所有服务器进程的信息 V$lock：记录数据库中当前存在的所有对象锁 V$latch_children：记录数据库中所有内存锁的统计信息
用户进程 用户进程 Select 服务器进程 DBWR 数据缓存池 否 该写? 数据文件 否 有? 是 是 数据文件 读过 程 否 数据缓存池 DML 写过 程
服务器进程
有?
是
内容
1
2
Oracle结构
PL/SQL
3
数据库优化
4
SQL优化
PL/SQL
• PL/SQL语言
PL/SQL是Oracle提供的基于数据库的二次开发语言，用来帮助用户在数据库 中实现业务逻辑和一些日常维护管理工作。作为一门开发语言，其组成部分 理应包括： 变量类型、变量定义、赋值语句、流程控制语句、函数。 因为PL/SQL基于SQL92标准之上，所以它另外还包括SQL语句。 变量定义
数据库优化
抓取性能快照
为啥要抓两次？
Snap_id保存在表stats$snapshot
生成statspack报告了
数据库优化
（3）AWR
抓取性 能快照
这一步可选，系统默认 每隔一小时自动抓取一 次
Snap_id保存在表 dba_hist_snapshot
生成awr报告了
数据库优化
（4）EM
默认一小 时启动一 次 ADDM (自动数据库诊断监控程序)
包
Segments
数据段
索引段
临时段
回滚段 这些对象都保 存在哪里？
Oracle结构
System表空间 数据字典表 数据字典表 数据字典表 数据字典表
Oracle结构
• 程序结构
内存结构
进程结构
文件结构
Oracle结构
Sql解析速 度 数据抽取速 度
先前滚再回 滚
Oracle结构
数据的读写过程有何不同？
V$filestat：记录所有数据文件的IO情况 V$tempstat：记录所有临时文件的IO V$sql：记录共享池中缓存的SQL语句 V$session_longops：记录数据库所有处理时长超过6秒的SQL语句
数据库优化
（2）statspack
为statspack 创建一个表空 间
开始安装 statspac k
PL/SQL
6>合计函数
函数名称
COUNT() MAX() MIN()
功能描述
计算满足条件的记录数 对指定的列求最大值 对指定的列求最小值
AVG()
SUM()
对指定的列求平均值
計算列的和
PL/SQL
• PL/SQL程序 C语言的程序最终是一些.c文件，而PL/SQL程序的最终是 存储过程、包、触发器以及PL/SQL块，而最主要是存储 过程。
ABS(N) CEIL(N) EXP(N) FLOOR(N) LN(N) LOG(M,N) MOD(M,N) POWER(M,N) SORT(N) TRUNC(N,M) COS(N) SIN(N) TAN(N)
功能描述
得到N的绝对值 得到大于或等于N的最大整数 得到E的N次幂 得到小于或等于N的最小整数 得到N的自然对数 得到以M为底N的对数 得到M除以N的余数 得到M的N次幂 得到N的平方根,N>=0 得到在M位截断的N的值 得到N的余弦值 得到N的正弦值 得到N的正切值
LOOP 处理语句; EXIT WHEN <条件> END LOOP;
看一个for循环例子： for i in 1..2 loop dbms_output.put_line(i); end loop;
PL/SQL
（5）异常处理语句 EXCEPTION WHEN 异常名称 THEN 异常处理语句;
CURSOR_ALREADY_OPEN 试图打开一个已打开的游标 DUP_VAL_ON_INDEX 试图破坏一个唯一性限制 INVALID_CURSOR 试图使用一个无效的游标 INVALID_NUMBER 试图对非数字值进行数字操作 LOGIN_DENIED 无效的用户名或者口令 NO_DATA_FOUND 查询未找到数据 NOT_LOGGED_ON 还未连接就试图数据库操作 TIMEOUT_ON_RESOURCE 发生超时 TOO_MANY_ROWS SELECT INTO命令返回的多行 TRANSACTION_BACKED_OUT 由于死锁提交被退回 ZERO_DIVIDE 试图被零除
内容
1
2
Oracle结构
PL/SQL
3
数据库优化
4
SQL优化
数据库优化
• OS性能收集工具
显示CPU占用率、 详细进程列表
top
发生等待的进程数、 swap和内存剩余量
vmstat
每秒磁盘IO量和IO 次数
iostat
网络流量统计信息
netstat -i
数据库优化
• DB性能收集工具 性能视图 statspack AWR EM
变量类型
赋值语句
PL/SQL SQL语句 流程控制语句 异常处理语句 函数
PL/SQL
（1）变量类型
变量类型
Number Int Pls_integer Binary_integer Char Varchar2 Long Boolean LOB Cursor 自定义变量类型
类型说明
数字型 整数型 整数型 整数型 定长字符型 变长字符型 变长字符型 布尔型 大对象 游标
Select语 句可以 带条件
PL/SQL
（3）赋值语句 1）直接赋值： 变量 := 值; 2）Select赋值： Select 列名 into 变量 from 表名 where条件;
PL/SQL
（4）流程控制语句
条件语句
循环语句
PL/SQL
1）条件语句：
IF <条件> THEN 处理语句; END IF;
得到两个日期之间的月数
得到日期D后第一个由CHAR命名的星期的日期 得到按指定的FMT模式舍入到的最近的日期 得到当前系统的日期和时间
FMT：日期格式说明符 如：’YYYY-MM-DD HH24:MI:SS’
PL/SQL
3>转换函数 函数名称
TO_CHAR(D,FMT) TO_DATE(CHAR,FMT) TO_TIMESTAMP(CHAR,FMT) TO_NUMBER(CHAR)
备注
产生溢出时出现错误 表示带符号的整数 最大255个字符 最大2000个字符 最长2GB
PL/SQL
（2）变量定义 1）一般变量定义： 变量名 类型标识符 [not null]:=缺省值;
定义时 初始化
2）游标定义： CURSOR cursor_name IS select_statement;
PL/SQL程序
存 储 过 程
包
触 发 器
PL / SQL 程 序 块
PL/SQL
（1）存储过程
CREATE PROCEDURE 存储过程名(参数列表) AS 变量定义列表; BEGIN 处理语句; END存储过程名;
参数和变量有何不同？
PL/SQL
（2）包
包头： CREATE PACKAGE 包名 AS 变量定义列表; 函数定义列表; 包体： 存储过程定义列表; CREATE PACKAGE BODY 包名 AS END包名; 函数实现; 存储过程实现; END包名;
功能描述
将日期D转换为FMT格式的字符串 将字符串CHAR转换为FMT格式的日期 将字符串CHAR转换为FMT格式的时间戳 将字符转换为数值
PL/SQL
4>条件测试 函数名称
DECODE(CHAR,N1,CHAR1,N2,CHAR2...)
功能描述
CHAR的值为N1，替换为CHAR1; 为N2，替换为CHAR2，以此类推
IF <条件> THEN 处理语句; ELSE 处理语句; END IF;
IF <条件> THEN 处理语句; ELSIF <条件> THEN 处理语句; ELSE 处理语句; END IF;
PL/SQL
2）循环语句：
FOR 计数器 IN 下限..上限 LOOP 处理语句; WHILE <条件> END LOOP; LOOP 处理语句; END LOOP;
表
索引
视图
序列
存储过程
函数
触发器
包
Segments Extents Blocks
数据段 分区 块 块
索引段 分区 块 分区
临时段
回滚段
Segment包含Extent Extent包含Block Block包含OS Block 最大为32K
Oracle结构
Objects
表
索引
视图
序列
存储过程
函数
触发器
Decode函数 和什么SQL语句 功能类似？
select tablespace_name, case when tablespace_name=‘SYSTEM’ then ‘系统表空间' else ‘用户表空间' end case from dba_tablespaces
PL/SQL
5>算数函数 函数名称
LENGTH(CHAR)
LOWER(CHAR) UPPER(CHAR) LPAD(CHAR1,N,CHAR2) RPAD(CHAR1,N,CHAR2) REPLACE(CHAR1,CHAR2,CHAR3)
取一字符串CHAR的长度
将字符串CHAR全部转换为小写 将字符串CHAR全部转换为大写 用字符串CHAR2包括的字符左填CHAR1,使其长度为N 用字符串CHAR2右填字符串CHAR1,使其长度为N 将字符串CHAR1中含CHAR2的子串全部替换为CHAR3 去掉字符串CHAR左边的空格 去掉字符串CHAR右边的空格
内容
1
2
Oracle结构
PL/SQL
3
数据库优化
4
SQL优化
Oracle结构
• 逻辑结构
表 视图 存储过程 函数
索引 序列 触发器 包
segment extent block
Oracle结构
Oracle数据库
Database 表空间
Tablespaces
表空间
表空间
Objects
AWR
EM
数据库优化
功能模块切换 区 实例状态 区
告警区，调优诊断的关注区
数据库优化
• DB优化主要关注点
SGA 回滚段 内存 Lock 排序 资源竞争 磁盘
DB级
Latch
IO SQL级
Block
SQL SQL
OS级
OS OS
数据库优化
（1）SGA
library cache 计算公式： sum(pins-reloads)/sum(pins) 查询语句： select sum(pins-reloads)/sum(pins) from v$librarycache 一般要求： 应大于90% dictionary cache 计算公式： sum(gets-getmisses-usage-fixed)/sum(gets) 查询语句： select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache 一般要求： 应大于90% data buffer cache 计算公式： 1-(physical reads/(db block gets+consistent gets)) 查询语句： select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets') 一般要求： 应大于90% log buffer 计算公式： redo buffer allocation retries/redo entries 查询语句： select name,value from v$sysstat where name in ('redo buffer allocation retries','redo entries') 一般要求： 应小于1%
PL/SQL
（6）函数 1）内部函数： 1>字符函数 函数名称
ASCII(CHAR) CHR(N) CONCAT(CHAR1,CHAR2) LPAD('123',3,'51') LPAD('123',5,'51') LPAD('123',6,'51') 结果分别是什么？
功能描述
得到字符串的第一个字符的ASCII值 得到数值N指定的字符 连接两个字符串
系统级触发器： CREATE TRIGGER 触发器名 AFTER 系统级触发条件 ON DATABASE|SCHEMA BEGIN 处理语句; END 触发器名;
触发器中能用commit吗？
PL/SQL
（4）PL/SQL程序块
DECLARE 变量列表; BEGIN 处理语句; EXCEPTION WHEN 异常名称 THEN 异常处理语句; END;
关注一下 存储过程怎么传出游标
PL/SQL
（3）触发器 Oracle的触发器分为：系统级、表级、行级。 其中系统级主要用来实现数据库的管理和维护工 作，而其他两种主要用来实现企业的业务逻辑。
触发器
系 统 级 触 发 器
表 级 触 发 器
行 级 触 发 器
PL/SQL
表级触发器： CREATE TRIGGER 触发器名 BEFORE|AFTER INSERT| DELETE|UPDATE OF 列名 ON 表名 BEGIN 处理语句; END 触发器名; 行级触发器： CREATE TRIGGER触发器名 BEFORE|AFTERINSERT| DELETE|UPDATE OF 列名 ON 表名 FOR EACH ROW BEGIN 处理语句; END 触发器名;
123 51123 515123
LTRIM(CHAR) RTRIM(CHAR)
PL/SQL
2>日期函数 源自文库数名称
ADD_MONTHS(D,N) LAST_DAY(D)
函数功能
在D日期的基础上增加N个月 得到D日期所在月份最后一天的日期
MONTH_BETWEEN(D1,D2)
NEXT_DAY(D,CHAR) ROUNT(D,FMT) SYSDATE
数据库优化
（1）性能视图
V$sysstat：记录的是数据库系统的统计信息
V$session_wait：记录当前所有session的等待信息
V$process：记录所有服务器进程的信息 V$lock：记录数据库中当前存在的所有对象锁 V$latch_children：记录数据库中所有内存锁的统计信息
用户进程 用户进程 Select 服务器进程 DBWR 数据缓存池 否 该写? 数据文件 否 有? 是 是 数据文件 读过 程 否 数据缓存池 DML 写过 程
服务器进程
有?
是
内容
1
2
Oracle结构
PL/SQL
3
数据库优化
4
SQL优化
PL/SQL
• PL/SQL语言
PL/SQL是Oracle提供的基于数据库的二次开发语言，用来帮助用户在数据库 中实现业务逻辑和一些日常维护管理工作。作为一门开发语言，其组成部分 理应包括： 变量类型、变量定义、赋值语句、流程控制语句、函数。 因为PL/SQL基于SQL92标准之上，所以它另外还包括SQL语句。 变量定义
数据库优化
抓取性能快照
为啥要抓两次？
Snap_id保存在表stats$snapshot
生成statspack报告了
数据库优化
（3）AWR
抓取性 能快照
这一步可选，系统默认 每隔一小时自动抓取一 次
Snap_id保存在表 dba_hist_snapshot
生成awr报告了
数据库优化
（4）EM
默认一小 时启动一 次 ADDM (自动数据库诊断监控程序)
包
Segments
数据段
索引段
临时段
回滚段 这些对象都保 存在哪里？
Oracle结构
System表空间 数据字典表 数据字典表 数据字典表 数据字典表
Oracle结构
• 程序结构
内存结构
进程结构
文件结构
Oracle结构
Sql解析速 度 数据抽取速 度
先前滚再回 滚
Oracle结构
数据的读写过程有何不同？
V$filestat：记录所有数据文件的IO情况 V$tempstat：记录所有临时文件的IO V$sql：记录共享池中缓存的SQL语句 V$session_longops：记录数据库所有处理时长超过6秒的SQL语句
数据库优化
（2）statspack
为statspack 创建一个表空 间
开始安装 statspac k
PL/SQL
6>合计函数
函数名称
COUNT() MAX() MIN()
功能描述
计算满足条件的记录数 对指定的列求最大值 对指定的列求最小值
AVG()
SUM()
对指定的列求平均值
計算列的和
PL/SQL
• PL/SQL程序 C语言的程序最终是一些.c文件，而PL/SQL程序的最终是 存储过程、包、触发器以及PL/SQL块，而最主要是存储 过程。
ABS(N) CEIL(N) EXP(N) FLOOR(N) LN(N) LOG(M,N) MOD(M,N) POWER(M,N) SORT(N) TRUNC(N,M) COS(N) SIN(N) TAN(N)
功能描述
得到N的绝对值 得到大于或等于N的最大整数 得到E的N次幂 得到小于或等于N的最小整数 得到N的自然对数 得到以M为底N的对数 得到M除以N的余数 得到M的N次幂 得到N的平方根,N>=0 得到在M位截断的N的值 得到N的余弦值 得到N的正弦值 得到N的正切值
LOOP 处理语句; EXIT WHEN <条件> END LOOP;
看一个for循环例子： for i in 1..2 loop dbms_output.put_line(i); end loop;
PL/SQL
（5）异常处理语句 EXCEPTION WHEN 异常名称 THEN 异常处理语句;
CURSOR_ALREADY_OPEN 试图打开一个已打开的游标 DUP_VAL_ON_INDEX 试图破坏一个唯一性限制 INVALID_CURSOR 试图使用一个无效的游标 INVALID_NUMBER 试图对非数字值进行数字操作 LOGIN_DENIED 无效的用户名或者口令 NO_DATA_FOUND 查询未找到数据 NOT_LOGGED_ON 还未连接就试图数据库操作 TIMEOUT_ON_RESOURCE 发生超时 TOO_MANY_ROWS SELECT INTO命令返回的多行 TRANSACTION_BACKED_OUT 由于死锁提交被退回 ZERO_DIVIDE 试图被零除
内容
1
2
Oracle结构
PL/SQL
3
数据库优化
4
SQL优化
数据库优化
• OS性能收集工具
显示CPU占用率、 详细进程列表
top
发生等待的进程数、 swap和内存剩余量
vmstat
每秒磁盘IO量和IO 次数
iostat
网络流量统计信息
netstat -i
数据库优化
• DB性能收集工具 性能视图 statspack AWR EM
变量类型
赋值语句
PL/SQL SQL语句 流程控制语句 异常处理语句 函数
PL/SQL
（1）变量类型
变量类型
Number Int Pls_integer Binary_integer Char Varchar2 Long Boolean LOB Cursor 自定义变量类型
类型说明
数字型 整数型 整数型 整数型 定长字符型 变长字符型 变长字符型 布尔型 大对象 游标
Select语 句可以 带条件
PL/SQL
（3）赋值语句 1）直接赋值： 变量 := 值; 2）Select赋值： Select 列名 into 变量 from 表名 where条件;
PL/SQL
（4）流程控制语句
条件语句
循环语句
PL/SQL
1）条件语句：
IF <条件> THEN 处理语句; END IF;
得到两个日期之间的月数
得到日期D后第一个由CHAR命名的星期的日期 得到按指定的FMT模式舍入到的最近的日期 得到当前系统的日期和时间
FMT：日期格式说明符 如：’YYYY-MM-DD HH24:MI:SS’
PL/SQL
3>转换函数 函数名称
TO_CHAR(D,FMT) TO_DATE(CHAR,FMT) TO_TIMESTAMP(CHAR,FMT) TO_NUMBER(CHAR)
备注
产生溢出时出现错误 表示带符号的整数 最大255个字符 最大2000个字符 最长2GB
PL/SQL
（2）变量定义 1）一般变量定义： 变量名 类型标识符 [not null]:=缺省值;
定义时 初始化
2）游标定义： CURSOR cursor_name IS select_statement;
PL/SQL程序
存 储 过 程
包
触 发 器
PL / SQL 程 序 块
PL/SQL
（1）存储过程
CREATE PROCEDURE 存储过程名(参数列表) AS 变量定义列表; BEGIN 处理语句; END存储过程名;
参数和变量有何不同？
PL/SQL
（2）包
包头： CREATE PACKAGE 包名 AS 变量定义列表; 函数定义列表; 包体： 存储过程定义列表; CREATE PACKAGE BODY 包名 AS END包名; 函数实现; 存储过程实现; END包名;
功能描述
将日期D转换为FMT格式的字符串 将字符串CHAR转换为FMT格式的日期 将字符串CHAR转换为FMT格式的时间戳 将字符转换为数值
PL/SQL
4>条件测试 函数名称
DECODE(CHAR,N1,CHAR1,N2,CHAR2...)
功能描述
CHAR的值为N1，替换为CHAR1; 为N2，替换为CHAR2，以此类推
IF <条件> THEN 处理语句; ELSE 处理语句; END IF;
IF <条件> THEN 处理语句; ELSIF <条件> THEN 处理语句; ELSE 处理语句; END IF;
PL/SQL
2）循环语句：
FOR 计数器 IN 下限..上限 LOOP 处理语句; WHILE <条件> END LOOP; LOOP 处理语句; END LOOP;
表
索引
视图
序列
存储过程
函数
触发器
包
Segments Extents Blocks
数据段 分区 块 块
索引段 分区 块 分区
临时段
回滚段
Segment包含Extent Extent包含Block Block包含OS Block 最大为32K
Oracle结构
Objects
表
索引
视图
序列
存储过程
函数
触发器
Decode函数 和什么SQL语句 功能类似？
select tablespace_name, case when tablespace_name=‘SYSTEM’ then ‘系统表空间' else ‘用户表空间' end case from dba_tablespaces
PL/SQL
5>算数函数 函数名称
LENGTH(CHAR)
LOWER(CHAR) UPPER(CHAR) LPAD(CHAR1,N,CHAR2) RPAD(CHAR1,N,CHAR2) REPLACE(CHAR1,CHAR2,CHAR3)
取一字符串CHAR的长度
将字符串CHAR全部转换为小写 将字符串CHAR全部转换为大写 用字符串CHAR2包括的字符左填CHAR1,使其长度为N 用字符串CHAR2右填字符串CHAR1,使其长度为N 将字符串CHAR1中含CHAR2的子串全部替换为CHAR3 去掉字符串CHAR左边的空格 去掉字符串CHAR右边的空格