Home » RDBMS Server » Performance Tuning » problem with outlines #2
problem with outlines #2 [message #514544] Tue, 05 July 2011 06:04 Go to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
thanks everyone for answering on my other posts and willing to help me with this topic.

i decided not to give up until i understand HOW IT WORKS. i admit that maybe my thinking about this topic is wrong so please don't treat me harsh. Wink

John. i know that there is new technology than stored outlines in the newest versions of oracle but i am very stubborn to do this this way because we are using also older versions of oracle.

John gave me an example in the previous topic and this example works fine to me too, but in the statement from this example didn't use conditions so the outlines could be adjusted without problems.

Johns example on my database (lets try to force oracle to do the full scan over table):

for every example i am using the same update statement sent me by Leonid (thanks Leonid) which updates hints for the queries.

SQL> select t.col2 from tab2 t;

      COL2
----------
         1
         2
         3
         5


Plan wykonywania
----------------------------------------------------------
Plan hash value: 3153152498

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     4 |     8 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | T2_PK |     4 |     8 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------


SQL> create outline ORIG for category mycat on select t.col2 from tab2 t;

Outline created.

SQL> create outline HINTED for category mycat on select /*+ FULL(t) */t.col2 from tab2 t;

Outline created.

SQL> SELECT ol_name, hint_text FROM outln.ol$hints;

OL_NAME                        HINT_TEXT
------------------------------ --------------------------------------------
HINTED                         OUTLINE_LEAF(@"SEL$1")
HINTED                         ALL_ROWS
HINTED                         OPT_PARAM('optimizer_index_caching' 80)
HINTED                         OPT_PARAM('optimizer_index_cost_adj' 45)
HINTED                         DB_VERSION('11.2.0.1')
HINTED                         OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
HINTED                         IGNORE_OPTIM_EMBEDDED_HINTS
ORIG                           INDEX(@"SEL$1" "T"@"SEL$1" ("TAB2"."COL2"))
ORIG                           OUTLINE_LEAF(@"SEL$1")
ORIG                           ALL_ROWS
ORIG                           OPT_PARAM('optimizer_index_caching' 80)

OL_NAME                        HINT_TEXT
------------------------------ --------------------------------------------
ORIG                           OPT_PARAM('optimizer_index_cost_adj' 45)
ORIG                           DB_VERSION('11.2.0.1')
ORIG                           OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
ORIG                           IGNORE_OPTIM_EMBEDDED_HINTS
HINTED                         FULL(@"SEL$1" "T"@"SEL$1")

16 rows selected.


SQL> update outln.ol$hints set ol_name = decode(ol_name, 'HINTED', 'ORIG', 'ORIG','HINTED')
  2  where
  3  ol_name in ('HINTED', 'ORIG') and
  4  2 = (select count(*) from outln.ol$ where ol_name in ('HINTED', 'ORIG'));

16 rows updated.


SQL> SELECT ol_name, hint_text FROM outln.ol$hints;

OL_NAME                        HINT_TEXT
------------------------------ -------------------------------------------------------------
ORIG                           OUTLINE_LEAF(@"SEL$1")
ORIG                           ALL_ROWS
ORIG                           OPT_PARAM('optimizer_index_caching' 80)
ORIG                           OPT_PARAM('optimizer_index_cost_adj' 45)
ORIG                           DB_VERSION('11.2.0.1')
ORIG                           OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
ORIG                           IGNORE_OPTIM_EMBEDDED_HINTS
HINTED                         INDEX(@"SEL$1" "T"@"SEL$1" ("TAB2"."COL2"))
HINTED                         OUTLINE_LEAF(@"SEL$1")
HINTED                         ALL_ROWS
HINTED                         OPT_PARAM('optimizer_index_caching' 80)

OL_NAME                        HINT_TEXT
------------------------------ -------------------------------------------------------------
HINTED                         OPT_PARAM('optimizer_index_cost_adj' 45)
HINTED                         DB_VERSION('11.2.0.1')
HINTED                         OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
HINTED                         IGNORE_OPTIM_EMBEDDED_HINTS
ORIG                           FULL(@"SEL$1" "T"@"SEL$1")

SQL> select t.col2 from tab2 t;

      COL2
----------
         1
         2
         3
         5


Execution Plan
----------------------------------------------------------
Plan hash value: 2156729920

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



do you see the last operation? oracle did full table scan - so it works for this example.

but (for the hell) i am not able do the same for different example (like that from below) or the binded variable statement :/

sometimes it works, sometime not...

another static statement example (i did this after puring ol$ and ol$hints - so this tables was emptied so don't bother):

again - lets try to force oracle to do the full scan over table

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

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


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

SQL> create outline ORIG for category mycat on select t.col2 from tab2 t where t.col5=1;

Outline created.

SQL> create outline HINTED for category mycat on select /*+ FULL(t) */t.col2 from tab2 t where t.col5=1;

Outline created.

SQL> update outln.ol$hints set ol_name = decode(ol_name, 'HINTED', 'ORIG', 'ORIG','HINTED')
  2  where
  3  ol_name in ('HINTED', 'ORIG') and
  4  2 = (select count(*) from outln.ol$ where ol_name in ('HINTED', 'ORIG'));

16 rows updated.


SQL> SELECT ol_name, hint_text FROM outln.ol$hints where hint_text like ('%INDEX%') or hint_text like ('%FULL%');

OL_NAME                        HINT_TEXT
------------------------------ --------------------------------------------------------------------------------
HINTED                         INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("TAB2"."COL5"))
ORIG                           FULL(@"SEL$1" "T"@"SEL$1")

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

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


Execution Plan
----------------------------------------------------------
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 like you see the oracle still uses T2_I1. :/

need a favour to everyone who are willing to do another example on your machines - could you do this example on in your sessions?

lets purge hint tables:

and again: lets try to force oracle to do the full scan over table

SQL> delete from  outln.ol$;

2 wierszy zosta│o usuniŕtych.

SQL> delete from  outln.ol$hints;

16 wierszy zosta│o usuniŕtych.

SQL> commit;

Zatwierdzanie zosta│o uko˝czone.


and go with generating outline for bind variable:

SQL> create outline ORIG for category mycat on select t.col2 from tab2 t where t.col5=:"SYS_B_0";

Outline created.

SQL> create outline HINTED for category mycat on select /*+ full(t) */ t.col2 from tab2 t where t.col5=:"SYS_B_0";

Outline created.

SQL> update outln.ol$hints set ol_name = decode(ol_name, 'HINTED', 'ORIG', 'ORIG','HINTED')
  2  where
  3  ol_name in ('HINTED', 'ORIG') and
  4  2 = (select count(*) from outln.ol$ where ol_name in ('HINTED', 'ORIG'));

16 rows updated.

SQL> SELECT ol_name, hint_text FROM outln.ol$hints where hint_text like ('%INDEX%') or hint_text like ('%FULL%');

OL_NAME                        HINT_TEXT
------------------------------ --------------------------------------------------------------------------------
HINTED                         INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("TAB2"."COL5"))
ORIG                           FULL(@"SEL$1" "T"@"SEL$1")

SQL> select t.col2 from tab2 t where t.col5=&_variable;
Enter value for _variable: 2
old   1: select t.col2 from tab2 t where t.col5=&_variable
new   1: select t.col2 from tab2 t where t.col5=2

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


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



Re: problem with outlines #2 [message #514550 is a reply to message #514544] Tue, 05 July 2011 06:26 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello Piotr

first of all you should use "drop outline" command and not deletes on internal tables.

I asked you already to upload COMPLETE protocols. I cannot find for example "alter session set use_stored_outlines = mycat" in your protocols. I suppose, you didn't forget it but I'm not sure.

It would be fine, if you would use dbms_xplan.display_cursor for execution plan to verify immediately, if your outlines will be used or not.

Regards
Leonid
Re: problem with outlines #2 [message #514552 is a reply to message #514544] Tue, 05 July 2011 06:30 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
From your 2nd example:
create outline ORIG for category mycat on select t.col2 from tab2 t where t.col5=1;
......
SQL> select t.col2 from tab2 t where col5=1;

Queries aren't the same. you missed the table alias from the where clause in the select. I'm no expert in outlines but I suspect that makes a difference.
Re: problem with outlines #2 [message #514554 is a reply to message #514550] Tue, 05 July 2011 06:40 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
LNossov wrote on Tue, 05 July 2011 06:26
Hello Piotr
I asked you already to upload COMPLETE protocols. I cannot find for example "alter session set use_stored_outlines = mycat" in your protocols. I suppose, you didn't forget it but I'm not sure.


i could add this to the protocol (sorry) but believe me - this "alter system ..." was set for the system level.

cookiemonster: yes. queries are bit different but i switch all hints for ORYG query with HINTED query so it should disturb with anything - but i can be wrong.

please, if someone could do my last example and attach log from the session to this forum then i will be very gratefull.

i would like to see this as a prove and believe that it work.

i am adding scripts for table creation and input.

-- Create table
create table TAB2
(
  COL2 NUMBER not null,
  COL3 NUMBER,
  COL4 NUMBER,
  COL5 NUMBER,
  COL6 NUMBER
);

alter table TAB2
  add constraint T2_PK primary key (COL2)
  using index ;

create unique index T2_I1 on TAB2 (COL5);

insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (1, 1, 5, 1, 223);
insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (2, 3, 5, 2, 223);
insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (3, 4, 6, 3, 223);
insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (5, 1, 5, 5, 11);



thanks in advance.

[Updated on: Tue, 05 July 2011 06:41]

Report message to a moderator

Re: problem with outlines #2 [message #514555 is a reply to message #514554] Tue, 05 July 2011 06:48 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You are right regarding different sql's. I overlooked it.
Re: problem with outlines #2 [message #514557 is a reply to message #514554] Tue, 05 July 2011 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
piotrtal wrote on Tue, 05 July 2011 12:40
cookiemonster: yes. queries are bit different but i switch all hints for ORYG query with HINTED query so it should disturb with anything - but i can be wrong.

Query isn't the same as the hinted one either. Query needs to be absolutely 100% identical to the one you created the plan for.
Re: problem with outlines #2 [message #514560 is a reply to message #514557] Tue, 05 July 2011 07:08 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Sorry, it is not so. Sqltexts can be different (to distinguish in blanks, upper or low cases or in carriage returns, perhaps in something else). The had been the same in 8i.
Re: problem with outlines #2 [message #514564 is a reply to message #514560] Tue, 05 July 2011 07:22 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok. But table alisas count right?
Re: problem with outlines #2 [message #514565 is a reply to message #514564] Tue, 05 July 2011 07:26 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Sure Smile.
Re: problem with outlines #2 [message #514569 is a reply to message #514565] Tue, 05 July 2011 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the main point stands. In the 2nd example neither of the outlines can be used as the usage of table alisas is different.

As for the third example: A sqlplus substitution variable (&_variable) is not the same thing as a bind variable.
Substitution variables get replaced by literals by sqlplus before the query is sent to the DB.
So unless you have cursor_sharing set to similar or force there's no chance of that one working either.
Re: problem with outlines #2 [message #514583 is a reply to message #514569] Tue, 05 July 2011 08:17 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
ok. forget about second example - let's asume that there was mistake.

lets focus on the third example.

could one of you do me third example, and add session logs on the forum?
the idea is simple:

1. do the outline (ORYG) for tab2 (with condition selecting one row from the table)
2. do the outline (HINTED) for tab2
3. replace hints
4. show me the execution plan for first query. how it looks like after this changes.

update statement used for switch hints bewtween this two queries is attached above in one of my posts.
Re: problem with outlines #2 [message #514586 is a reply to message #514569] Tue, 05 July 2011 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
But the third example is flawed as well:

cookiemonster wrote on Tue, 05 July 2011 13:44

As for the third example: A sqlplus substitution variable (&_variable) is not the same thing as a bind variable.
Substitution variables get replaced by literals by sqlplus before the query is sent to the DB.
So unless you have cursor_sharing set to similar or force there's no chance of that one working either.

Re: problem with outlines #2 [message #514590 is a reply to message #514586] Tue, 05 July 2011 08:31 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
cookiemonster wrote on Tue, 05 July 2011 08:19
But the third example is flawed as well:

cookiemonster wrote on Tue, 05 July 2011 13:44

As for the third example: A sqlplus substitution variable (&_variable) is not the same thing as a bind variable.
Substitution variables get replaced by literals by sqlplus before the query is sent to the DB.
So unless you have cursor_sharing set to similar or force there's no chance of that one working either.



ok. besides last line from my example.

.. so could you instruct me how can i execute statement with bind variable under plsql to check if this example works? - don't know how.
Re: problem with outlines #2 [message #514592 is a reply to message #514583] Tue, 05 July 2011 08:32 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello Piotr,

did you use cursor_sharing=exact for your third example? If not, you should have used sql with system generated bind variables in create outline statement.

If you want me to test your testcase, upload it please as a plain text.

Regards
Leonid
Re: problem with outlines #2 [message #514593 is a reply to message #514592] Tue, 05 July 2011 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Using binds.
Method 1, sqlplus specific:
SQL> variable x number;
SQL> exec :x := 1;

PL/SQL procedure successfully completed.

SQL> select 1 from dual where 1 = :x;

         1
----------
         1



Method 2, use PL/SQL:
SQL> declare
  2  x number := 1;
  3  l number;
  4  begin
  5  select 1 into l from dual where 1 = x;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 


@LNossov - we generally like test cases to be posted in line in [code] tags as the OP has done, not attached in files.
Re: problem with outlines #2 [message #514596 is a reply to message #514592] Tue, 05 July 2011 08:46 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
my cursor sharing is set as FORCE;

just execute below statements, and give me logs from this.

create table TAB2
(
  COL2 NUMBER not null,
  COL3 NUMBER,
  COL4 NUMBER,
  COL5 NUMBER,
  COL6 NUMBER
);

alter table TAB2
  add constraint T2_PK primary key (COL2)
  using index ;

create unique index T2_I1 on TAB2 (COL5);

insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (1, 1, 5, 1, 223);
insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (2, 3, 5, 2, 223);
insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (3, 4, 6, 3, 223);
insert into TAB2 (COL2, COL3, COL4, COL5, COL6)
values (5, 1, 5, 5, 11);

set autotrace on explain;
var var1 number;
exec :var1 := 1;
select t.col2 from tab2 t where t.col5=:var1;


create outline ORIG for category mycat on select t.col2 from tab2 t where t.col5=:"SYS_B_0";
create outline HINTED for category mycat on select /*+ full(t) */ t.col2 from tab2 t where t.col5=:"SYS_B_0";

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'));

commit;

SELECT ol_name, hint_text FROM outln.ol$hints where hint_text like ('%INDEX%') or hint_text like ('%FULL%');

set autotrace on explain;
var var1 number;
select t.col2 from tab2 t where t.col5=:var1;



cookiemonster: am i doing right now with bindind statements?

[Updated on: Tue, 05 July 2011 08:49]

Report message to a moderator

Re: problem with outlines #2 [message #514597 is a reply to message #514596] Tue, 05 July 2011 08:52 Go to previous messageGo to next message
John Watson
Messages: 8937
Registered: January 2010
Location: Global Village
Senior Member
Piotr, you have to be precise in your examples. Your outline ORIG is for this statement,
select t.col2 from tab2 t where t.col5=:"SYS_B_0";
but then you test with this statement:
select t.col2 from tab2 t where t.col5=:var1;

What are you trying to do?
Re: problem with outlines #2 [message #514598 is a reply to message #514596] Tue, 05 July 2011 08:55 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
i am just reading "Tuning and Performance" book and look what i found two minutes ago:

--------------
Why Isn't My Hint Working?
Often we find that a hint won't behave like we want it to. There are times when the optimizer
overrides the hint, but usually people have a problem related to one of the following:
■ The hint syntax is incorrect.
■ The table(s) is not analyzed.
■ There is a conflict with another hint.
■ The hint requires a system parameter to be set for it to work.
■ The table name was aliased in the query, but you used the table name, not the alias,
in the hint.
■ The hint requires a different version of Oracle than you have.
■ You don't understand the correct application for the hint.
■ You haven't slept lately; it is for many of the reasons cited here.
■ There is a software bug.

--------------

i think that the third reason from the end is the most probably accurate if we are talking about my issue. Smile
Re: problem with outlines #2 [message #514599 is a reply to message #514597] Tue, 05 July 2011 08:59 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
John Watson wrote on Tue, 05 July 2011 08:52
Piotr, you have to be precise in your examples. Your outline ORIG is for this statement,
select t.col2 from tab2 t where t.col5=:"SYS_B_0";
but then you test with this statement:
select t.col2 from tab2 t where t.col5=:var1;

What are you trying to do?


should i declare SYS_B_0 and use it instead? something like that:

var SYS_B_0 number;
exec :SYS_B_0 := 1;
select t.col2 from tab2 t where t.col5=:SYS_B_0;


sorry but i am not experienced with oracle. it is still my beginings.
Re: problem with outlines #2 [message #514615 is a reply to message #514599] Tue, 05 July 2011 10:17 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You can do that. Or
select t.col2 from tab2 t where t.col5=1

if cursor_similar<>EXACT.
Previous Topic: Solution req :Performance issue with partitioned table
Next Topic: Query taking more time
Goto Forum:
  


Current Time: Sun Jun 16 23:22:36 CDT 2024