Home » RDBMS Server » Performance Tuning » problem with outlines #2
| problem with outlines #2 [message #514544] |
Tue, 05 July 2011 06:04  |
 |
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. 
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 #514552 is a reply to message #514544] |
Tue, 05 July 2011 06:30  |
cookiemonster
Messages: 13958
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 |
|
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
|
Senior Member |
|
|
LNossov wrote on Tue, 05 July 2011 06:26Hello 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 #514557 is a reply to message #514554] |
Tue, 05 July 2011 07:01 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
piotrtal wrote on Tue, 05 July 2011 12:40cookiemonster: 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 #514569 is a reply to message #514565] |
Tue, 05 July 2011 07:44 |
cookiemonster
Messages: 13958
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 #514586 is a reply to message #514569] |
Tue, 05 July 2011 08:19 |
cookiemonster
Messages: 13958
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 #514593 is a reply to message #514592] |
Tue, 05 July 2011 08:37 |
cookiemonster
Messages: 13958
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 |
|
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 |
John Watson
Messages: 8960
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?
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 21:34:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
.