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