Loading...

Follow Striving for Optimal Performance Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

The contents of the V$SQL_CS_HISTOGRAM view is used by the SQL engine to decide when a cursor is made bind aware, and therefore, when it should use adaptive cursor sharing. For each child cursor, the view shows three buckets. It is of general knowledge that the first one (BUCKET_ID equal 0) is associated with the executions that process up to and including 1,000 rows, the second one (BUCKET_ID equal 1) with the executions that processes between 1,001 and 1,000,000 rows, and the third one (BUCKET_ID equal 2) with the executions that processes more than 1,000,000 rows. The idea is that after an execution, the SQL engine associates (that is, increments the COUNT column) the execution to one of the three buckets. Then, depending on the distribution, decides whether a cursor has to be made bind aware.

As far as I know, Oracle does not document the value associated to the two thresholds (1,000 and 1,000,000 rows). A way of getting a “confirmation” about their value is to activate event 10507 at level 1 and to execute a SQL statement that requires an adaptive sharing context being initialized. The following is an example illustrates:

CREATE TABLE t 
AS 
SELECT rownum AS id, rpad('*',100,'*') AS pad 
FROM dual
CONNECT BY level  user, tabname => 't');

VARIABLE id NUMBER

ALTER SESSION SET events '10507 trace name context forever, level 1';

EXECUTE :id := 42;

SELECT pad FROM t WHERE id 
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In the last days, I am running a number of tests based on the TPC-DS benchmark against Oracle’s Autonomous Data Warehouse Cloud service (ADWC). One of the first thing I did is of course to create the TPC-DS schema and populate it. The aim of this blog post is to share some observations related to the population step.

I started by generating the data with the tool provided by TPC-DS: dsdgen. With the following command, I generated 1TB of data:

$ dsdgen -scale 1000 -dir /data/tpcdsdata

The tool generated, in about 24 hours, the following files:

$ ls -1s --block-size=M /data/tpcdsdata/ *.dat
     1M /data/tpcdsdata/call_center.dat
     5M /data/tpcdsdata/catalog_page.dat
 22375M /data/tpcdsdata/catalog_returns.dat
302796M /data/tpcdsdata/catalog_sales.dat
   642M /data/tpcdsdata/customer_address.dat
  1560M /data/tpcdsdata/customer.dat
    77M /data/tpcdsdata/customer_demographics.dat
    10M /data/tpcdsdata/date_dim.dat
     1M /data/tpcdsdata/dbgen_version.dat
     1M /data/tpcdsdata/household_demographics.dat
     1M /data/tpcdsdata/income_band.dat
 16373M /data/tpcdsdata/inventory.dat
    83M /data/tpcdsdata/item.dat
     1M /data/tpcdsdata/promotion.dat
     1M /data/tpcdsdata/reason.dat
     1M /data/tpcdsdata/ship_mode.dat
     1M /data/tpcdsdata/store.dat
 34016M /data/tpcdsdata/store_returns.dat
399328M /data/tpcdsdata/store_sales.dat
     5M /data/tpcdsdata/time_dim.dat
     1M /data/tpcdsdata/warehouse.dat
     1M /data/tpcdsdata/web_page.dat
 10349M /data/tpcdsdata/web_returns.dat
151044M /data/tpcdsdata/web_sales.dat
     1M /data/tpcdsdata/web_site.dat

There are two main ways to load that data into ADWC: SQL*Loader or the DBMS_CLOUD package. Since I decided to use the latter, I had to load those files into an object cloud service.

I settled to load them into Oracle’s Object Storage with the following command:

$ oci os object bulk-upload --bucket-name tpcdsdata --src-dir /data/tpcdsdata --include "*.dat"

Since I used the defaults, the load was carried out by ten parallel threads. In case you wonder, it took almost 6 hours and during that time, two CPU cores were fully used.

Once all data was available in Oracle’s Object Storage, to decide how many CPU cores to allocate for the full load, I ran a number of loads of a single table of average size (CATALOG_RETURNS, ca. 22GB) and an increasing number of CPU cores allocated in ADWC. The simplified version of the PL/SQL block I used for one run was the following:

DECLARE
  l_field_list CLOB;
  l_table_name VARCHAR2(128) := 'CATALOG_RETURNS';
BEGIN
  -- get rid of old data   
  EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_table_name;

  -- generate field list
  SELECT listagg(column_name || decode(data_type, 'DATE', ' DATE "YYYY-MM-DD"', ''), ',') WITHIN GROUP (ORDER BY column_id)
  INTO l_field_list
  FROM user_tab_cols
  WHERE table_name = l_table_name;

  -- load data
  dbms_cloud.copy_data(
    schema_name => user,
    table_name => l_table_name,
    credential_name => 'CHRIS',
    file_uri_list => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tpcdsdata/catalog_returns.dat',
    field_list => l_field_list
  );
END;

The following chart summarizes what I observed:

As you can see, as the number of CPU cores doubles, the load time decreased of factor 1.7-1.8. Which, in my opinion, is rather good. Note that I did not test with 128 CPU cores (the maximum ADWC supports), because I was not able to allocate so many CPU cores for a single service.

What I found interesting is to have a look at the way the database engine parallelize the loads. By the way, if you are asking yourself how the data is loaded, it is done with a simple SQL statement like “INSERT /*+ append enable_parallel_dml */ INTO “TPCDS1″.”CATALOG_RETURNS” SELECT * FROM COPY$RD0ZOZY5DB25HSH0CB24”. For that purpose, for every load, I generated a real-time SQL monitoring report for the INSERT statement. Here is, for each of them, the information about the parallel execution:

  • 2 CPU cores – DOP 4, one instance involved

  • 4 CPU cores – DOP 8, one instance involved

  • 8 CPU cores – DOP 16, one instance involved

  • 16 CPU cores – DOP 32, one instance involved

  • 32 CPU cores – DOP 64, two instances involved (distribution of parallel execution processes: 32/32)

  • 64 CPU cores – DOP 128, three instances involved (distribution of parallel execution processes: 43/43/42)

As you can see:

  • The database engine selects a DOP which is two times the number of allocated CPU cores.
  • Up to a DOP of 32, all parallel execution processes are started on the same instance (5) as the query coordinator.
  • With a DOP of 64, half of the parallel execution processes are started on instance 5 and the other half is started on instance 8.
  • With a DOP of 128, one third of the parallel execution processes are started on instance 5, another third on instance 7, and the others on instance 8.
  • Since the processing is CPU bound and that the number of involved parallel execution processes is two times the number of allocated CPU cores, the Resource Manager kicks in. As a result, a lot of time was spent on the “resmgr: cpu quantum” wait event.

The last test I did was loading the full 1TB of data. The PL/SQL code I used was similar to the one used for the CATALOG_RETURNS table. The only major difference is an additional loop to process all tables/files. With 32 CPU cores allocated, the load took 67 minutes.

In summary, the loads scale well with an increasing number of CPU cores. But, be careful, because of the high DOP, they can also use all CPU resources of the service. It is therefore essential to start them wisely. E.g. by associating them with a sensible Resource Manager group.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The Autonomous Data Warehouse Cloud (ADWC) service provides a Service Console that can be used to monitor the service activity and to carry out a small number of administration tasks (e.g. changing some resource management rules).

The documentation specifically says to use the ADMIN user to login. But, actually, any unlocked database user having the CREATE SESSION privilege can be used to login. This is not only useful, but, in my opinion, necessary. In fact, developers should be able to see the service activity without knowing the password of the ADMIN user.

The documentation provides no information about what privileges are needed to take advantage of the provided functionalities. If you login with a user having only the CREATE SESSION privilege you get either empty charts or errors when trying to use some of the functionality. The following figure illustrates:

In other words, the UI is static and privileges are only checked when a specific functionality is used. I would expect something dynamic…

By looking at the roles owned by the ADMIN user, I noticed three roles with a name that could match what I was looking for: CONSOLE_MONITOR, CONSOLE_OPERATOR and CONSOLE_ADMIN. Then, with some trial and error, I came up with the following table that summarizes which role provides which functionality:

Functionality CONSOLE_MONITOR CONSOLE_OPERATOR CONSOLE_ADMIN
Overview No data Data visible Data visible
Activity No data Data visible Data visible
Activity – Cancel execution Unauthorized Authorized Authorized
Administration – Download Client Credentials Unauthorized Authorized Authorized
Administration – Set Resource Management Rules Unauthorized Authorized Authorized
Administration – Set Administrator Password Unauthorized Unauthorized Authorized
Administration – Manage Oracle ML Users Unauthorized Unauthorized Authorized
Administration – Download Oracle Instance Client Authorized Authorized Authorized
Administration – Send Feedback to Oracle Authorized Authorized Authorized

Notes:

  • A user with the CONSOLE_MONITOR role has the same privileges than a user without any role. To me this state of affairs do not look correct. I would expect that the CONSOLE_MONITOR role makes visible the Overview and Activity data.
  • With these roles it is not possible to provide only the privileges to see data without being able to modify something or kill sessions.
  • The “Cancel execution” functionality is actually a “kill session”. My guess is that as soon as the service is upgrade to 18c then new functionality to cancel executions will be used instead.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The Appendix A of the Using Oracle Autonomous Data Warehouse Cloud guide describes the DBMS_CLOUD package. Unfortunately, it documents only a subset of the subroutines. And, for some of them, the description could also be enhanced. Therefore, while I was testing all the subroutines the DBMS_CLOUD package provides, I took a number of notes. By the end of my tests, I got what I can call my personal reference guide to the package. Since it might help others, here it is…

Introduction

To interact with other cloud services, the Oracle Autonomous Data Warehouse Cloud service provides the DBMS_CLOUD package. With it, the database engine can store/retrieve objects into/from the following cloud services:

  • Oracle’s Object Storage
  • Amazon Simple Storage Service (S3)
  • Microsoft’s Azure Blob Storage

Those services store data as objects within buckets (containers). Therefore, when in this post I use the terms “object” and “bucket”, I mean the concepts provided by those services.

Objects are identified with an URI having the following format (for more details refer to the documentation):

  • Oracle Cloud: https://swiftobjectstorage.<region>.oraclecloud.com/v1/<tenant>/<bucket>/<objectname>
  • AWS: https://s3-<region>.amazonaws.com/<bucket>/<objectname>
  • Azure: https://<account>.blob.core.windows.net/<container>/<objectname>

The access to the objects stored within buckets is protected. To access an object the Oracle Autonomous Data Warehouse Cloud service has to provide identification information. That information is stored into a credential object.

Managing Credentials

This section describes subroutines to handle credential objects that give access to cloud services.

CREATE_CREDENTIAL

The CREATE_CREDENTIAL procedure creates a credential into the current schema. Its specification is the following:

PROCEDURE create_credential(
  credential_name IN VARCHAR2,
  username        IN VARCHAR2,
  password        IN VARCHAR2 DEFAULT NULL,
  tenancy_ocid    IN VARCHAR2 DEFAULT NULL,
  user_ocid       IN VARCHAR2 DEFAULT NULL,
  private_key     IN VARCHAR2 DEFAULT NULL,
  public_key      IN VARCHAR2 DEFAULT NULL,
  region          IN VARCHAR2 DEFAULT NULL
);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

The meaning of the other parameters depends on the accessed cloud services. Refer to the documentation for information about them.

Notes:

  • A credential is a regular database object belonging to a schema. Its name must comply the usual naming rules used in Oracle Database.

Example:

BEGIN
  dbms_cloud.create_credential(
    credential_name => 'CHRIS',
    username => 'chris',
    password => 'mysecret'
  );
END;
DROP_CREDENTIAL

The DROP_CREDENTIAL procedure drops a credential object from the current schema. Its specification is the following:

PROCEDURE drop_credential(credential_name IN VARCHAR2);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

Notes:

  • The credential is dropped also in case another object relies on it.

Example:

BEGIN
  dbms_cloud.drop_credential(credential_name => 'CHRIS');
END;
ENABLE_CREDENTIAL

The ENABLE_CREDENTIAL procedure enables a credential stored in the current schema. Its specification is the following:

PROCEDURE enable_credential(credential_name IN VARCHAR2);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

Example:

BEGIN
  dbms_cloud.enable_credential(credential_name => 'CHRIS');
END;
DISABLE_CREDENTIAL

The DISABLE_CREDENTIAL procedure disables a credential stored in the current schema. Its specification is the following:

PROCEDURE disable_credential(credential_name IN VARCHAR2);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

Example:

BEGIN
  dbms_cloud.disable_credential(credential_name => 'CHRIS');
END;
UPDATE_CREDENTIAL

The UPDATE_CREDENTIAL procedure updates an attribute associated to a credential stored in the current schema. Its specification is the following:

PROCEDURE update_credential(
  credential_name IN VARCHAR2,
  attribute       IN VARCHAR2,
  value           IN VARCHAR2
);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • ATTRIBUTE: identify the attribute to update (valid values are USERNAME, PASSWORD, TENANCY_OCID, USER_OCID, PRIVATE_KEY, PUBLIC_KEY and REGION)
  • VALUE: the value to associate to the attribute to update

Example:

BEGIN
  dbms_cloud.update_credential(
    credential_name => 'CHRIS',
    attribute => 'PASSWORD',
    value => 'anothersecret'
  );
END;
Managing Tables

This section describes subroutines that interact with database tables.

CREATE_EXTERNAL_TABLE

The CREATE_EXTERNAL_TABLE procedure creates an external table that can be used to query data stored into a cloud service from the database engine. Its specification is the following:

PROCEDURE create_external_table(
  table_name      IN VARCHAR2,
  credential_name IN VARCHAR2,
  file_uri_list   IN CLOB,
  column_list     IN CLOB,
  field_list      IN CLOB DEFAULT NULL,
  format          IN CLOB DEFAULT NULL
);

Parameters:

  • TABLE_NAME: the name of the external table
  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • FILE_URI_LIST: comma-delimited list of one or several objects stored in a cloud service
  • COLUMN_LIST: comma-delimited list of column names and data types
  • FIELD_LIST: describes the fields in the object; it has the same syntax as the FIELD_LIST clause of external table; refer to the documentation for further information
  • FORMAT: data formatting options in JSON format; refer to the documentation for further information.

Notes:

  • The external table is created with the NOLOGFILE and NOBADFILE options. So, in case of troubles, there is no easy way to immediately understand what is wrong. To validate its content and know what is wrong, the VALIDATE_EXTERNAL_TABLE procedure is available (see next section).
  • The FILE_URI_LIST parameter supports the wildcards “*” and “?”.
  • The external table is created in the current schema

Example:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS',
    credential_name => 'CHRIS',
    file_uri_list => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt',
    column_list => 'channel_id CHAR(1), channel_desc VARCHAR2(20), channel_class VARCHAR2(20)',
    format => json_object('type' VALUE 'CSV')
  );
END;
VALIDATE_EXTERNAL_TABLE

The VALIDATE_EXTERNAL_TABLE procedure validates an external table, specifically it checks whether data can be loaded through it. It is overloaded to provide also the ability to return an id associated to the load operation. This id permits to get access to details about the operation itself. Its specification is the following:

PROCEDURE validate_external_table(
  table_name    IN VARCHAR2,
  schema_name   IN VARCHAR2 DEFAULT  NULL,
  rowcount      IN NUMBER   DEFAULT  0,
  stop_on_error IN BOOLEAN  DEFAULT  TRUE
);
PROCEDURE validate_external_table(
  table_name    IN         VARCHAR2,
  operation_id  OUT NOCOPY NUMBER,
  schema_name   IN         VARCHAR2 DEFAULT  NULL,
  rowcount      IN         NUMBER   DEFAULT  0,
  stop_on_error IN         BOOLEAN  DEFAULT  TRUE
);

Parameters:

  • TABLE_NAME: the name of the external table
  • SCHEMA_NAME: the owner of the external table (NULL = current schema)
  • ROWCOUNT: how many rows to read during the validation (0 = all rows)
  • STOP_ON_ERROR: whether to stop immediately in case bad records are found
  • OPERATION_ID: output parameter that provides the id of the load operation; it can for example be used to identify the corresponding information in USER_LOAD_OPERATIONS

Notes:

  • For two days, the log and bad files associated to the operation can be accessed through external tables. Their name is visible in USER_LOAD_OPERATIONS.LOGFILE_TABLE and USER_LOAD_OPERATIONS.BADFILE_TABLE.

Example:

BEGIN
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS'
  );
END;
COPY_DATA

The COPY_DATA procedure reads, through an external table it creates, an object stored in a cloud service and loads its contents into a heap table. It is overloaded to provide also the ability to return an id associated to the load operation. This id permits to get access to details about the operation itself. In other words, the procedure can be used to move data from a cloud service to a heap table. Its specification is the following:

PROCEDURE copy_data(
  table_name      IN VARCHAR2,
  credential_name IN VARCHAR2,
  file_uri_list   IN CLOB,
  schema_name     IN VARCHAR2 DEFAULT NULL,
  field_list      IN CLOB     DEFAULT NULL,
  format          IN CLOB     DEFAULT NULL
);
PROCEDURE copy_data(
  table_name      IN         VARCHAR2,
  credential_name IN         VARCHAR2,
  file_uri_list   IN         CLOB,
  operation_id    OUT NOCOPY NUMBER,
  schema_name     IN         VARCHAR2 DEFAULT NULL,
  field_list      IN         CLOB     DEFAULT NULL,
  format          IN         CLOB     DEFAULT NULL
);

Parameters:

  • TABLE_NAME: the name of the heap table
  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • FILE_URI_LIST: comma-delimited list of one or several objects stored in a cloud service; refer to the documentation for further information
  • SCHEMA_NAME: the owner of the heap table (NULL = current schema)
  • FIELD_LIST: describes the fields in the object; it has the same syntax as the FIELD_LIST clause of external table; refer to the documentation for further information
  • FORMAT: data formatting options in JSON format; refer to the documentation for further information
  • OPERATION_ID: output parameter that provides the id of the load operation; it can for example be used to identify the corresponding information in USER_LOAD_OPERATIONS

Notes:

  • The FILE_URI_LIST parameter supports the wildcards “*” and “?”.
  • To access the object stored in the cloud service, an external table is created and, when the load operation is over, it is immediately dropped.
  • For two days, the log and bad files associated to the operation can be accessed through external tables. Their name is visible in USER_LOAD_OPERATIONS.LOGFILE_TABLE and USER_LOAD_OPERATIONS.BADFILE_TABLE.

Example:

BEGIN
  dbms_cloud.copy_data(
    table_name => 'CHANNELS',
    credential_name => 'CHRIS',
    file_uri_list => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt',
    format => json_object('type' VALUE 'CSV')
  );
END;
DELETE_OPERATION

The DELETE_OPERATION procedure cleans up the objects and information created during the execution of an operation carried out by the VALIDATE_EXTERNAL_TABLE and COPY_DATA procedures. Its specification is the following:

PROCEDURE delete_operation(id IN NUMBER);

Parameters:

  • ID: the id of the operation to be cleaned up (USER_LOAD_OPERATIONS.ID)

Notes:

  • Only operations carried out by the current user are considered.

Example:

BEGIN
  dbms_cloud.delete_operation(id => 42);
END;
DELETE_ALL_OPERATIONS

The DELETE_ALL_OPERATIONS procedure cleans up the objects and information related to all operations carried out by the VALIDATE_EXTERNAL_TABLE and COPY_DATA procedures. Its specification is the following:

PROCEDURE delete_all_operations(type IN VARCHAR DEFAULT NULL);

Parameters:

  • TYPE: the type of the operations to delete (USER_LOAD_OPERATIONS.TYPE; NULL = all types)

Notes:

  • Only operations carried out by the current user are considered.

Example:

BEGIN
  dbms_cloud.delete_all_operations(type => 'VALIDATE');
END;
Managing Objects

This section describes subroutines to handle objects stored in a cloud service.

PUT_OBJECT

The PUT_OBJECT procedure stores an object into a bucket. It is overloaded to provide the ability to read data from a BLOB or from a directory object. In other words, it can be used to move data from the database server to a cloud service. Its specification is the following:

PROCEDURE put_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  contents        IN BLOB,
  compression     IN VARCHAR2 DEFAULT NULL
);
PROCEDURE put_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  directory_name  IN VARCHAR2,
  file_name       IN VARCHAR2,
  compression     IN VARCHAR2 DEFAULT NULL
);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • OBJECT_URI: the URI of the object or, when the data is read from a directory object, the URI of the bucket
  • DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)
  • FILE_NAME: the name of the file
  • COMPRESSION: the compression algorithm used to store the object; valid values are DBMS_CLOUD.COMPRESS_NONE (NULL), DBMS_CLOUD.COMPRESS_AUTO (‘AUTO’) and DBMS_CLOUD.COMPRESS_GZIP (‘GZIP’).

Notes:

  • If the object already exists, it is overwritten.
  • In case a directory object is used and only the destination bucket is specified, the file name is used as object name.
  • Even though the compression algorithms ZLIB and BZIP2 are defined by the package, they are not supported by the PUT_OBJECT procedures.

Example:

  • Get data from a BLOB stored in the database
DECLARE
  l_contents BLOB;
BEGIN
  SELECT report_compressed INTO l_contents FROM dba_hist_reports_details WHERE rownum = 1;
  dbms_cloud.put_object(
    credential_name => 'CHRIS',
    object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/report.dat',
    contents => l_contents
  );
END;
  • Get data from the DATA_PUMP_DIR directory object
BEGIN
  dbms_cloud.put_object(
    credential_name => 'CHRIS',
    object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/',
    directory_name => 'DATA_PUMP_DIR',
    file_name => 'channels.txt',
    compression => dbms_cloud.compress_auto
  );
END;
LIST_OBJECTS

The LIST_OBJECTS pipelined function returns the list of objects stored in a bucket. Its specification as well as the specification of the type of the return value are the following:

FUNCTION list_objects(
  credential_name IN VARCHAR2,
  location_uri    IN VARCHAR2
) RETURN dbms_cloud_types.list_object_ret_tab PIPELINED;
TYPE list_object_ret_t IS RECORD (object_name VARCHAR2(4000), bytes NUMBER);
TYPE list_object_ret_tab IS TABLE OF list_object_ret_t;

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • LOCATION_URI: the address of the bucket

Return value:

  • The table contains one record for each object. Every record provides the name of the object as well as its size in bytes. If the object is compressed, it is the size of the compressed file, not the original one.

Example:

SELECT * 
FROM table(dbms_cloud.list_objects(
             credential_name => 'CHRIS', 
             location_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/'
           ))
WHERE object_name LIKE '%.txt'

OBJECT_NAME    BYTES
------------- ------
channels.txt     104
GET_METADATA

The GET_METADATA function returns the metadata associated to an object. Its specification is the following:

FUNCTION get_metadata(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2
) RETURN CLOB;

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • OBJECT_URI: the URI of the object

Return value:

  • The metadata associated to the object in JSON format.

Example:

SELECT dbms_cloud.get_metadata(
         credential_name => 'CHRIS', 
         object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt'
       ) AS metadata
FROM dual

METADATA 
-----------------------
{"Content-Length":104}
GET_OBJECT

The GET_OBJECT function reads an object stored into a bucket and returns it as a BLOB. It is overloaded to provide also the ability to write the data into a file stored in a directory object. In other words, it can be used to move data from a cloud service to the database server. Its specification is the following:

FUNCTION get_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  startOffset     IN NUMBER   DEFAULT 0,
  endOffset       IN NUMBER   DEFAULT 0,
  compression     IN VARCHAR2 DEFAULT NULL
) RETURN BLOB;
FUNCTION get_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  directory_name  IN VARCHAR2,
  file_name       IN VARCHAR2 DEFAULT NULL,
  startOffset     IN NUMBER   DEFAULT 0,
  endOffset       IN NUMBER   DEFAULT 0,
  compression     IN VARCHAR2 DEFAULT NULL
) RETURN BLOB;

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • OBJECT_URI: the URI of the object
  • STARTOFFSET: the offset (in bytes) from where data is read
  • ENDOFFSET: the offset (in bytes) until where data is read
  • DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)
  • FILE_NAME: the name of the file
  • COMPRESSION: the compression algorithm used to store the object; valid values are DBMS_CLOUD.COMPRESS_NONE (NULL), DBMS_CLOUD.COMPRESS_AUTO (‘AUTO’) and DBMS_CLOUD.COMPRESS_GZIP (‘GZIP’).

Return value:

  • A LOB locator giving access to the data read from the object.

Notes:

  • In case a directory object is used and the file name is not specified, the object name is used as file name.
  • The offsets start from 0. For example, to read the first 1000 bytes, STARTOFFSET has to be set to 0 and ENDOFFSET has to be set to 999.
  • If the compression algorithm matches the one used to store the object, data is uncompressed.

Example:

SELECT to_clob(dbms_cloud.get_object(
         credential_name => 'CHRIS',		
	     object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt',
         startOffset => 0,
         endOffset => 20
       )) AS data
FROM dual

DATA 
----------------------
S,Direct Sales,Direct
DELETE_OBJECT

The DELETE_OBJECT procedure removes an object from a bucket. Its specification is the following:

PROCEDURE delete_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2
);

Parameters:

  • CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
  • OBJECT_URI: the URI of the object

Example:

BEGIN
  dbms_cloud.delete_object(
    credential_name => 'CHRIS',
    object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt'
  );
END;
Managing Files

This section describes subroutines to handle files stored in the file system of the database server. Since no directory object can be created, only the files stored in the directory referenced by the DATA_PUMP_DIR directory object are visible.

LIST_FILES

The LIST_FILES pipelined function lists the files stored in a directory. Its specification as well as the specification of the type of the return value are the following:

FUNCTION list_files(
  directory_name IN VARCHAR2
) RETURN dbms_cloud_types.list_object_ret_tab PIPELINED;
TYPE list_object_ret_t IS RECORD (object_name VARCHAR2(4000), bytes NUMBER);
TYPE list_object_ret_tab IS TABLE OF list_object_ret_t;

Parameters:

  • DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)

Return value:

  • The table contains one record for each file. Every record provides the name of the file as well as its size in bytes.

Notes:

  • To use the procedure successfully, the caller requires the READ privilege on the directory.

Example:

SELECT * 
FROM table(dbms_cloud.list_files('DATA_PUMP_DIR'))
WHERE object_name LIKE '%.txt'

OBJECT_NAME    BYTES
------------- ------
channels.txt      99
DELETE_FILE

The DELETE_FILE procedure deletes a single file from a directory. Its specification is the following:

PROCEDURE delete_file(
  directory_name IN VARCHAR2,
  file_name      IN VARCHAR2
);

Parameters:

  • DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)
  • FILE_NAME: the name of the file

Notes:

  • To use the procedure successfully, the caller requires the WRITE privilege on the modified directory.
  • If the file does not exist, no exception is raised.
  • Wildcards are not supported.

Example:

BEGIN
  dbms_cloud.delete_file(
    directory_name => 'DATA_PUMP_DIR',
    file_name      => 'channels.txt'
  );
END;
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c.

The new features can be grouped in three categories:

  • Partition-wise GROUP BY enhancements available as of version 12.2
  • Partition-wise DISTINCT enhancements available as of version 12.2
  • Partition-wise windowing functions enhancements available as of version 18.1

Before looking at the new features, here are the SQL statements I executed to create a partitioned table that I use through the examples. You can download the script here.

CREATE TABLE t (
  id NUMBER,
  d1 DATE,
  n1 NUMBER,
  n2 NUMBER,
  n3 NUMBER,
  pad VARCHAR2(4000),
  CONSTRAINT t_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (d1)
SUBPARTITION BY LIST (n1)
SUBPARTITION TEMPLATE (
  SUBPARTITION sp_1 VALUES (1),
  SUBPARTITION sp_2 VALUES (2),
  SUBPARTITION sp_3 VALUES (3),
  SUBPARTITION sp_4 VALUES (4)
)(
  PARTITION t_q1_2018 VALUES LESS THAN (to_date('2018-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q2_2018 VALUES LESS THAN (to_date('2018-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q3_2018 VALUES LESS THAN (to_date('2018-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q4_2018 VALUES LESS THAN (to_date('2019-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
);

INSERT INTO t
SELECT rownum AS id,
       trunc(to_date('2018-01-01','YYYY-MM-DD')+rownum/27.4) AS d1,
       1+mod(rownum,4) AS n1,
       rownum AS n2,
       rownum AS n3,
       rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level  user,
    tabname          => 'T'
  );
END;
/

Partition-wise GROUP BY (12.2)

The enhancements in this section are minimal. In fact, there are only two new hints: USE_PARTITION_WISE_GBY and NO_USE_PARTITION_WISE_GBY. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.

The following examples show, for the query SELECT n1, d1, sum(n2) FROM t GROUP BY n1, d1, serial/parallel execution plans with/without the partition-wise optimization. In the serial execution plans notice the placement of the HASH GROUP BY operation. In the parallel execution plans, notice the reduction of involved processes and data distribution.

serial
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH GROUP BY       |      |
|   2 |   PARTITION RANGE ALL|      |
|   3 |    PARTITION LIST ALL|      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
serial+pwise
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  PARTITION RANGE ALL |      |
|   2 |   PARTITION LIST ALL |      |
|   3 |    HASH GROUP BY     |      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
parallel
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
parallel+pwise
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |  Q1,00 | PCWC |            |
|   4 |     HASH GROUP BY       |          |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL  | T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

Partition-wise DISTINCT (12.2)

The key enhancement of this section is the ability to execute a DISTINCT as a parallel partition-wise operation. In addition, there are two new hints: USE_PARTITION_WISE_DISTINCT and NO_USE_PARTITION_WISE_DISTINCT. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.

The following examples show, for the query SELECT DISTINCT n1, d1 FROM t, serial/parallel execution plans with/without the partition-wise optimization. In the serial execution plans notice the placement of the HASH UNIQUE operation. In the parallel execution plans, notice the reduction of involved processes and data distribution.

serial
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH UNIQUE         |      |
|   2 |   PARTITION RANGE ALL|      |
|   3 |    PARTITION LIST ALL|      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
serial+pwise
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  PARTITION RANGE ALL |      |
|   2 |   PARTITION LIST ALL |      |
|   3 |    HASH UNIQUE       |      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
parallel
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH UNIQUE           |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       HASH UNIQUE        |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
parallel+pwise
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |  Q1,00 | PCWC |            |
|   4 |     HASH UNIQUE         |          |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL  | T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

Partition-wise Windowing Functions (18.1)

The key enhancement of this section is the ability to execute a windowing function as a parallel partition-wise operation. In addition, there are two new hints: USE_PARTITION_WISE_WIF and NO_USE_PARTITION_WISE_WIF. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.

The following examples show, for the query SELECT n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t, serial/parallel execution plans with/without the partition-wise optimization. Notice that the serial execution plan without the optimization is missing because either I did not correctly understand how to use the NO_USE_PARTITION_WISE_WIF hint (as it too often happens, no documentation about it is provided) or it does not work (bug?) for the serial execution plan. In the parallel execution plans, notice the reduction of involved processes and data distribution.

serial+pwise
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  PARTITION RANGE ALL |      |
|   2 |   PARTITION LIST ALL |      |
|   3 |    WINDOW SORT       |      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
parallel
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    WINDOW SORT          |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------
parallel+pwise
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |  Q1,00 | PCWC |            |
|   4 |     WINDOW SORT         |          |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL  | T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

All in all, those are good features that can not only make some operation faster, but also reduce the number of involved processes in case the database engine uses parallel execution.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview