Re: PARALLEL_ENABLE...Usage and Measurement...

alincoln_at_edgewater.com (alincoln) wrote in news:3c5e17cb.0301101056.5cacc0b9_at_posting.google.com:

> Hi, newbie to this news group...
>
> I have a simple function that I wish to PARALLEL_ENABLE under Oracle
> 8.1.7...
>
> CREATE OR REPLACE function cleanString (
> in_string in varchar2
> ,in_verbose in number default 0
> )
> return varchar2 parallel_enable is
>
> v_OutString VARCHAR2(50) := '';
> v_Char CHAR(1);
>
> begin
>
> if ( in_string is null ) then
> v_OutString := '';
> else
> print.Value( in_string, 'Starting' );
> begin
> for v_ind in 1..length( in_string ) loop
> v_Char := SUBSTR(in_string, v_ind, 1);
>
> if( ('A' <= v_Char AND v_Char <= 'Z') OR ('0' <=
> v_Char AND v_Char <= '9') ) then
> v_OutString := v_OutString || v_Char;
> elsif( 'a' <= v_Char AND v_Char <= 'z' ) then
> v_OutString := v_OutString || upper(v_Char);
> end if;
>
> end loop;
> exception
> when others then
> v_OutString := '';
> end;
> end if;
>
> print.Value( v_OutString, 'Returning' );
>
> return v_OutString;
>
> end;
> /
>
> I am then using this in an INSERT statement in the 'Hope' that it will
> run in Parallel.
>
>
> insert into duplicate_check_superkey (
> order_id
> ,line_item
> ,key
> ,timestamp
> ,forced_ind
> )
> values (
> theLine.idOrder
> ,theLine.lineItem
> ,rtrim(ltrim(upper(theLine.idProduct)))
> || rtrim(ltrim(theLine.shipZip))
> || theLine.shipState
> || cleanString(nvl(theLine.shipCity,' '))
> || cleanString(nvl(theLine.shipFirstName,' '))
> || cleanString(nvl(theLine.shipMiddleName,' '))
> || cleanString(nvl(theLine.shipLastName,' '))
> || cleanString(nvl(theLine.shipAddressOne,' '))
> ,SYSDATE
> ,0
> );
>
> The code runs fine except that it appears to simply run in a
> sequential manner. How does one verify/confirm that a function is
> truely running in parallel?
>
> Configuration :
> - Solaris 2.4
> - Sun 8500 Server
> - Oracle 8.1.7
>
> Thx in advance,
>
> Adym Lincoln
> Edgewater Technologies, Inc.

Hello Adym,

Why are you trying to run it in parallel? If you want to speed it up you could try a couple of things that would probably help before looking for more CPUs.

Firstly translate the looping clean string function and use a single statement with the handily named translate function, something like this.

SQL> var str varchar2(20)
SQL> exec :str := 'yb#$dx123-9-'

PL/SQL procedure successfully completed.

SQL> select str,
  2 translate (upper(str),'A'||x,'A') str_out   3 from (
  4 select :str str,
  5 translate(upper(:str),
  6 '*ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','*') x   7 from dual)
SQL> / STR STR_OUT
-------------- --------------
yb#$dx123-9- YBDX1239

SQL> exec :str := ')ab#$*Ad1_at_23--'

PL/SQL procedure successfully completed.

SQL> / STR STR_OUT
-------------- --------------
)ab#$*Ad1_at_23-- ABAD123

SQL> Then concatenate all your shipping info together first and call the function one time for the whole string. Even better just work out how to translate it in line and forget the function altogether.

You should see the SQL reference in the online documentation for the translate function.

http://download-west.oracle.com/docs/cd/B10501_ 01/server.920/a96540/functions150a.htm#79574

http://tahiti.oracle.com/

I don't know if any of this helps, you are showing a singleton insert which I would think would be the least likely kind of thing to benefit from being run in parallel.

Martin Received on Fri Jan 10 2003 - 21:51:36 CST