Re: Semi-deterministic?

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Wed, 5 Feb 2014 16:21:12 -0800
Message-ID: <CAA2DszxpBn3QHGfRPFCkJkfWpuXKeTkDzxF5SwcCeWcD7tr0PQ_at_mail.gmail.com>

Hi Matt
  I reviewed the executions in detail with a test case. I think, in your case, inline view is rewritten and merged in to the main query block and that explains the increase in executions. Please review 10053 trace output of your code with the inline view ( I know, you probably don't care).

Following is the test case setup (to test it yourself, replace rs. with your username)

REM tables log1 and t1 are created. Package pkg1 is created with an anonymous transaction so that we can keep track number of executions of the function call.
REM function simply inserts a row in to log1 table for every execution.

• code---- drop table rs.log1; create table rs.log1 (n1 number, d1 timestamp); drop table rs.t1; create table rs.t1 as select l1 , lpad(l1, 1000,'X') v1 from (select level l1 from dual connect by level <=1000); create or replace package rs.pkg1 as function calculate_n1( p_in in number ) return number; end; / show errors create or replace package body rs.pkg1 as function calculate_n1( p_in in number ) return number is pragma autonomous_transaction; begin insert into rs.log1 values ( p_in, systimestamp); commit; return (p_in+10); end; end; / show errors;
• code--- Now, let's call the function inline with rownum<=100. As expected, there are 100 rows in the log1 table. This is expected. For each row, it is calling the function.

truncate table rs.log1;
select l1, rs.pkg1.calculate_n1(l1)

   from rs.t1 where rownum <=100;
select count(*) from rs.log1;

  COUNT(*)

---

         100

Now, let's call the function as inline view.

truncate table rs.log1;
select /*+ monitor use_hash (p1 t1) */ l1, p1.n1 from rs.t1 ,

   (select rs.pkg1.calculate_n1(100) n1 from dual) p1 where rownum <=100
;

select count(*) from rs.log1;

  COUNT(*)

---

         100

This is a shocker, I expected 1 row, but 100 rows!

Even SQL Monitor output for the above SQL is showing one execution of the row source:(Execs column below) Sorry for the format, Not sure if it is possible to format in Oracle-l postings.

---

| Id |                Operation                 | Name |  Rows   | Cost |
Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                          |      | (Estim) |      |
Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
==============================================================================================================================================
|  0 | SELECT STATEMENT                         |      |         |      |
      1 |     +0 |     1 |      100 |          |                 |
|  1 |   COUNT STOPKEY                          |      |         |      |
      1 |     +0 |     1 |      100 |          |                 |
|  2 |    NESTED LOOPS                          |      |    1000 |    4 |
      1 |     +0 |     1 |      100 |          |                 |
|  3 |     VIEW                                 |      |       1 |    2 |
      1 |     +0 |     1 |        1 |          |                 |
|  4 |      COUNT STOPKEY                       |      |         |      |
      1 |     +0 |     1 |        1 |          |                 |
|  5 |       FAST DUAL                          |      |       1 |    2 |
      1 |     +0 |     1 |        1 |          |                 |
|  6 |     TABLE ACCESS STORAGE FULL FIRST ROWS | T1   |    1000 |    2 |
      1 |     +0 |     1 |      100 |          |                 |
==============================================================================================================================================

However, 10053 trace of the SQL statement shows an interesting rewrite! Essentially, optimizer rewrote the inline view and merged with the main query block. That caused 100 execution and so, 100 rows inserted into the log1 table. I am fine with this explanation.

SELECT /*+ USE_HASH ("DUAL") USE_HASH ("T1") */ "T1"."L1" "L1","TMP"."PKG1"."CALCULATE_N1"(100) "N1" FROM "TMP"."T1" "T1","SYS"."DUAL" "DUAL" WHERE ROWNUM<=100

now, let's see if we can force the optimizer not to merge with the rownum and no_merge double protection.

truncate table rs.log1;
select /*+ monitor */ l1, p1.n1
from rs.t1 ,

   (select /*+ no_merge */ rs.pkg1.calculate_n1(100) n1 from dual where rownum=1) p1
where rownum <=100
;

select count(*) from rs.log1;
  COUNT(*)

---

           8

Eight is better than 100, But, why 8 executions, when there is a need for just 1 execution?. Interesting.

Also, the timestamp is scattered around little bit. I am not sure why, but something to research later :) (I am actually taking lazy way out hoping somebody has answer right away).

select * from rs.log1;

        N1 D1

---

---

       100 05-FEB-14 03.46.14.003892 PM
       100 05-FEB-14 03.46.14.005318 PM
       100 05-FEB-14 03.46.14.005912 PM
       100 05-FEB-14 03.46.14.006587 PM
       100 05-FEB-14 03.46.14.007230 PM
       100 05-FEB-14 03.46.14.007862 PM
       100 05-FEB-14 03.46.14.008482 PM
       100 05-FEB-14 03.46.14.009133 PM

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 06 2014 - 01:21:12 CET