| Union (all) and outline hints [message #633524] |
Fri, 20 February 2015 03:20  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Hello 
I'm sure most of you are aware that you can dump outline hints from xplan.
I've dumped these hints from a query with a few union alls and a subfactoring clause and....well I'm not sure where to put them.
I don't want to baseline, this is a one off query - I just wanted the hint plan available in case the optimizer decides to "help" me at the time of running in anger.
I don't usually have a problem but this one isnt working well for me.
Cut down code with placeholders for brevity.
with dfn as(SELECT file_name FROM dba_data_files
where tablespace_name in
(select
--tablespace_list
tablespace_name
from dba_segments
where
tablespace_name in (select tablespace_name
from dba_Segments a
where segment_name in (select index_name
from dba_indexes
where table_name in (<LIST>)
union all select table_name
from dba_tables
where table_name in (<LIST>)
)
)
)
order by dba_data_files.FILE_ID)
select
------Hint plan from outline placed here
statement1
from dfn
union all
select
'----------------------------------------------'
from dual connect by level < 6
union all
select
statement2
from dfn
union all
select
'----------------------------------------'
from dual connect by level < 6
union all
select
statement3
from dfn
;
The hint plan partially takes (because cardinality feedback stops), but I do no get the plan I lifted them from.
Do I need to do this the old fashioned way with block level hinting?
|
|
|
|
| Re: Union (all) and outline hints [message #633525 is a reply to message #633524] |
Fri, 20 February 2015 03:59  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I had a related problem earlier this week with a query against DBA_OBJECTS. On test, it's about twenty gets but on prod about 3000 gets. I did the same as you: got the outline hints and tried to use them, but I couldn't. The problem was clear: the good plan used an indexed loop to join user$ to obj$, the bad plan used a scanned hash join. No matter what I did, I could not push a hint into the view. It's a UNION ALL view, as is your DBA_SEGMENTS.
I gave up eventually. If it had been a client system, I would have raised a TAR but I can't really do that for our own development systems.
I think that placing hints where you have them should be enough, but it didn't work for me against DBA_OBJECTS, I couldn't push the hint further down. Perhaps one needs a more extended syntax? This trivial example uses an index, and then if I hint a scan in only the first SELECT, a scan is what I get:
orclz>
orclz> set autot trace exp
orclz>
orclz> with d as (select * from dept where deptno=10)
2 select * from d
3 union all
4 select * from d
5 union all
6 select * from d;
Execution Plan
----------------------------------------------------------
Plan hash value: 728916096
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 6 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D664E_3044F8 | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | VIEW | | 1 | 30 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664E_3044F8 | 1 | 20 | 2 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 30 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664E_3044F8 | 1 | 20 | 2 (0)| 00:00:01 |
| 10 | VIEW | | 1 | 30 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664E_3044F8 | 1 | 20 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=10)
orclz>
orclz> with d as (select * from dept where deptno=10)
2 select /*+ full(d.dept) */ * from d
3 union all
4 select * from d
5 union all
6 select * from d;
Execution Plan
----------------------------------------------------------
Plan hash value: 2056277111
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 6 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D664F_3044F8 | | | | |
|* 3 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | VIEW | | 1 | 30 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664F_3044F8 | 1 | 20 | 2 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 30 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664F_3044F8 | 1 | 20 | 2 (0)| 00:00:01 |
| 9 | VIEW | | 1 | 30 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D664F_3044F8 | 1 | 20 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
orclz>
|
|
|
|
| Re: Union (all) and outline hints [message #633526 is a reply to message #633525] |
Fri, 20 February 2015 04:23 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Yes, the dictionary views seem "special". I tried wrapping the unions under a giant select * from () but the hints didnt take at that top level either.
It's not enough of a big deal for me to chase, I had hoped I was just being dim and doing it wrong.
|
|
|
|
| Re: Union (all) and outline hints [message #635124 is a reply to message #633526] |
Sat, 21 March 2015 04:05 |
 |
billgate123
Messages: 1
Registered: March 2015
|
Junior Member |
|
|
I just wanted the hint plan available in case the optimizer decides to "help" me at the time of running in anger.
I don't usually have a problem but this one isnt working well for me.
______________
Nawaz
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
