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