Limiting the Degree of Parallelism via Resource Manager and a gotcha

This might be something very obvious for the reader but I had an interesting revelation recently when implementing parallel_degree_limit_p1 in a resource consumer group. My aim was to prevent users mapped to a resource consumer group from executing any query in parallel. The environment is fictional, but let’s assume that it is possible that maintenance operations for example leave indexes and tables decorated with a parallel x attribute. Another common case is the restriction of PQ resource to users to prevent them from using all the machine’s resources.

This can happen when you perform an index rebuild for example in parallel to speed the operation up. However the DOP will stay the same with the index after the maintenance operation, and you have to explicitly set it back:

SQL> alter index I_T1$ID1 rebuild parallel 4;

Index altered.

SQL> select index_name,degree from ind where index_name = 'I_T1$ID1';

INDEX_NAME		       DEGREE
------------------------------ ----------------------------------------
I_T1$ID1		       4

SQL>

Advertisement

Explain plan doesn’t peek at variables

I was trying to experiment with the 11g SQL Plan Management feature when stumbing across this. You will most likely go “ohh-of course it doesn’t” but I didn’t know it for sure until now. It’s another reason the expected explain plan differs from the actual explain plan, as discussed on the oracle-l mailing list recently.

UPDATE 230306 as you can imagine Oracle 11.2 and RHEL 5 have been out of support for quite some time. The information in this article is outdated and should not be referred to anymore

My test setup is 11.2.0.1 on RHEL 5.2 32bit, running para-virtualised on my laptop; your system might be different but I assume the general principle is still the same. So here we go:

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> var i number;
SQL> exec :i := 100

PL/SQL procedure successfully completed.

SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

SQL> explain plan for select id from test2 where id = :i;

Explained.

In the trace file (which again changed quite a lot from 10.2 I use in production), you find the bind variable section:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
 oacflg=00 fl2=1010000 frm=01 csi=46 siz=32 off=0
 kxsbbbfp=00e62024  bln=32  avl=00  flg=05

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=TO_NUMBER(:B1)
kkoqbc: optimizing query block SEL$1 (#0)

You will notice the absence of the value of the bind variable. When I did my testing I randomly executed quieries with the explan plan option and without so this hasn’t become apparent at first. Re-executing the same statement without “explan plan” prefix yields the following result:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
 kxsbbbfp=007cca70  bln=22  avl=02  flg=05
 value=10

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST2"."ID" "ID" FROM "MARTIN"."TEST2" "TEST2" WHERE "TEST2"."ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)

Here we have the value in the relevant column. By the way, finding your trace file has become trivial in 11.1 and later:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3291.trc

Right, I agree this isn’t shaking the Oracle world but it cost me half an hour when developing my test case so I thought I’d share this with you. Bear in mind that your actual execution plan most likely will be different from the explain plan (with bind variables) so testing ACS and other new features will need to make use of dbms_xplan.display_cursor to fetch the plan from the v$ views.