iLeichun

[oracle@localhost oracle]$ top
10:50:49 up 19:17, 3 users, load average: 4.77, 5.85, 6.58
135 processes: 132 sleeping, 2 running, 0 zombie, 1 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 0.7% 0.0% 1.1% 0.5% 0.0% 97.4% 0.0%
Mem: 510432k av, 503788k used, 6644k free, 0k shrd, 1432k buff
345208k actv, 64068k in_d, 6964k in_c
Swap: 1052248k av, 238344k used, 813904k free 338432k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
4665 oracle 15 0 52032 49M 49784 D 0.3 10.0 0:04 0 oracle
5 root 15 0 0 0 0 SW 0.1 0.0 1:41 0 kswapd
6 root 16 0 0 0 0 SW 0.1 0.0 1:15 0 kscand
2912 oracle 15 0 47344 44M 45256 D 0.1 8.9 5:18 0 oracle
4482 oracle 15 0 58752 56M 56504 S 0.1 11.3 0:19 0 oracle
4486 oracle 15 0 98.7M 96M 97760 D 0.1 19.4 0:21 0 oracle
4768 oracle 15 0 736 736 440 S 0.1 0.1 0:07 0 top
4991 testcent 15 0 708 708 420 S 0.1 0.1 0:00 0 top
5020 oracle 15 0 944 944 664 R 0.1 0.1 0:00 0 top
1 root 15 0 116 80 56 S 0.0 0.0 0:04 0 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 bdflush
8 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kupdated
9 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
13 root 15 0 0 0 0 SW 0.0 0.0 0:01 0 kjournald
78 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd

由于早上导了十几万的数据进去,一直怀疑是表索引失效,做了以下数据库调整,并删除了运行时间很短的jobs,果然速度快了不少...以下是我的调整步骤

几个简单的步骤大幅提高Oracle性能--我优化数据库的三板斧
　　
　　数据库优化的讨论可以说是一个永恒的主题。资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack，贴出数据库配置等等。还有的人认为要抓出执行最慢的语句来进行优化。但实际情况是，提出疑问的人很可能根本不懂执行计划，更不要说statspack了。而我认为，数据库优化，应该首先从大的方面考虑：网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整。实际上网络、硬件等往往无法决定更换，应用程序一般也无法修改，因此应该着重从数据库配置、数据结构上来下手，首先让数据库有一个良好的配置，然后再考虑具体优化某些过慢的语句。我在给我的用户系统进行优化的过程中，总结了一些基本的，简单易行的办法来优化数据库，算是我的三板斧，呵呵。不过请注意，这些不一定普遍使用，甚至有的会有副作用，但是对OLTP系统、基于成本的数据库往往行之有效，不妨试试。（注：附件是Burleson写的用来报告数据库性能等信息的脚本，本文用到）
　　
　　一．设置合适的SGA
　　
　　常常有人抱怨服务器硬件很好，但是Oracle就是很慢。很可能是内存分配不合理造成的。
　　
　　(1)假设内存有512M，这通常是小型应用。建议Oracle的SGA大约240M，其中：共享池（SHARED_POOL_SIZE）可以设置60M到80M，根据实际的用户数、查询等来定。数据块缓冲区可以大致分配120M-150M，8i下需要设置DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小。9i 下的数据缓冲区可以用db_cache_size来直接分配。
　　
　　(2)假设内存有1G，Oracle 的SGA可以考虑分配500M：共享池分配100M到150M，数据缓冲区分配300M到400M。
　　
　　(3)内存2G，SGA可以考虑分配1.2G，共享池300M到500M，剩下的给数据块缓冲区。
　　
　　(4)内存2G以上：共享池300M到500M就足够啦，再多也没有太大帮助；(Biti_rainy有专述)数据缓冲区是尽可能的大，但是一定要注意两个问题：一是要给操作系统和其他应用留够内存，二是对于32位的操作系统，Oracle的SGA有1.75G的限制。有的32位操作系统上可以突破这个限制，方法还请看Biti的大作吧。
　　
　　二．分析表和索引，更改优化模式
　　
　　Oracle默认优化模式是CHOOSE，在这种情况下，如果表没有经过分析，经常导致查询使用全表扫描，而不使用索引。这通常导致磁盘I/O太多，而导致查询很慢。如果没有使用执行计划稳定性，则应该把表和索引都分析一下，这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。对于少于100万的表，可以考虑分析整个表，对于很大的表，可以按百分比来分析，但是百分比不能过低，否则生成的统计信息可能不准确。可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间，索引可以通过DBA_INDEXES的LAST_ANALYZED列。
　　
　　下面通过例子来说明分析前后的速度对比。（表CASE_GA_AJZLZ大约有35万数据，有主键）首先在SQLPLUS中打开自动查询执行计划功能。(第一次要执行RDBMSADMINutlxplan.sql来创建PLAN_TABLE这个表)
　　
　　SQL> SET AUTOTRACE ON
　　SQL>SET TIMING ON
　　
　　通过SET AUTOTRACE ON 来查看语句的执行计划，通过SET TIMING ON 来查看语句运行时间。
　　
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　----------
　　346639
　　
　　已用时间: 00: 00: 21.38
　　
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE
　　1 0 SORT (AGGREGATE)
　　2 1 TABLE ACCESS (FULL) OF ¹CASE_GA_AJZLZ¹
　　……………………
　　
　　请注意上面分析中的TABLE ACCESS(FULL)，这说明该语句执行了全表扫描。而且查询使用了21.38秒。这时表还没有经过分析。下面我们来对该表进行分析：
　　
　　SQL> analyze table CASE_GA_AJZLZ compute statistics;
　　
　　表已分析。
　　
　　已用时间: 00: 05: 357.63
　　
　　然后再来查询：
　　
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　----------
　　346639
　　
　　已用时间: 00: 00: 00.71
　　
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=351 Card=1)
　　1 0 SORT (AGGREGATE)
　　2 1 INDEX (FAST FULL SCAN) OF ¹PK_AJZLZ¹ (UNIQUE) (Cost=351
　　Card=346351)
　　…………………………
　　
　　请注意，这次时间仅仅用了0.71秒！这要归功于INDEX(FAST FULL SCAN)。通过分析表，查询使用了PK_AJZLZ索引，磁盘I/O大幅减少，速度也大幅提升！下面的实用语句可以用来生成分析某个用户的所有表和索引，假设用户是GAXZUSR：
　　
　　SQL> set pagesize 0
　　SQL> spool d:analyze_tables.sql;
　　SQL> select ¹analyze table ¹||owner||¹.¹||table_name||¹ compute statistics;¹ from dba_tables where owner=¹GAXZUSR¹;
　　SQL> spool off
　　SQL> spool spool d:analyze_indexes.sql;
　　SQL> select ¹analyze index ¹||owner||¹.¹||index_name||¹ compute statistics;¹ from dba_indexes where owner=¹GAXZUSR¹;
　　SQL> spool off
　　SQL> @d:analyze_tables.sql
　　SQL> @d:analyze_indexes.sql
　　
　　解释：上面的语句生成了两个sql文件，分别分析全部的GAXZUSR的表和索引。如果需要按照百分比来分析表，可以修改一下脚本。通过上面的步骤，我们就完成了对表和索引的分析，可以测试一下速度的改进啦。建议定期运行上面的语句，尤其是数据经过大量更新。
　　
　　当然，也可以通过dbms_stats来分析表和索引，更方便一些。但是我仍然习惯上面的方法，因为成功与否会直接提示出来。
　　
　　另外，我们可以将优化模式进行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。对于OLTP系统，可以改成FIRST_ROWS，来要求查询尽快返回结果。这样即使不用分析，在一般情况下也可以提高查询性能。但是表和索引经过分析后有助于找到最合适的执行计划。
　　
　　三．设置cursor_sharing=FORCE 或SIMILAR
　　
　　这种方法是8i才开始有的，oracle805不支持。通过设置该参数，可以强制共享只有文字不同的语句解释计划。例如下面两条语句可以共享：
　　
　　SQL> SELECT * FROM MYTABLE WHERE NAME=¹tom¹
　　SQL> SELECT * FROM MYTABLE WHERE NAME=¹turner¹
　　
　　这个方法可以大幅降低缓冲区利用率低的问题，避免语句重新解释。通过这个功能，可以很大程度上解决硬解析带来的性能下降的问题。个人感觉可根据系统的实际情况，决定是否将该参数改成FORCE。该参数默认是exact。不过一定要注意，修改之前，必须先给ORACLE打补丁，否则改之后oracle会占用100%的CPU,无法使用。对于ORACLE9i，可以设置成SIMILAR，这个设置综合了FORCE和EXACT的优点。不过请慎用这个功能，这个参数也可能带来很大的负面影响！
　　
　　四．将常用的小表、索引钉在数据缓存KEEP池中
　　
　　内存上数据读取速度远远比硬盘中读取要快，据称，内存中数据读的速度是硬盘的14000倍！如果资源比较丰富，把常用的小的、而且经常进行全表扫描的表给钉内存中，当然是在好不过了。可以简单的通过ALTER TABLE tablename CACHE来实现，在ORACLE8i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般来说，可以考虑把200数据块之内的表放在keep池中，当然要根据内存大小等因素来定。关于如何查出那些表或索引符合条件，可以使用本文提供的access.sql和access_report.sql。这两个脚本是著名的Oracle专家 Burleson写的，你也可以在读懂了情况下根据实际情况调整一下脚本。对于索引，可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中。
　　
　　将表定在KEEP池中需要做一些准备工作。对于ORACLE9i 需要设置DB_KEEP_CACHE_SIZE，对于8i，需要设置buffer_pool_keep。在8i中，还要修改db_block_lru_latches，该参数默认是1，无法使用buffer_pool_keep。该参数应该比2*3*CPU数量少，但是要大于1，才能设置DB_KEEP_CACHE_BUFFER。buffer_pool_keep从db_block_buffers中分配，因此也要小于db_block_buffers。设置好这些参数后，就可以把常用对象永久钉在内存里。
　　
　　五．设置optimizer_max_permutations
　　
　　对于多表连接查询，如果采用基于成本优化(CBO)，ORACLE会计算出很多种运行方案，从中选择出最优方案。这个参数就是设置oracle究竟从多少种方案来选择最优。如果设置太大，那么计算最优方案过程也是时间比较长的。Oracle805和8i默认是80000，8建议改成2000。对于9i，已经默认是2000了。
　　
　　六．调整排序参数
　　
　　(1) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是32K，通常显得有点小，一般可以考虑设置成1M（1048576）。这个参数不能设置过大，因为每个连接都要分配同样的排序内存。
　　
　　(2) SORT_MULTIBLOCK_READ_COUNT:增大这个参数可以提高临时表空间排序性能，该参数默认是2，可以改成32来对比一下排序查询时间变化。注意，这个参数的最大值与平台有关系。
　　
　　七．调整其它几个关键的性能参数
　　
　　很多人认为使用oracle数据库，系统的默认参数就是最好的，其实不是这样

hongmo086 发表于:2006.12.12 12:29 ::分类: ( oracle管理 ) ::阅读:(20次) :: 评论 (0) :: 引用 (0)
2006 年 11 月 23日, 星期四一些知识
2 不借助第三方工具，怎样查看sql的执行计划
I) 使用Explain Plan,查询PLAN_TABLE;
EXPLAIN PLAN
SET STATEMENT_ID=¹QUERY1¹
FOR
SELECT *
FROM a
WHERE aa=1;
commit;
SELECT operation, options, object_name, object_type, ID, parent_id
FROM plan_table
WHERE STATEMENT_ID = ¹QUERY1¹
ORDER BY ID;

II)SQLPLUS中的SET TRACE 即可看到Execution Plan Statistics
SET AUTOTRACE ON;

3:如何使用CBO,CBO与RULE的区别
IF 初始化参数 OPTIMIZER_MODE = CHOOSE THEN --(8I DEFAULT)
IF 做过表分析
THEN 优化器 Optimizer=CBO(COST); /*高效*/
ELSE
优化器 Optimizer=RBO(RULE); /*高效*/
END IF;
END IF;

区别：
RBO根据规则选择最佳执行路径来运行查询。
CBO根据表统计找到最低成本的访问数据的方法确定执行计划。
使用CBO需要注意：
I) 需要经常对表进行ANALYZE命令进行分析统计;
II) 需要稳定执行计划;
III)需要使用提示(Hint);

hongmo086 发表于:2006.11.23 13:22 ::分类: ( oracle管理 ) ::阅读:(122次) :: 评论 (0) :: 引用 (0)
2006 年 11 月 16日, 星期四如何启动ARCHIVELOG模式
系统环境：
1、操作系统：Windows 2000 Server，机器内存128M
2、数据库：　Oracle 8i R2 (8.1.6) for NT 企业版
3、安装路径：C:ORACLE

实现步骤：

1、管理器
SVRMGR> connect internal
SVRMGR> shutdown
SVRMGR> startup mount [dbname]
SVRMGR> alter database [dbname] archivelog; --起用归档模式
SVRMGR> archive log start --启动自动归档模式，重起数据库后，按init.ora配置
SVRMGR> alter database [dbname] open; --打开数据库
SVRMGR> exit

2、修改数据库初始化参数文件，定义归档模式（自动）、归档日志文件保存路径、归档日志文件命名方法

3、重新启动数据库

具体实例：

C:>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

版权所有 (c) 1997，1999，Oracle Corporation。保留所有权利。

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal
连接成功。
SVRMGR> shutdown
已关闭数据库。
已卸下数据库。
已关闭 ORACLE 实例。
SVRMGR> startup mount
已启动 ORACLE 实例。
系统全局区域合计有 57124108个字节
Fixed Size 70924个字节
Variable Size 40198144个字节
Database Buffers 16777216个字节
Redo Buffers 77824个字节
已装入数据库。
SVRMGR> alter database archivelog;
语句已处理。
SVRMGR> archive log start
语句已处理。
SVRMGR> alter database open;
语句已处理。
SVRMGR> alter system switch logfile; --强制系统进行日志切换，可马上观察到归档日志的产生
语句已处理。
SVRMGR> exit
服务器管理程序结束。

修改数据库参数文件c:oracleadminoradbpfileinit.ora，
取消以下语句的#注释
log_archive_start = true
log_archive_dest_1 = "location=C:Oracleoradataoradbarchive"
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
关闭数据库，重新启动

查看C:Oracleoradataoradbarchive目录下，可以看到类似ORADBT001S01201.ARC的文件，说明归档成功

解释init.ora参数文件中关于归档重做日志参数项的含义

归档模式是自动还是手工，true为自动，false为手工
log_archive_start = true

归档日志文件所保存的路径
log_archive_dest_1 = "location=C:Oracleoradataoradbarchive"

归档日志文件的命名方法
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

归档命令：

启动自动归档模式，系统重起后，将按init.ora中的参数log_archive_start的值设置归档方式
SVRMGR> archive log start

启动手工归档模式
SVRMGR> archive log stop

查看归档信息：重做日志是否归档方式、是自动归档还是手工归档、归档路径、最旧的联机日志循序号...
SVRMGR> archive log list

归档一个已满，但没有归档的联机重做日志
SVRMGR> archive log next

归档所有已满，但没有归档的联机重做日志
SVRMGR> archive log all

注意：一个事务即使不被提交，也会被写入到重做日志中

实际上，对于特定的环境，总是存在不同的最优设置的，没有任何一种普遍使用的最优方案。对于设置这个参数，那仅仅是出于一个目的，避免过度的犯错误。事实上，在任何一个生产系统正式投入使用之前，我们不拥有任何系统运行信息让我们去调整，这样只有两种可能，一是根据文档推荐设置，另外一种就是根据经验设置。相对来说，根据经验根据的设置比根据文档的设置要可靠一些。尤其是那些7×24的系统，我们更要减少错误的发生。
在设置参数之前，先看下面几个问题

1.物理内存多大
2.操作系统估计需要使用多少内存
3.数据库是使用文件系统还是裸设备
4.估计系统会有多少并发数
 根据这几个问题的答案，我们可以粗略地为系统估计一下内存设置。那我们现在来逐个问题讨论。
 首先物理内存多大是最容易回答的一个问题。
 然后操作系统估计使用多少内存呢？从经验上来看，不会太多，通常应该在300M以内(不包含大量进程PCB).
 接下来我们要讨论一个重要的问题，那就是关于文件系统和裸设备的问题，这往往容易备我们所忽略。操作系统对于文件系统，使用了大量的buffer来缓存操作系统块。这样当数据库获取数据块的时候，虽然在SGA里没有命中，但却实际上可能是操作系统的文件缓存中获取的。而假如数据库和操作系统支持异步I/O，则实际上当数据库写进程DBWR写磁盘时，操作系统在文件缓存中标记该块为延迟写，等到真正地写入磁盘后，操作系统才通知DBWR写磁盘完成。对于这部分文件缓存，所需要的内存可能比较大，作为保守的估计，我们应该考虑在0.2－0.3倍内存大小。但是如果我们使用的是裸设备，则不考虑这部分缓存问题。这样的情况下SGA就有调大的机会。
 关于数据库有多少并发数，这实际上关系到PGA的大小(MTS下还有large_pool_size).相关的参数包括：
SQL> show parameter area_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     16777216
sort_area_size                       integer     8388608
在这部分内存中我们最关注的通常是sort_area_size，这是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参数的设置很重要。这四个参数都是针对会话进行设置的，是单个会话使用内存的大小，而不是整个数据库使用的。
根据上面这些参数，我们看一个公式：
OS使用内存+SGA+*并发进程数(sort_area_size+hash_area_size+2M)<07*总内存
(公式是死的，系统是活的，实际应用的调整不必框公式，不过是一个参考建议罢了)
SGA内参数设置

Log_buffer
对于日志缓冲区的大小设置，通常我觉得没有过多的建议，以为参考LGWR写的触发条件之后，我们会发现通常超过3M意义不是很大。作为一个正式系统，可能考虑先设置这部分为Log_buffer＝1－3M大小，然后针对具体的情况再做调整。
large_pool_size
对于大型缓冲池的设置，假如我们不使用MTS，建议在20－30M就够了。这部分主要是来保存并行查询时候的一些信息，还有就是RMAN在备份的时候可能会使用到。
java_pool_size
假如数据库没有使用java，我们通常认为保留10－20M大小就足够了。事实上可以更少，但具体根据安装数据库的时候的组件相关(比如 http server).
shared_pool_size
在一个充分使用绑定变量的比较大的系统中，shared_pool_size的开销通常应该维持在300M以内。除非系统使用了大量的存储过程、函数、包，比如oracle erp这样的应用，可以到达500M甚至更高。假定一个1G内存的系统，可考虑该参数设置为100M，2G的系统考虑设置为150M，8G的系统可以考虑设置为200－300M。当然，如果通过在操作系统监控，没有发现严重的cpu问题，而发现了共享池命中率不高可以适当的增加shared_pool_size。但是不主张这部分内存超过800M。

data_buffer
现在我们来谈数据缓冲区，在确定了SGA的大小并分配完了前面部分的内存后，其余的都分配给这部分内存。通常，在允许的情况下，我们都尝试使得这部分内存更大。这部分内存的作用主要是缓存在DB BLOCK，减少甚至避免从磁盘上获取数据。如果设置了buffer_pool_keep和buffer_pool_recycle，则应该加上后面这两部分内存的大小。