gp借助类DBLINK访问oracle性能测试
0. Oracle测试数据准备:
[oracle@db1 ~]$ sqlplus system/000000


SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 25 10:26:06 2014


Copyright (c) 1982, 2011, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>  drop table test ;


Table dropped.


SQL> create table test(id int,name varchar2(20),age int,msg varchar2(20));


Table created.


SQL> insert into test values(1,'aaaaa',1,'aaaaa');


1 row created.


SQL> insert into test values(2,'bbbbb',2,'bbbbb');


1 row created.


SQL> insert into test values(3,'ccccc',3,'ccccc');


1 row created.


SQL>  insert into test values(4,'ddddd',4,'ddddd');


1 row created.


SQL> insert into test values(5,'eeeee',5,'eeeee');


1 row created.


SQL> 
SQL> commit;


Commit complete.


SQL>  select count(*) from test;


  COUNT(*)
----------
         5


SQL> INSERT INTO TEST SELECT * FROM TEST;


5 rows created.


SQL> commit;


Commit complete.


SQL> select count(*) from test;


  COUNT(*)
----------
    100000


SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='TEST';


SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
TEST
              3
 模拟插入10W的数据。


1. 使用落地文件外部表加载测试
首先在oracle服务器端建数据导出，并开启gpfdist服务进程:
[oracle@db1 ~]$ gtlions.ora2text.bin user=system/000000 query='select * from test' text=csv file=test.sql fast=true
           0 rows exported at 2014-03-25 10:30:19, size 0 MB.
      100000 rows exported at 2014-03-25 10:30:20, size 2 MB.
         output file test.sql closed at 100000 rows, size 2 MB.
[oracle@db1 ~]$ nohup gpfdist -d . -p 9999 &
[1] 15147
[oracle@db1 ~]$ nohup: 忽略输入并把输出追加到"nohup.out"


[1]+  Exit 1                  nohup gpfdist -d . -p 9999
[oracle@db1 ~]$ ps -ef | grep gpfdist
oracle   15149 15068  0 10:30 pts/8    00:00:00 grep gpfdist
oracle   62994 62778  0 Mar24 ?        00:00:04 gpfdist -d . -p 9999
导出阶段耗时1S；


接下来在gp创建相关外部表并加载数据入库:
[gpadmin@bdb ~]$ psql postgres
Timing is on.
psql (8.2.15)
Type "help" for help.


postgres=# \timing on
Timing is on.
postgres=# drop table if exists gt_test;
create table gt_test(id int,name character varying(20),age int,msg character varying(20)) distributed randomly;
DROP TABLE
Time: 17.802 ms
postgres=# create table gt_test(id int,name character varying(20),age int,msg character varying(20)) distributed randomly;
drop external table if exists gt_test_ext;
CREATE TABLE
Time: 17.386 ms
postgres=# drop external table if exists gt_test_ext;
create external table gt_test_ext(like gt_test) location ('gpfdist://192.168.1.2:9999/test.sql') format 'csv' (header);
DROP EXTERNAL TABLE
Time: 6.734 ms
postgres=# create external table gt_test_ext(like gt_test) location ('gpfdist://192.168.1.2:9999/test.sql') format 'csv' (header);
NOTICE:  HEADER means that each one of the data files has a header row.
CREATE EXTERNAL TABLE
Time: 13