Exploring Autonomous Data Warehouse: Part 4

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 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

Topics: Oracle


Recent Posts

Predicting the Rugby World Cup – How did we do?

read more

It’s official, New Zealand will win the 2019 Rugby World Cup!

read more

Why is modernizing your Security key for a move to the Cloud?

read more

Contact us today

 

 

DSP