SECTION6数据库性能优化.docx
《SECTION6数据库性能优化.docx》由会员分享，可在线阅读，更多相关《SECTION6数据库性能优化.docx（27页珍藏版）》请在冰豆网上搜索。
SECTION6数据库性能优化
1）Check;DB的可用性
2）Crashtroubleshoting
Performance:
定位,分析,处理
3）BASELINE，每天都有BASELINE，做REPORT，趋势走向，进行对比，分析原因
4）mornitor
网络,分配内存,内存解析,取数据,REDO写数据,底层存储通过交换机,数据分布不够合理（热点块问题）
碎片问题带来PLSQL唤进唤出,可以增加内存方式或者整理碎片
各种索引的创建：
创建索引目标是，访问的路径短，访问开销即量少，访问数据块即量少，内存开销少
找到大对像，KEEP到内存中
告诉ORACLE表的数据分步是怎么样的，让CBO找到一个最优的执行计划。
对于索引的监控，查看哪些索引使用，哪些没有使用，没有使用的删除掉，因为维护相关表的时候会维护索引，对于索引UPDATE，INSERT，DELETE开销比较大，UPDATE和DELETE会出现碎片化比较严重，这样会读更多的块得到最终数据，这样代码比较高，分周期监控，如果几个周期这个索引都未使用，那么可以尝试把这个索引删除掉。
ORACLE11G有个新技术，不可见索引，当发现有索引没有用的时候，可以让索引置为不可见，如果某一时间用到了，性能下降，这样在把这个索引置为可见。
让某条SQL语句执行我们想要执行计划、改变执行计划：
可以使用OUTLN；也可以使用HINTS方式。
1、检查点：
对检查点事件的理解？
检查点事件对数据库性能有什么冲击？
检查运行效果冲击的情况频度是什么样的？
使数据文件,控制文件,日志文件的SCN一致。
CKPT过于频繁，性能非常低，I/O写频繁。
频繁CKPT只有一个优点那么就是实例恢复速度特别快；如果CKPT不频繁，那么在实例恢复的时候就特别长，因为需要重演的数据块就特别的多，REDO是重演块的改变，而且也不能保证所有数据块都是可用有效的，一旦日志写满会切换，而且会发生检查点事件。
增量检查点事件减轻了系统缓慢，如果脏块比CKPT快，那么还是得告诉数据库已经写到哪里了？
直接告诉控制就可以了，这样恢复的时候需要控制文件的SCN来进行实例恢复。
2、
1检查点
问：
将检查点（checkpoint）信息记录到alert日志文件中
参：
book->reference->log_checkpoints_to_alert
答：
方法：
调整log_checkpoints_to_alert参数“TRUE”
注：
可以确定检查点事件的频度是否高
1.1确认并调整log_checkpoints_to_alert参数
Showparameteralert
Altersystemsetlog_checkpoints_to_alert=true;
Showparameteralert
1.2测试功能是否实现
1.2.1手工触发检查点
altersystemcheckpoint;
1.2.2查看日志中是否记录检查点信息
SunApr1418:
26:
252013
IncrementalcheckpointuptoRBA[0x10.324.0],currentlogtailatRBA[0x10.373.0]
SunApr1418:
26:
582013
BeginningglobalcheckpointuptoRBA[0x10.373.10],SCN:
662467
CompletedcheckpointuptoRBA[0x10.373.10],SCN:
662467
2规范文件目录
问：
把所有用户信息跟踪文件放到一个目录下（setallusertracefilestouser_dump_dest）
参：
books->reference->user_dump_dest
答：
Altersystemsetuser_dump_dest=/u01/app/oracle/admin/PROD/udmp
3内存调整
问：
调整sga_target、sga_max_size、pga_aggregate_target、java_pool_size参数使数据库运行在autosharememory管理模式下，即设置sga_target
设置SGA的最大值为512M
PGA大小调整到120M
JAVA_POOL调整到200M
参：
book->SQLReference->altersystem
答：
3.1调整sga_target参数到400M
Showparametersga_target
Altersystemsetsga_target=400m;
3.2调整sga_max_size参数到512M
Showparametersga_max_size
Altersystemsetsga_max_size=512mscope=spfile;
3.3调整pga_aggregate_target参数到120M
Showparameterpga_aggregate_target
Altersystemsetpga_aggregate_target=120m;
3.4调整java_pool_size参数到200M
Showparameterjava_pool_size
Altersystemsetjava_pool_size=200mscope=spfile;
3.5重启数据库使参数调整失效
Shutdownimmediate;
startup
4资源管理器
平衡各资源的使用，比如说对临时表空间
当资源争抢的时候，资源管理器才生效
对于DSS和OLTP混合型系统比较有用的
问：
按照题目要求配置资源管理器
SetupandconfigureResourceManagerusingthefollowingspectifiacation:
1、AssigntheuserSHastheresourceadministrator.
2、Createtowresourcemanagerconsumergroups,OLTPandDSS（Usecommentwhichwillbeusedfor）
3、Createaplan（资源计划，所有资源使用的限定）namedweekdays（按照周末的方式做资源计划）withthefollowingdirectivesonly:
a）ForOLTPgroup,wecannotallowmorethan20activesessions.IFmorethan20sessionstherequestshouldbeabortedifthewaitexceeds60seconds.短事务小事务密极型事务OLTP事务组
b）ThemaximumnumberofactivesessionsfortheDSSgroupto5.IFmorethan5sessionsthentherequestshouldabortafter120seconds.长事务大事务的DSS事务组
c）ThemaximumexecutiontimeforaqueryforasessionintheOLTPgroupis100seconds.IFthequeryisestimatedtotakelongerthan5secondsthesessionshouldswichtotheDSSgroup.已经开始运行的业务不能杀掉，不能因为资源争抢，业务不做了！
之所以刚才敢KILL是说明还没有跑起来呢
d）ThemaximumamountofundothattheOLTPgroupcanuseshouldbe1024kb;（没有做限制说明是无限制大的）
e）SetCPUratiosforOLTP,DSSandother_groupsas50,30and20RESPECTIVELY.
f）DSSgrouphasparalleldegreelimitof20。
（OLTP不做并行，是由于一旦启用并行，那么系统资源集中消耗并且执行该业务SQL，不惜一切代价执行完事务，启用并行的目的就是一次性快速返回给结果给客户端）
g）MakesurethatanidleOLTPsessionscannotblockaDMLstatementfor100seconds.
4、AssignthedefaultconsumergroupfortheOLTP_USERusertoOLTPgroup.
5、AssignthedefaultconsumergroupfortheSHusertoDSSgroup.
（4和5是两个用户按照不同资源计划组进行分配）
6、SpecifythattheWEEKDAYSplanbeusedbytheinstanceasdefault.工作日计划为该实例默认`计划
参：
books->Administrator’sguide->24usingthedatabaseresourcemanager
答：
使用GC解答方法
1：
选择ConsumerGroups进入，点击create，点击ADD添加SH用户，将复选框选中，点击OK即可
2：
进行ConsumerGroups，点击Create
3：
右上角选择ResourcePlan，点击Create，点击Modify添加ConsumerGroups
a）在SessionPool中
b）在SessionPool中
c）在MaximumExecutionTime和ConsumerGroupSwitching中
d）在UndoPool选项中
在IdleTime选项中，需要设置“MaxIdleTimeifBlockingAnotherSession（sec）”
e）在General选项中Level1中进行设置
f）在Parallelism选项中
g）在IdleTime选项中设置“MaxIdleTime”
4：
上一级页面，在ConsumerGroupMappings中的第一项中进行修改
5：
上一级页面，在ConsumerGroupMappings中的第一项中进行修改
6：
General中选中“Activatethisplan”，应用激活即可
使用命令解答方法
通过dbms_resource_manager_privs包处理
Begin
dbms_resource_manager_privs.grant_system_privilege（
grantee_name=>"SH",
privilege_name=>"ADMINISTRATOR_RESOURCE_MANAGER",
admin_option=>FALSE）;
end;
/
通过调整resource_manager_plan参数实现
altersystemsetresource_manager_plan='WEEKDAYS';
5创建IOT表
问：
创建IOT表
环境中已存在CLASS表，CLASS_ID为主键：
创建STUDENT表，STUDENT_ID为主键
创建多对多的中间转换表T_IOT，要求包含student_id和class_id列，要求表和索引是一体的（提示使用IOT表），需要自己创建
存储参数题目中没有要求
参：
books->sqlreference->createtable搜索“Index-OrganizationTableExample”
参：
books->Administrator‘sGuide->15ManagingTables搜索“creatinganIndex-OrganizationTable”
答：
5.1环境准备
创建CLASS表
Connsec/sec
CREATETABLEclass
（
class_idNUMBERCONSTRAINTpk_classPRIMARYKEY,
class_nameVARCHAR2（30）
）;
5.2创建STUDENT表
CREATETABLEstudent
（
student_idNUMBERCONSTRAINTpk_studentPRIMARYKEY,
student_nameVARCHAR2（30）
）;
5.3创建IOT表T_IOT
CREATETABLET_IOT
（
student_idNUMBER,
class_idNUMBER,
CONSTRAINTpk_T_IOTPRIMARYKEY（student_id,class_id）
）
ORGANIZATIONINDEX
TABLESPACEusers—整张T_IOT表放在USERS表空间中
PCTTHRESHOLD20–百分比预值，叶子结点块的百分比预值，如果这些记录超过了20%，该行就有部分字段的值放在溢出段表空间中
INCLUDINGclass_id—从这列，包括这列以及之前的放到USERS（上面指定的索引表空间），后边的列放在溢出表空间
OVERFLOWTABLESPACEtools;溢出表空间，整行特别长的话都放在索引块中，存储和遍历都会有影响的，索引字段肯定会在索引组织表中的BTREE块的叶子结点，超过了该块的容纳程度，那么溢出的内容会放到TOOLS表空间。
Including和PCTTHRESHOD20同时存在，那么pctthreshod20生效
把索引字段放入到索引段表空间，另一部分放入到其他表空间，这样为了避免碎片化的问题
5.4插入测试数据
insertintoclassvalues（1,'OCP'）;
insertintoclassvalues（2,'OCM'）;
insertintostudentvalues（1,'A'）;
insertintostudentvalues（2,'B'）;
commit;
select*fromclass;
select*fromstudent;
insertintot_iotvalues（1,1）;
insertintot_iotvalues（1,2）;
insertintot_iotvalues（2,1）;
insertintot_iotvalues（2,2）;
commit;
select*fromt_iot;
5.5可以考虑使用GC方法完成
6索引创建
BTREE-多用于OLTP
BITMAPPING数据大量的重复值，种类特别少的，DSS，大量的INSERT和SELECT
FBI不可能避免对函数计算的时候
REVERSE减少热点快发生，范围和BETWWEN开销特别大
PARTITION索引放在不同的磁盘上和表空间，提高检索效率，避免索引热点块的发生，HASH
复合索引：
多个列的索引，非前导列的检索效率，由于复合索引比较大，有COMPRESS选项，重复值比较高的放在前面，这样
可以实现压缩适合于DSS；OLTP的复合索引的前导列要惟一值定位比较快的
问：
创建位图索引、反向索引和函数索引
参：
book->sqlreference->createindex中的function-basedindexexamplesandbitmapindexexample还有关键字reverse
答：
6.1位图索引
T表包含2万行记录，Country_ID列为经常使用并且重复值很多，可枚举，数据不会经常更新。
Connsec/sec
Droptabletpurge;
Createtablet（country_idnumber）;
Createbitmapindext_bitmap_idxont（country_id）;
6.2反向索引
如果不让使反向索引，那么使用分区HASH索引
droptabletpurge;
createtablet（xnumber）;
createindext_reverse_idxont（x）reverse;
6.3函数索引
在T表上曾经创建了个索引，但应用总是用不到，发现应用都是以UPPER（X）的形式进行查询
droptabletpurge;
setautottraceon
createtablet（xvarchar2（20））;
select*fromtwhereupper（x）=’SECLOOER’;
dropindext_idex;
createindext_fbiont（upper（x））;
select*fromtwhereupper（x）=’SECLOOER’;
6.4复合索引
创建复合压缩索引：
索引包含两个列，第一列存在大量重复值，要求创建索引以便节省空间
压缩技术使单个数据块中的数据条目变多了
BOOK-》SQLREFERENCE-》CREATEINDEX-》搜索“COMPRESSINGANDINDEXEXAMPLE”关键字
索引压缩的优点：
1）索引占用的磁盘空间少
2）块缓冲区缓存能存放更多的索引条目
3）缓存命中率较高
4）物理I/O较少
索引压缩的缺点：
1）需要更多的CPU处理索引
2）维护索引时，更耗时
3）查询时，搜索索引需要较长的时间，因为需要更多的计算
4）增加了块竞争
复合压缩技术适合DSS系统
droptabletpurge;
createtablet（xnumber,ynumber,znumber）;
createindexi_compress_idxont（x,y,z）compress1;-1代表的第几列
analyzeindexi_tvalidatestructure;--得到索引的统计信息
selectheight,lf_blks,br_blks,_btree_space,opt_cmpr_count（最佳压约压缩系数）,opt_cmpr_pctsave（最佳压缩百分比）fromindex_stats;
如果采用opt_cmpr_count的系数的话，在btree_space基础上可以压缩到opt_cmpr_pctsave
opt_cmpr_count*（1-opt_cmpr_pctsave）
7缓解SQL硬解析
使用该参数，CPU会算哪些去要SHARING，后台会触发很多的BUG
问：
针对SQL语句无法共享，硬解析严重问题的调优
参：
book->reference->cursor_sharing
答：
Cursor_sharing参数默认值为EXACT，将其高速为SIMILAR或FORCE
7.1查看cursor_sharing参数默认值
Showparametercursor_sharing
7.2调整cursor_sharing参数为SIMILAR方法
Altersystemsetcursor_sharing=similar;
7.3调整cursor_sharing参数为FORCE方法
Altersystemsetcursor_sharing=force;
8获取PL/SQL大对象
问：
创建一个公共同名指向一个视图，此视图能够获得所有缓存在内存中大小超过50K的PACKAGE，PROCEDURE，TRIGGERS，FUNCTIONS
参：
v$db_object_cache视图官方文档参考链接：
答：
8.1创建视图
CREATEVIEWv_xxx
AS
SELECTname,TYPE,sharable_mem
FROMv$db_object_cache
WHEREsharable_mem>51200
ANDTYPEIN
（'PACKAGE',
'PACKAGEBODY',
'PROCEDURE',
'TRIGGER',
'FUNCTION'）;
注意v$db_object_cache视图的TYPE字段取值范围是：
INDEX，TABLE，CLUSTER，VIEW，SET，SYNONYM，SEQUENCE，PROCEDURE，FUNCTION，PACKAGE，PACKAGEBODY，TRIGGER，CLASS，OBJECT，USER，DBLINK。
不可以直接拷贝题目中出现的复数单词作为关键字！
8.2创建视图
使用SYS用户创建同义词PUBLICSYNONYM，使所有的用户都能查询这个同义词
1）创建军公共同义词指向视图
createpublicsynonymp_v_xxxforsys.v_xxx;
grantallonsys.v_xxxtopublic;
grantallonp_v_xxxtopublic;
select*fromp_v_xxx;
9缓存大对象
问：
使用DBMS_SHARED_POOL包将STANDARD包保存到SharedPool
参：
books->pl/sqlpackagesandtypesreference->97dbms_shared_pool
答：
9.1创建dbms_shared_pool包
@?
/rdbms/admin/dbmspool.sql
9.2使用dbms_shared_pool包将STANDARD包保存SHAREDPOOL
9.2.1确认STANDARD包是否被保存到SHAREDPOOL
selectowner,name,type,keptfromv$db_object_cachewherename='STANDARD';
最后一列KEPT值为“NO”表明STANDARD包此时没有被保存到SHAREDPOOL
KEPT的取值绝定这个对象是事能缓存在sharedpool的能力。
9.2.2保存存储过程P_INSERT到SHAREDPOOL
execdbms_shared_pool.keep（'STANDARD','P'）;
hash连接比较适合两张大表连接效率比较高，只有CBO才有HASH连接
9.2.3再次确认STANDARD包是否被保存存到SHAREDPOOL
selectowner,name,type,keptfromv$db_object_cachewherename='STANDARD';
最后一列KETP值为“YES”表明STANDARD包此时没有被永久地PIN保存到SHAREDPOOL内存中。
9.2.4DBMS_SHARED_POOL包解除shardpool中保存的存储过程
execdbms_sh
升级会员 