with
    rest_of_query as (
                         select
                                  a.policy_number
                                , a.caseid
                                , a.funding_method_cd
                                , a.CLMEVNTID
                                , sum(a.PAID_AMT) paid_amt
                                , a.disability_dt
--
-- problem here with POWER
-- these lines cause the query to not finish
-- goes from 3 minutes to 4+ hours
--
                                , sum(a.PAID_AMT * POWER ( 1 + Y.GAAP, a.mnths_btwn/12 )) gaap_x
                                , sum(a.PAID_AMT * POWER ( 1 + Y.PRICING, a.mnths_btwn/12 )) pricing_x
                                , sum(a.PAID_AMT * POWER ( 1 + Y.STAT, a.mnths_btwn/12 )) stat_x
                                , sum(a.PAID_AMT * POWER ( 1 + Y.UW, a.mnths_btwn/12 )) uw_x
                                , sum(a.PAID_AMT * POWER ( 1 + Y.VALUATION, a.mnths_btwn/12 )) valuation_x
                         from
                                kevtemp1 a
                              , (select * from experience.EXP_DRIVING_MONTH where rownum = 1) e
                              , experience.dlr_discount Y
                         where a.RPLCTNDT_calculation <= e.driving_date
                         and y.YEAR = TO_CHAR (a.disability_dt,'YYYY')
                         group by
                                  a.policy_number
                                , a.caseid
                                , a.funding_method_cd
                                , a.CLMEVNTID
                                , a.disability_dt
                       )
select *
from rest_of_query
/

create table kevtemp9
(
    anumber number not null
  , bnumber number not null
)
/

insert into kevtemp9
select mod(rownum,100)/10000,mod(rownum,33)
from dual
connect by level <= 20000
/


select * from kevtemp9 where rownum < 10
/

select count(*) from kevtemp9
/

select count(power(1+anumber,bnumber/12)) from kevtemp9
/

11:44:53 SQL> create table kevtemp9
11:44:53   2  (
11:44:53   3      anumber number not null
11:44:53   4    , bnumber number not null
11:44:53   5  )
11:44:53   6  /

Table created.

Elapsed: 00:00:00.01
11:44:53 SQL> 
11:44:53 SQL> insert into kevtemp9
11:44:53   2  select mod(rownum,100)/10000,mod(rownum,33)
11:44:53   3  from dual
11:44:53   4  connect by level <= 20000
11:44:53   5  /

20000 rows created.

Elapsed: 00:00:00.04
11:44:54 SQL> 
11:44:54 SQL> 
11:44:54 SQL> select * from kevtemp9 where rownum < 10
11:44:54   2  /

   ANUMBER    BNUMBER
---------- ----------
     .0091         31
     .0092         32
     .0093          0
     .0094          1
     .0095          2
     .0096          3
     .0097          4
     .0098          5
     .0099          6

9 rows selected.

Elapsed: 00:00:00.01
11:44:54 SQL> 
11:44:54 SQL> select count(*) from kevtemp9
11:44:54   2  /

  COUNT(*)
----------
     20000

1 row selected.

Elapsed: 00:00:00.01
11:44:54 SQL> 
11:44:54 SQL> select count(power(1+anumber,bnumber/12)) from kevtemp9
11:44:54   2  /

COUNT(POWER(1+ANUMBER,BNUMBER/12))
----------------------------------
                             20000

1 row selected.

Elapsed: 00:00:05.01
11:44:59 SQL> 

PFK@test> select count(*) from kevtemp9
/
  COUNT(*)
----------
     20000
Elapsed: 00:00:00.04

select count(power(1+anumber,bnumber/12)) from kevtemp9
/
COUNT(POWER(1+ANUMBER,BNUMBER/12))
----------------------------------
                             20000

Elapsed: 00:00:01.38

select count(*) 
from
 kevtemp9


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         38          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0         38          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 42  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=38 pr=0 pw=0 time=2280 us)
  20000   TABLE ACCESS FULL KEVTEMP9 (cr=38 pr=0 pw=0 time=114 us)

********************************************************************************

select count(power(1+anumber,bnumber/12)) 
from
 kevtemp9


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.37       1.34          0         38          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.37       1.34          0         38          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 42  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=38 pr=0 pw=0 time=1347071 us)
  20000   TABLE ACCESS FULL KEVTEMP9 (cr=38 pr=0 pw=0 time=36 us)

declare 
 i number;
 x number;
begin

for i in 1..20000 loop 
x := power (i,10);
end loop; 
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05


declare 
 i number;
 x number;
begin

for i in 1..20000 loop 
x := power (i,10.54146415412);
end loop; 
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.09

tmp > cat test.pl 
#!/bin/perl

for ( $i =0; $i < 20000 ; $i++ ) {
  $e = $i ** 10.33535334646;
}
tmp > time perl test.pl

real	0m0.008s
user	0m0.003s
sys	0m0.003s

SQL> with
  2       some_data as (
  3                      select mod(rownum,100)/10000 anumber,mod(rownum,33) bnumber
  4                      from dual
  5                      connect by level <= 20000
  6                    )
  7  select count(*) from some_data
  8  /

  COUNT(*)
----------
     20000

1 row selected.

Elapsed: 00:00:00.02
SQL> with
  2       some_data as (
  3                      select mod(rownum,100)/10000 anumber,mod(rownum,33) bnumber
  4                      from dual
  5                      connect by level <= 20000
  6                    )
  7  select count(power(1+anumber,bnumber/12)) from some_data
  8  /

COUNT(POWER(1+ANUMBER,BNUMBER/12))
----------------------------------
                             20000

1 row selected.

Elapsed: 00:00:05.03

SQL> with
  2       some_data as (
  3                      select mod(rownum,100)/10000 anumber,mod(rownum,33) bnumber
  4                      from dual
  5                      connect by level <= 20000
  6                    )
  7  select count(*) from some_data
  8  /

  COUNT(*)
----------
     20000

1 row selected.

Elapsed: 00:00:00.01
SQL> with
  2       some_data as (
  3                      select mod(rownum,100)/10000 anumber,mod(rownum,33) bnumber
  4                      from dual
  5                      connect by level <= 20000
  6                    )
  7  select count(power(1+anumber,bnumber/12)) from some_data
  8  /

COUNT(POWER(1+ANUMBER,BNUMBER/12))
----------------------------------
                             20000

1 row selected.

Elapsed: 00:00:00.04

--make sure we do direct reads
alter session set "_serial_direct_read"=always

--check that the power function is offloadable
select offloadable from v$sqlfn_metadata where name='POWER';

--display whether the step COULD (not necessarily WOULD !) be offloaded
alter session set cell_offload_plan_display=always;
set autotrace on exp
select count(power(1+anumber,bnumber/12)) from kevtemp9;
select count(*) from kevtemp9;
set autotrace off

--check the timings with offload
set timing on
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set cell_offload_processing=true;
select count(*) from kevtemp9;
alter system flush buffer_cache;
select count(power(1+anumber,bnumber/12)) from kevtemp9;

--did we actually get any offload?
select 
sql_text,
physical_Read_bytes,
io_cell_offload_eligible_bytes,
io_cell_offload_returned_bytes
from v$sql
where sql_text='select count(power(1+anumber,bnumber/12)) from kevtemp9';

select 
sql_text,
physical_Read_bytes,
io_cell_offload_eligible_bytes,
io_cell_offload_returned_bytes
from v$sql
where sql_text='select count(*) from kevtemp9';


--and now without offload
alter session set cell_offload_processing=false;
alter system flush shared_pool;
alter system flush buffer_cache;
select count(*) from kevtemp9;
alter system flush buffer_cache;
select count(power(1+anumber,bnumber/12)) from kevtemp9;


--did we actually get any offload?
select 
sql_text,
physical_Read_bytes,
io_cell_offload_eligible_bytes,
io_cell_offload_returned_bytes
from v$sql
where sql_text='select count(power(1+anumber,bnumber/12)) from kevtemp9';

select 
sql_text,
physical_Read_bytes,
io_cell_offload_eligible_bytes,
io_cell_offload_returned_bytes
from v$sql
where sql_text='select count(*) from kevtemp9';