OraFAQ Forum: Performance Tuning » problem with outlines

Home » RDBMS Server » Performance Tuning » problem with outlines

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem with outlines [message #513934]

Thu, 30 June 2011 06:10

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

i have tryied outlines under 9i and 11i and it seems that it
works only if i have cursor_sharing set to exact.

there is some logs from my work:

SQL> desc tab2;
Nazwa WartoťŠ NULL? Typ
-----------
COL2 NOT NULL NUMBER
COL3 NUMBER
COL4 NUMBER
COL5 NUMBER
COL6 NUMBER

SQL> select * from tab2;

COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ----------
1 1 5 1 223
2 3 5 2 223
3 4 6 3 223
5 1 5 5 11

there is one index unique on col5

SQL> SELECT table_name as c1, column_name as c2 FROM Dba_Ind_Columns where table_name='TAB2';

C1 C2
---------- ----------
TAB2 COL2
TAB2 COL5

execution plan for query is below:

SQL> select t.col2 from tab2 t where t.col5=3;

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 4 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

so it uses index.

outln.ol$ and outln.ol$hints are empty.

now i make outline for this statement.

alter system flush shared_pool;
alter session set create_stored_outlines =true;
select t.col2 from tab2 t where t.col5=3;
alter session set create_stored_outlines =false;

after above comands i have sql statement which is parsed and stored in shared pool.

SQL> SELECT hash_value as col1, sql_text as col2 FROM v$sql
2 WHERE lower(sql_text) LIKE '%tab2%';

2952636881 select t.col2 from tab2 t where t.col5=:"SYS_B_0"

so i create outline for this statement:

SQL> exec dbms_outln.create_outline('2952636881',0,'MYCAT');

and there is:

SQL> select ol_name, sql_text from outln.ol$;

OL_NAME SQL_TEXT
------------------------------ ------------------------------------------------------------------------------
SYS_OUTLINE_11063012550580837 select t.col2 from tab2 t where t.col5=:"SYS_B_0"

and change system parameter:
alter system set use_stored_outlines=MYCAT;

try to see how execution plan looks for this statement

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 4 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

so it is using index

now i create another outline with hint in the statement

SQL> create outline just_for_full_scan on select /*+full(t) */ t.col2 from tab2 t where t.col5=3;

and there is:

SQL> select ol_name, sql_text from outln.ol$;

OL_NAME SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
JUST_FOR_FULL_SCAN select /*+full(t) */ t.col2 from tab2 t where t.col5=3
SYS_OUTLINE_11063012550580837 select t.col2 from tab2 t where t.col5=:"SYS_B_0"

now please look into hint table (ol$hints)

SQL> SELECT ol_name as col1, hint_text as col2 FROM OUTLN.OL$HINTS o
2 WHERE o.HINT# = 1;

COL1 COL2
---------------------------------------- ----------------------------------------
SYS_OUTLINE_11063012550580837 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("TAB2
"."COL5"))

JUST_FOR_FULL_SCAN FULL(@"SEL$1" "T"@"SEL$1")

so after one update on col2 it looks:

SQL> SELECT ol_name as col1, hint_text as col2 FROM OUTLN.OL$HINTS o
2 WHERE o.HINT# = 1;

COL1 COL2
---------------------------------------- ----------------------------------------
SYS_OUTLINE_11063012550580837 FULL(@"SEL$1" "T"@"SEL$1")
JUST_FOR_FULL_SCAN FULL(@"SEL$1" "T"@"SEL$1")

it means that SYS_OUTLINE_11063012550580837 outline should be used for the first statement (i mean select t.col2 from tab2 t where t.col5=3;).

so lets try how it works.

SQL> alter system set use_stored_outlines=MYCAT;
SQL> select t.col2 from tab2 t where t.col5=3;

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 4 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SO IT IS NOT WORKING. i tryed ewerything and i have no idea what i should do to use this outline.

thanks in advance.