News & Events

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

Get in touch

For more information or to schedule a demo please contact us

Contact us

News

DSP winners at the UK Oracle User Group Awards 2017/18

DSP attended the UK Oracle User Group Partner of the Year Awards last week, and we came away with awards for both categories in which we were nominated

Read more
View All

Event

"Self Managing Databases: Fable, Fantasy or the Future"

Join us for lunch and to hear some war stories from staff and customers with experience of Oracle Database Cloud Service. We'll also understand more about the Autonomous Database and how Oracle will compete with AWS.

Read more
View All

Blog

How to choose the right MSP for Your Business

How has the role of an MSP changed in the past decade, and what should your selection criteria be for choosing an MSP to work with your business?

Read more
View All