Re: Interesting PL/SQL Puzzle

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Mon, 10 Nov 2003 14:34:26 -0800
Message-ID: <F001.005D63D8.20031110143426@fatcity.com>

Hi!

NAME SHARABLE_MEM
----------- ------------

TEST_PLSQL1       185607
TEST_PLSQL5         9123

A lot of junk, right? :)

PL/SQL engine works with interpretive code, it does not have any optimizations -- here I do simplify, so do not consider this statement as an absolute truth -- like, e.g. most of the C compilers have. It has its own rules that are not clear, usually. dbms_profiler won't help here (It could mislead, however. For a good example see recent post of Raj), IMHO. As I told:

"I would suggest to consider some simple things:

. standard Oracle and your application's package(s) dependencies . proper datatypes usage
"

These two things are simple but important.

So, I would sugget to change it to (sorry for dirty coding):

CREATE OR REPLACE PACKAGE test
IS

   PROCEDURE test_plsql2 (
     var1 in out varchar2
, var2 in out varchar2
, out1 in out varchar2
, out2 in out varchar2

   );
END;
/
CREATE OR REPLACE PACKAGE BODY test
IS

pat1 CONSTANT varchar2(1000) := '%tttttttttttttttttt%';
pat2 CONSTANT varchar2(1000) := 'lllllllllllllllllllllll';
pat3 CONSTANT varchar2(1000) := '%dfddddddddddddddddddiii%';
pat4 CONSTANT varchar2(1000) := 'yyyyyyyyyyyyyyyyyyyyyyyyy';

ls       VARCHAR2(1000);
b1       BOOLEAN;
b2       BOOLEAN;

   PROCEDURE test_plsql2 (
     var1 in out varchar2

, var2 in out varchar2
, out1 in out varchar2
, out2 in out varchar2

   );
begin

if false
then

   b1 := var1 LIKE pat3;
   b2 := var2 LIKE pat1;

--
if b1
then
    if b2
    then
       ls := pat2;
    else
       ls := pat4;
    end if;
end if;
..
   out1 := ls;
END;

or something alike, hope you get the idea. On my system it gives:
00:01:28.12 vs 00:00:08.60.

> When I looked at the C code generated by the native compilation, I was not
> very pleased the way native compilation works.

I think this statement of yours does answer your original question --
bad PL/SQL coding --> bad NC results.

HTH,
-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Khedr, Waleed wrote:

> Below are two dummy procs that are good enough to explain the issue (Jared
> forgive me for posting this big code).
> All the code in proc test_plsql1 is inside an IF clause that will not run.
[...]
> proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20
> minutes.
> Also test_2 required more CPU resources while running.
> 
> Also I tried native compilation, which did not do a lot (only 10 % faster).
> When I looked at the C code generated by the native compilation, I was not
> very pleased the way native compilation works.
> 
> Does anybody have a clue why?
> 
> I tried to include the proc in a package and pin it but there was no
> difference.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Nov 10 2003 - 16:34:26 CST