Home » RDBMS Server » Performance Tuning » problem with outlines
problem with outlines [message #513934] Thu, 30 June 2011 06:10 Go to next message
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.
Re: problem with outlines [message #513941 is a reply to message #513934] Thu, 30 June 2011 06:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It is very difficult to read your post, because you haven't used code tags. But I think your stored outlione is being used perfectly. You have created an outline for this statement
select t.col2 from tab2 t where t.col5=3;

and the outline used an index. You run the statement, and you use the index. QED.

By the way, I see no point in creating an outline for a statement with a hint: the outline itself is a set of hints, so why bother?
Re: problem with outlines [message #513950 is a reply to message #513941] Thu, 30 June 2011 07:02 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
thanks John for your answer and sorry for putting my post twice on the forum - i didn't intend to spam you.

you are right about first execution of my query - just after i created outline for it. this statement uses index to get result from table. and it is right.

my intention was force oracle to not use this index - just for trainig purposes - and make full scan for the table instead of the fact that col5 was indexed and query have condition for this column.

so i created second outline for the statement with hint in it:

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

and update hints for the first one statement with hints generated for this second statement.

original hint was: INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("TAB2
"."COL5"))

but i updated this original hint with (using hint from secoud one statement) : JUST_FOR_FULL_SCAN FULL(@"SEL$1" "T"@"SEL$1")

so after such modification i presume that oracle will do full scan over this table using statement:

select t.col2 from tab2 t where t.col5=3

... but it doesn't. Smile

i have spend over 8 hours digging with this topic and it get me angry that i can't do anything to understand how it works and finally do it working perfectly. Smile

it is very interesting and usefull topic - store execution plan for queries, and i thing that i will someday need use it.

thanks again for answer John.
Re: problem with outlines [message #513954 is a reply to message #513950] Thu, 30 June 2011 07:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I am not going to answer, unless you use code tags.
Re: problem with outlines [message #513968 is a reply to message #513954] Thu, 30 June 2011 07:58 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
sorry. i will try.

so my plan was to force oracle to not use index for the column. i know that i can such options like add hints to query, or just delete index and some other solutions.

but i decided to do this using outlines. so i created outline for one query

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


and create outline for second query

select /*+full(t) */ t.col2 from tab2 t where t.col5=3;


i used hint for second query so outline created for it was completely different then for the first query.

outline hint for first query suggest using index

INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("TAB2"."COL5"))


so i updated this hint with hint from the second query

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


after this modification i suspected that for the first query oracle won't use index (because hint tells that "do the full scan") - like for the second query.

but i was supprised that oracle uses this index after i changed execution plan for the first query.

the only situation where it works for me was when i modified parameter cursor_sharing=exact. after this modification shared pool contains queries without binded variables. after i created outline for such unbinded quer my outline works perfectly.

but creating outline for parsed queries without binded variables is without sense, because for such queries:

select t.col2 from tab2 t where t.col5=1;
select t.col2 from tab2 t where t.col5=2;
select t.col2 from tab2 t where t.col5=3;
select t.col2 from tab2 t where t.col5=4;


i need four stored outlines - for each query.

so the only sense using outlines is to store outlines for binded statement like:

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


which i tryed to do, but without surprising effect.
Re: problem with outlines [message #513969 is a reply to message #513968] Thu, 30 June 2011 08:00 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
so summarizing:

i need to force oracle not to use index for col5 using stored outlines. how to do this? i think that i do this right way but i don't know why it works for me.

help please.
Re: problem with outlines [message #514307 is a reply to message #513969] Sun, 03 July 2011 15:51 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello,

you have to use the same setting of cursor_sharing at creation and at execution time for stored outlines. As I see you used the different parameter setting at least at creation time. What was the value of cursor_sharing at your execution? The other possible reason is your update.

I'm absolutely sure, if you do the following, it will work fine:

1. create outline ORIG for category mycat on select t.col2 from tab2 t where t.col5=:"SYS_B_0";
2. create outline HINTED for category mycat on select /*+ full(t) */ t.col2 from tab2 t where t.col5=:"SYS_B_0";
3. update outln.ol$hints set ol_name = decode(ol_name, 'HINTED', 'ORIG', 'ORIG','HINTED')
where
ol_name in ('HINTED', 'ORIG') and
2 = (select count(*) from outln.ol$ where ol_name in ('HINTED', 'ORIG'));
4. alter session set cursor_sharing=force;
select t.col2 from tab2 t where t.col5=3;
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '',''));

You can verify the applying of stored outlines using dbms_xplan.display_cursor.

Regards
Leonid
Re: problem with outlines [message #514346 is a reply to message #514307] Mon, 04 July 2011 03:44 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
thanks for you interesting with my case...

turning back to my problem....

hm.. this is a little confusing and i don't know what i should thing about that because now i'm not confident if this plan is modified by outlines or not.

i did completly the same like you instructed me.

look at my examples.

after your update i have such execution plans for my queries:

SQL> SELECT * FROM User_Outline_Hints;

NAME                                 NODE      STAGE   JOIN_POS HINT
------------------------------ ---------- ---------- ---------- ----------------------------------------------------
ORIG                                    1          1          1 FULL(@"SEL$1" "T"@"SEL$1")
ORIG                                    1          1          0 OUTLINE_LEAF(@"SEL$1")
ORIG                                    1          1          0 ALL_ROWS
ORIG                                    1          1          0 OPT_PARAM('optimizer_index_caching' 80)
ORIG                                    1          1          0 OPT_PARAM('optimizer_index_cost_adj' 45)
ORIG                                    1          1          0 DB_VERSION('11.2.0.1')
ORIG                                    1          1          0 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
ORIG                                    1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS
HINTED                                  1          1          1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("TAB2"."COL5"))
HINTED                                  1          1          0 OUTLINE_LEAF(@"SEL$1")
HINTED                                  1          1          0 ALL_ROWS
HINTED                                  1          1          0 OPT_PARAM('optimizer_index_caching' 80)
HINTED                                  1          1          0 OPT_PARAM('optimizer_index_cost_adj' 45)
HINTED                                  1          1          0 DB_VERSION('11.2.0.1')
HINTED                                  1          1          0 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
HINTED                                  1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS


so.. it looks like ORIG query should be execuded using FULL table scan.

look what is happening if i log into other session:


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

      COL2
----------
         1

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '',''));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  ca6ur9arzvafj, child number 0
-------------------------------------
select t.col2 from tab2 t where t.col5=:"SYS_B_0"

Plan hash value: 2156729920

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB2 |     1 |     4 |     3   (0)| 00:00:01 |




so... i was happy for a whiile when i tryied to look another method to check my execution plan.

the same session the same query - look what is happening

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2666505758

-------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------



????

and after turning on autotrace in plsql

SQL> set autotrace on
SQL> select t.col2 from tab2 t where t.col5=1;

      COL2
----------
         1


Plan wykonywania
----------------------------------------------------------
Plan hash value: 2666505758

-------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------


i am completly distracted :/ i don't know what is going on.

why there is different results from different methods which check the execution plan for query.
Re: problem with outlines [message #514350 is a reply to message #514346] Mon, 04 July 2011 04:07 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that you don't have several plans for the same statement? You might need to specify which cursor you want to see, give dbms_xplan the sql_id and child cursor number:

dbms_xplan.display_cursor('ca6ur9arzvafj',0)
dbms_xplan.display_cursor('ca6ur9arzvafj',1)

and so on?
Re: problem with outlines [message #514357 is a reply to message #514350] Mon, 04 July 2011 04:27 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
there is only one statement in shared pool with this sql_id (rownum 10)

SQL> SELECT rownum, s.sql_text, SQL_ID FROM V$SQL  s
  2  WHERE lower(sql_text) LIKE '%tab2%';

    ROWNUM SQL_TEXT                                                                         SQL_ID
---------- -------------------------------------------------------------------------------- -------------
         1 EXPLAIN PLAN SET STATEMENT_ID='PLUS5831825' FOR SELECT rownum, s.sql_text, SQL_I dt9tj5g9a022p
           D FROM V$SQL  s WHERE lower(sql_text) LIKE '%tab2%'

         2 EXPLAIN PLAN SET STATEMENT_ID='PLUS5831825' FOR SELECT rownum, s.sql_text, SQL_I dt9tj5g9a022p
           D FROM V$SQL  s WHERE lower(sql_text) LIKE '%tab2%'

         3 EXPLAIN PLAN SET STATEMENT_ID='PLUS5831825' FOR SELECT rownum, s.sql_text, SQL_I dt9tj5g9a022p
           D FROM V$SQL  s WHERE lower(sql_text) LIKE '%tab2%'

         4 EXPLAIN PLAN SET STATEMENT_ID='PLUS5731801' FOR select t.col2 from tab2 t where  dtsxvj5q595gf
           t.col5=1

    ROWNUM SQL_TEXT                                                                         SQL_ID
---------- -------------------------------------------------------------------------------- -------------

         5 EXPLAIN PLAN SET STATEMENT_ID='PLUS5731801' FOR select t.col2 from tab2 t where  dtsxvj5q595gf
           t.col5=1

         6 EXPLAIN PLAN SET STATEMENT_ID='PLUS5831825' FOR select t.col2 from tab2 t where  arbc0j3ps968r
           t.col5=1

         7 EXPLAIN PLAN SET STATEMENT_ID='PLUS5831825' FOR select t.col2 from tab2 t where  arbc0j3ps968r
           t.col5=1

         8 EXPLAIN PLAN SET STATEMENT_ID='PLUS5831825' FOR select t.col2 from tab2 t where  arbc0j3ps968r

    ROWNUM SQL_TEXT                                                                         SQL_ID
---------- -------------------------------------------------------------------------------- -------------
           t.col5=1

         9 EXPLAIN PLAN SET STATEMENT_ID='PLUS5731801' FOR select t.col2 from tab2 t where  58v8utpt4yn7t
           t.col5=3

        10 select t.col2 from tab2 t where t.col5=:"SYS_B_0"                                ca6ur9arzvafj
        11 explain plan for select t.col2 from tab2 t where t.col5=1                        3wcj8avfzmbv5
        12 EXPLAIN PLAN SET STATEMENT_ID='PLUS5731801' FOR select t.col2 from tab2 t where  1fwgun5kbmqmj
           t.col5=2


and after i did what you suggested the result is:

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( 'ca6ur9arzvafj','0'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  ca6ur9arzvafj, child number 0
-------------------------------------
select t.col2 from tab2 t where t.col5=:"SYS_B_0"

Plan hash value: 2156729920

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB2 |     1 |     4 |     3   (0)| 00:00:01 |


... and no other plans for this statement:

SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( 'ca6ur9arzvafj','1'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID: ca6ur9arzvafj, child number: 1 cannot be found


SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( 'ca6ur9arzvafj','2'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID: ca6ur9arzvafj, child number: 2 cannot be found


SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( 'ca6ur9arzvafj','3'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID: ca6ur9arzvafj, child number: 3 cannot be found



but the strange is that turned on autotrace displays completely different plan than dbms_xplan package, and i don't know which plan is finally used.

i am usually using Pl/SQL Developer tool and if i hit F5 it displays actual statement plan for query, and plan from this tool is equal to this from the method which send me Leonid.

so there are new question:

- if Leonid method is the right method to find execution plan (because it displays full scan over tab2) than why other method differ from the other which i used.

sorry for spamming with my issues.

[Updated on: Mon, 04 July 2011 04:31]

Report message to a moderator

Re: problem with outlines [message #514359 is a reply to message #514357] Mon, 04 July 2011 04:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That proves that you have only one child cursor (child 0) for the version of the statment that uses a bind variable, what about the statement that uses a literal? Isn't that the one that you are interested in? It doesn't look as though you have executed that at all.
And don;t forget that with 11g you have the Adaptive Cursor Sharing facility, which allows the optimizer to maintain multiple plans and choose between them depending on the value of the bind variable. That could be complicating things a bit. Actually, with 11g, stored outlibes are deprecated: you are meant to use SQL Plan Baselining instead.
Re: problem with outlines [message #514369 is a reply to message #514359] Mon, 04 July 2011 05:40 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
i have just tryed the same table, the same data, the same structure and outlines for oracle9i.
i don't know why it is not working for me.
statement is still using index regardless stored outline generated for it.

SQL> select * from dba_outline_hints;

NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- ------------------
HINTED1                        SDP2                                    1          3          0 NO_EXPAND
HINTED1                        SDP2                                    1          3          0 ORDERED
HINTED1                        SDP2                                    1          3          0 NO_FACT(T)
HINTED1                        SDP2                                    1          3          1 INDEX(T T2_I1)
HINTED1                        SDP2                                    1          2          0 NOREWRITE
HINTED1                        SDP2                                    1          1          0 NOREWRITE
HINTED1                        SDP2                                    1          1          0 RULE
ORIG1                          SDP2                                    1          3          0 NO_EXPAND
ORIG1                          SDP2                                    1          3          0 ORDERED
ORIG1                          SDP2                                    1          3          0 NO_FACT(T)
ORIG1                          SDP2                                    1          3          1 FULL(T)

NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- ------------------
ORIG1                          SDP2                                    1          2          0 NOREWRITE
ORIG1                          SDP2                                    1          1          0 NOREWRITE

13 wierszy zosta│o wybranych.

SQL> explain plan for select t.col2 from tab2 t where t.col5=2;

Wyjaťniono.

SQL> select plan_table_output from table ( dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB2        |       |       |       |
|   2 |   INDEX UNIQUE SCAN         | T2_I1       |       |       |       |
---------------------------------------------------------------------------

Note: rule based optimization, PLAN_TABLE' is old version

10 wierszy zosta│o wybranych.

SQL> set autotrace on;
SQL> select t.col2 from tab2 t where t.col5=2;

      COL2
----------
         2


Plan wykonywania
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
   2    1     INDEX (UNIQUE SCAN) OF 'T2_I1' (UNIQUE)




Statystyki
----------------------------------------------------------
         69  recursive calls
          3  db block gets
         18  consistent gets
          0  physical reads
        552  redo size
        375  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter system flush shared_pool;

System zosta│ zmieniony.

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

      COL2
----------
         2


Plan wykonywania
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
   2    1     INDEX (UNIQUE SCAN) OF 'T2_I1' (UNIQUE)




Statystyki
----------------------------------------------------------
       1418  recursive calls
          0  db block gets
        204  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
          1  rows processed

Re: problem with outlines [message #514370 is a reply to message #514369] Mon, 04 July 2011 05:44 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
alter system set use_stored_outlines=mycat; <-- of course this parameter was set
Re: problem with outlines [message #514372 is a reply to message #514370] Mon, 04 July 2011 06:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes, but is your outline ORIG1 for the statement with the bind variable, or the statement with the literal? I've got confused with there being so much in this thread. Perhaps you need to destroy everything, and make a new clean example.
Re: problem with outlines [message #514374 is a reply to message #514372] Mon, 04 July 2011 06:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hi - here's a new example, it works perfectly for me:
conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create table t1 as select * from all_objects;
create outline noindex for category joncat on select count(*) from t1;
create index i1 on t1 (object_id);
set autotrace on explain
select count(*) from t1;
alter session set use_stored_outlines=joncat;
select count(*) from t1;

Here's the output of the last few lines:
orcl> select count(*) from t1;

  COUNT(*)
----------
     71962


Execution Plan
----------------------------------------------------------
Plan hash value: 129980005

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    48   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I1   | 64795 |    48   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

orcl> alter session set use_stored_outlines=joncat;

Session altered.

orcl> select count(*) from t1;

  COUNT(*)
----------
     71962


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   287   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 85846 |   287   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - outline "NOINDEX" used for this statement

orcl>
orcl>

This is 11.2.0.2, by the way.
Re: problem with outlines [message #514377 is a reply to message #514372] Mon, 04 July 2011 06:38 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
ORIG1 (example from oracle 9i) was for the statement with bind variable

SQL> select name, sql_text, category, used from dba_outlines;

NAME                           SQL_TEXT                                                                 CATEGORY                       USED
------------------------------ -------------------------------------------------------------------------------- -----------------------------
ORIG1                          select t.col2 from tab2 t where t.col5=:"SYS_B_0"                        MYCAT                          USED
HINTED1                        select /*+ full(t) */ t.col2 from tab2 t where t.col5=:"SYS_B_0"         MYCAT                          UNUSED


i am probably doing something wrong witht this outages and don't know what is this - lack of knowledge.
Re: problem with outlines [message #514382 is a reply to message #514377] Mon, 04 July 2011 06:53 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You aren't thinking, man! You say this,
Quote:
ORIG1 (example from oracle 9i) was for the statement with bind variable
so of course the outline isn't used when you execute a statement that uses a literal. The statement must be identical, or the outline won't be used.
Re: problem with outlines [message #514393 is a reply to message #514382] Mon, 04 July 2011 08:20 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello Piotr,

John had a good suggestion with a completely new test. Could you please drop all stored outlines, execute the statements, which I sent already to you and upload the COMPLETE protocol.

Regards
Leonid
Re: problem with outlines [message #514395 is a reply to message #514382] Mon, 04 July 2011 08:43 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
i know very well that i am using statement with literal (during executions) and stored outline is prepared for statement with bind variable but...

... but as i know, oracle parses statement, and store it in shared pool with bind values (it of course depend on my cursor sharing parameter), and after i execute another statement with literal (for example 1,2,3) oracle should do the same thing - it should:

1. parse statement to the bind variable form
2. find proper sql statement in shared pool (in this case "select t.col2 from tab2 t where t.col5=:"SYS_B_0")
3. determine if there is stored outline for this statement, and if yes - it should use it.

i thought that it work this way - didn't know that i am wrong.

so - summarizing.

i thought that if i have outline plan for something like that...

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


after each execution of query with diferent condition ...

select t.col2 from tab2 t where t.col5=1;

or

select t.col2 from tab2 t where t.col5=2

or 

select t.col2 from tab2 t where t.col5=4


oracle should parse each from this statement to the form of...

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


and use stored outline which is saved for this variable binded statement.

am i wrong?
Re: problem with outlines [message #514396 is a reply to message #514395] Mon, 04 July 2011 08:45 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
ok. i will try example from the Johns post and write if i succeded.
Re: problem with outlines [message #514399 is a reply to message #514396] Mon, 04 July 2011 09:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
1. parse statement to the bind variable form
2. find proper sql statement in shared pool (in this case "select t.col2 from tab2 t where t.col5=:"SYS_B_0")
3. determine if there is stored outline for this statement, and if yes - it should use it.

Hi - I think your description above is wrong: if I understand it correctly, the whole idea of enabling a stored outline category is that the optimizer does NOT parse first, but will go straight to your step 3. Only if there is no ouline found, will it parse the statement.
This has been an interesting exercise, hasn't it? But you really should look at the 11g SQL plan baselining facility instead. I believe it to be a far superior method of getting some plan stability.
Re: problem with outlines [message #514400 is a reply to message #514396] Mon, 04 July 2011 09:39 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Oracle parses in each case with and without stored outlines. If there are stored outlines, it applies them firstly and then parses.
Piotr, sorry, I don't understand you. Originally you wanted to make a test with hidden hints via stored outlines for some workshop. Is it correct? The testcase of John doesn't involve this method. What do you want to test?
Re: problem with outlines [message #514406 is a reply to message #514400] Mon, 04 July 2011 10:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes, LNossov, thinking it through of course you are correct: the statement must always be parsed. The outline will be used to re-write the statement before parsing, which is why a requirement for using store outlines is query_rewrite_enabled=true. Which, Piotr, is important with your 9i database: if I rememebr correctly, the default was false back then.

Re: problem with outlines [message #514408 is a reply to message #514406] Mon, 04 July 2011 10:11 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
sorry, query_rewrite_enabled=true isn't a requirement for using of stored outlines.
Re: problem with outlines [message #514410 is a reply to message #514408] Mon, 04 July 2011 10:22 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Sure, if you say so. The docs say thisQuote:
20.1.3 Enabling Plan Stability

Settings for several parameters, especially those ending with the suffix _ENABLED, must be consistent across execution environments for outlines to function properly. These parameters are:

QUERY_REWRITE_ENABLED

STAR_TRANSFORMATION_ENABLED

OPTIMIZER_FEATURES_ENABLE
which I may have mis-interpreted. I'm not going to bother to test it. Actually, I think this topic has probably reached the end of its useful life (unless OP adds something).
Bye for now.

Re: problem with outlines [message #514412 is a reply to message #514410] Mon, 04 July 2011 10:31 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
With other words they should not be changed. It seems to be a little bit different statement Smile .
Re: problem with outlines [message #514413 is a reply to message #514410] Mon, 04 July 2011 10:32 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It says they have to be consistent, not all set to true. So it'll work if it's false on both DBs, but not if it's false on one and true on another. A change in the default for those parameters may be part of the problem.
Previous Topic: how to clear cache for testing tuned code
Next Topic: Query doing FTS on a big table
Goto Forum:
  


Current Time: Thu Mar 28 03:14:41 CDT 2024