Oracle optimizer hints xmlagg function

Question

I have a function which calls several tables / views etc. with a few xmlaggs of the data.

For some reason I am getting a performance increase when I am pulling in additional information even though this extra information isn't used for the rest of the code (such as an indexing of a key value used again).

I have run tkprof on the fast and slow ones and I am seeing a few issues - the first is the slow query has misses during the parse and execute whereas the fast one doesn't.

My main issue is that looking further down I can see a high cost for one of my views - the faster query is using the 3 indexes on the underlying tables whereas the slow one isn't using any.

I have tried to insert a hint:

SELECT /*+ index(view_alias,table1_index, table2_index, table3_index) */     
XMLCONCAT (...

However it is still doing full table scans. Am I putting the optimizer hint in the wrong place or using the wrong syntax for this?

Edit - I've been doing some more investigating and it appears that it might be a knock on from Oracle doing a hash join rather than a Nested loop however my select is from several tables - can I force a USE_NL on all 3? How do I know which area of the pl/sql is causing this as it is called several times.

Update 28/08 - Bounty added. Let me know if there's anything extra required.

Update 01/09 -

> SELECT XMLCONCAT (  XMLELEMENT (  "1",  (SELECT XMLCONCAT(  XMLELEMENT
> (  "2",  XMLELEMENT (  "3",  XMLFOREST (  )),  CASE  WHEN   THEN    
> XMLELEMENT (  "3",  XMLFOREST (  ))  END),  /*   (SELECT XMLELEMENT ( 
> "4",  XMLAGG (XMLELEMENT ("5")))  FROM TABLE t1,  t2  WHERE t1.col1 =
> t2.col2)  ,*/    CASE  WHEN   THEN  (SELECT XMLAGG (  XMLELEMENT ( 
> "5", */(SELECT col1  FROM TABLE t1,  t2  WHERE t1.col1 = t2.col2),*/ 
> XMLFOREST ( ....

There two commented out selects which when EITHER are uncommented make it into a faster performing query. t1 and t2 are NOT used elsewhere in the query at all.

Update 01/09 Here are the execution plans: fast http://pastebin.com/pbJMSxrB slow http://pastebin.com/zt3eUYNd

It's the high cost ones from line 86 which I wish to correct. It may be a result of the full scans here, or the joins further up.

Answer 1

A reason for not using indexes is the theoretical possibility of existence of nulls. Nulls are not indexed, so if your query need/thinks that there may be nulls, it cannot access the table via indexes.

Also, your hint must be at the same level as your table is read from:

select /*+parallel(table_a)*/ ...
from (
      select ...
      from table_a
      ...
      )
...

won't work

but

select  ...
from (
      select /*+parallel(table_a)*/ ...
      from table_a
      ...
      )
...

will work.

Answer 2

A small change in a query can potentially have an influence on a very different part of the query. Here, the change influenced how the view VIP_CODES_VW is joined (it is done in two places, but the second one has greater influence on performance): in the fast query it is done using NESTED LOOPS (line 79), and in the slow one - HASH JOIN (line 75). To tell the optimizer to use NESTED LOOPS, you can add a hint /*+ USE_NL(VIP_CODES_VW) */ after SELECT in which VIP_CODES_VW is queried.