oracle高级培训笔记及特性.doc

概述2一、 升级到11g的准备工作：2二、 安装oracle 11g升级过程3三、 升级完成后4四、 oracle常用工具4五、 诊断工具调优：5六、 数据库级别优化、调优6七、 统计信息10概述数据中心：各个应用系统采用的数据库不同，各自独立，oracle通过虚拟化或者集群技术将不同数据整合起来，形成数据中心。oracle 数据库版本介绍现在最新版本为11g release 2，各版本升级顺序如下图所示。oracle 11g的新特性：虚拟化技术和RAC；lob类型全新算法；自动内存管理，说明文件越来越简单；数据压缩技术；统计信息收集。备注：Oralce10g以前对执行计划分析采用规则算法，oracle 9i 其内置有15个规则，10g后的版本采用基于统计信息的成本优化算法，以前buffer pool命中率测试在基于统计信息的性能测试中不在有效。一、 升级到11g的准备工作：1. 主要为备份原有数据库、备份配置文件以及日志，从网上下载版本对应的手册，每个文档有对应的文档号，各版本升级说明书文档号详见培训文件Upgrade11gR2_2day_workshop.pdf。2. 根据网上的最佳实践来升级。3. 选择正确的oracle升级方式：使用DBUA 工具升级oracle；CLI命令行方式来升级；如果不允许宕机来升级，可采用sql语句来升级。如果为不同平台的系统来进行升级可采用Export和Import命令来传递数据，也可以使用transportable space或者database来升级数据，跨系统间升级判断方式如下图所示。4. 升级前检查数据对象，看看是否有INVALID。SQL SELECT UNIQUE object_name, object_type, owner FROM dba_objects WHERE status=INVALID;5. 删除SYS.AUD$; 语句SQL truncate SYS.AUD$;6. 如果从10g升级到11G需要把recyclebin 清掉。SQL purge DBA_RECYCLEBIN;7. 升级前对性能的统计信息需要保留起来，文件后缀为AWR，升级后将新的文件与老文件进行比较，SQL exec DBMS_SWRF_INTERNAL.AWR_EXTRACT；SQL exec DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML 。二、 安装oracle 11g升级过程1. 安装要求：RAM: 1GB；Between 3 GB (Windows), 3.9 GB (Linux32) and 7 GB (HP-UX)，其它详见说明文档Upgrade11gR2_2day_workshop.pdf。2. 根据向导安装oracle 11g 软件：./runInstaller ignoreSysPrereqs，详见说明文档。使用dbua工具升级到11g ，例子：dbua -silent -sid dwh，详见说明文档。3. 使用utlu112i.sql（utluxxi.sql其中xx是数据库版本）4. 使用 nettca 创建listener5. 启动数据库 startup upgrade，并创建sysaux表空间（如果为9i）6. 使用catupgrd.sql进行 upgrade7. 执行utlrp.sql utluiobj.sql8. 执行catuppst.sql9. 检查升级状态：utlu112s.sql10. 执行统计信息比较 。其它升级方式如：Command Line Upgrade 略，详见说明书Upgrade11gR2_2day_workshop.pdf。三、 升级完成后1、执行create an editable init.ora 生成SPFILE（oracle自动管理的文件）SPFILE为二进制文件。2、 alter system set PARAMETER = VALUE scope=both;或者 creat pfile fromspfile started force spfile=db.ora3、system statistics /列组统计信息EXEUTE dbms_stats.gather_system_stats(start);/启动时cpu以及内存等的情况EXEUTE dbms_stats.gather_system_stats(stop);/停止时cpu以及内存等的情况SQL execute dbms_stats.gather_fixed_objects_stats;4、导入数据时尽量采用impdp，empdp命令。四、 oracle常用工具(NEW_2_GRID_v2.2.pdf)1. instant client 不需要安装的客户端，只需要拷贝后设置环境变量即可使用。2. oracle 客户端与服务器之间有版本要求，往后3个版本为默认支持，再后两个版本为扩展支持，其他的不支持。3. enterprise manager 默认的管理控制台4. SQL Developer可设断点调试存储过程，内含其他数据库的移植工具。5. APEX 为瘦客户的开发工具，通过浏览器访问数据库。6. 可通过support workbench 控制台，查看错误信息。7. 常用的语法以及参数：ALTER TABLE SHRINK SPACE /可整理hight workmark 高水位线，要求表空间为自动段管理的表空间flashback 类似于回收站。表删除时，暂时存放在这里，有专门的管理界面alter table t1 flashback archive fda1; /将表fda1设为删除模式total recall 可存放多年的历史数据变化,dba 也无权限修改，可通过DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA或者DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA来隐藏但是其内的数据不能修改。注：11g以前通过查看alert.log文件来查找错误，11g后通过adrci工具来看log文件，可通过 SQL SELECT name, value FROM v$diag_info; 来查看log位置execute DBMS_STATS.LOCK_TABLE_STATS(SH,TAB1); 可锁住统计信息比较正确的表，表统计信息发生10%变化时可重新进行统计信息更新。注：统计信息排序先收集统计信息为empty，然后在根据表大小，最后根据统计信息数据记录变化多少来进行统计。oracle监控设置TYPICAL ,basic或者all选项 参数为：STATISTICS_LEVEL=TYPICAL五、 诊断工具调优：(NEW_2_GRID_v2.2.pdf)1. oracle使用addm进程来不定时快照，快照只存放8天，addmresult进程会分析快照，然后给出建议，dba可通过awr，来reactive管理、查看快照。EXEC dbms_workload_repository.create_snapshot();2. ASH 是数据库活动session历史，调优可通过报告来进行。SQL ?/rdbms/admin/ashrpt.sql，SQL ?/rdbms/admin/ashrpti.sql。报告以时间、颜色划分。可划分：对象、数据文件、session、sql访问频率、数据量等。3. addm、war、ash都是诊断包。执行计划的管理SQL Plan Management，Real-time SQL Monitoring。4. SQL profiles（配置文件）收集到的执行计划放在V$SQL_PLAN_MONITOR，统计信息放在V$SQL_MONITOR内，这些可通过管理控制台来查看，oracle会比较执行计划，发现执行计划比现有的快3倍以上则替换现有的计划。现在版本只能针对查询以及profile 语句来调优，dba可以通过建索引、分区来调优。5. database replay：先生成快照，第二步测试，第三步生成报告6. wrc启动模拟用户测试，一个wrc可启动50个用户$ wrc mode=calibrate replaydir=/tmp/testing$ wrc system/oracle mode=replay7. 创建到临时表空间中的数据跟用户session相关，每个访问session都只能访问自己的数据，session退出时，系统自动清除session相关的数据。8. 诊断顺序先找到需要调优的语句查找oracle的建议：根据建议查看是否更新索引、统计信息、分区、执行计划。不同环境下，oracle执行计划会变化，因此需要管理执行计划，存放到plan baseline中执行计划为可接受执行计划。六、 数据库级别优化、调优(Inside Oracle SG.ppt)数据库I/O介绍1. 内部存储与外部存储（instance & Database）：调优共分三部分：内部存储、外部存储、内外交互中的进程优化。2. PGA：为私有的链接信息，包含堆栈、会话等，服务进程（server process），一般排序时使用，存放在内存中，需要设置的足够大，防止从磁盘读取空间，3. SGA：为共有的数据信息，主要为缓存。在instance内包含多个进程，解析时，次数越少越好，建议采用preapare模式，提前绑定变量。4. shared pool 尽量减少数据库重定义5. buffer pool设置要足够大（需要一个合理值）6. database buffer cache 标准块大小为8k,减少散读，大对象放到大块中，小的放到小块中。7. redo log buffer 需要设置的合理，redo log files存放位置很重要8. 事务commit提交频率也影响系统性能。9. DGWNLGWRCKPT进程比较重要 进程说明1. DBWN进程将内存中的数据（缓存）写入文件中，一般做checkpoint时会写、脏块达到指定值时、没有缓存空间了、超时、表空间离线只读、在表上执行DROP、truncate、begin backup2. LGWR&CKPT（日志写入&checkpoint）：3. 日志写入：事务提交、日志缓存1/3满、每3秒、一条记录超过1m的重复记录、DBWn进程操作前。4. checkpoint：执行检查点时通知DBWn执行写操作、更新数据文件头的检查点信息、更新控制文件的检查点信息。 性能和可用性需要平衡协调。注意：10g需要设置 PGA以及SGA的最小内存即可。 11g只需要设置一个最小内存即可。oracle优化主要内容 最重要的是sql语句优化；session管理；shared pool大小；buffer cache 大小； 数据块contention redo log and redo buffer tuning；undo tuning；I/O锁的问题；连接管理； bad use of cursors and shared pool；bad sql,basic tool；段可以理解为对象在数据库中的物理存在，LOBundo都为段。SQL无法快速定位性能瓶颈位置时，可采用段分析，找到占用时间最多的对象后，根据对象名查找引用sql；V$SEGMENT_STATISTICS提供段级统计信息。oracle最小的存储单元为block，block被访问的次数越多，则所属段访问的频率越高。性能来自设计，9i的数据复制设计不合理，9i数据库在高级复制中需要维护队列表，10g后将其挪到SYSAUX表空间中。优化常使用的工具：1. 动态性能视图（Dynamic performance views）2. Statistics （统计信息，oracle 9i以前不用，9i之后被经常使用）3. Enterprise Manager工具（优势:图形化、直观方式了解系统情况）4. AWR or Statspack(数据库体检报告)5. addm（根据awr以及statspack、统计信息等信息，给出建议）6. DBA scripts，在10g以前dba通过sql来调优数据库，10g以后该方式已经取消，采用数据库自动进行调优。优化例子：1、等待事件为调优的最重要的入手点select count(*) FROM v$event_name (查询系统等待时间)select count(*) FROM v$sysstat（数据库统计信息）尽量count（列），不要count（*）查询V$SYSTEM_EVENT V$SESSION_EVENT(系统以及session级事件)系统等待分为：RAC 等待、user i/o等待、System I/O等待、并行处理根据文件、块、查找对象，根据对象查找sql，根据sql查找原因减少响应事件、增加吞吐量、增加性能、关注db time（cpu*执行数据库时间）2、根据日志查询性能瓶颈alert system set event = 10046 trace name context forever.level=12 scope = spfile;产生trace事件，记录数据库操作记录oracle查询中如果进行了数据类型转换，则在该列中定义的索引不能使用。解决方法可建立一个func索引（函数型索引）3、优化方式优化需要了解系统、了解架构、了解机制（内外存、主键、sharpool、优化途径（awr、视图、statspack） 数据结构优化、改代码优化、数据库管理角度优化；开发dba (研发人员充当)、管理dba;临时表优化。调优基本准则：优化消耗资源大的？执行次数多的？4、keepbuffer cache 需要手工设，执行方法 alert table keep /将表放到keepbuffer缓存中解决I/O问题：第一种：首先要找到sql，然后找到对象，然后根据问题原因解决问题。第二种：将对象用到的区域放到缓存里，避免从物理空间中读取。企业管理器ASM：自动存储管理，11g不支持裸设备，必须要asm或者文件系统，asm会将表数据均匀的存放到磁盘中，而且数据有冗余，防止磁盘损坏。日志文件由active变为INACTIVE需要日志归档完成或者检查点完成。查看oracle进程情况 ps -ef|grep ora_ 查看cpu以及系统情况， top命令查看i/o情况iostat -xn 3Result Cache 用法：select /*+ result_cache */ count(*) from test; /从缓存中读取，磁盘没有任何压力。索引 先从索引树中找到节点，然后根据节点的ID从表中找出数据；对索引统计信息进行收集。统计信息收集完后，查询索引如果乱掉，可以重新建表，create table as select * from xx order by 索引。索引表空间最好大于数据表空间。如何监控索引？SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS wastage FROM index_stats;当以上大于20%的时候需要重建索引，执行命令ALTER INDEX acct_no_idx REBUILD;物化视图 视图本身不存任何东西：只是sql语句，是个逻辑结构，而物化视图则是真实存数据的视图。采用物化视图可减少临时表的使用机会。创建物化视图后，如果访问的表在物化视图内，则不需要访问表了，直接访问视图。七、 统计信息统计信息收集，收集统计信息时可以设置的选项、对合并列收集统计信息，对表达式和函数收集统计信息以及延迟发布统计信息四个方面。1. 设置收集统计信息时的选项我们知道，数据库里的对象的统计信息（statistics）对于优化器得到正确的执行计划来说起着至关重要的作用。因此从10g R1开始，只要使用DBCA安装的数据库，都会自动创建一个job，该job缺省周一到周五每天晚上10点到第二天早上6点（周末则为全天）负责收集数据库所有对象的统计信息。不过，可能存在某些情况，你需要用自己的脚本来收集某些特殊对象的统计信息。但是由于你采用了自动收集统计信息，oracle就会对所有对象使用相同的选项来收集统计信息，这样你就失去了对某个对象的控制权。当你发现缺省的统计信息收集方式对某个对象不是很合适时，你必须锁定该对象的统计信息，并使用一个特殊的选项值对该对象来收集统计信息。比如，某个表的列的数据倾斜（列为某种值的记录行数非常多，而某种值的记录行数又非常少）的非常严重，这时如果采用标准的采样率：ESTIMATE_PERCCENT=AUTO_SAMPLE_SIZE可能就不适合了。这时你就需要单独指定该对象的采样率。我们知道，在11g之前的收集统计信息方面，oracle提供的类似的其他选项还包括：CASCADE、DEGREE、METHOD_OPT、NO_INVALIDATE、GRANULARITY.到了11g里，则提供了更大的灵活性，从而使得你可以很简单的处理上面所说的这种情况。在11g里，上面说的这些选项可以在不同的级别上分别设置，级别由高到低分别为：global级别、数据库级别、schema级别、表级别。其中，低级别的选项覆盖高级别的选项。比如，对于上面所举的例子来说，如果要对你的一个特殊的、列上的值倾斜的很严重的表收集统计信息时，你只需要简单的调用如下的存储过程来设置该表级别上的的ESTIMATE_PERCCENT=100即可，如下所示：SQL exec dbms_stats.set_table_prefs（Schema_name，Table_name，ESTIMATE_PERCCENT，100）；这样设置以后，当数据库在自动收集统计信息时，对于其他没有单独设置采样率的表来说，采样率会采用AUTO_SAMPLE_SIZE，而对于你单独设置的Table_name表，则会使用100的采样率来收集统计信息。类似的，如果需要设置global级别上的选项，则调用dbms_stats.set_global_prefs；如果要设置数据库级别上的选项，则调用dbms_stats.set_database_prefs；如果要设置schema级别上的选项，则调用dbms_stats.set_schema_prefs即可。同时到了11g里，除了上面提到的这些选项以外，还添加了另外三种新的选项：PUBLISH、INCREMENTAL、STALE_PERCENT.其中：1） PUBLISH：收集完统计信息以后是否立即将统计信息发布到数据字典里，还是将它们存放在私有区域里。TRUE表示立即发布，FALSE表示存放到私有区域里。2） STALE_PERCENT：确定某个对象的统计信息过时的上限，如果过时就需要重新收集统计信息，缺省为10.计算某个表的统计信息是否过时，oracle会计算自从上一次收集该表的统计信息以来，该表中被修改的数据行数占该表的总行数的百分比。然后用得出的百分比值与该选项配置的值（如果缺省，就是10）进行比较，大于10，则说明该表的统计信息过时了，需要重新收集统计信息；否则就认为该表的统计信息不过时，不用再次收集。3） INCREMENTAL：在分区表上收集global的统计信息时（将GRANULARITY设置为GLOBAL），采用增量方式完成。使用该选项是因为对于某些分区表来说，比如按照月份进行范围分区的分区表来说，除了代表当前月的分区里的数据会经常变化以外，其他分区里的数据不会变动。因此在收集该分区表上的global的统计信息时，就没有必要再次扫描那些非当前月的分区了。如果你将INCREMENTAL设置为TRUE时，则在收集统计信息时，就不会扫描那些非当前月的分区里的数据，而只会扫描当前月的分区里的数据。最后将非当前月的分区上已经存在的统计信息加上当前月新算出来的统计信息合并就得出了分区表的global的统计信息。可以从视图：DBA_TAB_STAT_PREFS里看到所有的收集统计信息时的各个选项的值。2. 对合并列收集统计信息对于where条件里具有两个列以上的情况，比如where c1=A and c2=B来说，11g以前优化器评估其selectivity时，总是将每个列的selectivity相乘，从而得到整个where条件的selectiviey.但是如果两个列具有很强的依赖关系，比如汽车制造商与汽车型号这两个列来说，我们知道每个汽车制造商所生产的汽车型号几乎都不会重复，也就是说当你发出where 汽车制造商列=XXX and 汽车型号列=XXX时，与发出where汽车型号列=XXX时返回的记录行数可能几乎一样。这时如果在计算where条件的selectivity时仍然采用将汽车制造商列的selectivity乘以汽车型号列的selectivity时，就会导致总的selectivity过低，从而导致优化器估计返回的记录行数过少，从而可能导致不正确的执行计划。为了弥补这样的问题，11g以后可以让你将多个依赖程度很高列合并成一个组，然后对该组收集统计信息。具体如何实现，则可以看下面的例子。select dbms_stats.create_extended_stats（Schema_name，Table_name，（C1，C2） from dual；通过调用函数dbms_stats.create_extended_stats将两个或多个列合并，并返回一个虚拟的隐藏列的列名，其名字类似于：SYS_STUW_5RHLX443AN1ZCLPE_GLE4.然后，我们可以开始对表收集统计信息，收集完以后，你可以使用ALL|DBA|USER_STAT_EXTIONSIONS视图来查看列组合的统计信息。exec dbms_stats.gather_table_stats（Schema_name，Table_name）；如果你要对组合列收集直方图，则可以如下所示：exec dbms_stats.gather_table_stats（Schema_name，Table_name，method_opt=for columns （C1，C2） size AUTO）；3. 对函数以及表达式收集统计信息如果where条件类似于function_name（table_name.column_name）=XXX时，则优化器在估计这样的where条件的selectivity时，总是会假设其selectivity为1，也就是该where条件将返回table_name里总记录行数的1的记录行数。很明显的，这种假设肯定是错误的，从而可能导致优化器产生了不够优化的执行计划。从11g开始，我们可以对函数或者表达式收集统计信息了。该特性依赖于虚拟列，也就是说你需要先用dbms_stats.create_extended_stats函数为function_name（table_name.column_name）创建一个虚拟列，然后对该虚拟列收集统计信息。比如下面的例子。select dbms_stats.create_extended_stats（Schema_name，Table_name，length（C1） from dual；下面则显示的是对表达式来收集统计信息。select dbms_stats.create_extend