Home » RDBMS Server » Performance Tuning » Union (all) and outline hints (11.2.0.3)
Union (all) and outline hints [message #633524] Fri, 20 February 2015 03:20 Go to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Hello Smile

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 Go to previous messageGo to next message
John Watson
Messages: 8922
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>
But I couldn't make it work with a DBA view as the source.

Re: Union (all) and outline hints [message #633526 is a reply to message #633525] Fri, 20 February 2015 04:23 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: CPU consumption calculation for sqls
Next Topic: Image File Size
Goto Forum:
  


Current Time: Thu Mar 28 17:00:26 CDT 2024