function all disables parallel execution
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- 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):
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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
