You Asked

Hi,

I have this statement,

create table TWO nologging parallel 15 as
select a, b, c, pkg_xyz(l,m,n) d
from ONE;

As you can see, there is a package call in the select query.
There are 7 million rows in table ONE.

It is taking approx 3.5 hours to execute. It is being called from a shell script via sqlplus.
I need to optimise the same.

I can make batches using NTILE and then try to induce some parallelism. I can calculate the value d by invoking the pkg_xyz and update the value.
Say I made 10 batches via NTILE, now how can I initiate 10 separate update statements to act on the same table, based on the batch_num?

and Connor said...

Not sure what you're asking here. A PL/SQL function does not prohibit the use of a parallel operation, eg

SQL> create or replace
  2  function my_func(p_in varchar2) return varchar2 is
  3  begin
  4    return lower(p_in);
  5  end;
  6  /

Function created.

SQL>
SQL> create table t1 parallel 8 as
  2  select t.owner, my_func(t.owner) f
  3  from t;

Table created.

SQL>
SQL>
SQL> explain plan for
  2  create table t1 parallel 8 as
  3  select t.owner, my_func(t.owner) f
  4  from t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 550883001

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT             |          |  7840K|    44M|  6081   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |  7840K|    44M|  5753   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T1       |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |  7840K|    44M|  5753   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |  7840K|    44M|  5753   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | T        |  7840K|    44M|  5753   (1)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property

17 rows selected.

SQL>

That *very much* depends on what the *content* of your PL/SQL function contains and whether it is a suitable candidate for parallelising, but this sounds more like tuning what is going on inside the package.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This is a response, not a review

Sankalp, October 23, 2017 - 9:12 am UTC

Sorry, I could not find any option to respond, only got a review option.

You understood it correctly. There is already parallelism given in my original code. My question is how can I do it faster.

With a parallelism of 15, it is taking 3.5 hours to work on 7mn data. I have more resources (hardware wise) to utilise.

Yes, package optimisation is a valid remark, I am checking on that end also. But is there any way to introduce more parallelism? Could there be some reason that because a package is being invoked (not a function), the parallelism isn't working?

October 25, 2017 - 7:54 am UTC

Could there be some reason that because a package is being invoked (not a function), the parallelism isn't working?

As I said before:

"That *very much* depends on what the *content* of your PL/SQL function contains and whether it is a suitable candidate for parallelising"

You can simply "tell" the database a plsql routine is fine for parallelism (via parallel_enable) but you need to make sure this is indeed valid (ie, check the docs about session state etc).

But I would trace the code - see *where* the time is being lost. And then act on that.

Need to use parallel_enable option

Sankalp, October 24, 2017 - 3:27 pm UTC

To enable parallel processing for functions, need to use parallel_enable clause in the function. This link helped. The script which was taking 3 hours got completed in 16 minutes now.

https://blogs.oracle.com/datawarehousing/parallel-plsql-functions-and-global-temporary-tables-and-wrong-results

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

This is a response, not a review

Need to use parallel_enable option

More to Explore

PL/SQL demos

PL/SQL docs

Update same table in parallel using batch number

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

This is a response, not a review

Need to use parallel_enable option

More to Explore

PL/SQL demos

PL/SQL docs