报表系统数据库效率问题及专家建议.doc

报表系统数据库效率问题及专家建议概述由于OCBS报表子系统存在效率问题，用户反映报表运行时间长，于是请来了Oracle数据库专家对系统进行调查。调查结果经调查后，专家指出，影响系统效率的主要原因是系统存在大量的I/O等待，而引起I/O等待的主要原因有以下几点：1. 系统物理内存不足，导致系统页面交换频繁。2. 报表系统主要使用数据库作为联机分析用途，存在大量查询操作，而物理内存不能提供足够的空间把数据库的大部分数据装入内存，所以在查询时候产生大量的物理读取，导致I/O等待。3. 报表系统测试环境数据库的存储介质为普通内置硬盘，性能较差，而且所有数据都存放在同一个硬盘内，导致I/O性能低下。专家建议根据报表系统环境的情况，专家给出了以下几点建议：调整操作系统缓存参数，释放更多物理内存通过调整系统缓存参数，降低操作系统对内存的占用率，释放更多物理内存。修改方法：使用root用户登录，输入以下命令：vmo -p -o maxperm%=10 -o maxclient%=10适当减少SGA空间在oracle数据库中，SGA空间主要是用于缓存数据以及保存Oracle运行时必备的系统信息的。由于物理内存不足以装入数据库内大部分数据，所以通过增加数据库SGA空间来提高查询效率的效果不明显，反而增加系统页面交换频率，产生I/O等待，得不偿失，因此可以适当减少数据库SGA空间，释放内存。修改方法：作为dba登录数据库，执行下列命令：alter system set sga_max_size=1500m scope=spfile;alter system set sga_target=1500m scope=spfile;修改后需重启数据库才生效。适当增加PGA空间在oracle数据库中PGA空间主要用于存放会话和散列信息，对数据进行排序，位图合并等操作。由于报表系统对数据库的查询中，存有大量的分组和排序操作，通过增加数据库PGA空间，使排序时更多的使用内存空间而不是硬盘空间，这样可以在一定程度上的提高报表生成速度。修改方法：作为dba登录数据库，执行下列命令：alter system set pga_aggregate_target=350m;PGA修改不需要重启数据库。适当设置多块读参数如果是全表扫描，那么区间的尺寸大小就有可能导致性能问题。因为全表扫描时，Oracle会一次读取多个Blocks。每次读取的块数将受初始化参数DB_FILE_MULTIBLOCK_READ_COUNT和操作系统的I/O缓冲区大小的限制。比如说，如果Oracle Block的大小是4KB，操作系统I/O缓冲区大小是64KB，那么在全表扫描时每次最多可以读取16各块（Oracle Blocks）。专家建议报表系统数据库的DB_FILE_MULTIBLOCK_READ_COUNT设置为64。修改方法：作为dba登录数据库，执行下列命令：alter system set db_file_multiblock_read_count=64 scope=spfile;修改后需重启数据库才生效。合理安排表空间专家建议，大表、小表、经常变动的表和索引分开不同的表空间来存放，这样可以减少表空间文件中的碎片，大表空间建立时采用UNIFORM参数限制增长速率。采用分区表来存放大数据ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。分区提供以下优点： 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。命令样例：CREATE TABLE range_sales( prod_id NUMBER(6), cust_id NUMBER,time_id DATE,channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE(01-APR-1998,DD-MON-YYYY), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE(01-JUL-1998,DD-MON-YYYY), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE(01-OCT-1998,DD-MON-YYYY), PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE(01-JAN-1999,DD-MON-YYYY), PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE(01-APR-1999,DD-MON-YYYY), PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE(01-JUL-1999,DD-MON-YYYY), PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE(01-OCT-1999,DD-MON-YYYY), PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE(01-JAN-2000,DD-MON-YYYY), PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE(01-APR-2000,DD-MON-YYYY), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE(01-JUL-2000,DD-MON-YYYY), PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE(01-OCT-2000,DD-MON-YYYY), PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE);使用dbms_stats包，对数据库进行信息统计dbms_stats包问世以后，Oracle专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用老式的分析表和dbms_utility方法来生成CBO统计数据。那些古老的方式甚至有可能危及SQL的性能，因为它们并非总是能够捕捉到有关表和索引的高质量信息。CBO使用对象统计，为所有SQL语句选择最佳的执行计划。dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。作为dba登录数据库，执行以下命令：exec dbms_stats.gather_schema_stats(ownname = COGNOS,options = GATHER AUTO,estimate_percent = dbms_stats.auto_sample_size,method_opt = for all columns size repeat,degree = 15)execute dbms_stats.gather_table_stats(ownname = COGNOS,tabname = table_name);注意，在对数据库进行信息统计时，会占用一定的系统资源，最好安排在空闲时候进行。监控数据库与调整SQL语句通过跟踪SQL语句的执行计划，可以对SQL查询语句进行调整，减少I/O操作。使用autotrace跟踪SQL通过开通自动跟踪，可以监视执行的SQL语句的执行计划和资源使用情况。操作样例：sqlplus cognos/cognosreportdbSQL set autotrace on;SQL select count(*) from bpttlt; COUNT(*)- 180Execution Plan-Plan hash value: 3378105748-| Id | Operation | Name | Rows | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | 1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FAST FULL SCAN| SYS_C009879 | 179 | 2 (0)| 00:00:01 |-Statistics- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 516 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed生成数据库报告通过查看数据库报告，可以查看数据库在一个时段内的各种状态。操作方法：进入 $ORACLE_HOME/rdbms/admin目录下，执行以下命令：sqlplus cognos/cognosreportdb awrrpt.sql按提示输入报告格式，报告文件名称和时间段就可以生成报告了。监视数据库警告日志通过监视数据库警告日志，可以查看数据库当前的警告信息。操作方法：进入 $ORACLE_BASE/ admin/reportdb/bdump 目录下，执行以下命令：tail f alert_reportdb.log(shutdown 数据库时日志也会输出执行情况)数据库监控通过执行以下sql可以查看数据库中的会话：Select * from v$session;通过执行以下sql可以查看当前会话正在执行的sql语句select sid, SERIAL#,event,sql_text from v$sqlarea inner join v$session on v$sqlarea.hash_value=v$session.sql_hash_value where v$session.username=COGNOS; 在关闭数据库前请先杀掉会话：select sid, SERIAL# from v$session where username=COGNOS; alter system kill session sid,serial#;系统内存监控在AIX操作系统中，可以使用vmstat命令来监视系统内存。样例：System configuration: lcpu=8 mem=11264MBkthr memory page faults cpu- - - - - r b avm fre re pi po fr sr cy in sy cs us sy id wa 4 2 4041220 5421 0 28 27 1304 2691 0 237 75012 25334 16 6 74 4命令结果显示系统剩余5421*4KB=20MB左右的空闲内存。增加日志库大小降低切换频率通过监视数据库警告日志，可以看到日志库切换的频率稍高，专家建议增加日志库空间大小，降低日志库切换频率。相关操作：查看当前日志组：Select * from v$log;增加日志组：alter database add logfile group 5 (/oradata/oradata/reportdb/redo05.log) size 100m;删除日志组：alter database drop logfile group 1;限制undo表空间大小专家建议，在数据库运行一个月后，限制undo表空间大小。操作语句：alter tablespace UNDOTBS1 autoextend on maxsize 500m;修改归档日志存储路径archive log list;alter system set log_archive_dest=保存的绝对地址 scope=spfile;find .-name *.sql -print | xargs -t -I ? sqlplus -s cognos/cognosreportdb ?-普通表空间(含系统表空间数据文件和undo表空间数据文件) ALTER DATABASE DATAFILE E:hs01datnfdat01.ora AUTOEXTEND OFF;select USED_UBLK,USED_UREC from v$transaction inner join v$session on v$transacti