Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 求助：RAC上大表全表扫描比单实例慢
发新帖
pandarabbit

0

积分

1

好友

4

主题

[Exadata硬件] 求助：RAC上大表全表扫描比单实例慢

1#
发表于 2013-9-12 10:46:47 | 查看: 8473| 回复: 22
本帖最后由 pandarabbit 于 2013-9-12 12:25 编辑

版本： 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, 下载次数: 1143) 见附件。



为了解决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，就可以把速度提升一些呢？我这现在把实验环境拆掉了，再有环境时试一试。

大师及各位牛人是否遇到过这个问题，是怎么解决的呢？
2#
发表于 2013-9-12 10:59:30
可以试试在使用部分本地硬盘，用它的RAW来对比。

回复 只看该作者 道具 举报

3#
发表于 2013-9-12 11:07:31
你的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

回复 只看该作者 道具 举报

4#
发表于 2013-9-12 11:09:05
ASM Inherently Performs Asynchronous I/O Regardless of filesystemio_options Parameter [ID 751463.1]

回复 只看该作者 道具 举报

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

direct path read

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

回复 只看该作者 道具 举报

6#
发表于 2013-9-12 11:19:23
本帖最后由 pandarabbit 于 2013-9-12 11:24 编辑
harryzhang 发表于 2013-9-12 11:09
ASM Inherently Performs Asynchronous I/O Regardless of filesystemio_options Parameter


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

回复 只看该作者 道具 举报

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

ASM不受FILESYSTEMIO_OPTIONS=DIRECTIO 影响

回复 只看该作者 道具 举报

8#
发表于 2013-9-12 12:05:06
给出 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;
/

回复 只看该作者 道具 举报

9#
发表于 2013-9-12 12:19:58
还有 需要看

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

回复 只看该作者 道具 举报

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

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

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

回复 只看该作者 道具 举报

11#
发表于 2013-9-12 12:29:29
只要你能重现该现象 那么都可以

回复 只看该作者 道具 举报

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

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

回复 只看该作者 道具 举报

13#
发表于 2013-9-13 10:45:22
本帖最后由 pandarabbit 于 2013-9-13 10:47 编辑
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买了两台相同配置的服务器。

回复 只看该作者 道具 举报

14#
发表于 2013-9-13 11:04:06
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>

回复 只看该作者 道具 举报

15#
发表于 2013-9-13 11:30:30
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

回复 只看该作者 道具 举报

16#
发表于 2013-9-13 12:20:27
还需要一个测试

在你的单机环境里

echo 3 > /proc/sys/vm/drop_caches

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

回复 只看该作者 道具 举报

17#
发表于 2013-9-13 12:44:46
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

回复 只看该作者 道具 举报

18#
发表于 2013-9-13 13:01:48
解释几个问题

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

回复 只看该作者 道具 举报

19#
发表于 2013-9-13 13:07:21
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


  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

  16. Misses in library cache during parse: 1
  17. Optimizer mode: ALL_ROWS
  18. Parsing user id: 86  (CRM)

  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)


  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)



  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的物理内存

回复 只看该作者 道具 举报

20#
发表于 2013-9-13 13:10:52
我想我们能回答最初的问题了：

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

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

如17楼的现象


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

回复 只看该作者 道具 举报

21#
发表于 2013-9-13 13:24:02
学习了！

回复 只看该作者 道具 举报

22#
发表于 2013-9-13 14:00:09
学习了，思路好重要

回复 只看该作者 道具 举报

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

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

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

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-19 02:24 , Processed in 0.060932 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569