www.eygle.com

---

September 16, 2009

SUN + Oracle推出Exadata 2 终止与HP的合作

作者:eygle

出处:http://blog.eygle.com

Oracle终于发布了SUN + Oracle版的Exadata 2，并且据说已经宣布了终止与HP的合作。
这几天备受关注的OLTP Database Machine with Sun FlashFire Technology也初露端倪。

以下是关于这个新闻的摘要：
据国外媒体报道，因甲骨文准备收购惠普竞争对手Sun，目前双方已终止合作关系。
甲骨文CEO拉里-埃里森（Larry Ellison）周二披露了新Exadata的消息。一年前，埃里森曾公布了甲骨文在惠普帮助下进入硬件业务的消息。他当时曾表示，惠普将是甲骨文进军硬 件业的关键盟友。但自甲骨文今年4月公布以逾70亿美元收购Sun的消息后，双方的关系就发生了巨大变化。

惠普和Sun在服务器和储存设备市场上是激烈的竞争对手。埃里森表示，新Exadata计算机将是甲骨文软件与Sun硬件结合的第一款产品。一名甲骨文女发言人称，公司将不再发售与惠普合作开发的Exadata计算机。

在Oracle的官方站点上，也可以看到这个迹象，所有关于HP的字样已经找不到了，取而代之的是SUN + Oracle的组合：

http://www.oracle.com/database/database-machine.html

不过个人感觉这是一件好事，Oracle和HP的合作一直充满了诸多变数，不可控因素过多，这也是过去一年Oracle的HP Oracle Database Machine销售有限的原因之一。
现在SUN + Oracle形成了紧密的结合，自然可以以更稳健、可靠的关系为用户提供产品。

该硬件的指标是：

Posted by eygle at 9:53 AM | Comments (3)

---

CBO的魔术 - 一个错误的索引选择会带来的后果

作者:eygle

出处:http://blog.eygle.com

在对20亿记录大表的查询中，发现Oracle的执行计划选择并不稳定，当然这是CBO的正常行为，然而当选择不同时，结果是巨大的。

在以下查询中，使用指定的索引，查询快速得出结果，但是这依赖于Hints的强制指定：

SQL> select /*+  index(smsmg IDX_smsmg_DEST_MDN)  */ count(*)
   2 from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';

  COUNT(*)
----------
         1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1659057974

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |     1 |    18 |    98   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                     |                          |     1 |    18 |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| smsmg                    |     1 |    18 |    98   (0)| 00:00:02 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | IDX_smsmg_msg_to_des_mdn |   106 |       |     4   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SERVICE_ID"='54')
   3 - access("msg_to_dest_mdn"='861318888888')


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

而如果不加Hints，查询是一时无法得出结果的：

SQL> select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';
 
select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54'
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation


Elapsed: 00:04:27.88

其执行计划显示，这一缺省的执行方式导致了错误的索引选择：

SQL> set autotrace trace explain
SQL> select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1152948967

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |    18 |     5   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                      |     1 |    18 |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| smsmg                |     1 |    18 |     5   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | IDX_smsmg_SERVICE_ID |     1 |       |     4   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("msg_to_dest_mdn"='861318888888')
   3 - access("SERVICE_ID"='54')

说CBO是Oracle最为博大精深的技术一点也不为过，只是这技术越复杂越深奥出错的机会就越多了。

这个表的数据量大约是500G：

SQL> select segment_name,partition_name,bytes/1024/1024/1024 GB,blocks from dba_segments
  2  where owner='SMSMSG' and segment_name='SMSSENDMSG';

SEGMENT_NAME                   PARTITION_NAME          GB     BLOCKS
------------------------------ --------------- ---------- ----------
SMSSENDMSG                    M01                 30.625    4014080
SMSSENDMSG                    M02                 29.875    3915776
SMSSENDMSG                    M03                  43.25    5668864
SMSSENDMSG                    M04                     38    4980736
SMSSENDMSG                    M05                43.1875    5660672
SMSSENDMSG                    M06                50.6875    6643712
SMSSENDMSG                    M08                55.4375    7266304
SMSSENDMSG                    M09                 32.125    4210688
SMSSENDMSG                    M10                23.9375    3137536
SMSSENDMSG                    M11                25.6875    3366912
SMSSENDMSG                    M12                31.9375    4186112

SEGMENT_NAME                   PARTITION_NAME          GB     BLOCKS
------------------------------ --------------- ---------- ----------
SMSSENDMSG                    M13             .000061035          8
SMSSENDMSG                    M07                   58.5    7667712

13 rows selected.

SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments where owner='SMSMSG' and segment_name='SMSSENDMSG';

        GB
----------
463.250061

每个分区的记录数量大约是1~2亿条：

SQL> select table_owner,table_name,partition_name,num_rows from dba_tab_partitions
  2  where table_owner='SMS9885' and table_name='SMS_TO_ISMG';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME    NUM_ROWS
------------------------------ ------------------------------ --------------- ----------
SMSMSG                        SMSSENDMSG                    M01              135605804
SMSMSG                        SMSSENDMSG                    M02              134599287
SMSMSG                        SMSSENDMSG                    M03              187959758
SMSMSG                        SMSSENDMSG                    M04              169663942
SMSMSG                        SMSSENDMSG                    M05              187435468
SMSMSG                        SMSSENDMSG                    M06              222079762
SMSMSG                        SMSSENDMSG                    M07              256482704
SMSMSG                        SMSSENDMSG                    M08              229089535
SMSMSG                        SMSSENDMSG                    M09              122453724
SMSMSG                        SMSSENDMSG                    M10              104093080
SMSMSG                        SMSSENDMSG                    M11              116095184

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME    NUM_ROWS
------------------------------ ------------------------------ --------------- ----------
SMSMSG                        SMSSENDMSG                    M12              143216009
SMSMSG                        SMSSENDMSG                    M13                      0

13 rows selected.

前面使用的都是单键值索引，其索引选择性参考前几天的记录。


-The End-

Posted by eygle at 8:19 AM | Comments (22)

---