oracle_fdw test case-CSDN博客

前面一篇BLOG主要介绍了oracle_fdw的部署，这里来看看它的一些应用特性。

1. 摄取速度

测试从oracle数据库获取数据的速度 .

oracle表 :

tbl_oracle_fdw(id int primary key,firstname varchar2(32),lastname varchar2(32),crt_time date);

数据量 2000W，不含索引的大小是994 MB。

insert into tbl_oracle_fdw select rownum,'zhou'||rownum,'digoal'||rownum,sysdate from dual connect by level<=20000000;
postgreSQL表 : 
tbl_copy_from_oracle (id int primary key,firstname varchar(32),lastname varchar(32),crt_time timestamp(0) without time zone);

开始获取数据 :

digoal=> insert into tbl_copy_from_oracle (id,firstname,lastname,crt_time) select id,firstname,lastname,crt_time from tbl_oracle_fdw;

从oracle服务器的sar -n DEV输出看到的网络流量txbyt/s约1916557，994MB数据需要约531秒传输完成。

测试环境是内网，服务器网卡都是1GB的，所以网络带宽不存在问题。速度慢在ORACLE的获取接口上。如果另外再开一个同样的SQL，sar -n DEV监测到的流量将翻倍。

下面来看看真实的时间开销 :

INSERT 0 20000000
Time: 435654.745 ms

除去ORACLE的头部信息和FREEFCT的剩余空间，这个速度是可以预料的。

获取速度和oracle到oracle之间的dblink还是有很大差距的。

2. 连接复用

这一项要测试的是，当连接空闲的时候是否会复用，结果是不会。请看如下测试：

postgresql SESSION A :

# 假设tbl_oracle_fdw是foreign表,执行下面的SQL将会开启一个到oracle的连接。

select * from tbl_oracle_fdw where id=1;

# 查看postgresql的PID

digoal=> select procpid from pg_stat_activity ;
 procpid 
---------
   25412
(1 row)

# 查看oracle下对应的session信息

SQL> select username,module,action from v$session where username='DIGOAL';

USERNAME                       MODULE                                           ACTION
------------------------------ ------------------------------------------------ --------------------------------
DIGOAL                         postgres                                         25412

# postgresql SESSION B :

#　在确保SESSION A没有操作FOREIGN表的情况下，新开一个session

postgres@db5-> psql -h 127.0.0.1 digoal digoal
psql (9.1.0)
Type "help" for help.
digoal=> select * from tbl_oracle_fdw where id=1; 
 id | firstname | lastname |      crt_time       
----+-----------+----------+---------------------
  1 | zhou1     | digoal1  | 2011-09-15 14:11:19
(1 row)

# 查看oracle下对应的session信息

# 显然没有复用刚才的连接,新开了一个SESSION.

SQL> select username,module,action from v$session where username='DIGOAL';
USERNAME                       MODULE                                           ACTION
------------------------------ ------------------------------------------------ --------------------------------
DIGOAL                         postgres                                         27362
DIGOAL                         postgres                                         25412

3. postgreSQL session退出后连接oracle的SESSION是否断开

退出上一步测试的SESSION A，然后查看ORACLE的v$session如下,答案是postgreSQL到oracle的连接在postgresql本地的SESSION断开后随之断开。

SQL> select username,module,action from v$session where username='DIGOAL';

USERNAME                       MODULE                                           ACTION
------------------------------ ------------------------------------------------ --------------------------------
DIGOAL                         postgres                                         27362

# 还可以通过oracle_close_connections()函数来关闭当前SESSION产生的到ORACLE的连接，不能关闭其他SESSION打开的到ORACLE的连接。

# select oracle_close_connections(); 这个普通用户也可以执行。

4. 执行计划

digoal=> explain verbose select id from tbl_oracle_fdw limit 1; 
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Limit  (cost=3360000.00..3360000.00 rows=1 width=252100251)
   Output: id
   ->  Foreign Scan on public.tbl_oracle_fdw  (cost=3360000.00..3360000.00 rows=19392327 width=252100251)
         Output: id
         Oracle query: SELECT /*7b8c87cefce2813f580b573fb8160ca0*/ "ID" FROM digoal.tbl_oracle_fdw
         Oracle plan: SELECT STATEMENT
         Oracle plan:   TABLE ACCESS FULL TBL_ORACLE_FDW
(7 rows)

digoal=> explain verbose select id from tbl_oracle_fdw order by id limit 1;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=19395313283402.54..19395313283402.54 rows=1 width=252100251)
   Output: id
   ->  Sort  (cost=19395313283402.54..19395313331883.35 rows=19392327 width=252100251)
         Output: id
         Sort Key: tbl_oracle_fdw.id
         ->  Foreign Scan on public.tbl_oracle_fdw  (cost=3360000.00..3360000.00 rows=19392327 width=252100251)
               Output: id
               Oracle query: SELECT /*7b8c87cefce2813f580b573fb8160ca0*/ "ID" FROM digoal.tbl_oracle_fdw
               Oracle plan: SELECT STATEMENT
               Oracle plan:   TABLE ACCESS FULL TBL_ORACLE_FDW
(10 rows)

digoal=> explain verbose select id from tbl_oracle_fdw where id=2; 
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tbl_oracle_fdw  (cost=10000.00..10000.00 rows=1 width=13)
   Output: id
   Filter: (tbl_oracle_fdw.id = 2)
   Oracle query: SELECT /*21430ee36f6e64ef458e259660612a93*/ "ID" FROM digoal.tbl_oracle_fdw WHERE ("ID" = 2)
   Oracle plan: SELECT STATEMENT
   Oracle plan:   INDEX RANGE SCAN IDX_ID_1 (condition "ID"=2)
(6 rows)

接下来看看这三条语句真正的执行时间 :

digoal=> select id from tbl_oracle_fdw order by id limit 1;
 id 
----
  1
(1 row)
Time: 99100.774 ms
digoal=> select id from tbl_oracle_fdw limit 1;
 id 
----
  1
(1 row)
Time: 15.459 ms
digoal=> select id from tbl_oracle_fdw where id=1;
 id 
----
  1
(1 row)
Time: 14.921 ms

# 相差非常之大，

首先，select id from tbl_oracle_fdw where id=1; 这句SQL快是无可非议的,因为走索引了，GET过来只有1条。

然后是 select id from tbl_oracle_fdw limit 1; 虽然是全表扫描，但是这里返回一条结果之后就不会在GET了。所以也是很快的。

最慢的是select id from tbl_oracle_fdw order by id limit 1;原因是需要把ID全部GET到POSTGRESQL，然后排序，在LIMIT 1。这个如果能改进一下就完美了，应该在ORACLE里面排好序再过来的。

5. HINT

digoal=> explain verbose select /*+ parallel(t1,7) */ count(distinct id) from tbl_oracle_fdw t1;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3408480.82..3408480.83 rows=1 width=252100251)
   Output: count(DISTINCT id)
   ->  Foreign Scan on public.tbl_oracle_fdw t1  (cost=3360000.00..3360000.00 rows=19392327 width=252100251)
         Output: id, firstname, lastname, crt_time
         Oracle query: SELECT /*7b8c87cefce2813f580b573fb8160ca0*/ "ID" FROM digoal.tbl_oracle_fdw
         Oracle plan: SELECT STATEMENT
         Oracle plan:   TABLE ACCESS FULL TBL_ORACLE_FDW
(7 rows)

# 显然未使用oracle的HINT

# 下面是直接在ORACLE里面使用HINT的EXPLAIN

SQL> explain plan for select /*+ parallel(t1,7) */ count(distinct id) from tbl_oracle_fdw t1;
Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1669356901

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |     1 |    13 |  4428   (1)| 00:00:54 |        |      |            |
|   1 |  SORT GROUP BY            |                |     1 |    13 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |                |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001       |     1 |    13 |            |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT GROUP BY         |                |     1 |    13 |            |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE           |                |     1 |    13 |            |          |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH        | :TQ10000       |     1 |    13 |            |          |  Q1,00 | P->P | HASH       |
|   7 |        SORT GROUP BY      |                |     1 |    13 |            |          |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR |                |    19M|   240M|  4428   (1)| 00:00:54 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| TBL_ORACLE_FDW |    19M|   240M|  4428   (1)| 00:00:54 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
20 rows selected.

6. join

可以远程表和本地表join,还可以不同数据库的远程表JOIN。

如:

table1 来自172.16.3.2上的ORACLE

table2 来自172.16.3.3上的ORACLE

table3 是本地表

这三个表可以任意JOIN。

【注意】

大表的全表GET一定要小心，速度是比较慢的。

【参考】

http://blog.163.com/digoal@126/blog/static/16387704020118151162340/

【其他fdw】