求助：RAC上大表全表扫描比单实例慢

pandarabbit

版本： oracle 11.2.0.3     oracle linux 6.1

我的测试过程如下：
单实例上现有一张大表CUSTOMER_TEMP
在rac上通过dblink查询单实例大表所占空间：
SQL> select bytes/1024/1024 M from user_segments@todb1 where segment_name='CUSTOMER_TEMP';
         M
----------
      1285

在rac（只有一个节点）上创建这张大表：
SQL> create table customer_temp as select * from customer_temp@todb1;
Table created.

在rac上查询新建表所占空间：
SQL> select bytes/1024/1024 M from user_segments where segment_name='CUSTOMER_TEMP';
         M
----------
      1280
两者所占空间差不多。


rac上测试单实例查询大表的效率：
SQL> select /*+ full(cu_customer) */ count(*) from customer_temp@todb1;
  COUNT(*)
----------
   2434230
Elapsed: 00:00:00.73  --sql执行时间都是取的多次执行后的时间。


测试rac查询大表的效率：
SQL> select /*+ full(cu_customer) */ count(*) from customer_temp;
  COUNT(*)
----------
   2434230
Elapsed: 00:00:02.97

rac比单实例慢了2秒多，而且rac一直跑不进2秒内。

其中sql的10046跟踪和执行计划， 10046和执行计划.rar (20.6 KB, 下载次数: 1369)

2013-9-12 10:36 上传

点击文件名下载附件

见附件。



为了解决rac慢的问题，尝试了如下：
节点1删除rac集群，重装了oracle软件，把库的rman备份恢复到了节点1的本地硬盘上，在这个节点上以上sql速度很快，排除节点1硬件问题。
把盘柜格式化成ext3，在节点2上dd测试本地硬盘和盘柜的速度，都正常。
节点2重装oracle软件，在节点2上把库的rman备份恢复到了盘柜上，sql速度也很快，所以节点2服务器、盘柜IO、各种驱动、光纤都是没有问题的。
在节点2重装了一个单节点的rac，再测试同一张表，速度又下来了，所以问题应该出在asm上。
之前用的是asmlib方式管理asm设备，又改用udev方式管理asm设备，还是速度很慢，问题确认出在asm上。
我这里盘柜用的是raid10，划了三个lun，空间分别是10g、1.8t、1t，分别用于ocrvot、data、fra，asm磁盘组冗余用的是external。
大家有没有遇到或注意到用asm后，库变慢的问题？

这几天为了解决这个问题，看到了这篇文章http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2109833600346625821
tom好像在说asm没有buffer，而文件系统是有cache的，asm通过增大buffer cache的大小来弥补。
我这11g默认用了memory_target内存自动管理，是否改为手动分配SGA、PGA，手动分配一个很大的db buffer cache，就可以把速度提升一些呢？我这现在把实验环境拆掉了，再有环境时试一试。

大师及各位牛人是否遇到过这个问题，是怎么解决的呢？

harryzhang

可以试试在使用部分本地硬盘，用它的RAW来对比。

harryzhang

你的10046里有

direct path read

你可以设置event
10949, 00000, "Disable autotune direct path read for full table scan"
// *Cause:
// *Action:  Disable autotune direct path read for serial full table scan.


检查你的参数
FILESYSTEMIO_OPTIONS=DIRECTIO.
This will enable asynch IO to raw devices and ASM, but not to datafiles stored on the filesystem

harryzhang

ASM Inherently Performs Asynchronous I/O Regardless of filesystemio_options Parameter [ID 751463.1]

pandarabbit

harryzhang 发表于 2013-9-12 11:07
你的10046里有

direct path read

感谢回复。
设置event10949是禁用direct path read这个功能吗？
FILESYSTEMIO_OPTIONS=DIRECTIO这个参数是打开asm的异步IO吧？

pandarabbit

harryzhang 发表于 2013-9-12 11:09
ASM Inherently Performs Asynchronous I/O Regardless of filesystemio_options Parameter

这是一篇文档吧，我这metalink账号不在我手中，要到后，去看看。如果这个文档比较短的话，能在这里贴一下吗？

harryzhang

pandarabbit 发表于 2013-9-12 11:11
感谢回复。
设置event10949是禁用direct path read这个功能吗？
FILESYSTEMIO_OPTIONS=DIRECTIO这个参数 ...

ASM不受FILESYSTEMIO_OPTIONS=DIRECTIO 影响

Maclean Liu(刘相兵

给出 RAW  10046 trace 不要 处理过的，给出2个环境的一份AWR报告

给出2个环境中下面的指标,

set serveroutput on;
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/

Maclean Liu(刘相兵

还有 需要看

select count(*) ,blocks from dba_extents where segment_name='CUSTOMER_TEMP'  group by blocks;

pandarabbit

Maclean Liu(刘相兵 发表于 2013-9-12 12:05
给出 RAW  10046 trace 不要 处理过的，给出2个环境的一份AWR报告

给出2个环境中下面的指标,

非常感谢老大回复。
我这里是11.2 rac 用的是asm，是要搭建一个raw的rac，然后把10046trace发上来？
但现在我这环境拆掉了，这两天尽快我再搭一个，然后把10046和这些指标发上来。

Maclean Liu(刘相兵

只要你能重现该现象 那么都可以

pandarabbit

Maclean Liu(刘相兵 发表于 2013-9-12 12:29
只要你能重现该现象 那么都可以

老大，相关资料已收集，请有空时帮忙分析一下。 awr、10046、脚本结果.rar (186.84 KB, 下载次数: 1196)

2013-9-13 10:35 上传

点击文件名下载附件

pandarabbit

Maclean Liu(刘相兵 发表于 2013-9-12 12:19
还有 需要看

select count(*) ,blocks from dba_extents where segment_name='CUSTOMER_TEMP'  group by b ...

不好意思，刚注意到这个回复。
收集信息如下：
RAC上：
SQL> select count(*) ,blocks from dba_extents where segment_name='CUSTOMER_TEMP'  group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
        63        128
        16          8
         3        896
       118       1024
         4       8192

Elapsed: 00:00:05.15
SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128


单实例上：
SQL> select count(*) ,blocks from dba_extents where segment_name='CUSTOMER_TEMP'  group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
        69        128
       120       1024
        16          8
         1       8064
         3       8192

Elapsed: 00:00:10.63
SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

另外，单实例和单节点RAC的服务器硬件配置是一样的，当时为了搭RAC买了两台相同配置的服务器。

pandarabbit

harryzhang 发表于 2013-9-12 11:24
ASM不受FILESYSTEMIO_OPTIONS=DIRECTIO 影响

这个参数默认是none，设为DIRECTIO后，sql速度没有提高。

SQL> show parameter FILESYSTEMIO_OPTIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
SQL> alter system set FILESYSTEMIO_OPTIONS=DIRECTIO scope=spfile;

System altered.

重启库后：

SQL> select /*+ full(customer_temp) */ count(*) from customer_temp;

  COUNT(*)
----------
   2434230

Elapsed: 00:00:03.37  --仍然执行时间较长

Execution Plan
----------------------------------------------------------
Plan hash value: 1322927158

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 42453   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEMP |  2434K| 42453   (1)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     155842  consistent gets
     155836  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

pandarabbit

Maclean Liu(刘相兵 发表于 2013-9-12 12:19
还有 需要看

select count(*) ,blocks from dba_extents where segment_name='CUSTOMER_TEMP'  group by b ...

老大，我把direct path read禁用后，RAC速度立马上来了。
SQL> alter system set "_serial_direct_read"=never;
关库重启
然后就比单实例快了。
SQL> select /*+ full(customer_temp) */ count(*) from customer_temp;

  COUNT(*)
----------
   2434230

Elapsed: 00:00:00.49

Maclean Liu(刘相兵

还需要一个测试

在你的单机环境里

echo 3 > /proc/sys/vm/drop_caches

set timing on;
alter system flush buffer_cache;
alter system flush shared_pool;
在测试2次你的语句

pandarabbit

Maclean Liu(刘相兵 发表于 2013-9-13 12:20
还需要一个测试

在你的单机环境里

单实例上：
SQL> set timing on;
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:01.57
SQL> alter system flush shared_pool;
System altered.

Elapsed: 00:00:00.62
SQL> select /*+ full(customer_temp) */ count(*) from customer_temp;

  COUNT(*)
----------
   2434230

Elapsed: 00:00:12.19


RAC上第一次读该表的用时：
SQL> set timing on
SQL> select /*+ full(customer_temp) */ count(*) from customer_temp;

  COUNT(*)
----------
   2434230

Elapsed: 00:00:06.08

第一次物理读时，RAC是比单实例快的，但之后RAC速度一直不如单实例。



=====================================================================================
在RAC和单实例都禁用direct path read后，RAC和单实例都没有物理读了，此时RAC比单实例快了一点点。

单实例上：
SQL> select /*+ full(customer_temp) */ count(*) from customer_temp;

  COUNT(*)
----------
   2434230

Elapsed: 00:00:00.50

Execution Plan
----------------------------------------------------------
Plan hash value: 1322927158

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 42453   (1)| 00:00:06 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEMP |  2434K| 42453   (1)| 00:00:06 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     155858  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


RAC上：
SQL> select /*+ full(customer_temp) */ count(*) from customer_temp;

  COUNT(*)
----------
   2434230

Elapsed: 00:00:00.46

Execution Plan
----------------------------------------------------------
Plan hash value: 1322927158

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 42453   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMER_TEMP |  2434K| 42453   (1)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     155858  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Maclean Liu(刘相兵

解释几个问题

1、 为什么是direct path read?

因为11g中引入了adaptive serial direct path read ，具体见 http://www.askmaclean.com/archiv ... al_direct_read.html

因为direct path read是直接读入到PGA的，而并不进入buffer cache。

所以 每一次都是物理读 FULL SCAN TABLE，而不是逻辑读取

因为全是物理读所以 速度取决于IO

Maclean Liu(刘相兵

2、关于 IO

通过IO calibrate测试 来了解IO

RAC:
RAC上第一次执行：
SQL> set serveroutput on;
SQL> DECLARE
  2  lat INTEGER;
  3  iops INTEGER;
  4  mbps INTEGER;
  5  BEGIN
  6  --DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
  7  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
  8  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9  DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
10  dbms_output.put_line('max_mbps = ' || mbps);
11  end;
12  /
max_iops = 4137
latency = 13
max_mbps = 427

PL/SQL procedure successfully completed.

RAC上第二次执行：
SQL> set serveroutput on;
SQL> DECLARE
  2  lat INTEGER;
  3  iops INTEGER;
  4  mbps INTEGER;
  5  BEGIN
  6  --DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
  7  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
  8  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9  DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
10  dbms_output.put_line('max_mbps = ' || mbps);
11  end;
12  /
max_iops = 4182
latency = 13
max_mbps = 435

PL/SQL procedure successfully completed.



单实例：
单实例上第一次执行：
SQL> set serveroutput on;
SQL> DECLARE
  2  lat INTEGER;
  3  iops INTEGER;
  4  mbps INTEGER;
  5  BEGIN
  6  --DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
  7  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
  8  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9  DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
10  dbms_output.put_line('max_mbps = ' || mbps);
11  end;
12  /
max_iops = 586
latency = 14
max_mbps = 64

PL/SQL procedure successfully completed.


单实例上第二次执行：
SQL> set serveroutput on;
SQL> DECLARE
  2  lat INTEGER;
  3  iops INTEGER;
  4  mbps INTEGER;
  5  BEGIN
  6  --DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
  7  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 15, iops, mbps, lat);
  8  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  9  DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
10  dbms_output.put_line('max_mbps = ' || mbps);
11  end;
12  /
max_iops = 663
latency = 15
max_mbps = 64

PL/SQL procedure successfully completed.




上面可以看到 RAC的IO  iops 4000多 mbps 450M左右， 而单机 iops 600左右 mbps 64M


按道理来说RAC的IO 还是比单机好

但为什么之前的执行中 RAC比单机慢？

==》 我想主要是因为 文件系统缓存，因为单机使用了 文件系统， 而 RAC是用ASM的

1. SQL ID: 3hyucdmmh9s9m
   
2. Plan Hash: 1322927158
   
3. select count(*)
   
4. from
   
5. customer_temp
   
6. 
   
7. 
   
8. call     count       cpu    elapsed       disk      query    current        rows
   
9. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
   
10. Parse        1      0.00       0.00          0          0          0           0
    
11. Execute      1      0.00       0.00          0          0          0           0
    
12. Fetch        2      1.65       1.65     155837     155843          0           1
    
13. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
14. total        4      1.65       1.65     155837     155843          0           1
    
15. 
    
16. Misses in library cache during parse: 1
    
17. Optimizer mode: ALL_ROWS
    
18. Parsing user id: 86  (CRM)
    
19. 
    
20. Rows     Row Source Operation
    
21. -------  ---------------------------------------------------
    
22.       1  SORT AGGREGATE (cr=155843 pr=155837 pw=0 time=0 us)
    
23. 2434230   TABLE ACCESS FULL CUSTOMER_TEMP (cr=155843 pr=155837 pw=0 time=1718475 us cost=42429 size=0 card=2434230)
    
24. 
    
25. 
    
26. Rows     Execution Plan
    
27. -------  ---------------------------------------------------
    
28.       0  SELECT STATEMENT   MODE: ALL_ROWS
    
29.       1   SORT (AGGREGATE)
    
30. 2434230    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'CUSTOMER_TEMP' (TABLE)
    
31. 
    
32. 
    
33. 
    
34. Elapsed times include waiting on following events:
    
35.   Event waited on                             Times   Max. Wait  Total Waited
    
36.   ----------------------------------------   Waited  ----------  ------------
    
37.   SQL*Net message to client                       2        0.00          0.00
    
38.   Disk file operations I/O                        4        0.00          0.00
    
39.   db file sequential read                         1        0.00          0.00
    
40.   direct path read                            38994        0.00          0.63
    
41.   SQL*Net message from client                     2        7.81          7.81

复制代码

单机物理读取155837 个物理块( 大约1217M)的数据 仅用了1.65 秒，配合上面 单机 iops 600左右 mbps 64M 不借助 文件系统缓存是不可能的。 单机有62.9  G的物理内存

Maclean Liu(刘相兵

我想我们能回答最初的问题了：

为什么这个查询一开始在RAC上比单机上慢？

==》 由于11g adaptive serial direct path read特性导致该SQL每次都是物理全表读取， 而单机使用文件系统具有文件系统缓存的优势，所以其IO显得很快， 但如果 清楚了文件系统缓存则 要比RAC使用的存储+ASM来得慢。

如17楼的现象


当全部关闭 11g adaptive serial direct path read特性后， 对这个表的FULL SCAN进入buffer cache，变为逻辑读，则 RAC和单机不再有区别

Alex

学习了！

singlemice

学习了，思路好重要

pandarabbit

Maclean Liu(刘相兵 发表于 2013-9-13 13:10
我想我们能回答最初的问题了：

为什么这个查询一开始在RAC上比单机上慢？

感谢老大分析。
我总结一下，标题中问题原因是单机上有文件系统缓存，而RAC+ASM没有缓存。
禁用direct path read后，单机和RAC都可以在buffer cache中缓存，于是RAC速度也就上来了，并且比单机快了。