RE: Semi-deterministic?
- From: "Larry Elkins" <elkinsl@xxxxxxxxxxx>
- To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 06 Feb 2014 00:06:10 -0600
Riyaj,
Using your test case on an 11.2.0.1 database, where you show 8 executions with
the no_merge and scalar caching, I only show 2
executions. Then moving it into a WITH clause, and with materialize as
originally alluded to, and what the OP also said he had also
done, drops it to 1, at least in my case.
Some years ago, for some folks writing large *ETL* processes against 11.1, it
seems like I couldn't count on the WITH materialize
approach for all cases (and my preferred solution for them was a join, not a
function ;-)). Anyway, with differences between
versions, the ways our queries may be rewritten/transformed internally now, or
in the future, it seems a bit risky to depend on any
one approach to make only one call, so maybe the scalar caching approach makes
sure worst case is avoided, though not always the
absolute minimum number of calls???
And of course, there's always the question to ask if they should really even be
using a function, and if they need to encapsulate
some sort of logic for re-use/consistency, could that be done in a view. Not
saying or implying that's the case here for the OP,
just that it's something commonly seen.
Larry G. Elkins
elkinsl@xxxxxxxxxxx
Cell: 214.695.8605
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Riyaj
> Shamsudeen
> Sent: Wednesday, February 05, 2014 6:21 PM
> 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.
>
> 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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
