如何提高Oracle对系统CPU和IO的利用率

Question

目前遇到了一个奇怪的现象，在我们的Oracle应用系统上，不管是直接在服务器上使用SQLPlus进行复杂查询，还是通过远程tns连接往Oracle里写数据，服务器的CPU和IO都始终在25%左右徘徊。上不去...那我的问题就很简单了，如何提高Oracle对CPU和IO的占用率，充分利用系统资源提升Oracle执行能力。由于我目前在海外，手头没有环境可以收集具体的数据，因此想先听听各路大侠的思路。1. 哪些因素可能导致Oracle对CPU和IO的占用率始终处于较低的水平上？（CPU和IO都很低，那瓶颈要么在于Oracle内部有什么机制进行了限制，要么是还有什么其他外部资源成为了瓶颈？）
2. 要提高Oracle对CPU和IO的占用，有哪些方法？PS. 我们的Oracle应用系统跑在Unix平台上。

Answer 1

你的问题基本明白，
但是你的愿望挺奇怪，没有比较CPU非要50%以上吧。
在Oracle有一部分东西是不能被并行执行的，
按照你的说的，一定存在"闩"冲突，我想，
解决的方法应该是追查冲突源，Oracle本身不会出现你说的情况，
你应该多看看你们的程序，是不是有太多串行的操作了。

Answer 2

也不是说非要Oracle把资源耗上去啦。只是我们目前面临大量数据需要以尽可能快的速度灌到数据库里。目前在系统的各个组件上都看不到瓶颈的迹象。也就是说可能存在你说的串行阻塞。但另一方面，我之前有在一个容纳千万记录数的Oracle系统里提交了一个写的不太好的复合查询，连续执行个1小时都不出结果的那种。当时观察服务器的资源，发现还有约75%的CPU空闲和IO空闲，因此怀疑是不是Oracle自身有什么保护机制，不让它自己占用过多资源...

Answer 3

写写你的服务器配置,什么CPU,内存多大?
看看你的SGA,PGA的配置.

Answer 4

设置并行查询,提高CPU利用率:http://www.oracle.com.cn/archiver/?tid-120936.html
-------------------------
Parallel query is the most commonly used of Oracle's parallel execution features. It was the first parallel execution feature to be developed by Oracle and was introduced in Oracle Release 7.1 as the Oracle Parallel Query Option (PQO). Parallel execution can significantly reduce the elapsed time for large queries, but it doesn't apply to every query. To parallelize a SELECT statement, the following conditions must be met: 1、At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions. 2、If the execution involves a full table scan, the statement must contain a PARALLEL hint specifying the corresponding table, or the corresponding table must have a parallel declaration in its definition. 3、If the execution involves an index range scan spanning multiple partitions, the statement must contain a PARALLEL_INDEX hint specifying the corresponding index, or the corresponding index must have a parallel declaration in its definition. The following two sections explain how the degree of parallelism is chosen for a SELECT statement and discuss restrictions on the use of the parallel query feature. Setting the Degree of Parallelism Once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules: Oracle retrieves the DEGREE and INSTANCES specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings. Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step. You can use the PARALLEL and PARALLEL_INDEX hints to specify the degree of parallelism for a SELECT statement. You can use the NOPARALLEL and NOPARALLEL_INDEX hints to ensure that parallel execution is not performed. Example alter table emp parallel (degree 4);
select degree from user_tables where table_name = 'EMP';
select count(*) from emp;
alter table emp noparallel; SELECT /*+ PARALLEL(emp,4) */ COUNT(*) 
FROM emp;

Answer 5

Oracle并行服务器（OPS）
--------------------------
http://doc.linuxpk.com/49010.html
本文以问答的方式阐述了Oracle并行服务器的相关概念。　　1、什么是OPS　　OPS(Oracle Parallel Server)可以让位于不同系统的多个实例同时访问同一个数据库。并行服务器可以有效地提高系统的可用性和对多系统的访问性能，但是，如果你的数据没有做很好的分割，性能可能还会下降。　　安装OPS时，多个实例mount同一数据库文件，实例间的通讯由分布式锁管理器（DLM）来管理。需要注意的是分布式锁管理器与你所使用的硬件和*作系统有着密切的关系。为了确定多个企图同时修改同一数据的实例，Oracle使用了十个后台进程：LCK0-LCK9，来锁定某一实例所使用的资源。　　OPS主要用于UNIX/LINUX集群环境中。　　2、OPS的优点　　1）高可用性　　2）加快事务响应时间 - 可用于决策支持系统　　3）增大交易连接数 - 可用于联机事务处理系统　　3、所有的应用都是适合OPS吗？　　可以根据功能或数据进行分割的应用最适合OPS。那些有"热数据"（经常被多实例同时访问的数据）的应用并不适合使用OPS。　　4、OPS需要特殊的硬件吗？　　OPS要求服务器之间互连并共享磁盘子系统。所有可以做成集群的系统都可以，常用的有UNIX/LINUX和NT等。　　5、如何设置OPS？　　1）关闭数据库　　2）启用OPS选项，在UNIX中通过重新连接Oracle软件的方式来完成。　　3）使Oracle软件在所有节点上都有效，可以通过复制软件到其他节点或共享磁盘的方式来完成。　　4）每个实例要有自己的Redo log file，所以要增加必要的log文件：　　ALTER DATABASE ADD LOGFILE THREAD 2　　GROUP G4 ('RAW_FILE1') SIZE 500k,　　GROUP G5 ('RAW_FILE2') SIZE 500k,　　GROUP G6 ('RAW_FILE3') SIZE 500k;　　ALTER DATABASE ENABLE PUBLIC THREAD 2;　　5）每个实例要有自己的回滚段，所以要增加必要的回滚段：　　CREATE ROLLBACK SEGMENT RB2 TABLESPACE RBS;　　6）编辑初始化参数文件initSID.ora文件，添加如下几项：　　PARALLEL_SERVER = TRUE　　INSTANCE_NUMBER = 1　　THREAD = 1　　ROLLBACK_SEGMENTS = (r01, r02, r03, r04)　　7）创建OPS所需的数据字典，即运行CATPARR.SQL。　　8）在所有的节点上启动实例。　　6、如何确定一个数据库是运行在并行状态？　　show parameter parallel_server　　7、如何跟踪活动的实例？　　SELECT * FROM SYS.V_$ACTIVE_INSTANCES;　　SELECT * FROM SYS.V_$THREAD;　　8、如何确定每个实例使用了多少个PCM锁？　　select count(*) "Number of hashed PCM locks"　　from v$lock_element where bitand(flags, 4) != 0　　/　　select count(*) "Number of fine grain PCM locks"　　from v$lock_element where bitand(flags, 4) = 0　　/　　9、如何查看每个数据文件分配了多少个PCM锁以及ping率？　　col file_name format a29　　col tablespace format a12　　col blocking format 9999999　　col nlocks format 99999　　col start_lk format 9999999　　select l.file_id || ' ' || l.file_name file_name,　　l.ts_name "TABLESPACE",　　start_lk, nlocks, blocking, frequency "PING COUNT"　　from sys.file_ping p, sys.file_lock l　　where l.file_id = p.file_id　　order by l.file_id　　/　　10、什么是pinging？　　Pinging是进程，用于协调多实例对同一数据块的读写*作。OPS性能优化的一个挑战就是要最小化pinging。　　11、如何监控PCM锁的活动情况？　　查看当前实例活动PCM锁的总数：select * from sys.v$lock_activity;　　查看每个数据库对象的PCM锁活动状况：　　col table format a40　　select file#, kind||' '||username||'.'||name "TABLE", sum(xnc) pings　　from sys.v$false_ping p, sys.dba_users u　　where u.user_id = p.owner#　　group by file#, kind||' '||username||'.'||name, xnc　　order by xnc desc　　/　　12、如何设置一个对所有OPS实例通用的SQL*Net连接串？　　1）首先要求所有节点上的SID相同，如果不相同可以按如下*作进行更改：　　关闭数据库的所有实例　　将ORACLE_SID环境变量设成一致　　复制原来的初始化文件initOLDSID.ora为initCOMMON.ora　　重起所有实例　　2）编辑本地TNSNAMES.ora，如下例：　　PHOENIX =　　(DESCRIPTION =　　(ADDRESS_LIST =　　(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.50)(PORT = 1521))　　(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.51)(PORT = 1521))　　)　　(CONNECT_DATA =　　(SERVICE_NAME = ora8)　　)　　)

Answer 6

磁盘IO的时候是不占CPU的
你应该看看你的硬盘的吞吐速度是否到达了硬件的上限，如果是的话你可以用多个硬盘来负担这样的吞吐量·

Answer 7

磁盘系统是一套高可靠性高性能的Raid阵列，这个系统跑其他应用的时候CPU和IO是可以接近100%的。

Answer 8

用Oracle并行查询发挥多CPU的威力
http://www.dedecms.com/web-art/shujuku/Oracle/20061008/37337.html并行查询并行度Degree与instances 设置
http://www.eygle.com/archives/2008/04/parallel_degree_instances.html

Answer 9

呃，具体来说，当下需要解决的是insert操作的问题，查询到不是问题...

Answer 10

你问题不是并行的问题，因为从你的描述CPU和IO都正常，无爆机现象，
所以还使用该考虑"闩"的问题，低级部件串行所致，你要插入的表存在什么索引吗？
比如位图索引，或是说索引很多？

Answer 11

的确有索引，要插入的每张表都存在至少一个Unique索引，通常是1-2个索引，个别表上还有联合索引。不过从一般应用角度来考虑，应该不算“很多”~ :P

Answer 12

的确有索引。基本上要插入的每张表都有至少1个Unique索引，通常是1-2个，个别表还会有联合索引。不过对于一般应用，索引数量应该不算“很多” :P

Answer 13

的确有索引。基本上要插入的每张表都有至少1个Unique索引，通常是1-2个，个别表还会有联合索引。不过对于一般应用，索引数量应该不算“很多” :P

Answer 14

都是B-tree索引吗？存储在位图索引吗？你的插入语句是用的参数绑定吗？
一次Insert大约多少条记录?会有都个人一起操作这个而程序吗？
还有类似大量数据插入语句吗？

Answer 15

我还是觉得你的是latch的问题，并行应该不是主要问题。

Answer 16

印象里是没有用位图索引的~
插入语句是是使用了参数绑定，基本上一次insert个几百到1000条记录（每张表）然后提交commit
操作期间不会有其他人同时访问数据库，因此可以排除因并发操作一类的问题。

Answer 17

恩，
了解了，
你使用Cursor循环插入吗？

Answer 18

系统配置应该不是问题，IBM Serise 5的小型机外加磁盘阵列。N个CPU以及几个G的内存。
SGA和PGA暂时查不了...

Answer 19

应该不涉及cursor，全部insert都是append操作，期间可能会夹杂少量的查询，用于对一些关键表进行检查以确认数据在业务层面的正确性，但这类查询分量比较轻。

Answer 20

了解了，
考虑中，没有环境，也只能思索了：）
IO不高不一定就代表没有问题，也可能存在IO冲突。

Answer 21

insert /*+append*/
都是这样的操作本应该快点，奇怪。
思索中

Answer 22

和同事一起讨论分析了一下，基本上搞定了。大体思路还是并发。
虽然数据产生速度不是问题，数据库所在环境的CPU和IO也都不是问题，网络传输也不是问题，但由于消息串行交互，本身就有大量的等待，小数据量频繁交互，导致数据收发效率极低。这一点的确比较难验证，通过statspack的报告来看，也只是显示等待网络消息收发占用了不少时间。
当我们使用多个进程并发向数据库中导入数据时，能够明显看到效率的提高。当采用5个并发的时候，效率提升到了原来的250%，而此时数据库所在环境的CPU和IO才30％多。
由此，这一问题的原因已经基本可以确定。实际上还留有1个疑点，就是为何在数据库本地执行重量级SQL，CPU占用率也那么低...