RE: function all disables parallel execution

From: Herring Dave - dherri <>
Date: Thu, 27 Aug 2009 17:01:10 -0500
Message-ID: <>

I found the problem. In the package declaration for this function, there's the following rule:

pragma restrict_references (dch_domain_group_fnc, WNPS);

That's incorrect, as a function does modify a variable. Since the coders wanted these rules in place, I changed the rule to WNDS and then added PARALLEL_ENABLE to the function declaration. This allowed the query to work as expected WITH the call to this function.

Got to learn something today! Maybe I'll even blog about it. :-)

David C. Herring | DBA, Acxiom Database Services  

630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. |  

-----Original Message-----

From: Herring Dave - dherri
Sent: Thursday, August 27, 2009 4:16 PM
To: ''
Subject: function all disables parallel execution

I got an odd one that I'm hoping you guys can help with.

I've got a query that involves 2 small tables of about 100,000 rows and 1 with at least 1 billion rows. If I include a user function within the column list, the query runs serially, even though the xplan still shows parallelism will be used. If I take the function call out, the query runs with parallelism as expected.

I took the SQL_ID and passed it to DBMS_XPLAN.DISPLAY_CURSOR, which should have given me the xplan that was used. This also shows that the query would run in parallel.

This is running on

I've never run into this before where a user function disables parallelism, especially so secretively. Here's the function (it's part of a package):

function domain_group

         (p_domain in varchar2, p_domain_suffix in varchar2) return varchar2 is
d varchar2(512);
  if p_domain_suffix in ('.mil','.edu','.org','.gov') then     d := upper(p_domain_suffix);

      select upper(domain_group)
        into d
        from dw.perm_domain
       where domain = p_domain;
      when others then d := 'OTHER';

  end if;
end domain_group;

Anyone ever seen this before?

David C. Herring  | DBA, Acxiom Database Services

630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. |

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

-- Received on Thu Aug 27 2009 - 17:01:10 CDT

Original text of this message