《SECTION6数据库性能优化.docx》由会员分享，可在线阅读，更多相关《SECTION6数据库性能优化.docx（19页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 1）Check;DB的可用性2）Crash troubleshotingPerformance:定位,分析,处理3）BASELINE，每天都有BASELINE，做REPORT，趋势走向，进行对比，分析原因4）mornitor网络,分配内存,内存解析,取数据,REDO写数据,底层存储通过交换机,数据分布不够合理(热点块问题)碎片问题带来PLSQL唤进唤出,可以增加内存方式或者整理碎片各种索引的创建：创建索引目标是，访问的路径短，访问开销即量少，访问数据块即量少，内存开销少找到大对像，KEEP到内存中告诉ORACLE表的数据分步是怎么样的，让CBO找到一个最优的执行计划。对于索引的监控，查看哪些
2、索引使用，哪些没有使用，没有使用的删除掉，因为维护相关表的时候会维护索引，对于索引UPDATE，INSERT，DELETE开销比较大，UPDATE和DELETE会出现碎片化比较严重，这样会读更多的块得到最终数据，这样代码比较高，分周期监控，如果几个周期这个索引都未使用，那么可以尝试把这个索引删除掉。ORACLE11G有个新技术，不可见索引，当发现有索引没有用的时候，可以让索引置为不可见，如果某一时间用到了，性能下降，这样在把这个索引置为可见。让某条SQL语句执行我们想要执行计划、改变执行计划：可以使用OUTLN；也可以使用HINTS方式。1、 检查点：对检查点事件的理解？检查点事件对数据库性能
3、有什么冲击？检查运行效果冲击的情况频度是什么样的？使数据文件,控制文件,日志文件的SCN一致。CKPT过于频繁，性能非常低，I/O写频繁。频繁CKPT只有一个优点那么就是实例恢复速度特别快；如果CKPT不频繁，那么在实例恢复的时候就特别长，因为需要重演的数据块就特别的多，REDO是重演块的改变，而且也不能保证所有数据块都是可用有效的，一旦日志写满会切换，而且会发生检查点事件。增量检查点事件减轻了系统缓慢，如果脏块比CKPT快，那么还是得告诉数据库已经写到哪里了？直接告诉控制就可以了，这样恢复的时候需要控制文件的SCN来进行实例恢复。2、1 检查点问：将检查点（checkpoint）信息记录到a
4、lert日志文件中参：book-reference-log_checkpoints_to_alert答：方法：调整log_checkpoints_to_alert参数“TRUE”注：可以确定检查点事件的频度是否高1.1 确认并调整log_checkpoints_to_alert参数Show parameter alertAlter system set log_checkpoints_to_alert=true;Show parameter alert1.1 测试功能是否实现1.1.1 手工触发检查点 alter system checkpoint;1.1.2 查看日志中是否记录检查点信息Su
5、n Apr 14 18:26:25 2013Incremental checkpoint up to RBA 0x10.324.0, current log tail at RBA 0x10.373.0Sun Apr 14 18:26:58 2013Beginning global checkpoint up to RBA 0x10.373.10, SCN: 662467Completed checkpoint up to RBA 0x10.373.10, SCN: 6624672 规范文件目录问：把所有用户信息跟踪文件放到一个目录下（set all user trace files to u
6、ser_dump_dest）参：books-reference-user_dump_dest答：Alter system set user_dump_dest=/u01/app/oracle/admin/PROD/udmp3 内存调整问：调整sga_target、sga_max_size、pga_aggregate_target、java_pool_size参数使数据库运行在auto share memory管理模式下，即设置sga_target设置SGA的最大值为512MPGA大小调整到120MJAVA_POOL调整到200M参：book- SQL Reference-alter syste
7、m答：3.1 调整sga_target参数到400MShow parameter sga_targetAlter system set sga_target=400m;3.2 调整sga_max_size参数到512MShow parameter sga_max_sizeAlter system set sga_max_size=512m scope=spfile;3.3 调整pga_aggregate_target参数到120MShow parameter pga_aggregate_targetAlter system set pga_aggregate_target=120m;3.4 调
8、整java_pool_size参数到200MShow parameter java_pool_sizeAlter system set java_pool_size=200m scope=spfile;3.5 重启数据库使参数调整失效Shutdown immediate;startup4 资源管理器平衡各资源的使用，比如说对临时表空间当资源争抢的时候，资源管理器才生效对于DSS和OLTP混合型系统比较有用的问：按照题目要求配置资源管理器Set up and configure Resource Manager using the following spectifiacation:1、 Ass
9、ign the user SH as the resource administrator.2、 Create tow resource manager consumer groups,OLTP and DSS(Use comment which will be used for )3、 Create a plan（资源计划，所有资源使用的限定） named weekdays（按照周末的方式做资源计划） with the following directives only:a) For OLTP group,we cannot allow more than 20 active session
10、s .IF more than 20 sessions the request should be aborted if the wait exceeds 60 seconds.短事务小事务密极型事务OLTP事务组b) The maximum number of active sessions for the DSS group to 5. IF more than 5 sessions then the request should abort after 120 seconds.长事务大事务的DSS事务组c) The maximum execution time for a query f
11、or a session in the OLTP group is 100 seconds.IF the query is estimated to take longer than 5 seconds the session should swich to the DSS group.已经开始运行的业务不能杀掉，不能因为资源争抢，业务不做了！之所以刚才敢KILL是说明还没有跑起来呢d) The maximum amount of undo that the OLTP group can use should be 1024kb;（没有做限制说明是无限制大的）e) Set CPU ratios
12、 for OLTP,DSS and other_groups as 50,30 and 20 RESPECTIVELY.f) DSS group has parallel degree limit of 20。（OLTP不做并行，是由于一旦启用并行，那么系统资源集中消耗并且执行该业务SQL，不惜一切代价执行完事务，启用并行的目的就是一次性快速返回给结果给客户端）g) Make sure that an idle OLTP sessions cannot block a DMLstatement for 100seconds.4、 Assign the default consumer grou
13、p for the OLTP_USER user to OLTP group.5、 Assign the default consumer group for the SH user to DSS group.（4和5是两个用户按照不同资源计划组进行分配）6、 Specify that the WEEKDAYS plan be used by the instance as default.工作日计划为该实例默认计划 参：books-Administrators guide-24 using the database resource manager答：使用GC解答方法1：选择Consumer
14、 Groups进入，点击create ，点击ADD添加SH用户，将复选框选中，点击OK即可2：进行Consumer Groups ，点击Create3：右上角选择Resource Plan，点击Create，点击Modify添加Consumer Groupsa) 在Session Pool中b) 在Session Pool中c) 在Maximum Execution Time和Consumer Group Switching中d) 在Undo Pool选项中在Idle Time选项中，需要设置“Max Idle Time if Blocking Another Session(sec)”e)
15、在General选项中Level1中进行设置f) 在Parallelism选项中g) 在Idle Time选项中设置“Max Idle Time”4：上一级页面，在Consumer Group Mappings中的第一项中进行修改5：上一级页面，在Consumer Group Mappings中的第一项中进行修改6：General中选中“Activate this plan”，应用激活即可使用命令解答方法通过dbms_resource_manager_privs包处理Begindbms_resource_manager_privs.grant_system_privilege(grantee_
16、name=SH, privilege_name=ADMINISTRATOR_RESOURCE_MANAGER, admin_option=FALSE);end;/通过调整resource_manager_plan参数实现alter system set resource_manager_plan=WEEKDAYS;5 创建IOT表问：创建IOT表环境中已存在CLASS表，CLASS_ID为主键：创建STUDENT表，STUDENT_ID为主键创建多对多的中间转换表T_IOT，要求包含student_id和class_id列，要求表和索引是一体的（提示使用IOT表），需要自己创建存储参数题目中没
17、有要求参：books-sql reference- create table 搜索“Index-Organization Table Example”参：books-Administrator s Guide- 15 Managing Tables搜索“creating an Index-Organization Table”答：5.1 环境准备创建CLASS表Conn sec /secCREATE TABLE class( class_id NUMBER CONSTRAINT pk_class PRIMARY KEY, class_name VARCHAR2 (30);5.2 创建STUDE
18、NT表CREATE TABLE student( student_id NUMBER CONSTRAINT pk_student PRIMARY KEY, student_name VARCHAR2 (30);5.3 创建IOT表T_IOTCREATE TABLE T_IOT( student_id NUMBER, class_id NUMBER, CONSTRAINT pk_T_IOT PRIMARY KEY (student_id, class_id)ORGANIZATION INDEXTABLESPACE users整张T_IOT表放在USERS表空间中PCTTHRESHOLD 20 百
19、分比预值，叶子结点块的百分比预值，如果这些记录超过了20%，该行就有部分字段的值放在溢出段表空间中INCLUDING class_id从这列，包括这列以及之前的放到USERS（上面指定的索引表空间），后边的列放在溢出表空间OVERFLOW TABLESPACE tools;溢出表空间，整行特别长的话都放在索引块中，存储和遍历都会有影响的，索引字段肯定会在索引组织表中的BTREE块的叶子结点，超过了该块的容纳程度，那么溢出的内容会放到TOOLS表空间。Including和PCTTHRESHOD 20同时存在，那么pctthreshod 20生效把索引字段放入到索引段表空间，另一部分放入到其他表空
20、间，这样为了避免碎片化的问题5.4 插入测试数据insert into class values(1,OCP);insert into class values(2,OCM);insert into student values(1,A);insert into student values(2,B);commit;select * from class;select * from student;insert into t_iot values(1,1);insert into t_iot values(1,2);insert into t_iot values(2,1);insert in
21、to t_iot values(2,2);commit;select * from t_iot;5.5 可以考虑使用GC方法完成6 索引创建BTREE-多用于OLTPBIT MAPPING数据大量的重复值，种类特别少的，DSS，大量的INSERT和SELECTFBI不可能避免对函数计算的时候REVERSE减少热点快发生，范围和BETWWEN开销特别大PARTITION索引放在不同的磁盘上和表空间，提高检索效率，避免索引热点块的发生，HASH复合索引：多个列的索引，非前导列的检索效率，由于复合索引比较大，有COMPRESS选项，重复值比较高的放在前面，这样可以实现压缩适合于DSS；OLTP的复合
22、索引的前导列要惟一值定位比较快的问：创建位图索引、反向索引和函数索引参：book- sql reference- create index中的function-based index examples and bitmap index example 还有关键字reverse答：6.1 位图索引T表包含2万行记录，Country_ID列为经常使用并且重复值很多，可枚举，数据不会经常更新。Conn sec/secDrop table t purge;Create table t (country_id number);Create bitmap index t_bitmap_idx on t(c
23、ountry_id);6.2 反向索引如果不让使反向索引，那么使用分区HASH索引drop table t purge;create table t(x number);create index t_reverse_idx on t(x) reverse;6.3 函数索引在T表上曾经创建了个索引，但应用总是用不到，发现应用都是以UPPER（X）的形式进行查询drop table t purge;set autottrace on create table t (x varchar2(20);select * from t where upper(x)=SECLOOER;drop index t
24、_idex;create index t_fbi on t(upper(x);select * from t where upper(x)=SECLOOER;6.4 复合索引创建 复合压缩索引：索引包含两个列，第一列存在大量重复值，要求创建索引以便节省空间压缩技术使单个数据块中的数据条目变多了BOOK-SQL REFERENCE- CREATE INDEX-搜索“COMPRESSING AND INDEX EXAMPLE”关键字索引压缩的优点：1） 索引占用的磁盘空间少2） 块缓冲区缓存能存放更多的索引条目3） 缓存命中率较高4） 物理I/O较少索引压缩的缺点：1） 需要更多的CPU处理索引2
25、） 维护索引时，更耗时3） 查询时，搜索索引需要较长的时间，因为需要更多的计算4） 增加了块竞争复合压缩技术适合DSS系统 drop table t purge; create table t (x number,y number,z number);create index i_compress_idx on t(x,y,z) compress 1;-1代表的第几列analyze index i_t validate structure;-得到索引的统计信息select height,lf_blks,br_blks,_btree_space,opt_cmpr_count（最佳压约压缩系数）,
26、opt_cmpr_pctsave（最佳压缩百分比） from index_stats;如果采用opt_cmpr_count的系数的话，在btree_space基础上可以压缩到opt_cmpr_pctsaveopt_cmpr_count*（1-opt_cmpr_pctsave）7 缓解SQL硬解析使用该参数，CPU会算哪些去要SHARING，后台会触发很多的BUG问：针对SQL语句无法共享，硬解析严重问题的调优参：book-reference-cursor_sharing答：Cursor_sharing参数默认值为EXACT，将其高速为SIMILAR或FORCE7.1 查看cursor_shar
27、ing参数默认值Show parameter cursor_sharing7.2 调整cursor_sharing参数为SIMILAR方法Alter system set cursor_sharing=similar;7.3 调整cursor_sharing参数为FORCE方法Alter system set cursor_sharing=force;8 获取PL/SQL大对象问：创建一个公共同名指向一个视图，此视图能够获得所有缓存在内存中大小超过50K的PACKAGE，PROCEDURE，TRIGGERS，FUNCTIONS参：v$db_object_cache视图官方文档参考链接：答：8.
28、1 创建视图CREATE VIEW v_xxxAS SELECT name, TYPE, sharable_mem FROM v$db_object_cache WHERE sharable_mem 51200 AND TYPE IN (PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, FUNCTION);注意v$db_object_cache视图的TYPE字段取值范围是：INDEX，TABLE，CLUSTER，VIEW，SET，SYNONYM，SEQUENCE，PROCEDURE，FUNCTION，PACKAGE，PACKAGE BODY，TRIGGER，
29、CLASS，OBJECT，USER，DBLINK。不可以直接拷贝题目中出现的复数单词作为关键字！8.2 创建视图使用SYS用户创建同义词PUBLIC SYNONYM，使所有的用户都能查询这个同义词1）创建军公共同义词指向视图create public synonym p_v_xxx for sys.v_xxx;grant all on sys.v_xxx to public;grant all on p_v_xxx to public;select * from p_v_xxx;9 缓存大对象问：使用DBMS_SHARED_POOL包将STANDARD包保存到Shared Pool参：book
30、s- pl/sql packages and types reference- 97 dbms_shared_pool答：9.1 创建dbms_shared_pool包?/rdbms/admin/dbmspool.sql9.2 使用dbms_shared_pool包将STANDARD包保存SHARED POOL9.2.1 确认STANDARD包是否被保存到SHARED POOLselect owner,name,type,kept from v$db_object_cache where name=STANDARD;最后一列KEPT值为“NO”表明STANDARD包此时没有被保存到SHARED
31、 POOLKEPT的取值绝定这个对象是事能缓存在shared pool的能力。9.2.2 保存存储过程P_INSERT到SHARED POOLexec dbms_shared_pool.keep(STANDARD,P);hash连接比较适合两张大表连接效率比较高，只有CBO才有HASH连接9.2.3 再次确认STANDARD包是否被保存存到SHARED POOLselect owner,name ,type,kept from v$db_object_cache where name=STANDARD;最后一列KETP值为“YES”表明STANDARD包此时没有被永久地PIN 保存到SHARE
32、D POOL内存中。9.2.4 DBMS_SHARED_POOL包解除shard pool中保存的存储过程exec dbms_shared_pool.unkeep(STANDARD,P);select owner,name,type,kept from v$db_object_cache where name=STANDARD;10 ASSM问：使用ASSM表空间以减少buffer busy wait一位初级DBA创建了一个表空间TBS_MSSM，然后在这个表空间上创建了一个表，但是每当insert大量数据时就会很慢。查v$session_wait视图的时候发现buffer busy wait
33、 等待事件很高，segment的head 争用很严重。查明原因。要求不影响大查询的条件下进行调整（large query should be maintained）参：books-sql reference -create tablespace中examples部分参：books- pl/sql packages and types reference -“dbms_redefinition”答：题目中创建的表空间采用手（MANUAL）段空间管理，需要将表空间调整为自动段空间管理方式（ASSM：Automatic segment space management）方式，再将表移动到新表空间上。
34、段中块争用导致的：缓解方法是多给一些事务槽10.1 初始化环境1）创建手动段空间管理表空间drop tablespace TBS_MSSM including contents and datafiles;CREATE TABLESPACE TBS_MSSM DATAFILE /u01/app/oracle/oradata/PROD/disk1/tbs_mssm_01.dbf SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;2）创建自动段空间管理（ASSM）表空间drop tablespace TBS_ASSM
35、including contents and datafiles;CREATE TABLESPACE TBS_ASSM DATAFILE /u01/app/oracle/oradata/PROD/disk1/tbs_assm_01.dbf SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;3）确认表空间类型SELECT segment_space_management, tablespace_name FROM dba_tablespaces WHERE tablespace_name IN (TBS_MSSM, TB
36、S_ASSM);SEGMEN TABLESPACE_NAME- -AUTO TBS_ASSMMANUAL TBS_MSSM4）在手动段空间管理表空间TBS_MSSM上创建表T初始化数据conn sec/secdrop table t purge;create table t(x number constraint pk_t primary key) tablespace TBS_MSSM;insert into t select rownum from dual connect by rownum=5;commit;select segment_name,segment_type,tables
37、pace_name from dba_segments where segment_name in (T,PK_T);10.2 使用在线重定义方法转换表存放的表空间10.2.1 基于主键的在线重定义（1） 验证是否满足基于主键的在线重定义要求exec dbms_redefinition.can_redef_table(SEC,T,dbms_redefinition.cons_use_pk);校验主建的语句，如果有主键的话就用主键来键（2） 创建在线重定义中间表T_REDEF，表空间设置为目录TBS_ASSMdrop table t_redef purge;create table t_rede
38、f tablespace tbs_assm as select * from t where 0=1;alter table t_redef add constraint pk_t_redef primary key (x);alter index pk_t_redef rebuild tablespace tbs_assm;用COPY方式也可以把原来的表的约束抓取过来（3） 查看T表和T_REDEF表以及他们主键索引所在的表空间SELECT segment_name, segment_type, tablespace_name FROM dba_segments WHERE segment_
39、name IN (T, PK_T, T_REDEF, PK_T_REDEF);SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME- - -T TABLE TBS_MSSMT_REDEF TABLE TBS_ASSMPK_T INDEX TBS_MSSMPK_T_REDEF INDEX TBS_ASSM（4） 启动在线重定义exec dbms_redefinition.start_redef_table(SEC,T,T_REDEF);用户，源表，中间表（5） 发现此时中间表的索引所在表空间发生了变化，指向默认USERS表空间SELECT segment_name, s
40、egment_type, tablespace_name FROM dba_segments WHERE segment_name IN (T, PK_T, T_REDEF, PK_T_REDEF);T TABLE TBS_MSSMT_REDEF TABLE TBS_ASSMPK_T INDEX TBS_MSSMPK_T_REDEF INDEX USERS（6） 在完成在线重定义之前可以手工同步数据exec dbms_redefinition.sync_interim_table(SEC,T,T_REDEF);（7） 完成在线重定义exec dbms_redefinition.finish_r
41、edef_table(SEC,T,T_REDEF);（8） 查看T表与主键索引的对应关系T表与中间表T_REDEF互换后，对应的主键索引并没有互换，因此T表此时对应的主键索引名称是PK_T_REDEFselect table_name,index_name from user_indexes where table_name=T;（9） 再次查看T表和T_REDEF表以及他们主键索引所在的表空间SELECT segment_name, segment_type, tablespace_name FROM dba_segmentsWHERE segment_name IN (T, PK_T, T
42、_REDEF, PK_T_REDEF);可此此时，T表的表空间已经从原来的手动段空间管理表空间TBS_MSSM调整到了自动段空间管理表空间TBS_ASSM。（10） 将T表的主键索引使用online选项调整到TBS_ASSM表空间上alter index PK_T_REDEF rebuild tablespace TBS_ASSM online;（11） 最终结果SELECT segment_name, segment_type, tablespace_name FROM dba_segments WHERE segment_name IN (T, PK_T, T_REDEF, PK_T_RE
43、DEF);SELECT * FROM T；10.2.2 基于ROWID的在线重定义重新初始化环境drop table t purge;drop table t_redef purge;create table t x(number) tablespace TBS_MSSM;insert into t select rownum from dual connect by rownum=5commit;select * from t;select table_name,tablespace_name from user_tables where table_name=T;（1） 验证是否满足基于R
44、OWID的在线重定义要求exec dbms_redefinition.can_redef_table(SEC,T,dbms_redefinition.cons_use_rowid);（2） 创建在线重定义中间表T_REDEF，表空间设置为目标TBS_ASMMcreate table t_refef (x number) tablespace tbs_asmm;（3） 查看T表和T_REDEF表所在的表空间select table_name,tablespace_name from user_tables where table_name in(T,T_REDEF);（4） 启动在线重定义exe
45、c dbms_redefinition.start_redef_table(SEC,T,T_REDEF,null,dbms_redefinition.cons_use_rowid);（5） 在完成在线重定义之间可以手工同步数据exec dbms_redefinition.sync_interim_table(SEC,T,T_REDEF);（6） 完成在线重定义exec dbms_redefinition.sync_interim_table(SEC,T,T_REDEF);（7） 再次查看T表和T_REDEF表所在的表空间select table_name,tablespace_name from user_tables where table_name in(T,T_REDEF);SELECT * FROM T