How to prevent parallel execution on query with no hint

Question

I'm trying to improve performance to specific query in my DB. One of the tables in the query, has parallel degree which is greater than 1 in dba_tables.

I founded that if I add a hint of

NO_PARALLEL

my query back to run very fast.

The problem is that I don't want to touch and change the source of query.

So I found this article:

http://intermediatesql.com/oracle/how-to-add-a-hint-to-oracle-query-without-touching-its-text/

which explain how to use alias with block names to create a profile and actually execute the hint with no touching the query.

I used it before for use an index even the optimizer decide to execute FTS.

But in case of parallel - I just didn't understand how to do that.

I just want to add the action that will simulate the no_parallel hint on the whole query.

Additionally, I don't want to set the parallel parameter or alter the session.. just change for this query.

Someone?

Thanks.

Jon Heller Jon Heller · Accepted Answer · 2018-01-23T04:29:43

Forcing a single query to use /*+ NO_PARALLEL */ is easy because it's a statement-level hint. There's no need to add query block names or figure out Oracle's internal names.

Add the NO_PARALLEL hint to the query:

begin
    dbms_sqltune.import_sql_profile(
        sql_text => 'select /*+ parallel */ * from dba_objects',
        profile => sqlprof_attr('no_parallel'),
        name => '0ftu9j02g81b0_noparallel'
    );
end;
/

Verify that the statement has a profile and does not run in parallel:

explain plan for select /*+ parallel */ * from dba_objects;
select * from table(dbms_xplan.display);

Results:

...

Note
-----
   - Degree of Parallelism is 1 because of hint
   - SQL profile "0ftu9j02g81b0_noparallel" used for this statement
   - this is an adaptive plan

How to prevent parallel execution on query with no hint

2 Answers