xtender

SQL> alter system flush shared_pool;

System altered.

SQL> @tests/sy1.sql
SQL> variable from_n number
SQL> variable to_n number
SQL> exec :from_n := 100;

PL/SQL procedure successfully completed.

xtender

    ''=~(        '(?{'        .('`'        |'%')        .('['        ^'-')
    .('`'        |'!')        .('`'        |',')        .'"'.        '\\$'
    .'=='        .('['        ^'+')        .('`'        |'/')        .('['
    ^'+')        .'||'        .(';'        &'=')        .(';'        &'=')
    .';-'        .'-'.        '\\$'        .'=;'        .('['        ^'(')
    .('['        ^'.')        .('`'        |'"')        .('!'        ^'+')
   .'_\\{'      .'(\\$'      .';=('.      '\\$=|'      ."\|".(      '`'^'.'
  ).(('`')|    '/').').'    .'\\"'.+(    '{'^'[').    ('`'|'"')    .('`'|'/'
 ).('['^'/')  .('['^'/').  ('`'|',').(  '`'|('%')).  '\\".\\"'.(  '['^('(')).
 '\\"'.('['^  '#').'!!--'  .'\\$=.\\"'  .('{'^'[').  ('`'|'/').(  '`'|"\&").(

xtender

with v as(
     select--+ materialize
         rowid rid
     from z_line_items li
     where li.item_id = 42
)
select--+ leading(v li o) parallel(v 4)
    sum(li.price)
  from v
     , z_line_items li

xtender

  1  with v as (select/*+ materialize */ '1' x from dual connect by level<=3e5)
  2  select /*+ parallel(v 8) */ distinct userenv('sid')
  3* from v
SQL> /

USERENV('SID')
--------------
            68
           145

xtender

insert into z_line_items select * from z_line_items;
insert into z_line_items select * from z_line_items;
begin
  dbms_stats.gather_table_stats(
    user
  , 'Z_LINE_ITEMS'
  , estimate_percent => 100
  , method_opt       => 'for all columns size 1 for columns size 254 item_id'
  , no_invalidate    => false
  );

xtender

alter session force parallel query parallel 8;

with v as (select/*+ materialize cardinality(100000) */ '1' x,rownum rn, rpad('x',1000,'x') padding from dual where rownum>0 connect by level<=3e5)
select/*+ parallel(v 8) dynalic_sampling(v 10) */ distinct userenv('sid')
from v;

USERENV('SID')
--------------
            18
            79

xtender

with v as(
   select /*+ inline */ * from (
      select /*+ index(li) */
      rowid rid
      from z_line_items li
      where li.item_id = 42
      union all
      select rowid rid
      from z_line_items li
      where 1 = 2

xtender

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b5smw3hmc9bw7, child number 0
-------------------------------------
with v as(    select /*+ materialize */ * from (       select /*+
index(li) */       rowid rid       from z_line_items li       where
li.item_id = 42       union all       select rowid rid       from
z_line_items li       where 1 = 2    ) ) select * From (    select /*+
leading(v li o) use_nl(li o) parallel(v 8) */       li.*, o.*    from v
      , z_line_items li       , z_orders o    where li.rowid = v.rid

xtender

SQL Monitoring Report

SQL Text
------------------------------
with v as (select/*+ materialize */ * from ( select '1' x,rownum rn, rpad('x',100,'x') padding from dual connect by level<=3e5 union all select dummy,null,null from dual where 1=0 ) ) select/*+ parallel(@SEL$D67CB2D2 T1@SEL$D67CB2D2 8) */ distinct userenv('sid') from v

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1

xtender

set echo on time on timing on;
alter session set "_query_execution_time_limit"=10;
select count(*),systimestamp from xmltable('1 to 10000000');
/
/
set echo on time off timing off;