DSP Blog

Exploring Autonomous Data Warehouse: Part 4

Written by Vic Milne | 26-Jun-2019 13:55:30

Exploring ADW

Part 4 - Auto-Scaling and Loading Data

For this set of blogs I’ve been using the smallest amount of ADW, 1TB storage and 1 OCPU.  I’ve been moving GBs of data in and out of ADW so although it’s not TBs of data it’s still taking several minutes to complete. Since I have the ability to scale up the instance, I thought I would try it and see what impact that has on loading of data.

 

Here are the load times using the DBMS_CLOUD procedure with 1 OCPU

Load Time In Seconds 386.57, 279.908, 309.22, 281.24, 299.11

 

Here are the load times using the DBMS_CLOUD procedure with 4 OCPU

Load Time In Seconds 86.26, 85.21, 79.12, 85.83, 84.13

 

That’s a pretty big improvement.  But for me there is something really interesting about this.  I didn’t change anything that I was doing. I didn’t change the procedure, tweak parameters, or spend ANY time tuning.  I just turned up the dial to FAST.  The other key thing to note is that the time it took to scale from 1 OCPU to 4 OCPUs was maybe a minute or two, if that, and to scale down was the same time as well.  I literally could turn up the performance when I needed and then turn it down again.  The process is agile, flexible and meets my needs - isn’t that what Cloud is all about.

 

For information about Oracle Cloud, making Autonomous Data Warehouse work for you, or anything covered in these blogs, feel free to contact DSP-Explorer using the form on the right of this page and keep an eye on our Social Media for upcoming events and Webinars!

 

Started late? Head back to Part 1: Uploading more than 1MB to Object Storage

 

BEGIN                                                                           DBMS_CLOUD.CREATE_CREDENTIAL(                                                    credential_name => 'CRED_NAME_2',                                            username => 'oacadwcreds',                                                    password => 'Kc:MpcFwI2RbKmcQ7O+r'

  );

END;

/

 

CREATE TABLE ORDERS1                                                                ( order_id number,                                                        user_id number,                                                               eval_set varchar2(100),                                                       order_number number,                                                         order_dow number,                                                             order_hour_of_day number,                                                     days_since_prior_order number (30,20));

 

CREATE TABLE ORDERS2                                                             ( order_id varchar2(100),                                                  user_id varchar2(100),                                                        eval_set varchar2(100),                                                      order_number varchar2(100),                                                  order_dow varchar2(100),                                                      order_hour_of_day varchar2(100),                                              days_since_prior_order varchar2(100));

 

BEGIN                                                                       DBMS_CLOUD.COPY_DATA(                                                            table_name => 'ORDERS1',                                                      credential_name => 'CRED_NAME_2',                                            file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/dsp231823777/b/oacadw/o/ADWHTEST.txt',                        format => json_object('delimiter' value ',' , 'recorddelimiter' value '''\r\n''')

 );

END;

/

 

select * from orders1;

 

select count(*) from orders_ext8;

 

BEGIN

  DBMS_CLOUD.create_external_table(

    table_name      => 'ORDERS_EXT8',

    credential_name => 'CRED_NAME_2',

    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/dsp231823777/b/oacadw/o/orders.csv',

    column_list     => 'ORDER_ID NUMBER,

                        USER_ID  NUMBER,

                        EVAL_SET VARCHAR2(100),

                        ORDER_NUMBER NUMBER,

                        ORDER_DOW NUMBER,

                        ORDER_HOUR_OF_DAY NUMBER,

                             DAYS_SINCE_PRIOR_ORDER NUMBER',

    format          => json_object('delimiter' value ',' , 'ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'blankasnull' value 'true', 'rejectlimit' value '10')

 );

END;

/

 

BEGIN

 DBMS_CLOUD.COPY_DATA(

    table_name => 'ORDERS1',

    credential_name => 'CRED_NAME_2',

    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/dsp231823777/b/oacadw/o/xaa',

    format => json_object('delimiter' value ',' , 'ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'blankasnull' value 'true')

 );

END;

/

 

select * from ORDERS_EXT6

select * from orders2;

select * from COPY$6_LOG

 

 

select 'drop table '||table_name||';' from user_tables

drop table copy$1_log;

 

 

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

    credential_name => 'ADWOAC',

    username => 'oacadwcreds',

    password => 'Kc:MpcFwI2RbKmcQ7O+r'

  );

END;

/

 

BEGIN

  DBMS_CLOUD.create_external_table(

    table_name      => 'ORDERS_EXT1',

    credential_name => 'ADWOAC',

    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/dsp231823777/b/oacadw/o/orders1.csv',

    column_list     => 'ORDER_ID NUMBER,

                        USER_ID  NUMBER,

                        EVAL_SET VARCHAR2(100),

                        ORDER_NUMBER NUMBER,

                        ORDER_DOW NUMBER,

                        ORDER_HOUR_OF_DAY NUMBER,

                             DAYS_SINCE_PRIOR_ORDER NUMBER',

    format          => json_object('delimiter' value ',' , 'ignoremissingcolumns' value 'true', 'blankasnull' value 'true', 'rejectlimit' value '1')

 );

END;

/

 

select * from orders_ext1;

 

CREATE TABLE ORDERS

   ( order_id number,

user_id number,

eval_set varchar2(100),

order_number number,

order_dow number,

order_hour_of_day number,

days_since_prior_order number (30,20));

 

BEGIN

 DBMS_CLOUD.COPY_DATA(

    table_name => 'ORDERS',

    credential_name => 'ADWOAC',

    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/dsp231823777/b/oacadw/o/orders1.csv',

    format => json_object('delimiter' value ',' , 'ignoremissingcolumns' value 'true', 'blankasnull' value 'true', 'rejectlimit' value '1')

 );

END;

/

 

select count(*) from orders;

 

select * from dba_users;

 

CREATE TABLE house_prices

   ( trans_id varchar2(100),

price number,

sale_date varchar2(100),

property_type varchar2(100),

old_new varchar2(100),

duration_ varchar2(100),

town varchar2(100),

district varchar2(100),

county varchar2(100),

PPDCat varchar2(100),

record_status varchar2(100));

 

{4C4EE000-2915-1854-E050-A8C063054F34},190000,2017-02-28 00:00,D,N,F,HUDDERSFIELD,KIRKLEES,WEST YORKSHIRE,A,A

 

drop table house_prices;

 

BEGIN

 DBMS_CLOUD.COPY_DATA(

    table_name => 'HOUSE_PRICES',

    credential_name => 'ADWOAC',

    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/dsp231823777/b/oacadw/o/price_paid_records.csv',

    format => json_object('delimiter' value ',' , 'ignoremissingcolumns' value 'true', 'blankasnull' value 'true', 'rejectlimit' value '100')

 );

END;

/

truncate table house_prices;

select count(*) from house_prices