function all disables parallel execution

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Thu, 27 Aug 2009 16:15:51 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36068EDC52_at_CWYMSX04.Corp.Acxiom.net>

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 10.2.0.3

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);
begin
  if p_domain_suffix in ('.mil','.edu','.org','.gov') then     d := upper(p_domain_suffix);
  else
    begin

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

    end;
  end if;
  return(d);
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. | www.acxiom.com

---

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.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 27 2009 - 16:15:51 CDT