Can we indicate the degree of parallelism dynamically?

Question

I have a choice of SQL query that uses parallelism, something like this

INSERT/*+ APPEND PARALLEL (tst, 6) */ INTO test_table tst
                (
                    **************
                    **************
                    **************
                )
    SELECT /*+  PARALLEL (a, 6) */ DISTINCT 
                    **************
                    **************
                    **************
         FROM src_table a;

As you can see here, I have a hard-coded degree, but I do not want to do this, because the number of processors is not the same in the entire database where this code is executed.

My requirement:I need to query V$PARAMETERfor the available amount of CPU and use the value as result-2in my query. Something like that...

DECLARE
   degree        varchar2(1);
BEGIN
select value-2 INTO degree from v$parameter where name='cpu_count';

            INSERT/*+ APPEND PARALLEL (tst, degree) */ INTO test_table tst
            (
                **************
                **************
                **************
            )
SELECT /*+  PARALLEL (a, degree) */ DISTINCT 
                **************
                **************
                **************
     FROM src_table a;
END;

But it does not work, as I expected, and I see 32 parallel threads, regardless of the available processors. Is it correct? If not, is there another solution for my requirement?

Answer 1

,

SQL INSERT PL/SQL- . , "", .

- :

DECLARE
   degree varchar2(1); 
BEGIN 
   select value-2 
     INTO degree 
     from v$parameter
    where name='cpu_count';              

   EXECUTE IMMEDIATE('INSERT /*+ APPEND PARALLEL (tst, '||degree||') */ '||
                     '  INTO test_table tst ( '||
                     '       **************  '||
                     '       **************  '||
                     '       **************  '||
                     '  ) '||
                     'SELECT /*+  PARALLEL (a, '||degree||') */ '||
                     '       DISTINCT '||
                     '       ************** '||
                     '       ************** '||
                     '       ************** '||
                     '  FROM src_table a');
END; 

Answer 2

parallelism?

Answer 3

Why not force the degree for the session with:

alter session force parallel dml parallel <dop>;
alter session force parallel query parallel <dop>;

Without a hint, you can pinpoint the size you like.