分类 ORACLE 下的文章

plsql是最常用的数据库sql查询结果表格导出工具,但是由于客户端和服务端之间的网络传输验证问题,存在一个结果过大,plsql无法保存。这个时候需要将查询结果保存到服务器的本地excle文件。操作如下:
sqlplus 能生产xls的excel文件

connect / as sysdba;
SET MARKUP HTML on SPOOL OFF
set heading off
set echo off
set term off
spool ./abc.xls
select * from tab;
spool off;
quit;

适用于 8i、11g,其他版本未测试,理论上也都一样。
更多参数:
set pagesize 0 //去掉头顶的空行
set linesize 250 //设置行字符数
set trims on //去掉空字符
set echo off //关掉回显
set heading off //关掉标题行
set termout off //关掉终端显示
set feedback off//关掉行数显示
更多相关设置,查看官网:https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch7.htm#CHDCECJG

概述

数据库的性能瓶颈通常是由于某些sql语句效率不高,在大数据量或者高并发的情况下,会拖慢整体性能。要找到这些语句可以通过查找报告中的“SQL Statistics”章节,此章节按各种资源分别列出对资源消耗最严重的SQL语句,并显示它们所占统计期内全部资源的比例,这给出我们调优指南。例如在一个系统中,CPU资源是系统性能瓶颈所在,那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中,调优的目标应该是physical IOs最多的SQL语句。

awr报告中SQL优化主要查看如下内容:

SQL ordered by Elapsed Time
总时间消耗最长的sql语句

SQL ordered by CPU TimeCPU
总耗时最长的sql语句

SQL ordered by Gets
读取缓存最多的sql语句

常见sql调优方法:

1、根据查询条件创建索引。
2、用exists、not exists替代in和not in。
3、用表连接代替exists 。
4、减少order by 、union的使用,这两个关键字都很耗性能。
5、避免使用distinct,效率较低。
6、select 避免使用* 。
7、减少访问数据库的次数,绑定变量,预处理。
8、用where子句代替having子句。
9、避免在索引列上使用is null 和is not null。将会使索引失效。
10、避免在索引列上使用计算。如select…from pay where money*10>100

Cache Sizes
shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析(或编译)后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。

Load Profile
显示数据库负载概况,将之与基线数据比较才具有更多的意义,如果每秒或每事务的负载变化不大,说明应用运行比较稳定。单个的报告数据只说明应用的负载情况,绝大多数据并没有一个所谓“正确”的值,然而Logons大于每秒1~2个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。
Redo size:每秒/每事务产生的redo大小(单位字节),可标志数据库任务的繁重程序。
Logical reads:每秒/每事务逻辑读的块数
Block changes:每秒/每事务修改的块数
Physical reads:每秒/每事务物理读的块数
Physical writes:每秒/每事务物理写的块数
User calls:每秒/每事务用户call次数
Parses:SQL解析的次数
Hard parses:其中硬解析的次数,硬解析太多,说明SQL重用率不高。
Sorts:每秒/每事务的排序次数
Logons:每秒/每事务登录的次数
Executes:每秒/每事务SQL执行次数
Transactions:每秒事务数
Blocks changed per Read:表示逻辑读用于修改数据块的比例
Recursive Call:递归调用占所有操作的比率
Rollback per transaction:每事务的回滚率
Rows per Sort:每次排序的行数
注:
Oracle的硬解析和软解析
  提到软解析(soft parse)和硬解析(hard parse),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:
  1、语法检查(syntax check)
  检查此sql的拼写是否语法。
  2、语义检查(semantic check)
  诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
  3、对sql语句进行解析(parse)
  利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)。
  4、执行sql,返回结果(execute and return)
  其中,软、硬解析就发生在第三个过程里。
  Oracle利用内部的hash算法来取得该sql的hash值,然后在library cache里查找是否存在该hash值;
  假设存在,则将此sql与cache中的进行比较;
  假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。
  诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。

Instance Efficiency Percentages (Target 100%)

本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。
Buffer Nowait表示在内存获得数据的未等待比例。
buffer hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统,如果此值低于80%,应该给数据库分配更多的内存。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低(可参考90%阀值),考虑增加LOG BUFFER。
library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率,当应用程序调用SQL或存储过程时,Oracle检查Library Cache确定是否存在解析过的版本,如果存在,Oracle立即执行语句;如果不存在,Oracle解析此语句,并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析,增加CPU消耗,降低性能。如果library hit ratio低于90%,可能需要调大shared pool区。
Latch Hit:Latch是一种保护内存结构的锁,可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%,否则意味着Shared Pool latch争用,可能由于未共享的SQL,或者Library Cache太小,可使用绑定变更或调大Shared Pool解决。
Parse CPU to Parse Elapsd:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),越高越好。
Non-Parse CPU :SQL实际运行时间/(SQL实际运行时间+SQL解析时间),太低表示解析消耗时间过多。
Execute to Parse:是语句执行与分析的比例,如果要SQL重用率高,则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
In-memory Sort:在内存中排序的比率,如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。
Soft Parse:软解析的百分比(softs/softs+hards),近似当作sql在共享区的命中率,太低则需要调整应用使用绑定变量。

Shared Pool Statistics
Memory Usage %:对于一个已经运行一段时间的数据库来说,共享池内存使用率,应该稳定在75%-90%间,如果太小,说明Shared Pool有浪费,而如果高于90,说明共享池中有争用,内存不足。
SQL with executions>1:执行次数大于1的sql比率,如果此值太小,说明需要在应用中更多使用绑定变量,避免过多SQL解析。
Memory for SQL w/exec>1:执行次数大于1的SQL消耗内存的占比。

Top 5 Timed Events
显示了系统中最严重的5个等待,按所占等待时间的比例倒序列示。当我们调优时,总希望观察到最显著的效果,因此应当从这里入手确定我们下一步做什么。例如如果‘buffer busy wait’是较严重的等待事件,我们应当继续研究报告中Buffer Wait和File/Tablespace IO区的内容,识别哪些文件导致了问题。如果最严重的等待事件是I/O事件,我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O,并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待,就需要察看详细的LATCH统计识别哪些LATCH产生的问题。
在这里,log file parallel write是相对比较多的等待,占用了7%的CPU时间。
通常,在没有问题的数据库中,CPU time总是列在第一个。

版本号说明

Oracle 数据库的发行版本号(release number)一般由五位数字组成

第一位数字是我们最常提到的一个大版本标识
第二位数字代表数据库维护版本发行号
第三位数字代表了 Oracle Application Server (OracleAS)的发行版,对于 Oracle database 软件而言这一位总是为 0
第四位数字代表了某个组件的发行版本号。
第五位数字代表了平台相关发行版本号。

补丁类型介绍

Release
标准产品发布。如 Oracle Database 10g Release 2
Patch Set Release
PSR:这是在主版本号上发布的补丁集,修复了较多的 Bug
Patch Set Update
PSU:Oracle 选取在每个季度用户下载数量最多,并且得到验证具有较低风险的补丁放入到每个季度的 PSU 中,修复比较严重的一些问题,包含每个季度的 CPU,是累积型的。
Critical Patch Update
CPU 补丁:每季度发布一次,用来修复安全方面的些补丁,是累积型的。目前(2012 年 10 月)已经更名
为 Security Patch Update (SPU)
Interim Patch/One-Off Patch 小补丁
Merged Patch 合并的补丁:当几个小补丁之间有冲突,不能同时安装的时候,需要提供这种 Merged Patch。补丁冲突主要是由于 2个或者多个补丁修改同一个文件,但是修改的内容是不同的。
BundlePatch(BP)
补丁集,修复多个 Bug。
Diagnostic Patch
诊断补丁:这类补丁不是用来解决问题的,而是用来寻找问题的原因的。这类补丁只在 Oracle 技术支持部门要求安装时,才需要安装。在得到需要的诊断信息后 ,应立即卸载这一补丁。
Composite Patch
Composite Patche 的改进包括减少补丁安装时间,减少回滚以前应用的overlay patches的需要。 新的Composite Patches 格式,使以前PSU应用的overlay patches和新安装的PSU并存成为可能。

准备工作
数据备份:expdp、rman、冷备

windows下补丁升级
aix下补丁升级
linux下补丁升级
rac集群环境下补丁升级
详见:

操作思路

安装、测试、对比

环境检查

SQL> show parameter session;
SQL> show parameter processes;
alter system set processes = 3000 scope = spfile;
alter system set sessions=3800 scope=spfile;

SQL> show parameter sharing ---->有三种不同的状态
SQL> alter session set cursor_sharing=exact; 通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作。
SQL> alter session set cursor_sharing=similar;similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL。
SQL> alter session set cursor_sharing=force; force是在任何情况下,无条件重用SQL。
------------------------------创建AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
drop tablespace soe including contents and datafiles;

测试前后分别取一次快照
安装swingbench测试软件,直接解压缩即可运行。
unzip -x swingbench25919.zip
 
注意要按照java8
yum -y install java-1.8.0-openjdk*
进入swingbench/bin目录执行oewizard导入1G测试数据,并修改数据库连接名和DBA密码
数据导完之后在该目录下运行swingbench执行测试,修改数据库连接名,用户连接数设置为300,测试时间设置为10分钟
修改Distributed Controls用于测试过程中搜集测试监控信息,修改完之后测试连接是否正常,并可以统计主机的cpu  disk IO 信息
参考信息
SwingBench下载:
http://www.dominicgiles.com/downloads.html
参考相关文章:
http://www.linuxidc.com/Linux/2016-04/130297.htm
http://blog.csdn.net/xiaofan23z/article/details/7978998
链接:https://pan.baidu.com/s/1o4CL0lENfmuEEFj-VwvDGQ
提取码:bwwh

压测流程
1、 应用模拟不同场景下的压力访问
10、50、100、200、500、1000
2、 数据库端启动osw 进程,每10s一次,报告cpu、io、内存结果进行压缩保存本地
3、 awr快照每15分钟保存一次
4、 使用sql查询获取会话超过200的时间点
5、 使用awr获取压测时间点的报告信息,并分析性能问题
6、 根据awr的top sql 分析sql tuning monitor报告,反馈给开发,或联系开发共同商讨。
7、 使用ash报告 addm报告对sql进行优化。