SQL编码建议与优化(Oracle).ppt

神州数码思特奇信息技术股份有限公司 2007年7月17日杨咸福 SQL编码建议与优化 Oracle 内容摘要 数据模型 E R 关系模型SQL语言数据库设计范式数据存储与查询事务管理Oracle数据库体系结构SQL编码建议SQL优化建议讨论 数据模型 E R模型 什么是ER模型ER模型的历史ER模型的基本元素实体联系属性例子 实体 联系 属性 ER模型图例 数据模型 属性分类 基本属性和复合属性 可否再分 单值属性和多值属性 对一个实体对象是否只能取一个值 多值属性的处理将原来的多值属性用几个新的单值属性来表示 将原来的多值属性用一个新的实体类型表示导出属性空值 数据模型 数据模型 联系设计联系集联系集是n n 2 个实体集上的数学关系 这些实体集不必互异 如果E1 E2 En为n个实体集 那么联系集R是 e1 e2 en e1 E1 e2 E2 en En 的一个子集 而 e1 e2 en 是一个联系 联系的元数一个联系涉及到的实体集个数联系的连通词联系涉及到的实体集之间实体对应的方式实体的基数有两个实体集E1和E2 E1中每个实体与E2中有联系实体的数目的最小值min和最大值max 称为E1的基数 用 min max 形式表示 数据模型 数据模型 数据模型 数据模型 ER模型的操作 关系模型 1 关系模型的数据结构 1 关系模型中的主要术语1 关系 一个关系对应通常所说的一张二维表 2 元组 表中的一行称为一个元组 3 属性 表中的一列称为一个属性 4 主码 表中的某个属性或属性组 值可以惟一地确定一个元组 且属性组中不含多余的属性 5 域 属性的取值范围称为域 6 分量 元组中的一个属性值称为分量 7 关系模式 关系的型称为关系模式 是对关系的描述 2 关系模型中的数据全部用关系表示 关系模式一般的表示是 关系名 属性1 属性2 属性n 关系模型 关系代数1970年IBM公司的E F Cood博士在论文 一个通用关系式数据库系统的模型 中首先提出了关系模型 它提供了格式化数据库系统难以做到的数据独立性和数据相容性 关系模型 基本运算选择投影并集合差笛卡儿积 关系模型 选择选择又称为限制 Restriction 从关系中找出满足给定条件的所有元组称为选择 其中的条件是以逻辑表达式给出的 该逻辑表达式的值为真的元组被选取 这是从行的角度进行的运算 即水平方向抽取元组 经过选择运算得到的结果可以形成新的关系 其关系模式不变 但其中元组的数目小于或等于原来的关系中的元组的个数 它是原关系的一个子集 例1查询信息系 IS系 全体学生例2查询信息系 IS系 年龄小于19岁的学生 关系模型 投影从关系中挑选若干属性组成的新的关系称为投影 这是从列的角度进行运算 经过投影运算可以得到一个新关系 其关系所包含的属性个数往往比原关系少 或者属性的排列顺序不同 如果新关系中包含重复元组 则要删除重复元组投影操作是从列的角度进行的运算例1 查询学生关系Student中都有哪些系 即查询关系Student在所在系属性上的投影 关系模型 并设有两个关系R和S具有相同的关系模式 R和S的并是由属于R和S的元组构成的集合 记为R S 注意 R和S的元数相同 例1 查询所有积分大于零或者预存款大于零的客户信息 关系模型 集合差设有两个关系R和S具有相同的关系模式 R和S的差是由属于R但不属于S的元组构成的集合 记为R S 注意 R和S的元数相同 例1 查询所有有预存款而无积分的客户信息 关系模型 笛卡儿积设关系R和S的元数分别为r和s 定义R和S的笛卡尔积是一个 r s 元的元组集合 每个元组的前r个分量 属性值 来自R的一个元组 后s个分量来自S的一个元组 记为R S 若R有M个元组 S有n个元组 则R S有m n个元组 例1 R S SQL语言 数据定义语言 DDL 提供定义关系模式 删除关系以及修改关系模式的命令数据操作语言 DML 关系数据查询 更新 删除命令视图定义事务控制嵌入式SQL和动态SQL 用于通用编程语言中 权限管理 SQL语言 基本结构Select子句 对应关系代数中的投影运算From子句 对应关系代数中的笛卡儿积运算Where子句 对应关系代数中的选择运算集合运算 并 交 差嵌套子查询关系的连接完整性和安全性约束触发器授权 数据库设计范式 第一范式 FirstNormalForm 1NF 通俗定义 属性具备原子性 即不可在分易犯的错误信息重复信息不准确第二范式通俗定义 1 满足1NF 2 一个table中的行是可以唯一标示的 即table中的行是不可以有重复的 第三范式通俗定义 1 满足2NF 2 一个table中列不依赖以另一个table中的非主键的列例如 部门 dept id pk dept name 员工 stuff id pk dept id 则在员工表中就不得再有除dept id列的其它有关部门信息的列 数据存储与查询 存储和文件结构存储介质高速缓存 cache 快速闪存 flashmemory 断电时 可以保存信息 数据写入慢 读出和主存速度相近磁盘光存储器 CD磁带 读取和写只能顺序访问 效率低sRAID 冗余独立磁盘阵列磁盘镜像 并行读取 提高效率 数据存储与查询 存储访问块 Oracle一般定义为8K 可以配置缓冲区管理缓冲区替换策略 bufferreplacementstrategy LRU Leastrecentlyused 最近最少使用MRU Mostrecentlyused 最近最常使用sOracle中 该策略可以配置不同的算法钉住的块 pinnedblock 当一个块上的更新操作正在进行时 一般不允许将该块写回磁盘 不允许写回磁盘的块被称为pinnedblock 强制写出 forecedoutputblocks 事务管理 Oracle数据库体系结构 SQL编码建议 换行SQL关键字必须大写SELECT FROM WHERE ORDERBY GROUPBY等子句必须另其一行写SQL语句中间不允许出现空行空格SQL内算数运算符 逻辑运算符连接的两个元素之间必须用空格分隔逗号之后必须接一个空格关键字 保留字和左括号之间必须有一个空格不等于统一使用 Oracle认为 和 是等价的 都代表不等于的意义 为了统一 不等于一律使用 表示SQL语句对表字段扩展的兼容性使用INSERT时 必须指定插入的字段名 主要是考虑以后表结构的变动减少子查询的使用子查询除了可读性差之外 还在一定程度上影响了SQL运行效率 请尽量减少子查询的使用 采用其他效率更高 可读性更好的方式替代适当添加索引以提高查询效率适当添加索引可以大幅度的提高检索速度 具体的优化方法见第8节性能优化部分 SQL优化建议 内容摘要数据库优化的几个环节什么样的SQL需要优化常见的问题发现问题的方法 SQL优化建议 数据库优化的几个环节物理存储IO均衡数据库规划合理 参数 表空间分布 数据表和索引的设计SQL语句的优化 SQL优化建议 什么样的SQL需要优化引发严重的等待事件 IO 锁 消耗大量的系统资源 CPU IO MEM 运行时间超长 排序段 回滚段占用太大 不能满足压力测试指标 SQL优化建议 常见的问题没有恰当的索引 全表扫描问题 索引不适当 索引低效 无用 重编译问题 程序开发问题 多表关联条件不当或关联太多分区表没有分析 未能使用索引 维护问题 死锁 应用逻辑问题 SQL优化建议 发现问题的方法从v session wait查看等待事件SQLPLUS使用AUTOTRACE查看执行计划在TOAD中直接查看执行计划从STATSPACK查看资源 CPU I O 消耗状况生成SESSIONTRACE文件 一般为DBA使用 用命令tkprof对TRACE文件进行分析 SQL优化建议 从等待事件中发现问题查看SESSIONWAIT的语句典型事件 DbFileSequentialRead典型事件 DbFileSequentialRead典型事件 LatchFree latch释放 SQL优化建议 查看SESSIONWAIT的语句setpagesize2000setlinesize110coleventformata25colprogramformata20selecta event substr b program 1 20 program b sid a p1 a p2 a p3fromgv session waita v sessionbwherea sid b sidanda eventnotlike SQL anda eventnotlike message anda eventnotlike time SQL优化建议 典型事件 DbFileScatteredRead 数据文件分散读取 这种情况通常显示与全表扫描相关的等待 一般表明该表找不到索引 或者只能找到有限的索引 特定条件下执行全表扫描可能比索引扫描更有效 但如果出现这种等待时 最好检查一下这些全表扫描是否必要 建议将小而常用的表CACHE到内存中 以避免一次又一次地重复读取它们 SQL优化建议 典型事件 DbFileSequentialRead 数据文件顺序读取 这一事件通常显示单个块的读取 如索引读取 表示表的连接顺序不佳 或者使用了不恰当的索引检查每个扫描是否必要的 并检查多表连接的连接顺序一般会消耗大量PGA内存 从而在顺序读取时导致大量等待 SQL优化建议 典型事件 LatchFree latch释放 latch是一种低级排队机制 用于保护系统全局区域 SGA 中共享内存结构 latch就像是一种快速地被获取和释放的内存锁 latch用于防止共享内存结构被多个用户同时访问 如果latch不可用 就会记录latch释放失败 大多数latch问题都与以下操作相关 不能使用绑定变量 库缓存latch 重复生成问题 重复分配latch 缓冲存储器竞争问题 缓冲器存储LRU链 以及缓冲存储器中的 热 块 缓冲存储器链 也有一些latch等待与bug 程序错误 有关当latch不命中率大于0 5 时 就应当研究这一问题 SQL优化建议 SQL语句的执行计划SQL语句的执行步骤ORACLE的优化器在SQLPLUS配置AUTOTRACE使用QUESTTOAD查看执行计划安装AUTOTRACE环境使用QUESTTOAD查看执行计划查看执行计划 SQL优化建议 SQL语句的执行步骤语法分析 分析语句的语法是否符合规范 衡量语句中各表达式的意义 语义分析 检查语句中涉及的所有数据库对象是否存在 且用户有相应的权限 视图转换 将涉及视图的查询语句转换为相应的对基表查询语句 表达式转换 将复杂的SQL表达式转换为较简单的等效连接表达式 选择优化器 不同的优化器一般产生不同的 执行计划 选择连接方式 ORACLE有三种连接方式 对多表连接ORACLE可选择适当的连接方式 选择连接顺序 对多表连接ORACLE选择哪一对表先连接 选择这两表中哪个表做为源数据表 选择数据的搜索路径 根据以上条件选择合适的数据搜索路径 如是选用全表搜索还是利用索引或是其他的方式 运行 执行计划 SQL优化建议 ORACLE的优化器ORACLE有两种优化器 基于规则的优化器 RBO RuleBasedOptimizer 和基于代价的优化器 CBO CostBasedOptimizer ORACLEV7以来缺省的设置应是 choose 即如果对已分析的表查询的话选择CBO 否则选择RBO 如果该参数设为 rule 则不论表是否分析过 一概选用RBO 除非在语句中用hint强制各 执行计划 的cost的计算根据 依赖于数据表中数据的统计分布 须要分析表和相关的索引 才能搜集到CBO所需的数据 SQL优化建议 在SQLPLUS配置AUTOTRACE SQL优化建议 查看执行计划 ExecutionPlan 0SELECTSTATEMENTOptimizer CHOOSE10TABLEACCESS FULL OF TEST Statistics 0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize547bytessentviaSQL Nettoclient655bytesreceivedviaSQL Netfromclient2SQL Netroundtripsto fromclient0sorts memory 0sorts disk 4rowsprocessed SQL优化建议 SQL重编译问题SQL共享原理SQL共享的三个条件PROC程序的SQL共享PROC程序中以下类型的语句不需进行变量绑定PROC程序的CLIENT参数存储过程的SQL共享SQL共享的数据库参数的利弊 SQL优化建议 SQL共享原理ORACLE将执行过的SQL语句存放在内存的共享池 sharedbufferpool 中 可以被所有的数据库用户共享当你执行一个SQL语句 有时被称为一个游标 时 如果它和之前的执行过的语句完全相同 ORACLE就能很快获得已经被解析的语句以及最好的执行路径 这个功能大大地提高了SQL的执行性能并节省了内存的使用 SQL优化建议 SQL共享的三个条件当前被执行的语句和共享池中的语句必须完全相同 包括大小写 空格 换行等 两个语句所指的对象必须完全相同 同义词与表是不同的对象 两个SQL语句中必须使用相同的名字的绑定变量 bindvariables SQL优化建议 PROC程序的SQL共享sprintf sqlstr insertintoscott test1 num1 num2 values d d n var1 n var2 EXECSQLEXECUTEIMMEDIATE sqlstr EXECSQLCOMMIT 使用绑定变量的语句strcpy sqlstr insertintotest num1 num2 values v1 v2 EXECSQLPREPAREsql stmtFROM sqlstr EXECSQLEXECUTEsql stmtUSING n var1 n var2 EXECSQLCOMMIT 动态表也可以使用以上方式避免重编译 SQL优化建议 PROC程序中以下类型的语句不需进行变量绑定for i 0 i 10000 i EXECSQLinsertintotab test1 id selectidfromtab testwhereid i EXECSQLCOMMIT 本语句在数据库解析后的结果insertintotab test1 id selectidfromtab testwhereid b0 SQL优化建议 PROC程序的CLIENT参数 一 PROC编译参数 控制客户端游标缓存 使用的是本地的内存 与服务器无关HOLD CURSOR yes 保留游标在缓存中 默认为noRELEASE CURSOR no 释放缓存 默认为no这两个参数也可以在程序中设置EXECORACLEOPTION HOLD CURSOR NO EXECORACLEOPTION RELEASE CURSOR YES 数据库参数OPEN CURSORS指保留在SERVER的shared