Help in Performance Tuning Required

Question

I have a query which involves joins across 7 different tables.

Below is the query:

SELECT        /*+qb_name(select1) dynamic_sampling(nbtd 2) */  
               td.tsf_no                      C1
              ,td.tsf_seq_no                  C2
              ,NVL(th.from_loc,-999)          C3
              ,NVL(th.to_loc,-999)            C4
              ,NVL(th.to_loc_type,'-1')       C5
              ,NVL (s.to_loc,'-999')          C6
              ,td.item                        C7
              ,NVL(ss.qty_expected, 0)        C8
              ,NVL(ss.carton,'-1')            C9
              ,NVL(ss.distro_no,'-1')         C10
              ,NVL(ss.distro_type,'-1')       C11
              ,NVL(ss.qty_expected, 0)        C12
              ,NVL(ss.weight_received, 0)     C13
              ,NVL(ss.weight_received_uom, 0) C14
              ,s.shipment                     C15
              ,NVL(s.bol_no,'-1')             C16
              ,NVL(s.order_no, 1)             C17
              ,ils.av_cost                    C18
              ,il.unit_retail                 C19
              ,user                           C20
              ,to_char(sysdate, 'YYYYMMDD')   C21
              ,'T'                            C22
              ,ss.seq_no                      C23
              ,nbtd.expense_ind               C24
              ,nbtd.process_expense_ind       C25
              ,to_char(th.create_date, 'YYYYMMDD')  C26
          FROM nb_tsfdetail nbtd
              ,tsfdetail td
              ,tsfhead th
              ,shipsku ss
              ,shipment s
              ,item_loc il
              ,item_loc_soh ils
         WHERE td.tsf_no        = nbtd.tsf_no
           AND td.tsf_seq_no    = nbtd.tsf_seq_no
           AND td.tsf_no        = th.tsf_no
           AND ss.distro_no     = td.tsf_no
           AND ss.item          = td.item
           AND ss.shipment      = s.shipment
           AND td.item          = il.item
           AND il.loc           = th.from_loc
           AND ils.item         = il.item
           AND ils.loc          = il.loc
           AND nbtd.process_expense_ind = 'N'
           AND nbtd.expense_ind = 'Y';

The data in each table is:

select count(1) from nb_tsfdetail;--692,336,190
select count(1) from tsfdetail;--693,317,818
select count(1) from tsfhead;--3,697,876
select count(1) from shipsku;-- 1,273,823,438
select count(1) from shipment;--514,593,396
select count(1) from item_loc;-- 109,587,391
select count(1) from item_loc_soh;--108,435,856

I have attached the explain of the above query.

Answer 1

Where to start

Start by defining the performance problem that you're experiencing as precisely as you can. I'm going to assume that the query that you have is running too slowly. How quickly does it need to finish? You need that information to figure out if certain trade-offs are worth it. For example, adding an index to an underlying table might improve the performance of this query but it will take up additional space on the database and require extra maintenance during DML.

Notes on EXPLAIN

Next understand as much as you can about the explain plan for the query that you have. Pay particular attention to the row estimates. If you think that Oracle is executing a query too slowly then coming up with an explain plan that you think would finish faster is a great exercise. You may think of an index that needs to be added. Worst case you can add a hint to test out a theory and figure out a way to rewrite the SQL (or keep the hint if absolutely necessary) to improve performance. An explain plan can give good clues but it only has what Oracle thinks will happen. If your database is licensed for the tuning pack then you can use real-time SQL monitoring in SQL Developer. That is a great feature for figuring out why a query is running slowly because it gives you runtime information about the actual versus estimated number of rows, wait times, and the amount of time spent on each step. All you posted is an explain plan so I'll do my best based on that alone.

For your query, the driving table is NB_TSFDETAIL. Oracle expects to filter down 700 M rows to 168k using a full scan on the table. Oracle expects the nested loop join to ITEM_LOC to filter down the result set further to 35k rows. The rest of the joins aren't expected to meaningfully change the cardinality. Every join is a nested loop join except for a hash join on TSFHEAD. That may have been implemented as a hash join simply because there wasn't a good index candidate on that table.

Importance of join order, table access method, and join type

When I'm looking at a query I focus on three main things: join order, table access method, and join type. If any of those seem inefficient or don't match what I expected to happen then I dig deeper into that.

To give an example of when join order can go wrong, if there's a join that eliminates many of the rows from the result set then I generally want that join to be processed as earlier as possible in the query plan. If it's processed as the final join then I may have done a lot of unnecessary work on rows that won't be returned in the final result set. For your query Oracle chose the NB_TSDETAIL table as the first table to access. That seems pretty reasonable because the filters are expected to eliminate over 99.9% of the rows from one of the largest ones in the query. Most of the joins aren't expected to change the cardinality so I'm not that concerned about join order for this query, assuming that the estimated cardinalities are close to reality. If there is an INNER JOIN that you know will eliminate many rows then you may need to give Oracle more information so it can build a better query plan with that knowledge.

To give an example of when table access method can go wrong, sometimes tables aren't indexed properly for queries. It looks like your query requires a full table scan on NB_TSFDETAIL. If that full scan takes longer than the maximum response time that you can tolerate then it doesn't matter how you tune your query. You need to fix the underlying data model problem, perhaps by creating an index. Another example of when table access method can go wrong is if an index is used when it shouldn't be. Using an index can be very inefficient if Oracle needs to access a large performance of rows from a table. In that scenario a full table scan is often more efficient.

To give an example of when the join type can go wrong, a nested loop join can be inefficient if the outer table has too many rows. Here Oracle estimates that only 168k rows will be returned from NB_TSDETAIL. That isn't that many rows relatively speaking so a nested loop join using that result set as an outer table may have a lower cost than doing a hash join or a merge join. However, what if the actual number of rows returned from the table is 168M? That means that the cost of the time will be roughly off by a factor of a thousand. If Oracle had more accurate cardinality information it may have picked hash joins instead which likely would have performed much better.

There are of course other possible issues with query plans that can lead to poor performance. The one most worth mentioning for this query I think is the lack of parallelism. Perhaps this is intentional and your server is too busy to allow for it, but the amount of work required here by the RDBMS makes me think that the query could benefit from parallelism.

Conclusion

In summary, the following are the most likely causes of the performance issue that you're experiencing:

1. Oracle is estimating the number of rows returned from NB_TSFDETAIL as 168982 which is a significant underestimate. If that estimate is fixed then Oracle will pick a plan with fewer nested loop joins that will perform better. Ways to improve that estimate including creating multi-column statistics (there may be a hidden relationship between the PROCESS_EXPENSE_IND and EXPENSE_IND columns that Oracle doesn't know about), updating statistics on the table, or by using a larger sample size for your dynamic sampling hint.

2. The number of estimated rows from NB_TSDETAIL is about right but it takes a long time to find them all with a table scan on a 700 million row table. Defining an index on those columns could speed up query performance.

3. You're running a query on 700 M row tables but not using parallelism. That may have been configured by the DBA and isn't intentional on your part. If your workload allows it and this query is important enough you should consider a PARALLEL(X) hint to see if that improves the execution time.

4. Some other database or hardware setting is causing the slowness of the query. This could be disk configuration, database parameters, or any number of things.

Further actions

Actionable next steps for you include:

1. Define your performance problem and response time requirement.

2. Determine the accuracy of the 168k row estimate on the NB_TSFDETAIL table.

3. Benchmark the time Oracle needs to perform a full table scan on the NB_TSFDETAIL. You may be able to do this with a carefully constructed COUNT(*) query.

4. If necessary, talk to your DBA about allowing this query to run in parallel.

5. You know more about the table structures and data than we do, so try out other theories to see if they improve query performance.

Answer 2

If this suppose to be an OLAP query -

Please add the following hint

use_hash(nbtd,td,th,ss,s,il,ils)

The idea is to eliminate all these NESTED LOOPS which are performance killer for large volumes.

---

If this suppose to be an OLTP query -

Please add the following hint

first_rows

The idea is to eliminate the HASH JOIN and direct the optimizer towards indexes use.

Answer 3

When you have a multi-column predicate such as:

AND nbtd.process_expense_ind = 'N'
AND nbtd.expense_ind = 'Y';

... it is very easy for the optimiser to infer incorrect cardinality from column statistics.

For queries expected to take more than a non-trivial amount of time, I would ensure that the optimiser uses dynamic sampling to accurately estimate the number of rows that match the predicate.

You could also investigate the use of extended statistics.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm