SQLstatementperformanceChangemanagement
2）实例调优（DBA负责）
MemoryDatabasestructure
Instanceconfiguration
3）操作系统（DBA与系统管理员合作）
I/O
Swap
Parameters
三、调优方法论
OWI全称OracleWaitInterface，即基于等待事件的调优方法。等待事件到11g已发展到近1000个。从10g开始，性能调优的重点已经不再单纯是提高缓存击中率了。
OWI是一种用于定位processbottlenecks（即waitevents）的方式：
通过解除或者降低WaitEvents，可以直接提高系统工作效率。这些数据都被记录在动态视图中或AWR报告里。
Oracle推荐使用OWI方法，通过等待事件的分析，直接消除问题。
调整目标具有三个特征：
1）具体的（Specific）
2）可测的（Measurable）
3）可实现的（Achievable）
OWI方法论总结起来就是三点：
1）自顶向下，抓主要矛盾
2）选择可获得最大收益的事件入手
3）目标达到后见好就收
第二章：基本调优工具
一、性能调优工具
1）Dynamicperformanceviews--动态性能视图
2）AWR或Statspack
LoadProfile--系统负荷
InstanceEfficiencyPercentages--实例有效率
SharedPoolStatistics--共享池统计
Topwaitevents--突出的等待事件
SQLstatistics
等等
3）告警日志
Alertlog文件和Tracefiles文件
4）EnterpriseManagerPages--OEM
5）诊断包和调优包
二、DBTimemodel
1、什么是DBTimemodel
"ThemostimportantofthetimemodelstatisticsisDBtime.Thisstatisticsrepresentsthetotaltimespentindatabasecallsandisaindicatorofthetotalinstanceworkload.ItiscalculatedbyaggregatingtheCPUandwaittimesofallsessionsnotwaitingonidlewaitevents(non-idleusersessions).DBtimeismeasuredcumulativelyfromthetimethattheinstancewasstarted."
DBtime反应的是所有user使用的数据库资源的总和,即：DBtime=DBCPU+DBWaittime(no-idletime)。
idletime比如处于连接状态的空闲session不包括在DBWaittime。
在一个正常的系统中一般来说DBtime要远远大于backgroundelapsedtime。
而Waittime的急剧增加又可能反映了一个资源争用的迹象。
注意，资源争用通过增加更多的处理器，或集群节点，其作用往往是非常有限的，有时甚至可能适得其反。
在DBtime的统计信息中，sqlexecuteelapsedtime和parsetimeelapsed以及DBCPU，这三项常常会占据90%以上的DBtime，而其中sqlexecuteelapsedtime又应该会在95%以上，值得注意的是DBCPU和sqlexecuteelapsedtime是有交集的，因此你会看到在一份AWR报告中有出现DBCPU+sqlexecuteelapsedtime超过DBtime的情况。
3、两个直接反应DBtime统计信息的视图
v$sys_time_model
v$sess_time_model
Secord秒
Centisecond厘秒--百分之一秒
Millisecond毫秒--千之一秒
Microsecond微秒--百万分之一秒
三、统计信息和等待事件
在Oracle数据库中，“统计信息”和“等待事件”是性能优化目标的重要原始数据。它们都是累计的信息。
一）统计信息的概念：
数据库的活动在内存中产生了大量的信息，把这些信息分门别类的统计出来，就是所谓的统计信息。
SYS@prod>selectcount(distinctname)statistcsfromv$sysstat;
STATISTCS
----------
604
二）等待事件的概念
先要弄清楚什么是事件
Oracle根据数据库各类活动的特性定义了许多事件（Event），
每个事件对应一个事件name,每个数据库版本的事件数量是不同的。
本版本是11.2.0.1的,总共有多少个事件呢？
SYS@prod>selectcount(distinctname)eventsfromv$event_name;
EVENTS
1118
现在回答什么是等待事件：
当一个进程无法顺利执行，那么只能通过排队等待某种资源，因为有堵塞才有等待。等待一定发生在共享资源上，一般分两种原因：
（1）资源不足
（2）资源争用
SYS@prod>selectcount(distinctevent)fromv$system_event;
COUNT(DISTINCTEVENT)
--------------------
80
本系统没跑业务，这里有等待事件80个，它是自上次实例启动后到目前为止一共记录了80个等待event，它们都是累计值。
如果你这时跑一个大的并发访问的应用，出现资源不足或资源争用，那么还可能增加其他的等待事件，一些事件的统计值也会累计叠加。
资源不足的解决方案可以增加硬件，如CPU、MEMORY等
资源争用的解决方案需要从应用层面和数据库结构层面想办法
资源争用不能用资源不足的办法解决：
"Whencontentionisevidencedbyincreasedwaittime,addingmoreCPUstoanode,ornodestoacluster,wouldprovideverylimitedbenefit."
统计信息和等待事件之间有一定的关系，但也不是包含关系，更不是一对一关系，它们侧重点不同，细分后命名方法也不同。从下面两个视图的对比就可说明问题。
select*fromv$statname;
select*fromv$event_name；
三）统计视图和等待视图
从三个方面（维度）反映重要的统计视图
1）基于系统级
v$sysstat
2）基于session级
v$sesstat所有session分别列出统计信息，每一行是某个session对应的某种统计信息
v$mystat当前session统计信息
3）基于service级
v$service_stats
此外还有一个常用的视图
v$statname此视图提供一个统计信息的完整列表，每行对应一种统计信息
四）示例：查询日志累计统计信息
第一步,确定session1的sid号
[oracle@cuug~]$sqlplus/assysdba
SYS@prod>grantselectonv_$mystattoscott;
SYS@prod>connscott/scott
SCOTT@prod>selectsidfromv$mystatwhererownum=1;
SID
46
第二步，在plsqldevelop端查看该session下的有关redo的两项统计信息
SQL>
selectss.sid,sn.name,ss.valuefromv$sesstatss,v$statnamesn
wheress.STATISTIC#=sn.statistic#
andsn.namein('redoentries','redosize')andss.sid=46;
SIDNAMEVALUE
---------------------------------------------
46redoentries630
46redosize80264
第三步，在该session下做一个dml操作
观察update前后日志的变化
SCOTT@prod>updateempsetsal=sal+1000whereempno=7788
SCOTT@prod>commit;
第四步，重复第二步并查看结果
46redoentries631
46redosize80780
可以看到46号session的update的动作产生的日志统计信息：
产生redoentries=1（631-630=1）
产生redosize=516（80780-80264）
五）从三个方面反映重要的等待事件视图
v$system_event
v$session_event----所有session分别列出等待事件，每一行是某个session对应的某种等待事件
v$session_wait----所有session当前正在等待的事件
v$service_event
另外提供一个等待事件的完整列表，每行对应一种等待事件
v$event_name
理解事件的三个参数
selectname,parameter1,parameter2,parameter3fromv$event_namewherenamelike'%bufferbusy%'；
四、常见的几个等待事件
1、Bufferbusywaits等待事件
这个等待事件的产生说明了一个会话曾经等待一个Buffer（数据块）
有两种情形是：
(1)当一个会话试图修改一个Buffer，但这个Buffer正在被另一个会话修改时。
热块是典型的是资源争用，分析热块产生原因，才可对症下药：以下为热块发生的部位：
①表块，②索引块，段头块（freelist）,undo块等
(2)当一个会话需要读取一个Buffer，而这个Buffer正在被另一个会话从磁盘读取到内存中时。
在11g的版本中，这种等待已经被独立出来，以readbyothersession命名等待事件。
Bufferbusywaits等待事件常见于数据库中存在热块的时候，当多个用户频繁地读取或者修改同样的数据块时，这个等待事件就会产生。
2、Freebufferwaits等待事件
当一个会话将数据块从磁盘读到dbbuffer中时，它需要找到空闲的内存空间来存放这些数据块，
当内存中没有空闲的空间时，就会产生这个等待；
会话在做一致性读时，需要构造数据块在某个时刻的前映像（image），
此时需要申请内存来存放这些新构造的数据块，但内存中无法找到这样的可用内存块。
当数据库中出现比较严重的freebufferwaits等待事件时，可能的原因是：
(1)databasebuffercache太小，
(2)内存中的脏数据太多，DBWR无法及时将这些脏数据写到磁盘中以释放空间
3、Readwaits的几个等待事件
①dbfilescatteredread
这里指的是读取的数据块在内存中是以连续的方式存放的。全表扫描和indexfullscan是其典型的代表。这是在一次性读取多个连续的BLOCK的时候，产生的等待事件。dbfilesequentialread是数据库中最常见的等待事件，一个状态良好的系统，这个等待应该占比较高的比重。
②)dbfilesequentialread
当Oracle需要每次I/O只读取单个数据块这样的操作时，最常见的情况有索引的访问,以ROWID的方式访问表中的数据.
③dbfileparalleread
同步的multiblockread,需要多CPU支持。
④directpathread(11g新特性)
大表走全表扫描可能使用directpathread方式，即全表扫描全部采用物理读，绕过SGA，以减轻对buffercache的压力
隐含参数：_serial_direct_read=trun|false可以开关此功能。
4、Enq:TX-rowlockcontention
Enqueue是lock的另一种描述语。
5、logfileswitch：
通常是因为归档速度不够快。表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。LogfileSwitch主要包含两个子事件:
①logfileswitch(archivingneeded)
这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。
②logfileswitch(checkpointincomplete)
当日志组都写完以后，LGWR试图写第一个logfile，如果这时数据库没有完成写出记录在第一个logfile中的dirty块时(例如第一个检查点未完成)，即没有Inactive日志组可重用，该等待事件通常表示你的DBWR写出速度太慢或者IO存在问题。
6、logfilesync：
表现为commit很慢，原因还是LGWR无法迅速写出这些日志条目。如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率,为减少这个等待事件，须一次提交更多记录，或将重做日志置于较快的磁盘上，以降低归档对LGWR的影响。
五、热块争用实验
Bufferbusywaits等待事件的测试
1）createtablescott.emp1asselect*fromscott.emp;
SQL>selectdbms_rowid.ROWID_RELATIVE_FNO(rowid)fn,dbms_rowid.rowid_block_number(rowid)bl,emp1.rowid,emp1.empnofromscott.emp1whereempnoin(7788,7900);
FNBLROWIDEMPNO
------------------------------------------------
4396AAARW6AAEAAAAGMAAH7788
4396AAARW6AAEAAAAGMAAL7900
证明两行记录都在386号块上。
session1
SQL>selectsidfromv$mystatwhererownum=1;
125
session2
132
测试写/写:
SESSION1运行:
----------------------------------------------------------------
session1运行：
begin
foriin1..4000000loop
UPDATEscott.emp1SETsal=sal+0whereempno=7788;
commit;
endloop;
end;
/
session2运行：
foriin1..400000loop
UPDATEscott.emp1SETsal=sal+0whereempno=7900;
查看buffer_busy_wait和latch:cachebufferschains
selectevent,total_waitsfromv$system_eventwhereeventin('bufferbusywaits','latch:cachebufferschains');
第三章使用AWR
一、什么是AWR
AWR工作时是由后台进程MMON负责，缺省下于每1小时生成一个内存统计的快照，并写入磁盘上的sysaux表空间，快照不能移动到其他位置，快照也会作为ADDM的原始数据，默认情况下，Oracle将快照保留8天。
二、AWR的内容
1）AWR收集数据库有关性能信息：它是新的数据库自动调优机制的核心，这些信息包括以下内容：
*基本统计数据，也是v$sysstat和v$sesstat视图中收集的系统和会话的统计信息；
*对象的统计信息，
*等待统计数据，v$system_event等（来自V$session视图中的几个新添加的字段）
*ASH统计信息，包含近期会话活动的历史记录
*数据库特性利用的统计数据
*各种管理顾问会话的结果，如ADDM、SegmentAdvisor、SqlAccessAdivisor等
*操作系统的统计数据，如I/O和内存的利用率
2）与AWR有关的参数
SQL>showparameterstatistics_level
NAMETYPEVALUE
-----------------------------------------------------------------------------
statistics_levelstringTYPICAL
若参数STATISTICS_LEVEL设置为TYPICAL或ALL将启用AWR采集数据库统计信息。ALL参数收集信息最全，参数的默认值是TYPICAL，
如果STATISTICS_LEVEL设为BASIC，将禁用自动收集快照和运行顾问。但你仍可以通过DBMS_WORKLOAD_REPOSITORY包来手动获得AWR统计信息。
三、AWR报告（OEM提供）
四、手动生成AWR快照和报告
第一步，列出数据库中有多少快照
SYS@prod>select*fromdba_hist_snapshotorderby1desc;
第二步，创建一个快照
SYS@prod>execdbms_workload_repository.create_snapshot;
第三步，根据两个快照建立一个AWR报告
SYS@prod>@/u01/oracle/rdbms/admin/awrrpt.sql
看到提示：SpecifythenumberofdaysofsnapshotstochoosefromSnap
InstanceDBNameSnapIdSnapStartedLevel
--------------------------------------------------------
prodPROD7078月201513:461
8078月201515:001
9078月201516:001
10078月201517:001
11088月201512:151
12088月201513:001
13088月201514:001
注意：有空行的地方是数据库关闭过，再次启动后所有统计重置，所以报告使用的起始和结束快照之间不能跨越空行。
第四步、得到html格式的报告，缺省的路径是/home/oracle,可以在Linux下进入图形界面使用缺省的火狐浏览器查看，也可以把它下载到win7下使用浏览器看。
第四章定位问题
一、如何看AWR报告.pdf
ExecutetoParse:是语句执行与分析的比例，SQL重用率越高该比例越高
二、AWR顾问（Advisor）
一）概念：顾问就是通过分析AWR捕获的数据，提出改进性能的建议。ADDM本身就是顾问，它的报告里还会建议你找其他的顾问。具体病症还要看专科。
二）Oracle11g主要的一些Advisor
1）SQLAdvisors其中包括：
SQLTuningAdvisor:对单个SQL语句提供调优建议,生成sqlprofile，sql语句执行路径分析，sql语句结构分析
SQLAccessAdvisor:评估SQL语句对数据库负荷的影响，提供建议。如index,partition,materializerview等
SQLRepairAdvisor:对可能的oracle内部错误，如ORA-600需要的patch(补丁）提出建议
3）SegmentAdvisor:提供段收缩命令（shrink)。释放未使用的空间
4）UndoAdvisor:为undo表空间的大小提供建议，如避免快照太旧的问题。从v$undostat取数据。
三）Advisor的API's程序包
首先是DBMS_ADVISOR:不过，它只是顾问管理中一部分包的套件，有一些Advisor有它们自己的包，如：
AutomaticDatabaseDiagnosticMonitor(DBMS_ADDM)
SQLPerformanceAnalyzer(DBMS_SQLPA)
SQLRepairAdvisor(DBMS_SQLDIAG)
SQLTuningAdvisor(DBMS_SQLTUNE)
第五章使用度量和预警
一、AWR度量（metric)：
两个或多个统计数据综合的结果。它是衡量累计性能统计数据变化率的统计指标。
度量有两个主要作用:
（1）几乎所有的advisor都使用metrics诊断性能问题并给出调优建议。
（2）基线的metrics是服务器产生预警特性的基础。
二、Oracle自动预警机制
自适应阈值可以帮你以最低的开销来监控和检测性能问题。自适应阈值只能从系统移动窗口基线（systemmovingwindow）捕获到的metric值里得到的统计信息
为系统metric自动设置预警和关键预警(warningandcriticalalert)的阈值。这些统计信息每周会重新生成，并可能随系统性能变化而改变，产生新的阈值。
阈值的呈现方式：
1）重要性级别(概率)：为了体现异常程度，阈值被设为一个统计学中的百分位来观察基于移动窗口基线数据的阈值以上的值。通俗的理解就是触发红线（或黄线）的概率，百分位能指定为以下几种：高(0.95)，非常高(0.99)；严重的(0.999)；极端的(0.9999)。计算出的阈值概率越低，越不容易达到，也就越不容易触发告警。
2）最大值的百分比：阈值以最大值的百分比方式设计，
3）固定值
第六章AWR基线
一）基线的作用
系统中的度量用来衡量系统工作状态，但到底是好还是不好，基线的度量就是性能好坏的参考标准。
Oracle数据库中包含了二种类型的基线：
第一类，动态基线：
即移动窗口基线(MovingWindowBaseline)
Oracle设计移动窗口基线是为了让系统动态的获得基线的度量。
只有一个移动窗口基线，即Oracle内置的systemmovingbaseline,
systemmovingbaseline的作用：使用保留期内所有AWR数据，可以产生并调整自适应阈值(AdaptiveThresholds)
第二类：静态基线
即用户自定义的基线，又可以分为两种
1）静态基线（使用已有的一对快照，过去时）
2）基线模板(BaselineTemplates)（结束快照还未生成，将来时）
①单一（Single）:
②重复的（Repeating）:
二、基线示例
1）建立一个singe基线模板的例子
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
START_TIME=>TO_TIMESTAMP(SYSDATE+2),
END_TIME=>TO_TIMESTAMP(SYSDATE+10),
BASELINE_NAME=>'Mybase1',
TEMPLATE_NAME=>'Mytemp1',
EXPIRATION=>NULL);
2）建立一个repeat基线模板的例子
BEGIN
day_of_week=>'monday',
hour_in_day=>17,
duration=>3,expiration=>30,
start_time=>'2018-06-0117:00:00',
end_time=>'2018-12-3120:00:00',
baseline_name_prefix=>'baseline_2017_mondays_',
template_name=>'template_2017_mondays');
END;
第七章使用AWR的工具
一、Oracle的自动维护任务(Autotask)
资源管理和任务调度的概念：
1)Autotask的三个job：
通过OEM->Server->OracleScheduler->AutomatedMaintenanceTasks查看。
AutomaticOptimizerStatisticsCollection，
AutomaticSegmentAdvisor，
AutomaticSQLTuningAdvisor。
2)预定义维护窗口
有7种预定义窗口，通过OEM->Server->OracleScheduler->WindowGroups可以查看，当一个维护窗口打开时，
数据库激活DEFAULT_MAINTENANCE_PLAN，3个自动维护任务运行在ORA$AUTOTASK_SUB_PLAN子计划之下。
3)Autotask使用的程序包
Oracle不给3种自动维护任务分配永久的Scheduler作业，所以不能用DBMS_SCHEDULER程序包管理这些任务。
如果想修改Autotask必须使用DBMS_AUTO_TASK_ADMIN程序包。
二、ADDM(AutomaticDatabaseDianosticMonitor)
一）概念：
ADDM根据AWR每小时采集的数据，自动发现快照间隔期间的性能问题。
与ADDM有关的参数：
control_management_pack_access缺省是DIAGNOSTIC+TUNING，如果设成NONE，则ADDM关闭。
默认情况下ADDM报告保存30天。
三、SQLadvisor和Segmentadvisor
四、OEM顾问实验
一）ADDM报告生成：
SQL>@/u01/oracle/rdbms/admin/addmrpt.sql这是单独生成ADDM报告的脚本
二）ADDM测试示例：
1）在OEM中了解最新AWR快照号和ADDM报告
2）模拟业务高峰
2.1）建立一个大表和小表，分别叫bigtable,smalltable.
sys:
grantconnect,resourcetotimidentifiedbytim;
tim:
createtablebigtable(idnumber(10),namevarchar2(100));
createtablesmalltable(idnumber(10),namevarchar2(100));
2.2）大表中插入3百万行记录
foriin1..2500000loop
insertintobigtablevalues(i,'test'||i);
ifmod(i,100)=0then
endif;
analyzetablebigtablecomputestatistics;
2.3）小表中插入1000条记录
foriin1..1000loop
insertintosmalltablevalues(i,'test'||i);
3）清除sharedpool，buffercache
altersystemflushshared_pool;
altersystemflushbuffer_cache;
关联查询
selectcount(*)fromsmalltableawherea.id=(selectb.idfrombigtablebwhereb.id=a.id);需要1.5--2分钟左右
4）手工即时生成AWR快照
execdbms_workload_repository.create_snapshot;
查看ADDM
OEM：Server-->AdvisorCentral-->AdvisorTaskResults（画面下方）已经显示了一个ADDM结果，
我们可以尝试单独生成一个ADDM报告。方法：
Server-->AdvisorCentral-->ADDM-->RunADDMtoanalyzepastperformance
结合图型高峰段选择PeriodStartTime和PeriodEndTime-->ok
可以看到报告生成的名称是task_nn,然后有Impact(%)，Finding，Occurrences等信息
进入第一行，即Finding是TopSQLbyDBTime,里面是Recommendations，
5）可以通过SQLTuningAdvisor分析该语句，得到推荐是在bigtable上建立索引。
6）可以通过SQLAccessAdvisor分析该语句，得到推荐是在bigtable上建物化视图。
7）可以通过SegmentAdvisor做表空间级shrink
到底如何优化，最后还是自己拿主意。
五、什么是ASH(ActiveSessionHistory)
Oracle的会话状态可以分为3种，
1）oncpu,
2）waiting,
3）idle（非活动状态）
实验1：测试oncpu
session1：取SID
SYS@prod>selectsidfromv$mystatwhererownum=1;
49
session2：观察结果inatcive
SYS@prod>selectsid,serial#,statusfromv$sessionwheresid=49;
session1：制造一个死循环
declare
aint;
loop
a:=a+1;
session2：观察结果atcive
SYS@prod>altersystemkillsession'49,157';
实验2：测试waiting
Session1:观察session1状态为inactive
SYS@prod>selectsid,serial#,statusfromv$sessionwheresid=1;
SIDSERIAL#STATUS
-----------------------------------------
49157INACTIVE
SYS@prod>updatescott.emp1setsal=10000;
Session2：
SYS@prod>selectsid,serial#fromv$sessionwheresid=1;
139120INACTIVE
SYS@prod>updatescott.emp1setsal=5000whereempno=7788;被锁住
Session3:观察session2状态为active，阻塞session2的是session1
SYS@prod>selectsid,serial#,status,BLOCKING_SESSIONfromv$sessionwheresid=139;
SIDSERIAL#STATUSBLOCKING_SESSION
-----------------------------------------------------------------------
139120ACTIVE49
ASH收集的是活动会话的样本数据
v$session包括了所有的（三种状态）当前会话，它每秒采样一次，那么ASH就以v$session为数据源，只记录活动会话信息，不活动的会话不会记录，记录数据在SGA缓冲区中。最终将ASH信息存入了AWR库。有关ASH数据采集由后台进程MMNL来完成的。
手动生成ASH报告
ASH反映的是内存中activesession的状态，它对v$session每秒采样一次，ASH信息就在内存，可以从v$active_session_history看到。
PERFSTAT@prod>selectcount(*)fromv$active_session_history;
COUNT(*)
1877
生成ASH报告：
SQL>@/u01/oracle/rdbms/admin/ashrpt.sql
ASHSamplesinthisWorkloadRepositoryschema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OldestASHsampleavailable:09-8月-1515:11:56[105minsinthepast]
LatestASHsampleavailable:09-8月-1516:55:49[1minsinthepast]
SpecifythetimeframetogeneratetheASHreport
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enterbegintimeforreport:
--Validinputformats:
--Tospecifyabsolutebegintime:
--[MM/DD[/YY]]HH24:MI[:SS]
--Examples:02/23/0314:30:15
--02/2314:30:15
--14:30:15
--14:30
--Tospecifyrelativebegintime:(startwith'-'sign)
---[HH24:]MI
--Examples:-1:15(SYSDATE-1Hr15Mins)
---25(SYSDATE-25Mins)
Defaultsto-15mins
Entervalueforbegin_time:-5
Reportbegintimespecified:
Enterdurationinminutesstartingfrombegintime:
DefaultstoSYSDATE-begin_time
PressEntertoanalyzetillcurrenttime
Entervalueforduration:
Thedefaultreportfilenameisashrpt_1_0809_1705.html.Tousethisname,
press
Entervalueforreport_name:/home/oracle
第三部分SQL性能分析
第八章使用SQL性能分析
一）真正应用测试一览
二）一个SPA的示例
示例包括两项内容：
1、预测参数在不同值下的性能表现。
2、评估走全表和走索引两种访问路径性能差异
1）准备工作
（1）改sh用户口令，解锁账户
SYS@prod>alterusershidentifiedbyshaccountunlock;
（2）建scott.emp1表，该表没有索引。
SYS@prod>createtablescott.emp1asselect*fromscott.emp;
SYS@prod>insertintoscott.emp1select*fromscott.emp1;
SYS@prod>/
3584rowscreated.
SYS@prod>commit;
SYS@prod>grantselectonscott.emp1topublic;
（3）保证cursor_sharing参数是EXACT
SYS@prod>showparametercursor_sharing
cursor_sharingstringEXACT
（4）建立一个shell脚本，产生SQL工作负荷。
$vimysts.sh
#!/bin/bash
CNT=1
while[$CNT-lt20]
do
sqlplussh/sh< SELECT ch.channel_class,c.cust_city,t.calendar_quarter_desc, SUM(s.amount_sold)sales_amount FROMsaless,timest,customersc,channelsch WHEREs.time_id=t.time_idAND s.cust_id=c.cust_idAND s.channel_id=ch.channel_idAND c.cust_state_province='CA'AND ch.channel_descin('Internet','Catalog')AND t.calendar_quarter_descIN('1999-01','1999-02','2000-03','2000-04') GROUPBYch.channel_class,c.cust_city,t.calendar_quarter_desc; select*fromscott.emp1whereempno=$CNT; exit EOF CNT=`expr$CNT+1` done --------------------------------------------------------------------- 2)改optimizer_features_enable参数 这个会影响一组性能特性，设的越高支持的特性越多，我们先把它设成10g的参数，然后运行调优集后再升到11g再对比前后的变化。 SYS@prod>altersystemsetoptimizer_features_enable='10.1.0.3'; 3)EM建立调优集job叫my_sts,采集数据 （1）LoadMethods incrementallycaptureactiveSQLstatementandloadSQLstatementsitotheSQLtuningset SpecifythedurationwithinwhichtheSQLstatementswillbecollected... Duration项选5minutes Frequency项选5seconds （2）FilterOptions ParsingSchemaName------>Value项下选sh （3）Review ------------------------------------------------------------------------------------------------------------------ SQLTuningSetNameMY_STS OwnerSYS Description CreateanemptySQLtuningsetNo LoadMethodsIncrementallycaptureactiveSQLstatementsoveraperiodoftimefromthecursorcache Duration5MINUTES Frequency5SECONDS FilterConditionsUPPER(PARSING_SCHEMA_NAME)='SH' JobNameCREATE_STS_MonSep7_165617_191 ScheduledStartTimeRunImmediately -------------------------------------------------------------------------------------------------------------------- （4）Submit 3)执行mysts.sh脚本 $shmysts.sh RefreshEM里SQLTuningSets---》MYSTS--》SQLCount看到已经有捕捉到的SQL语句数量 4)把采集下STS数据打包，然后传到测试系统下： SYS@prod>execDBMS_SQLTUNE.CREATE_STGTAB_SQLSET('MY_STS_TAB','SH');其实就是在sh用户下建一个表叫MY_STS_TAB SYS@prod>execDBMS_SQLTUNE.PACK_STGTAB_SQLSET('MY_STS','SYS','MY_STS_TAB','SH');把调优集的数据放到这个表中 5)用数据泵把sh下的所有对象都导出来叫sh.dmp [oracle@cuug~]$expdpsystem/oracleDUMPFILE=sh.dmpDIRECTORY=DATA_PUMP_DIRSCHEMAS=sh 在EM中删了调优集 6)模拟在新的环境做测试，假设新环境下没有sh用户,导入数据泵的sh.dmp (1)SYS@prod>dropusershcascade; (2)在新的测试系统下是没有MY_STS的，所以EM下删除MY_STS调优集 [oracle@cuug~]$impdpsystem/oracleDUMPFILE=sh.dmpDIRECTORY=DATA_PUMP_DIRSCHEMAS=sh 7)测试系统下把调优集解开 （1）先把优化器参数optimizer_features_enable改成11.2.0.1 SYS@prod>altersystemsetoptimizer_features_enable='11.2.0.1'; （2）解开调优集 SYS@prod>execDBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('MY_STS','SYS',TRUE,'MY_STS_TAB','SH'); （3）在EM中Refresh一下，MY_STS出现了 8）进入SPA做SQL分析 RelatedLinks--->SQLPerformanceAnalyzer ParameterChange--》 TaskInformation *TaskName--》选MY_TASK *SQLTuningSet--》选SYS.MY_STS ParameterChange *ParameterName--》选optimizer_features_enable *BaseValue--》10.1.0.3 *ChangeValues--》11.2.0.1 选Submit 点击MY_STS---》SQLTrials INITIAL_SQL_TRIAL SECOND_SQL_TRIAL SQLTrainComparisons INITIAL_SQL_TRIAL点击小眼镜，看性能比较 9）进阶GuidedWorkflow 目的：测试emp1无索引（初始环境）和有索引（改变环境）下的性能比较 SQLPerformanceAnalyzerTask:SYS.MY_TASK1 第一步CreateSQLPerformanceAnalyzerTaskbasedonSQLTuningSet name选调优集：MY_STS 第二步CreateSQLTrialinInitialEnvironment 注意在右端最下面一行，勾选方框Trialenvironmentestablished，然后summit 这步比较慢，原始环境下，scott.emp1表没有索引，它要把我们调优集中的所有sql都分析一遍 第三步CreateSQLTrialinChangedEnvironment 我们先在scott.emp1上建个索引， SYS@prod>createindexscott.emp1_empno_indonscott.emp1(empno); 然后再Execute 第四步CompareStep2andStep3 第五步ViewTrialComparisonReport 第九章SQL性能管理 一、11g新特性-SPM的引入 SQL的一些顾问可以调优SQL语句，但最多是一种被动的机制，而且需要DBA干预。 SPM（SQLPlanManagement)可以提供一种预防性的机制，为保持执行计划始终处于最优状态。 Oracle引入SQL计划基线的概念，目的是使SQL执行计划得以进化。 二、SQL计划基线的概念 如果一条语句经常被使用，Oracle为了防止因数据库环境的变化造成原执行计划出现性能退化，又会生成新的执行计划。 累积的这些计划组成了一部计划历史，在其中有标记为accepted的计划，这样的计划是比较优异的(成本较低的）， 计划基线就是计划历史中那些accepted的执行计划。标记为unaccepted的计划是非计划基线中的计划。 生成一条语句的最初的计划肯定是accepted,因为它无从比较。以后再生成的计划就可以同计划基线中的计划去比较了。 三、执行计划成为计划基线的方法 1）自动捕获； 设OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE参数为TRUE，数据库自动捕获新计划，但新计划不管是否优异，暂不列入accepted计划行列，要以观后效,一个未认可的计划必须优于认可的计划才能进化为计划基线。 2）手动装载； 手动在数据库中装载它们，一般是升级或已经测试过的，新计划直接列入accepted。因为你装载的计划是你自认为最优的计划。 四、管理SPM SMB（SQLManagementBase），又叫SQL管理库，所有的sql计划基线，sqlprofile等信息都保存在SYSAUX表空间。 SMB占用SYSAUX的比例缺省是10%，可以在1%-50%任意设置它，数据库执行每周一次的定期清除以删除不使用的SQL基线. 可以利用Oracle提供了DBMS_SPM程序包或EM管理SQL计划基线。 管理视图：DBA_SQL_PLAN_BASELINES 五、11g新特性ResultCache 结果cache是11g的新特性，将查询结果集保存在sharedpool以备其他session共享，这样减少物理读和逻辑读。 1）观察物理读和逻辑读，逻辑读总是有值，说明数据源来自dbbuffer SYS@prod>setautotraceon; SYS@prod>selectdeptno,sum(sal)fromscott.empgroupbydeptno; Statistics ---------------------------------------------------------- 214recursivecalls 0dbblockgets 48consistentgets 6physicalreads第一次有物理读 2）/再次执行，观察物理读消失，但有逻辑读，反复执行，都有逻辑读。 1recursivecalls 7consistentgets 0physicalreads 3）使用hint提示，使能result_cache功能 SYS@prod>select/*+result_cache*/deptno,sum(sal)fromscott.empgroupbydeptno; 0consistentgets 观察逻辑读为0了，说明直接从sharedpool中的resultcache取到结果集 换一个session做同样的查询，结果是一样的，共享了结果集 4）清除resultcache中缓存的结果集 SYS@prod>execDBMS_RESULT_CACHE.FLUSH(); 第四部分ASM 第十章ASM存储管理 一、GRID安装 一）创建用户和组及其目录 [root@cuug~]# groupaddasmadmin groupaddasmdba groupaddasmoper useradd-goinstall-Gasmadmin,asmdba,asmoper,dbagrid usermod-goinstall-Gasmdba,dbaoracle passwdgrid 二）创建grid的安装目录，并修改属性 root用户： mkdir-p/u02/grid chown-Rgrid:oinstall/u02/grid chmod-R775/u02/ 三）修改grid用户资源限制 [root@cuug~]#vi/etc/security/limits.conf 增加下列行： gridsoftnproc2047 gridhardnproc16384 gridsoftnofile1024 gridhardnofile65536 gridsoftstack10240 修改grid的环境文件.bash_profile [grid@cuug~]#vi.bash_profile ORACLE_BASE=/u01 ORACLE_HOME=/u02/grid GRID_HOME=/u02/grid ORACLE_SID=+ASM PATH=$ORACLE_HOME/bin:$PATH exportORACLE_BASEORACLE_HOMEGRID_HOMEORACLE_SIDPATH aliassqlplus='rlwrapsqlplus' aliasasmcmd='rlwrapasmcmd' NLS_LANG="simplifiedchinese"_china.AL32UTF8 exportNLS_LANG exportNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS' exportNLS_TIMESTAMP_FORMAT='yyyy-mm-ddHH24:MI:SSXFF' exportNLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-ddHH24:MI:SSXFFTZR' 四）安装GRID软件 1）XmanagerFTP上传grid软件 [root@cuug~]#cd/soft [root@cuug~]#unziplinux_11gR2_grid.zip [root@cuug~]#su-grid [grid@cuug~]$cd/soft/grid 2）启动XmanagerPassive [grid@cuuggrid]$exportDISPLAY=192.168.3.100:0.0 [grid@cuuggrid]$./runInstaller [root@cuug~]#/u02/grid/root.sh root用户执行： /Grid_home/perl/bin/perl-IGrid_home/perl/lib-IGrid_home/crs/installGrid_home/crs/install/roothas.pl 用实际的目录代替Grid_home,这里是/u02/grid /u02/grid/perl/bin/perl-I/u02/grid/perl/lib-I/u02/grid/crs/install/u02/grid/crs/install/roothas.pl grid用户： 到Grid_home/oui/bin目录下，这里是/u02/grid/oui/bin cd/u02/grid/oui/bin 运行：./runInstaller-updateNodeListORACLE_HOME=Grid_home-defaultHomeName用实际目录代替grid_home ./runInstaller-updateNodeListORACLE_HOME=/u02/grid-defaultHomeName OK结束图形安装，安装完毕。 3）启动cssd,查看状态 [grid@cuugbin]$crs_startora.cssd Attemptingtostart`ora.cssd`onmember`cuug` Attemptingtostart`ora.diskmon`onmember`cuug` Startof`ora.diskmon`onmember`cuug`succeeded. Startof`ora.cssd`onmember`cuug`succeeded. [grid@cuugbin]$crs_stat-t 名称类型目标状态主机 ------------------------------------------------------------ ora.cssdora.cssd.typeONLINEONLINEcuug ora.diskmonora....on.typeONLINEONLINEcuug [grid@cuug~] 二、创建ASM实例 一）配置ASM磁盘 1）在linux里增加1个8G的虚盘/dev/sdb, 关闭Linux，加盘后分区sdb1--sdb4各1G [root@cuug~]#fdisk-l/dev/sdb 2）提供ASM磁盘的方法 在Linux上创建ASM磁盘有两种方法： ASMlib方法：Linux5版之前支持 Udev方法：Linux5版（包括5版）之后推荐使用. 下面我们使用Udev方法提供ASM磁盘： [root@cuug~]#ll/dev/sdb* brw-r-----1rootdisk8,16Jun1621:28/dev/sdb brw-r-----1rootdisk8,17Jun1621:28/dev/sdb1 brw-r-----1rootdisk8,18Jun1621:28/dev/sdb2 brw-r-----1rootdisk8,19Jun1621:28/dev/sdb3 brw-r-----1rootdisk8,20Jun1621:28/dev/sdb4 3）编辑udev配置文件 [root@cuug~]#vi/etc/udev/rules.d/60-raw.rules按照example编写如下： ACTION=="add",ENV{MAJOR}=="8",ENV{MINOR}=="17",RUN+="/bin/raw/dev/raw/raw1%M%m" ACTION=="add",ENV{MAJOR}=="8",ENV{MINOR}=="18",RUN+="/bin/raw/dev/raw/raw2%M%m" ACTION=="add",ENV{MAJOR}=="8",ENV{MINOR}=="19",RUN+="/bin/raw/dev/raw/raw3%M%m" ACTION=="add",ENV{MAJOR}=="8",ENV{MINOR}=="20",RUN+="/bin/raw/dev/raw/raw4%M%m" KERNEL=="raw[1-4]",OWNER="grid",GROUP="dba",MODE="660" 4）映射udev裸设备 [root@cuug~]#start_udev [root@cuug~]#ll/dev/raw/* crw-rw----1griddba162,1Jun1621:30/dev/raw/raw1 crw-rw----1griddba162,2Jun1621:30/dev/raw/raw2 crw-rw----1griddba162,3Jun1621:30/dev/raw/raw3 crw-rw----1griddba162,4Jun1621:30/dev/raw/raw4 二）ASMCA创建asm实例 asmca创建asm实例时，要先修改$ORACLE_BASE目录下的cfgtoollog目录的权限，使grid用户可以写该目录。 [root@cuug~]#ll-d/u01/cfgtoollogs Drwxr-x---5oracleoinstall4096May242015/u01/cfgtoollogs [root@cuug~]#chmodg+w/u01/cfgtoollogs [grid@cuug~]$exportDISPLAY=192.168.3.100:0.0 [grid@cuug~]$asmca [root@cuug~]#mkdir-p/u01/admin/+ASM/pfile [root@cuug~]#chown-Rgrid:dba/u01/admin/+ASM/pfile 再次CreateASM [root@cuug~]#ps-ef|grepasm_ [grid@cuug~]$crs_stat-t ora.DG1.dgora....up.typeONLINEONLINEcuug ora.asmora.asm.typeONLINEONLINEcuug 三、ASM概念和术语 一）ASM实例及参数文件 [root@tim1~]#su-grid [grid@tim1~]$sqlplus/assysasm SQL>showparameterinstance_type instance_typestringasm SQL>showparameterspfile --------------------------------------------------------------------------------------------------- spfilestring+DG1/asm/asmparameterfile/regi stry.253.946984797 SQL>createpfilefrom SQL>createpfilefromspfile; [grid@tim1dbs]$catinit+ASM1.oraASM的参数文件 *.asm_power_limit=1 *.diagnostic_dest='/u01' *.instance_type='asm' *.large_pool_size=12M *.remote_login_passwordfile='EXCLUSIVE' [ 二）ASM磁盘、ASM磁盘组以及ASM文件 1）ASMdisk可以是实际的磁盘，也可以是磁盘的某个分区，或LVM管理的逻辑卷，实际工作中ASMdisk一般不使用文件系统格式化的磁盘。 2）ASMdiskgroup由ASMdisk组成，可以包含一个或多个ASMdisk。 3）建立ASMdiskgroup时会将所有ASMdisk划分成单元（AU）。AU大小缺省1M。也可指定其属性为2,4,8,16,32或64M。 4）ASM文件有数据文件，临时文件，控制文件，联机日志和归档日志文件，spfile,及RMAN备份集和映像副本等。ASM文件不支持口令文件、静态参数文件，跟踪文件、告警文件和Oracle二进制文件。 5）ASMfile会依据文件的动态增长分配ASMextent，ASMextent是(可变长）基于一个或多个ASMAU,一个ASMfile会分布到一个ASMdiskgroup的所有ASMdisk上。 6）可以通过ALTERDISKGROUP磁盘组名MOUNT手动加载ASMdisk，也可以通过指定参数ASM_DISKGROUP自动加载。 7）当ASMdiskgroup组中的ASMdisk发生增减后，ASMdiskgroup会自动再平衡，目的是使IO均衡在ASMdiskgroup的所有ASMdisk上。 三）ASM条带和镜像 1）ASMstriping(条带) 为了提高IO读写性能，ASM需要进行条带化处理，注意striping（条带）是基于AU的。 ASM不能禁用striping。有两种类型的条带化方式：粗粒度条带（coarse)和细粒度条带(fine)。粗粒度条带大小=AU大小，一般来说相对1M的AU，粗粒度条带也是1M,而细粒度条带总是128K,条带策略已由Oracle模板缺省定义了。若有特殊需求，你可自定义模板，然后在建立表空间时使用模板子句引用你自定义的文件模板。 细粒度条带主要用于controlfile和logfile 粗粒度条带用于个头较大的datafile 2）ASM镜像及故障组 1、ASM镜像提供了数据的冗余支持，镜像有三种模式： EXTERNALREDUNDANCY(无镜像,使用RAID） NORMALREDUNDANCY(两路）， HIGHREDUNDANCY(三路）， 2、ASM的镜像与操作系统提供的镜像有所不同。操作系统镜像是基于整个盘（分区）的镜像。而ASM是对ASM文件的extent做镜像，当ASM在某个磁盘上分配了一个primaryextent时,必定在另一个磁盘上分配一个与之对应的secondaryextent（镜像）。 3、对于双路磁盘控制器，为防止单路控制器同时损毁主副extent，ASM又引入故障组容错概念，使主副extent分布在不同的故障组下，即主extent和副extent分别属于不同的故障组。如果不设故障组，则每个磁盘就是一个独立的故障组。 4、一个磁盘组可有两个或多个故障组（failgroup），一个故障组由一个或多个ASM磁盘组成，故障组只能在两路或三路镜像模式下进行。 故障组是在标准冗余的基础上指定镜像策略，它是一种镜像功能的补充。假定有磁盘组DG1,且创建了两个故障组controller1,controller2,每个故障组由2个ASM磁盘组成,则对标准冗余而言,指定两个故障组互为镜像 3）磁盘组动态再平衡 1、无论是向磁盘组加盘还是减盘，只要改变了磁盘组中磁盘的数量，ASM就会重新分布磁盘上的数据。如果是增加磁盘还会为其划分au,进而划分条带，这样，文件会立即分布到新加的ASMdisk上。 2、自动重新平衡会消耗系统资源，有条件的话应该选择系统比较空闲的时段加减磁盘。 ASM_POWER_LIMIT这个参数值取0-11，0表示关闭动态平衡功能，此值越高平衡时消耗服务器资源越多，也可以在增加磁盘时在语句后中指定power的力度。 四）ASM别名（ASMalias） ASM别名是一个选项，目的就是为ASM文件默认的系统路径和命名起一个“外号”，便于用户适用于文件系统的命名方式。 1）加别名 SQL>alterdiskgroupdg1addalias'+data/test2_01.dbf'for'+data/prod/datafile/test2.257.977760221'; 修改控制文件和字典信息 SYS@prod1>alterdatabasedatafile7offline; SYS@prod1>altertablespacetest2renamedatafile'+DATA/prod/datafile/test2.268.959100175'to'+DATA/test2_01.dbf'; SYS@prod1>recoverdatafile7;可能需要recover一下 SYS@prod1>alterdatabasedatafile7online; 2）删别名 SQL>selectname,file_incarnationfromv$asm_aliaswherenamelike'%omf%'; SQL>alterdiskgroupkeldropalias'+kel/ipap/datafile/omf.dbf'; 3）查看别名 SYS@prod>selectfile_incarnationfromv$asm_aliaswherenamelike'%timran%'; FILE_INCARNATION ---------------- 977760367 SYS@prod>selectnamefromv$asm_aliaswherefile_incarnation=977760367; NAME ---------------------------------------------------------------------- TIMRAN.258.977760367 timran01.dbf 四、磁盘组及磁盘的操作 一）建立ASM磁盘 SYS@+ASM1>colnamefora10 SYS@+ASM1>selectGROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MBfromv$asm_diskgroup; GROUP_NUMBERNAMESTATETYPETOTAL_MBFREE_MB --------------------------------------------------------------------------------------------- 1DG1MOUNTEDEXTERN964904 SYS@+ASM1>colpathfora30 SYS@+ASM1>colnamefora20 SYS@+ASM1>selectGROUP_NUMBER,NAME,DISK_NUMBER,PATH,TOTAL_MB,FREE_MBfromv$asm_disk; GROUP_NUMBERNAMEDISK_NUMBERPATHTOTAL_MBFREE_MB 00/dev/raw/raw400 01/dev/raw/raw300 02/dev/raw/raw200 1DG1_00000/dev/raw/raw1964904 6.4数据库文件在ASM上的命名 建立ASM磁盘组 1）建立DG2磁盘组 SQL>creatediskgroupdg2externalredundancydisk'/dev/raw/raw2’; SQL>selectGROUP_NUMBER,DISK_NUMBER,NAME,PATHfromv$asm_disk; 2）删除DG2磁盘组 SQL>dropdiskgroupdg2includingcontents; 建立表空间 SYS@prod>showparametercreate_file db_create_file_deststring+dg1 方法1： SQL>createtablespacetest1;默认路径,100M 方法2：数据文件自动命名 SQL>createtablespacetest2datafile'+DG1'size10m; 方法3：数据文件指定别名 SQL>createtablespacetest3datafile'+DG1/prod/datafile/test3_01.dbf'size10m; SQL>selecttablespace_name,file_namefromdba_data_files; 在dg1上建个表试试 SQL>createtablescott.t1(idint)tablespacetest1; SQL>insertintoscott.t1values(1); SQL>commit; 建立DG2磁盘组两路容错 creatediskgroupDG2normalredundancy failgroupCON1disk'/dev/raw/raw3' failgroupCON2disk'/dev/raw/raw4'; SQL>selectGROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MBfromv$asm_diskgroup; ------------------------------------------------------------------------------------------------------- 2DG2MOUNTEDNORMAL19281826 SQL>selectGROUP_NUMBER,NAME,DISK_NUMBER,PATH,TOTAL_MB,FREE_MBfromv$asm_disk; 2DG2_00011/dev/raw/raw4964913 2DG2_00000/dev/raw/raw3964913 SQL>alterdiskgroupdg2rebalancepower3; 1）添加磁盘 SQL>alterdiskgroupdg2addfailgroupcon1disk'/dev/raw/raw2';