A tuning case study: The goofy optimizer (9i.x RDBMS )

Preface

They say: Tune the application first. And then, many a times, the CBO behaviour is not predictable or favourable, inspite of its best intentions or internal hueristic rules or availability of latest statistics with good sampling percent.
This is one such example and how the usage of hints had to be used as one of the last options to improve the performance of a concurrent program.
Ideally speaking, the CBO chooses what is usually either the best or one of the best available data access plan for executing and fetching the sql output. It should take into considerations all the underlying objects statistics and use the least costly data access plan. Sometimes, it doesnt happen out of the box.
Note: Some seeded 11i Oracle Application modules also depend heavily on hints to make the performance of their code acceptable. One such heavy user is the QP or Advanced Pricing module.

The Problem Scenario...

At a client site, a shipping related custom concurrent program started ~5 hrs to complete on an average, rather then the usual ~3 hrs. From preliminary investigation using RDBMS trace (event 10046) and PL/SQL dbms profiler, that the maximum time (about ~4.5 hrs) was taken up by the following innocent looking SQL itself!!
SELECT HP.CATEGORY_CODE CATEGORY_CODE ,
       HC.SALES_CHANNEL_CODE  SALES_CHANNEL_CODE ,
       HC.CUSTOMER_CLASS_CODE CUSTOMER_CLASS_CODE ,
       SUM(L.SHIPPED_QUANTITY * L.UNIT_SELLING_PRICE)
         ORDER_SHIP_TOT ,
       L.ACTUAL_SHIPMENT_DATE SHIP_DATE ,
       DECODE (H.ORG_ID, 362, 'US', 365, 'CAD',
                         5624, 'MEX', H.ORG_ID) OPERATING_UNIT ,
       TT.NAME ORDER_TYPE
FROM
  ONT.OE_ORDER_HEADERS_ALL H ,
  APPS.OE_ORDER_LINES_ALL L ,
  APPS.HZ_PARTIES HP ,
  APPS.HZ_CUST_ACCOUNTS HC ,
  APPS.HZ_CUST_SITE_USES_ALL HCSU ,
  APPS.HZ_CUST_ACCT_SITES_ALL HCAS ,
  APPS.OE_ORDER_SOURCES OS ,
  APPS.OE_TRANSACTION_TYPES_TL TT ,
  XXIW.XXIW_BS_OM_ORDER_TYPES XOT
WHERE
     H.INVOICE_TO_ORG_ID = HCSU.SITE_USE_ID(+)
AND H.ORDER_SOURCE_ID = OS.ORDER_SOURCE_ID
AND OS.NAME <> 'Satcom Closed'
AND H.ORDER_TYPE_ID = TT.TRANSACTION_TYPE_ID
AND TT.LANGUAGE = 'US'
AND TT.NAME NOT LIKE 'INTERNAL%'
AND TRUNC(L.ACTUAL_SHIPMENT_DATE)
     BETWEEN NVL(:B2 , TRUNC(L.ACTUAL_SHIPMENT_DATE))
         AND NVL(:B1 , TRUNC(L.ACTUAL_SHIPMENT_DATE))
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID(+)
AND HCAS.CUST_ACCOUNT_ID = HC.CUST_ACCOUNT_ID(+)
AND HC.PARTY_ID = HP.PARTY_ID(+)
AND UPPER(HC.CUSTOMER_CLASS_CODE) <> 'INTERNAL'
AND UPPER(HC.SALES_CHANNEL_CODE) <> 'INTERNAL'
AND H.FLOW_STATUS_CODE NOT IN ('CANCELLED','ENTERED')
AND H.BOOKED_DATE IS NOT NULL AND L.FLOW_STATUS_CODE = 'CLOSED'
AND L.LINE_CATEGORY_CODE <>'RETURN'
AND L.HEADER_ID = H.HEADER_ID
AND TT.NAME = XOT.ORDER_TYPE
AND XOT.TYPE = 'ALL'
GROUP BY HP.PARTY_NAME, HP.CATEGORY_CODE, HC.SALES_CHANNEL_CODE,
         HC.CUSTOMER_CLASS_CODE, H.ORDER_NUMBER, H.HEADER_ID,   
         H.BOOKED_DATE, L.ACTUAL_SHIPMENT_DATE,
         H.FLOW_STATUS_CODE, H.ORG_ID, OS.NAME, TT.NAME
iw_query_bad_plan:
The top time taking code section from pl/sql DBMS profiler data. As can be seen, the maximum time is spent in the Fetch of the cursor executing this query:
iw_query_bad_profiler_output:

Evaluating options

The first thing which comes to the mind is: Can we play with different sampling estimations? e.g. 10%, 50%, 70%. While the idea is appealing, it can destablize many other SQL queries and its an uphill task to judge the impact of such a change on the remaining application peformance as such. Heavy price to pay for tuning one lone SQL.
Again, the statistics of the objects were also recent enough (Stats gathered on a weekly basis as usual). There were no big changes in the data volumes as such.

Getting into the optimizer's thought process

From the SQL plan being used, It can be readily seen that the optimizer acted greedily like this: It saw that there are several small HZ* tables which could easily fit into PGA/SGA  memory and so it thought -- Cool, I can do a quick Hash join of these folks (that would also take care of the outer join predicate evaluation). After that, it proceeded to retrofit the remaining tables to be joined into the plan which was developing.
Fine approach. Only problem is that the index range scan of Header_id column using the non-unique index on the oe_order_lines_all table simply kills the performance of the fetch phase. It is very simply, very very inefficient.
19101503 INDEX RANGE SCAN OE_ORDER_LINES_N1 (object id 334899)
A much better approach would have been to first filter off the header_id values from OE_ORDER_HEADERS_ALL table so that it would use the unique primary key index of OE_ORDER_HEADERS_ALLand join with the OE_ORDER_LINES_ALL table using nested loop operation.
This itself would filter off a huge chunk of the data (which is mostly centered around OE_ORDER_LINES_ALL table). The remaining HZ* & miscellaneous data could easily be plugged in via Hash Joins. The idea was to drive the query using OE_ORDER_HEADERS_ALL to eliminate the maximum chunk of un-required data first.

Also, the predicate AND h.booked_date IS NOT NULL was replaced with
and h.booked_flag = 'Y'
. Why use a NOT NULL clause when a flag is available in OE_ORDER_HEADERS_ALL?

Also, a custom index xxoe_order_lines_n11 was found to exist on oe_order_lines_all.actual_shipment_date column. We thought we'll make use of it too. The more the merrier.

So then, after grouping together the related predicates together, formatting the query and Putting these ideas into practice, the original query was changed as following:
SELECT /*+ index (h,oe_order_headers_u1)
index (l,xxoe_order_lines_n11)
           use_nl(h,l)
       */
      hp.category_code  category_code
     ,hc.sales_channel_code sales_channel_code
     ,hc.customer_class_code customer_class_code
     ,SUM(l.shipped_quantity * l.unit_selling_pric order_ship_tot
     ,l.actual_shipment_date ship_date
     ,DECODE (h.org_id,
        362,  'US',
        365,  'CAD',
        5624, 'MEX',
        h.org_id)  operating_unit
     ,tt.NAME  order_type
   FROM
         ont.oe_order_headers_all  h
        ,apps.oe_order_lines_all   l
        ,apps.hz_parties           hp
     ,apps.hz_cust_accounts        hc
     ,apps.hz_cust_site_uses_all   hcsu
     ,apps.hz_cust_acct_sites_all  hcas
     ,apps.oe_order_sources        os
     ,apps.oe_transaction_types_tl tt
     ,xxiw.xxiw_bs_om_order_types  xot
   WHERE
          h.header_id                    = l.header_id
      AND h.ORDER_SOURCE_ID              = os.ORDER_SOURCE_ID
      AND h.ORDER_TYPE_ID                = tt.TRANSACTION_TYPE_ID
      AND h.flow_status_code not in ('CANCELLED','ENTERED')
      -- AND h.booked_date IS NOT NULL
      -- Replaced with the following condition                 
      and h.booked_flag = 'Y'
      -- Replaced with the above condition
  
      AND tt.LANGUAGE                    = 'US'
      AND tt.NAME                 not like 'INTERNAL%'
      AND l.flow_status_code             = 'CLOSED'
      AND l.LINE_CATEGORY_CODE          <>'RETURN'
      AND TRUNC(L.ACTUAL_SHIPMENT_DATE)
          BETWEEN NVL(:B2 , TRUNC(L.ACTUAL_SHIPMENT_DATE))
              AND NVL(:B1 , TRUNC(L.ACTUAL_SHIPMENT_DATE))
      AND h.invoice_to_org_id             = hcsu.site_use_id (+)
      AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
      AND hcas.cust_account_id           = hc.cust_account_id(+)
      AND hc.party_id                    = hp.party_id(+)
      AND UPPER(hc.customer_class_code) <> 'INTERNAL'
      AND UPPER(hc.sales_channel_code)  <> 'INTERNAL'
      and tt.name                        = xot.order_type
      and xot.type                       = 'ALL'
      AND os.name                       <> 'Satcom Closed'
   GROUP BY
      hp.party_name,
      hp.category_code,
      hc.sales_channel_code,
      hc.customer_class_code,
      h.order_number,
      h.header_id,
      h.booked_date,
      l.actual_shipment_date,
      h.flow_status_code,
      h.org_id,
      os.name,
      tt.name;

The Improvement

The same query now executed under 5 minutes!!! ~4.5 hrs to ~5 mins. Thats quantum.

The improved plan was as follows:

iw_query_good_plan:

Conclusion

While it may not be ideal, at times, it may be required to supplement of coach the CBO to achieve acceptable levels of application performance. The effect may be more long lasting if the "relative" data volume pattern of the underlying objects is not expected to change drastically in the system.

Comments:

We had an almost identical query once in my previous project which used to take a huge time for execution. Since I wasnt so much into Oracle performance tuning at that time, I split up the query into two parts; first part dealt with all the big tables and the second part actually executed in Teradata (our target system; where most of the smaller tables in the above query were replicated for reference in the datawarehouse). We were able to bring down the execution time from about 3 hours to a few minutes !! (yes the whole process). Still I would have loved to have the query tuned. Anyways, we had another query (from the same process in which the above query was the first part) which required us to fetch the reference_line_id from the line table along with its line id. The implemented process was rather convoluted (which means they didnt write a query for it; rather used a Joiner transformation in Informatica - an ETL tool) and it took around 2 hours to execute. So, how would you have fetched the reference_line_id additionally in the above query without incurring a huge performance penalty ? I would be keenly interested to hear from you on this matter.

Posted by Anjum on August 31, 2008 at 07:50 AM EDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today