How we can run the parallel hints SQL script in delphix via Postscript ( hook postscript file which have parallel hints SQL)

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
How we can run the parallel hints SQL script in delphix via Postscript ( hook postscript file which have parallel hints SQL)

UPDATE /* +PARALLEL(TABLE_NAME,64) */ TABLE_NAME SET UPDATED_COL =UPDATE_COL where CONDITION ;
COMMIT;
If we run this with plsql then it will run by 64 thread but if we run the same via delphix then it will run in 1 thread , please let me know how we can run via 64 thread ?
Photo of Rajesh

Rajesh

  • 592 Points 500 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Tim Gorman

Tim Gorman, Field Services

  • 3,154 Points 3k badge 2x thumb
Rajesh,

I assume that you mean post-scripts in the Delphix masking engine, and not hooks in the Delphix virtualization engine?

Either way, perhaps it is possible that you need to first prepare the JDBC session in which the UPDATE using an ALTER SESSION ENABLE PARALLEL DML?

Also, I couldn't help noticing a typo in the hint section of the SQL statement you displayed.  There, you had the "+" sign immediately next to the PARALLEL hint instead of being immediately next to the asterisk in the opening comment.  That is, instead of "/* +PARALLEL...", it should read "/*+ PARALLEL...", with a space between the "+" and the hint and no space before the "+".

I would try fixing the typo first (if it is indeed in your code and not just in the post) and then try the ALTER SESSION?

Hope this helps?

-Tim
Photo of fMarcin Przepiorowski

fMarcin Przepiorowski, Employee

  • 880 Points 500 badge 2x thumb
Official Response
Hello,

Tim advice is very important. You need to make sure there alter session statement is included in your pre/post script.

Unfortunately Masking Engine is removing a comments from SQL so as Oracle hints are enclosed by begin/end comment - there are removed as well.

You need to set parallel level on object and then Oracle CBO will execute this using a parallel mode, at the end you should reverse a change the value which was set on table before. As it executed in non parallel mode I assume it was set to non parallel.

You script should looks like this:

ALTER SESSION ENABLE PARALLEL DML;
ALTER TABLE TABLE_NAME  PARALLEL 64;
UPDATE  TABLE_NAME SET UPDATED_COL =UPDATE_COL where CONDITION ;
COMMIT;
ALTER TABLE TABLE_NAME  NOPARALLEL;


regards,
Marcin