“alter session force parallel query”, and indexes

This greatly simplifies hinting, but of course you must still edit the statement if you need to change the parallel degree.

Page 3 of 3 | Previous page

3 comments on this post.
  1. Bidu:

    Thanks for the clear and concise write up…

  2. Peter:

    When you try to enable and validate a foreign key constraint in parallel,
    behind the scenes, Oracle is executing a recursive SELECT statement to validate the constraint, but you cannot make that recursive query go parallel simply with “alter session force parallel query”, you have to alter the table parallel attribute, like so;

    alter table xyz parallel;
    alter table xyz modify constraint xyz_fk1 enable novalidate;
    alter table xyz modify constraint xyz_fk1 validate;
    alter table xyz noparallel;

    I couldn’t get it to go parallel otherwise unless I’ve missed something from reading your post.

  3. Alberto Dell'Era:

    Hi Peter,

    you are correct; this post focuses on parallel query, not DDL.

    For parallel DDL, I have never bothered forcing the session because I prefer to be specific and cautious when modifying/creating my objects, and avoid impacting the whole session.

    But Christian Antognini did check it, and in his book “Troubleshooting Oracle Performance”, 2nd edition, page 635, confirms that the technique you are using (and that I use myself) for FKs is the only one possible: the session parallel DDL settings are ignored.

    In passing, the post was mainly motivated for DWH/BI reporting scenarios, where you want parallelism for your analysis but cannot/do not want to specify hints in queries or to change the table/indexes degree. In 12c, a much better alternative is to use autoDOP, that will tune the DOP to the complexity of the query (mainly influenced by the size of scanned tables/indexes).

    ciao :)

Leave a comment