Upgrading to 12.2.2? Do you want extra speed? Use a logon trigger for parallelism
By JeffG-Oracle on Dec 03, 2013
When upgrading your Demantra application from an older version directly to 12.2.2, you can minimize the upgrade time. You can force parallelism using a Logon trigger on the server.
Creation of new Indexes on sales data can take long time.
As part of the upgrade there is a script building a new set of indexes, one per engine profile, on each profile quantity_form expression.
The creation of the index can take a long time on big data tables.
- Remember to disable or drop that trigger when the upgrade has completed.
The following trigger was created as SYS database user and tested successfully by creating DEMANTRA database user and logging in with it:
CREATE OR REPLACE TRIGGER force_parallel_ddl_trg
AFTER LOGON ON database
IF (USER='DEMANTRA') THEN
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DDL';
Make sure to:
- Modify DEMANTRA to be the database schema name.
- drop it after it is not needed anymore using the foillowing:
DROP TRIGGER force_parallel_ddl_trg;