How much redo data does you query generate (not about delayed block cleanup)

I want to show a tiny effect of materializing factored subquery, which can lead to huge redo generation.
It is also a good time to taste new feature of defining PL/SQL function in the WITH clause. But it is used here only for convenience.

So, let's check how much redo is generated for materializing just one row and just one time

SQL> with function calc(p_in in number) return number
  2  is
  3    res number;
  4  begin
  5    with a as (select /*+ materialize */ 1 c1 from dual)
  6    select calc.p_in + c1 into res from a;
  7    return res;
  8  end;
  9  select calc(1) from dual;
 10  /

Statistics
----------------------------------------------------------
          3  recursive calls
          8  db block gets
          5  consistent gets
          1  physical reads
        572  redo size
        357  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Nothing to worry about.
But what if you have some PL/SQL function, which employs materialization, and this function is called for each 100K rows output of your report query.

SQL> with function calc(p_in in number) return number
  2  is
  3    res number;
  4  begin
  5    with a as (select /*+ materialize */ 1 c1 from dual)
  6    select calc.p_in + c1 into res from a;
  7    return res;
  8  end;
  9  select calc(rownum) from dual connect by level <=1e5;
 10  /

Statistics
----------------------------------------------------------
     300001  recursive calls
     802222  db block gets
     500000  consistent gets
     100000  physical reads
   57171620  redo size
     611083  bytes sent via SQL*Net to client
        708  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     100000  rows processed

Things become much more interesting. And it is just for one row stored in the temporary table.

One question remains, what exactly causes redo generation. Unfortunately, SQL Trace does not show anything about materialization. But doing the following run, I can infer that redo data is generated for DDL part of operation only.

SQL> with function calc(p_in in number) return number
  2  is
  3    res number;
  4  begin
  5    with a as (select /*+ materialize */ 1 c1 from dual connect by level<=1e5)
  6    select calc.p_in + sum(c1) into res from a;
  7    return res;
  8  end;
  9  select calc(1) from dual;
 10  /

Statistics
----------------------------------------------------------
          5  recursive calls
        161  db block gets
        157  consistent gets
        152  physical reads
        572  redo size
        359  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

So, materialization feature sometimes is very useful, but it has to be applied with caution.

Including files in SQL*Plus script

Do you know that @ (at sign) can also work as an include directive?
Suppose you have a script for getting your session statistics from v$mystat. But you need a particular list of statistics. Furthermore, you want to use the same list in several places of your script without writing it multiple times.
It is easy to do using @ (at sign).
Here is an example of a script which needs to include another script:

select m.statistic#,n.name,value from v$mystat m, v$statname n
where n.statistic#=m.statistic# and
n.name in (
@mystat_list
);

And here is content of mystat_list.sql
'session logical reads',
'CPU used by this session',
'session connect time',
'user I/O wait time',
'session pga memory max',
'consistent gets',
'physical reads',
'physical writes',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'lob reads',
'lob writes',
'parse time cpu',
'parse time elapsed',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)'

The result is:
SQL> select m.statistic#,n.name,value from v$mystat m, v$statname n
  2  where n.statistic#=m.statistic# and
  3  n.name in (
  4  @mystat_list
 21  );

 STATISTIC# NAME                                                VALUE
----------- --------------------------------------------- -----------
         14 session logical reads                                 203
         19 CPU used by this session                                8
         24 user I/O wait time                                      6
         29 session connect time                           1479453315
         38 session pga memory max                            3395896
        132 consistent gets                                       200
        141 physical reads                                         20
        152 physical writes                                         0
        157 physical reads direct temporary tablespace              0
        158 physical writes direct temporary tablespace             0
        743 lob reads                                               0
        744 lob writes                                              0
       1082 parse time cpu                                          2
       1083 parse time elapsed                                      8
       1100 sorts (memory)                                         23
       1101 sorts (disk)                                            0
       1102 sorts (rows)                                           23

17 rows selected.

So, you can include whatever you need wherever you need. It can be part of PL/SQL anonymous block, part of a query or something else. Just make sure that resulted text will be syntactically correct.