---
title: "Exploring Autonomous Data Warehouse: Part 4"
description: 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 the loading of data.
---

[DSP Blog ](https://content.dsp.co.uk)

# [Exploring Autonomous Data Warehouse: Part 4](https://content.dsp.co.uk/exploring-autonomous-data-warehouse-auto-scaling)

 Written by [Vic Milne](https://content.dsp.co.uk/author/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](https://www.dsp.co.uk/oracle-cloud/), making [Autonomous Data Warehouse](https://content.dsp.co.uk/oracle-autonomous-data-warehouse-bitesize) 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](https://twitter.com/dsp_uk) for upcoming events and Webinars!

 

Started late? Head back to Part 1: [Uploading more than 1MB to Object Storage](https://www.dsp.co.uk/exploring-adw-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

[View full post](https://content.dsp.co.uk/exploring-autonomous-data-warehouse-auto-scaling)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Vic Milne"
  },
  "dateModified" : "2023-12-08T12:45:32.645Z",
  "datePublished" : "2019-06-26T13:55:30Z",
  "headline" : "Exploring Autonomous Data Warehouse: Part 4",
  "image" : {
    "@type" : "ImageObject",
    "height" : 668,
    "url" : "https://www.dsp.co.uk/hubfs/Close%20up%20low%20angle%20view%20of%20a%20man%20working%20from%20home%20on%20a%20laptop%20computer%20sitting%20at%20a%20desk%20surfing%20the%20internet-1.jpeg",
    "width" : 1000
  },
  "mainEntityOfPage" : "https://content.dsp.co.uk/exploring-autonomous-data-warehouse-auto-scaling",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60.0,
      "url" : "https://content.dsp.co.uk/hubfs/Logos/DSP/DSP-logo-2019-150px.png",
      "width" : 58.44156
    },
    "name" : "DSP Blog"
  }
}
```