4

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.

bob dylan
  • 1,458
  • 1
  • 14
  • 32
  • Can you post your table with any indexes as well as your query? – Nick Aug 26 '15 at 10:36
  • Hi, I was going to post the pl/sql however it is quite long (over 1000 lines) - in effect the format is xmlconcat ( xml agg ( xml agg (case ( xmlagg select value1,2,3 from views 1 2 3 where view1 value1 = view2 value2 and view1 value1 = view3 value3 ) ..... The view I am referencing is the same table x3 just split into distinct classes e.g. view = aggregate of class1+class2+class3. Apologies if this is still a bit unclear. – bob dylan Aug 26 '15 at 10:41
  • 1
    You can post it as a file. I think the syntax could be an issue. Can you specific 'index(view_alias.table1_alias table1_index) index(view_alias.table2_alias table2_index)' – Brainhash Aug 26 '15 at 10:47
  • Should this hint go at the first select or at the one that preceeds the 'from'? Or should it go after the from e.g. FROM /*+ index(view,table1index) index(view,table2index) index(view,table3index) */ schema.view_to_use view .... – bob dylan Aug 26 '15 at 10:54
  • t1 and t2 -- they're views, are they? If so, what are the view definitions? – David Aldridge Sep 01 '15 at 08:41
  • t1 is actually the result of a BULK COLLECT INTO from another function (again using a different view) t2 is just a table with an index. – bob dylan Sep 01 '15 at 10:26
  • I don't have much confidence that anyone will be able to solve the problem unless they can see the full code. However, if you are effectively equi-joining all the rows of two underlying tables together then it's likely that a hash join is the most effective means of doing so. – David Aldridge Sep 01 '15 at 10:43
  • I figured as much, however it's obviously not the most effective way if it can perform better. See edit for results of traces and the execution plans. – bob dylan Sep 01 '15 at 10:52

2 Answers2

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.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
  • Hi, I have tried to put the hint pretty much everywhere and It's not working. The issue I'm having is I have this select and it works slow, but when I insert another select using a different table it then performs fine with no buffer fails / hash join. See update for further details – bob dylan Sep 01 '15 at 08:22
  • Have you tried to insert the hints in the view? this is what I was saying in the answer: to put the hints where the tables are referenced. – Florin Ghita Sep 04 '15 at 07:38
  • I will create a new view - should the hint be the same (parallel) though ? – bob dylan Sep 04 '15 at 07:49
  • nope. Use the hints you need: `/*+ index(t1 t1_index_name) index(t2 t2_index_name) use_nl(t1 t2)` – Florin Ghita Sep 04 '15 at 08:03
  • I add this to the view? Do I put them all in-line for each select (bearing in mind there's 3 tables in this view) CREATE VIEW... SELECT /*+ index(tvic.vip_codes TVIC.VIP_CODES_IDX) index(tvic.vip_lcv_codes TVIC.VIP_LCV_CODES_IDX) use_nl(tvic.vip_codes tvic.vip_lcv_codes)*/ cols from table1... SELECT (another hint for T2?) from T2.... SELECT (another hint T3?) ? – bob dylan Sep 04 '15 at 08:10
  • I've added the hints to the view ... UNION ALL SELECT /*+ index(tvic.VIP_BIKE_CODES TVIC.VIP_BIKE_CODES_IDX)*/.. and as per @mik I have replaced the view refrence with the underlying tables: (SELECT /*+ use_nl(tvic.vip_codes tvic.vip_lcv_codes tvic.VIP_BIKE_CODES) */ however still no change. Apologies if I'm missing something else. – bob dylan Sep 04 '15 at 08:26
  • @bobdylan I've analised the code you gave to mik. YOu should edit line 292 with `/*+use_nl(vip pat pc)*/`. I saved the edit [http://pastebin.com/zFHVp0vz](http://pastebin.com/zFHVp0vz). maybe pc is not needed. You should put there the aliases of views you need to use nested loops. – Florin Ghita Sep 04 '15 at 08:36
  • I've tried vip pat pc - no change and vip pat m2v - and I got worse performance. I replaced it in all places (as per mik) as each in_vrm could hit either section. Also just vip and pat. – bob dylan Sep 04 '15 at 09:00
1

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.

mik
  • 3,575
  • 3
  • 20
  • 29
  • Hi, where after? I have done a replace on all (SELECT with (SELECT /*+ USE_NL(VIP_CODES_VW) (just to ensure I have got all instances). (SELECT /*+ USE_NL(VIP_CODES_VW) */XMLCONCAT ( CASE ..... (SELECT /*+ USE_NL(VIP_CODES_VW) */MAX ( .... etc. but the performance is the same. The issue is there's multiple nested statements and uses of this view in the function. – bob dylan Sep 03 '15 at 12:50
  • Can you post the subquery where VIP_CODES_VW is referenced? Both of them if there are two. Can be on [pastebin](http://pastebin.com). – mik Sep 03 '15 at 13:13
  • The view is on another schema (`tvic`), so normally you would need to prefix it also in the hint, but you can use an alias (`vip`) too, which is simpler. Try this: [pastebin.com/Hrsji1sM](http://pastebin.com/Hrsji1sM). – mik Sep 03 '15 at 17:15
  • http://pastebin.com/Wg4vREcm This is the result of the new function. I feel like this is on the right track (as per my earlier updates) though. – bob dylan Sep 04 '15 at 07:47
  • The plan has changed a bit, however the worst `HASH JOIN` remained. The hint is only a hint, i.e. Oracle does not need to obey it. Try to add `/*+ FIRST_ROWS */` hint to the top-level query (after the first `SELECT` in the slow query). This hint makes Oracle to prefer `NESTED LOOPS` over `HASH JOIN`. – mik Sep 04 '15 at 14:09
  • Apologies would it be possible to update the pastebin where this should go? I've attempted it but don't think it's in the right place. – bob dylan Sep 04 '15 at 14:31
  • Here is the link: http://pastebin.com/erxyKB5w. There are three variants of the query, I don't konw which one is atcually used, so I add hints to all variants (the dbms_output should show it). – mik Sep 04 '15 at 14:41
  • The section it uses varies depending on the earlier base tables. This hasn't resolved the issue entirely however my question of 'where' to put these hints has at least been answered. I will award the bounty to you (Would also give it to Florin if I could) but my issue still remains unresolved. As the crux of my issue is very specific I don't think we will be able to resolve it without plenty more back and forths. – bob dylan Sep 04 '15 at 15:20
  • I can give you a bunch of hints that may (or may not) help in your situation (to be added where the `use_nl(vip)` hint was added): - `leading(dom,mvd,vip)` - this makes Oracle join tables/views in specified order (you can also try another order, but this one was in the _fast_ query), - `index(vip)`, - `push_pred(vip)`, - `merge(vip)` or `no_merge(vip)`. You can also use combinations of these hints (including the `use_nl(vip)` hint). – mik Sep 07 '15 at 13:12