SECTION6数据库性能优化.docx

1、SECTION6数据库性能优化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 检查点问：将检查点（ch

4、eckpoint）信息记录到alert日志文件中参：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.2 测试功能是否实现1.2.1 手工触发检查点 alter system checkpoint;1.2.2

5、查看日志中是否记录检查点信息Sun 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

6、trace files to user_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 Refer

7、ence-alter system答：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_t

8、arget=120m;3.4 调整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 specti

9、fiacation:1、 Assign 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 2

10、0 active sessions .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 ti

11、me for a query for 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

12、) Set CPU ratios 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 defau

13、lt consumer group 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

14、解答方法1：选择Consumer 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

15、Session(sec)”e) 在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_pr

16、ivilege(grantee_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

18、(30);5.2 创建STUDENT表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表空间中P

19、CTTHRESHOLD 20 百分比预值，叶子结点块的百分比预值，如果这些记录超过了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 value

21、s(2,1);insert into 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选项，重复值比较高的放在前面，这样可以实现

22、压缩适合于DSS；OLTP的复合索引的前导列要惟一值定位比较快的问：创建位图索引、反向索引和函数索引参：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_b

23、itmap_idx on t(country_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)=SECLO

24、OER;drop index t_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较少索引压缩的缺点：

25、1） 需要更多的CPU处理索引2） 维护索引时，更耗时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_

26、count（最佳压约压缩系数）,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

27、.1 查看cursor_sharing参数默认值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_cach

28、e视图官方文档参考链接：答：8.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，PACKA

29、GE BODY，TRIGGER，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包保存到S

30、hared Pool参：books- 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”表明STANDAR

31、D包此时没有被保存到SHARED 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 保存到SHARED POOL内存中。9.2.4 DBMS_SHARED_POOL包解除shard pool中保存的存储过程exec dbms_sh