outline

SQL> create table t (id number , value number, name varchar2(30));

Table created.

SQL> insert into t select FILE_ID,BLOCKS,TABLESPACE_NAME from dba_data_files;

4 rows created.

SQL> commit;

Commit complete.

SQL> insert into t select * from t;

4 rows created.

SQL> select count(*) from t;

  COUNT(*)
----------
   1048576

 

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  t varchar2(100);
  3  begin
  4  dbms_advisor.tune_mview(t,'create materialized view mv_t refresh fast on commit enable query rewrite  as select count(*),count(value), count(id),sum(value),id from t group by id');
  5  dbms_output.put_line(t);
  6* end;
SQL> /
TASK_685

PL/SQL procedure successfully completed.

 

SQL> select STATEMENT from dba_tune_mview  where task_name='TASK_685'

SQL> CREATE MATERIALIZED VIEW LOG ON "QDL"."T" WITH ROWID, SEQUENCE ("ID","VALUE")  INCLUDING NEW VALUES;

Materialized view log created.

 

SQL> CREATE MATERIALIZED VIEW QDL.MV_T   REFRESH FAST WITH ROWID ENABLE QUERY REWRITE
 AS select count(*),count(value), count(id),sum(value),id from t group by id  2  ;

Materialized view created.

 

SQL> exec dbms_stats.gather_table_stats('QDL','T');

PL/SQL procedure successfully completed.

 

 

SQL> create private outline ol_sum on select sum(value) from t group by id;

Outline created.

SQL> alter session set use_private_outline=true;
alter session set use_private_outline=true
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

SQL> alter session set use_private_outlines=true;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%';

no rows selected

SQL> select sum(value) from t group by id;

SUM(VALUE)
----------
1.0905E+10
6710886400
5704253440
1.0905E+10

SQL> select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%'
  2  /

SQL_TEXT                                 ADDRESS  SQL_ID        OUTLINE_CA OUTLINE_SID
---------------------------------------- -------- ------------- ---------- ------------
select sum(value) from t group by id     2D527C80 cv1fchhu0gj5j DEFAULT    24445224

 

outline_sid is the session identifier for the outline , if you quit the session the private outline will disappear , that's why in the document they say there are 3 gobal temporary table for the outlines. 

 

SQL> alter session set use_private_outlines=false;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select sum(value) from t group by id;

SUM(VALUE)
----------
1.0905E+10
6710886400
5704253440
1.0905E+10

SQL> select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%'  ;

SQL_TEXT                                 ADDRESS  SQL_ID        OUTLINE_CA OUTLINE_SID
---------------------------------------- -------- ------------- ---------- ------------
select sum(value) from t group by id     2D527C80 cv1fchhu0gj5j

 

before we create pubic outlines , there is no row store in dba_outlines

 

SQL> create public outline ol_dd from private ol_sum;

Outline created.

 

SQL> select NAME,owner,CATEGORY,used from dba_outlines;

NAME          OWNER               CATEGORY                       USED
------------- ------------------- ------------------------------ ------
OL_DD         QDL                 DEFAULT                        USED

 

now the private outline can go away and we can use the "stored" outline now

 

SQL> conn qdl/oracle
Connected.

SQL> alter system set use_stored_outlines=false;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select sum(value) from t group by id;

SUM(VALUE)
----------
1.0905E+10
6710886400
5704253440
1.0905E+10

SQL> select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%'  ;

SQL_TEXT                                 ADDRESS  SQL_ID        OUTLINE_CA OUTLINE_SID
---------------------------------------- -------- ------------- ---------- ------------
select sum(value) from t group by id     2D527C80 cv1fchhu0gj5j

SQL> alter system set use_stored_outlines=default;

System altered.

SQL>  alter system flush shared_pool;

System altered.

SQL> select sum(value) from t group by id;

SUM(VALUE)
----------
1.0905E+10
6710886400
5704253440
1.0905E+10

SQL>  select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%'  ;

SQL_TEXT                                 ADDRESS  SQL_ID        OUTLINE_CA OUTLINE_SID
---------------------------------------- -------- ------------- ---------- ------------
select sum(value) from t group by id     2D527C80 cv1fchhu0gj5j DEFAULT

 

notice the outline_sid is empty because we are using a pulic outline.

SQL> exec dbms_outln.UPDATE_BY_CAT('DEFAULT','QDL');

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL>  select NAME,owner,CATEGORY,used from dba_outlines;

NAME          OWNER              CATEGORY                       USED
------------- ------------------ ------------------------------ ------
OL_DD         QDL                QDL                            USED

 

notice the category now changed into QDL

We then can use alter system/session set use_stored_outlines=QDL; to enable the outline not belong to default category.

 

SQL> alter outline ol_dd rename to ol_sum change category to OUTLN_NEW;

Outline altered.

SQL> select NAME,owner,CATEGORY,used from dba_outlines;

NAME       OWNER      CATEGORY                       USED
---------- ---------- ------------------------------ ------
OL_SUM     QDL        OUTLN_NEW                      USED

 

 

SQL> alter session set use_stored_outlines=OUTLN_NEW;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL>  select sum(value) from t group by id;

SUM(VALUE)
----------
1.0905E+10
6710886400
5704253440
1.0905E+10

SQL> select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%'  ;

no rows selected

SQL> select sum(value) from t group by id;

SUM(VALUE)
----------
1.0905E+10
6710886400
5704253440
1.0905E+10

SQL>  select sql_text ,address,sql_id ,outline_category ,outline_sid from v$sqlarea WHERE lower(SQL_TEXT) LIKE 'select sum(value) from t%'  ;

SQL_TEXT                                 ADDRESS  SQL_ID        OUTLINE_CA OUTLINE_SID
---------------------------------------- -------- ------------- ---------- ------------
select sum(value) from t group by id     2D527C80 cv1fchhu0gj5j OUTLN_NEW