function all disables parallel execution
Date: Thu, 27 Aug 2009 16:15:51 -0500
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):
(p_domain in varchar2, p_domain_suffix in varchar2) return varchar2
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; exception when others then d := 'OTHER';
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.
Received on Thu Aug 27 2009 - 16:15:51 CDT