3 Replies Latest reply: Jan 22, 2008 7:52 PM by 601585 RSS

    SQL vs PL/SQL execution - context switch between SQL and PL/SQL engine

    ora001
      Hi Gurus -

      What makes the PL/SQL execution of the following stmt runs forever while SQL type execution complets in less than 3 minutes?

      insert into tbl1
      select
      timestamp1,
      col2,
      col3,
      pak1.getgmt(dt1,'us/central'),
      pak1.getgmt(dt2,'us/central'),
      pak1.getgmt(dt3,'us/central'),
      col4,
      col5
      from
      tblx t1
      where
      t1.log_timestamp = (select max(t2.timestamp1) from tblx t2
                where t1.col2 = t2.col2
                and t1.col3 = t2.col3);

      --Elapsed: 00:03:00

      This one takes forever.

      BEGIN
      insert into tbl1
      select
      timestamp1,
      col2,
      col3,
      pak1.getgmt(dt1,'us/central'),
      pak1.getgmt(dt2,'us/central'),
      pak1.getgmt(dt3,'us/central'),
      col4,
      col5
      from
      tblx t1
      where
      t1.log_timestamp = (select max(t2.timestamp1) from tblx t2
                where t1.col2 = t2.col2
                and t1.col3 = t2.col3);
      END;
      /
      --Runs forever.

      Any suggestions for better code?

      Thanks,
      Prakash
        • 1. Re: SQL vs PL/SQL execution - context switch between SQL and PL/SQL engine
          153119
          As far as I know, PL/SQL (whether stored procedure or anonymous block) runs with optimizer_goal ALL_ROWS by default.
          This may be different from your current session.
          As you don't specify any information like database version, it is difficult to tell what is going on.
          Tracing the session is in order.
          In fact, this would have been the first step prior to post his.
          This probably would result in two different explain plans, and those would provide more clues.
          Obviously, what is in those homegrown packaged functions is also important.

          --
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: SQL vs PL/SQL execution - context switch between SQL and PL/SQL engine
            ora001
            I am running on "Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production".

            The homegrown function converts the given date into GMT based on the timezone. In the eg I have is 'US/CENTRAL' and the code is as below.

            if strtimezone = 'us/central' then
                      select      datein - numtodsinterval(
                      (extract(timezone_hour from to_timestamp(to_char(corrected_datein,'dd-mon-yy hh:mi:ss am'))
                      at time zone 'us/central')),'hour') into dateout
                      from dual;
            return dateout;

            Thnx,
            pr
            • 3. Re: SQL vs PL/SQL execution - context switch between SQL and PL/SQL engine
              601585
              As sybrandb said, you should do sql trace on both cases and compare the tkprof result.
              That all you need