xtender

alter session set plsql_optimize_level=0;

declare 
   i integer; 
   a number :=1; 
   b number :=1; 

   l integer:=dbms_utility.get_time();

   procedure print(v in varchar2) is

xtender

alter session set plsql_optimize_level=0;

declare 
   i integer; 
   a number :=1; 
   b number :=1; 

   l integer:=dbms_utility.get_time();

   procedure print(v in varchar2) is

xtender

create table xt(
   id number,
   x number,
   date1 date not null,
   date2 date not null,
   date3 date not null,
   date4 date not null,
   date5 date not null
);
 

xtender

SQL> select/*+ full(xt) */ count(*)
  2  from xt
  3  where
  4     date1 > trunc(sysdate) - 1 or
  5     date2 > trunc(sysdate) - 1 or
  6     date3 > trunc(sysdate) - 1 or
  7     date4 > trunc(sysdate) - 1 or
  8     date5 > trunc(sysdate) - 1;

  COUNT(*)

xtender

SQL> create table xt_tab(i int);

Table created.

SQL> create or replace procedure xt_no_data_found(i int) as
  2  begin
  3     if i>0 then
  4        raise NO_DATA_FOUND;
  5     end if;
  6  end;

xtender

set feed on timing on echo on;
spool &_SPOOLS./call_vs_exec_spool.sql

create or replace procedure xt_proc(n in int, o out number) as
   dummy number:=0;
begin
   for i in 1..n loop
      for j in 1..n loop
         dummy:=dummy+ln(i+j);
      end loop;

xtender

SQL> 
SQL> create or replace procedure xt_proc(n in int, o out number) as
  2     dummy number:=0;
  3  begin
  4     for i in 1..n loop
  5        for j in 1..n loop
  6           dummy:=dummy+ln(i+j);
  7        end loop;
  8     end loop;
  9     o:=dummy;

xtender

SQL> with t as (select/*+ materialize */ level id from dual connect by level<=1e3)
  2  select/*+ parallel(t) */ count(*) from t;

  COUNT(*)
----------
      1000

1 row selected.

SQL> @find_sql "with t as (select/*+ materialize%"

xtender

SQL> alter system set parallel_max_servers=4;

System altered.

SQL> select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
    100000

xtender

SQL> alter session set optimizer_inmemory_aware=true;

Session altered.

SQL> select count(*) from t_inmemory;

  COUNT(*)
----------
    342458