Oracle Cloud Infrastructure Documentation

Troubleshooting Errors with the Data Share Tool

This chapter provides instructions on what issues cause slow performance and how to troubleshoot the issues.

With an increase in ECPUs, does the performance of the Live share feature in the Data Share tool improve while running complex queries?

No, it does not. In such cases, you need to configure the Degree Of Parallelism (DOP) using Data Studio to improve the performance of complex running queries.

Configure the Degree Of Parallelism (DOP) using Data Studio

When the Live Share feature of the Database Actions instance runs on an increased value of ECPU, by default the performance of the complex running queries does not improve.

You can use the DOP option to improve the performance of the complex running queries. The DOP sets the number of processors employed to run a single statement, for each parallel plan execution. This section describes how to configure the degree of parallelism (DOP) configuration option in the Autonomous Database by using the Live Share feature of Data Studio.

In this example, you will use the Live share feature and share tables of an uncompressed size of 1TB.

Before you begin, here are a few things you need to consider:
  • The option to configure the value of DOP can only be done by an admin.
Prerequisite:
  • Log in to your Database Actions instance as an admin and under Administration, select Set Resource Management Rules.
  • Note the default concurrency value and DOP.
To configure the degree of parallelism using Data Studio to improve the performance of complex running queries, you will:
  • Create Share as a provider.
  • Consume Share as a consumer and create external queries on top of your shares to record the query execution time.
  • Increase the ECPUs to a value greater than the current value as provider and consumer and record the query execution time.
  • Configure the DOP value for the MEDIUM consumer group.
  • Consume the same share and record the query execution time of the same table.
Note

You must configure the ECPU count and DOP value as a provider with ADMIN privileges.
On the Database Actions launchpad, click Selector and select the Data Share tool from the Data Studio suite of tools.
  1. Create Share as a provider.
    1. Click Provide Shares and select +Create Share.
    2. On the Create Share wizard, in the Name field of the General tab, enter a name for the Share. For example: TEST.


      Description of createshare.png follows

      In the Description field, enter a description for the data you share. This field is optional.

      Select Next to progress to the Publish Details tab.

    3. In the Publish Details Tables tab of the wizard, select SHARE LIVE DATA USING DIRECT CONNECTION.


      Description of publishdetails.png follows

      Click Next.

    4. On the Select Tables tab of the wizard, select the schema from the drop-down menu, drag and drop the CUSTOMER table from the Available Tables.


      Description of selecttables.png follows

      Note

      In this example, we are sharing a single table named Customer of large size.

      Click Next to proceed to the Recipients tab of the Create Share Wizard.

    5. On the Recipients tab of the Create Share wizard, select MY_COMPARTMENT from the drop-down.
      Description of mycompartment.png follows

    6. Select Create to create the share.


      Description of provideshare.png follows

  2. Consume Share as a consumer and create external queries on top of your shares to record the query execution time.
    1. After you have created the share as a provider, you will consume the data shared by the provider.

      See Consume Share for more details.

    2. Log in as a consumer to subscribe to the data share provider and access the data in your share.
    3. On the Provider and Consumer page of the Data Share tool, click the CONSUME SHARE tile to display the Consume Share page.
    4. Click the Subscribe to Share Provider drop-down list, and then click the Subscribe to Live Share Provider option.


      Description of subscribe-share.png follows

    5. Under Share Provider Details, select the Provider Name. In this example, TPUSER is the name of the provider.


      Description of tpuser.png follows

    6. On the Add Shares panel of the Subscribe to Share Provider Settings wizard page, click the TEST in the Available Shares section, and then click the (>) icon.


      Description of add-share.png follows

    7. The tool adds the share to the Selected Shares section. Click Subscribe.
    8. A TPUSER share provider was created successfully informational message is displayed. You are directed to the Load Data page with the Share tab selected.


      Description of drag-drop-share.png follows

    9. Expand the TESTSHARE data share node to display the objects in it. Drag and drop the TPUSER.CUSTOMER shared table to add it to the data load job.
    10. The shared table is added to the data link job. When you run this job, a new CUSTOMER view will be created on top of the CUSTOMER table that was shared with you.
    11. Click Start. A Start Link from Cloud Store dialog box is displayed. Click Run. When the job completes successfully, a link icon is displayed. The CUSTOMER view is created.
    12. Click the Database Actions | Data Share banner. On the Database Actions | Launchpad, click the Development tab, and then click the SQL tab to display the SQL Worksheet.


      Description of consumer.png follows

    13. Drag and drop the CUSTOMER table to the SQL worksheet area.
    14. A dialog displays offering the types of available insertions.
    15. Click Select, then Apply.


      Description of select.png follows


      Description of consumer-drag.png follows

    16. Click Run. You can view the automatically generated query displayed in the Worksheet.


      Description of query-execution.png follows

    17. Note the query execution time in the Execution time field displayed under Query Result tab.
  3. Increase the ECPUs as provider and consumer and record the query execution time.
    1. Increase the ECPUs of your Autonomous Database instance as a provider and a consumer from your OCI console. See Add CPU or Storage Resources or Enable Auto Scaling.
      Note

      Make sure you have not enabled Auto Scaling.

      In this example we have increased the ECPU count to 32. You can upscale the ECPU count to any value of your choice. For more details on the maximum ECPU count you set and licensing see, View and Update Your License and Oracle Database Edition on Autonomous Database (ECPU Compute Model)

    2. Repeat consuming the same CUSTOMER share and calculating the query execution time. There will be no change in the time to query the table.
  4. Configure the DOP value for the MEDIUM consumer group.
    1. Log in to your database actions instance as a consumer with ADMIN privilege.

    2. Click Selector and under Administration, select Set Resource Management Rules.

    3. Decrease the Concurrency limit of the MEDIUM consumer group to 8 and click Save Changes. Decreasing the concurrency limit increases the DOP.


      Description of resource.png follows

    Note

    The concurrency limit you set in this step should be lesser than the default value you noted in the prerequisite.
  5. Follow up step- After you configure the degree of parallelism option, repeat the process of consuming the share, creating external queries on top of your shares to record the query execution time.

    You will see the value of query execution time has decreased.

The improvement in the performance of the same complex SQL queries happens immediately without restarting the server.

Parent topic: The Data Share Tool