This discussion is archived
14 Replies Latest reply: May 27, 2013 9:47 PM by Moazzam RSS

unable to generate optimal plan

Moazzam Pro
Currently Being Moderated
We are using Oracle 10g on Linux platform. The below query is taking about one hour to complete on our risk database. All these views are in Distribution database and we are executing the query from Risk database.
  SELECT  
        cash_balances.ID,
         CDATE,
         cash_balances.country,
         cash_accounts.id AS CASH_ACCOUNT_ID,
         cash_balances.currency_decimal,
         cash_accounts.system,
         myrmi_utils.apply_external (book_bal / POWER (10, curr_deci),exte_decimal) AS bal_formatted,
         myrmi_utils.apply_internal (cl_bal / POWER (10, curr_deci),exte_decimal) AS cle_formatted,
         CL_DBCR_IND
    FROM myrmi.cash_accounts
         INNER JOIN myrmi.cash_balances cash_balances
            ON cash_balances.accounts_id = cash_accounts.id
         LEFT OUTER JOIN myrmi.CURRENCY currency
            ON cash_accounts.ac_currency = currency.currency_code
   WHERE cash_balances.country IN ('GB')
         AND cash_balances.CDATE BETWEEN '01-Jan-2013' AND '30-Apr-2013'
         AND (cash_balances.accounts_id IN
                 (30222, 30223, 30224, 30225, 30226, 30227, 30228, 30229, 30230, 30231, 30232))
ORDER BY cash_balances.accounts_id, cash_balances.CDATE;
However, if we add the following hint, then query completes execution in just 5 seconds. In this case, the query uses cash_accounts as outer table while without these hints, the query uses cash_balances as outer table:
/*+ ORDERED USE_NL(cash_accounts cash_balances) INDEX(cash_accounts CSH_ACC_PK) INDEX(cash_balances CSH_BAL_IX_01)*/
I want to know as why the optimizer is unable to generate optimal plan when all stats are gathered and up-to-date.
  • 1. Re: unable to generate optimal plan
    Karthick_Arp Guru
    Currently Being Moderated
    Can you post the execution plan with the hint and without the hint. Also please mention the 4 digit version number. 10g is not considered as a version.

    Also look into the FAQ {message:id=9360003}. This will give good insight on how to approach performance issue.
  • 2. Re: unable to generate optimal plan
    BluShadow Guru Moderator
    Currently Being Moderated
    Consider removing the hints altogether and then see how it performs...

    From the documentation:

    http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements006.htm

    >
    Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.

    Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.
  • 3. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
    Below are the execution plans.

    Plan for query using no hints:
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    Plan hash value: 2409238183                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    -----------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    | Id  | Operation                               | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    -----------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   0 | SELECT STATEMENT                        |                   |     1 |    90 |    16   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   1 |  SORT ORDER BY                          |                   |     1 |    90 |    16   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   2 |   NESTED LOOPS OUTER                    |                   |     1 |    90 |    16   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   3 |    NESTED LOOPS                         |                   |     1 |    77 |    16   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   4 |     PARTITION RANGE SINGLE              |                   |     1 |    34 |    14   (0)| 00:00:01 |     9 |     9 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   5 |      PARTITION LIST SINGLE              |                   |     1 |    34 |    14   (0)| 00:00:01 |   KEY |   KEY |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   6 |       INLIST ITERATOR                   |                   |       |       |            |          |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   7 |        TABLE ACCESS BY LOCAL INDEX ROWID|     CASH_BALANCES |     1 |    34 |    14   (0)| 00:00:01 |   344 |   344 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |*  8 |         INDEX RANGE SCAN                |     CSH_BAL_IX_01 |     1 |       |    13   (0)| 00:00:01 |   344 |   344 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |   9 |     TABLE ACCESS BY GLOBAL INDEX ROWID  |     CASH_ACCOUNTS |     1 |    43 |     2   (0)| 00:00:01 | ROWID | ROWID |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |* 10 |      INDEX UNIQUE SCAN                  |     CSH_ACC_PK    |     1 |       |     1   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    |* 11 |    INDEX UNIQUE SCAN                    |     CURRENCY_PK   |     1 |    13 |     0   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
    -----------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    ---------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    Note                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    -----                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       - dynamic sampling used for this statement                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 4. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
    Plan for query using USE_NL and ORDERED and INDEX hints
    ----------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    | Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    ----------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   0 | SELECT STATEMENT                       |                   |     1 |    90 |  9809   (1)| 00:01:58 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   1 |  SORT ORDER BY                         |                   |     1 |    90 |  9809   (1)| 00:01:58 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   2 |   NESTED LOOPS OUTER                   |                   |     1 |    90 |  9808   (1)| 00:01:58 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   3 |    NESTED LOOPS                        |                   |     1 |    77 |  9808   (1)| 00:01:58 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   4 |     INLIST ITERATOR                    |                   |       |       |            |          |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| CASH_ACCOUNTS     |  2294 | 98642 |   594   (1)| 00:00:08 | ROWID | ROWID |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |*  6 |       INDEX UNIQUE SCAN                | CSH_ACC_PK        | 15597 |       |    57   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   7 |     PARTITION RANGE SINGLE             |                   |     1 |    34 |     4   (0)| 00:00:01 |     9 |     9 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   8 |      PARTITION LIST SINGLE             |                   |     1 |    34 |     4   (0)| 00:00:01 |   KEY |   KEY |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |   9 |       TABLE ACCESS BY LOCAL INDEX ROWID| CASH_BALANCES     |     1 |    34 |     4   (0)| 00:00:01 |   344 |   344 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |* 10 |        INDEX RANGE SCAN                | CSH_BAL_IX_01     |     1 |       |     3   (0)| 00:00:01 |   344 |   344 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    |* 11 |    INDEX UNIQUE SCAN                   | CURRENCY_PK       |     1 |    13 |     0   (0)| 00:00:01 |       |       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    ----------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    Note                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    -----                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       - dynamic sampling used for this statement                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 5. Re: unable to generate optimal plan
    jeneesh Guru
    Currently Being Moderated
    Is the table stats up to date?

    Please ignore..

    Edited by: jeneesh on May 24, 2013 3:10 PM
  • 6. Re: unable to generate optimal plan
    Martin Preiss Expert
    Currently Being Moderated
    the optimizer uses its information on data distribution to generate a fitting plan, but this does not mean that the result is always optimal: there are insufficient statistical values, bugs, some errors (or at least strange assumptions) in the costing model etc. If there is some data skew in your values for accounts_id (or country or cdate) then histograms could be helpful. But there are a lot of possible reasons for a suboptimal plan.

    Regards

    Martin

    P.S.: your execution plan without hints shows a lot of 1 row estimates - perhaps your sample size is to small to gather a fitting sample.

    Edited by: Martin Preiss on May 24, 2013 11:42 AM
  • 7. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
    >
    Consider removing the hints altogether and then see how it performs...
    >

    If I remove the hints, then although the cost of query is very low i.e. 16, but it takes about one hour to execute.

    I think optimizer is not choosing driving site correctly. It is trying to execute the statement on Risk database site while all the views belong to the Distribution database although Risk database contains only one myrmi_utils package being used in the select statement.

    The database version is 10.2.0.4.0.
  • 8. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
    One thing that I have noticed is that if I execute the query, without hints, directly on the distribution database, then same plan is generated (the plan without hints) and query executes in just one second.
  • 9. Re: unable to generate optimal plan
    Martin Preiss Expert
    Currently Being Moderated
    perhaps you could use the gather_plan_statistics hint to get rowsource statistics. I assume that the access on CASH_BALANCES returns more than 1 row.
  • 10. Re: unable to generate optimal plan
    Mohamed Houri Pro
    Currently Being Moderated
     I think optimizer is not choosing driving site correctly
    I don't see anything in your execution plan that indicates a presence of a REMOTE QUERY. Neither in the execution plan nor in the predicate part

    If you are sure about the presence of a REMOTE table in your query then you can use the strategic hint /*+ driving_site(table_name alias) */ to precise the site where you want the driving table should be

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 11. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
    >
    I don't see anything in your execution plan that indicates a presence of a REMOTE QUERY. Neither in the execution plan nor in the predicate part
    >

    You are correct. I have just come to know that the execution plans are from the actual distribution site, instead of the Risk. Actually we do not have rights to generate plans from Risk database. Before execution from Risk database, we have appended the /*+ driving_site(cash_balances) */ hint, but still the query is stuck and takes about an hour to execute. Due to rights, i cannot generate the execution plan.
  • 12. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
     I assume that the access on CASH_BALANCES returns more than 1 row.
    You are correct, the cash_balances should return 1000 rows for these 11 account ids. The cash_balance table is partitioned table and Stats are valid for this specific partition ('GB'). I could not understand why the execution plan is showing wrong estimated rows. Do you think, that we need to increase the sample size in the gather_stats job, executed daily to gather stats.
  • 13. Re: unable to generate optimal plan
    Martin Preiss Expert
    Currently Being Moderated
    the main question is how the CBO gets to its estimated value: in your plan without hints the automatic sampling does not seem to get the correct information, so perhaps the number of sampled blocks is too small. But why is there dynamic sampling at all if you have accurate statistics? And why there is no sign of remote operations in the plan?

    If your problem is data skew (= big differences in the cardinalities for different accounts_id values) then histograms could solve the problem. Or perhaps the accounts_id values of your list are far outside of the range of low_value - high_value in the column statistics (for the table or the partition or the subpartition) - to check the values you could use Greg Rahn's blog post: http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/.

    Before changing the statistics gathering I would try to understand the CBO's calculation.
  • 14. Re: unable to generate optimal plan
    Moazzam Pro
    Currently Being Moderated
    Thanks to all of you for helping me sorting this out. This is the final statement from my side for not generating optimal plan:

    >
    The explain plan (without hints) shows that the number of rows in each table as one which is not correct. This proves that optimizer is unable to get accurate statistics for the joined tables and columns. Moreover, the explain plan is using dynamic sampling to generate it. This again shows that the statistics available in the database are not enough for the optimizer to decide the best execution plans. We have seen that there are partition level stats available for the tables but there are no table level stats which are required when we are using global indexes (CSH_BAL_IX_01,CSH_ACC_PK) to scan tables. Therefore, for optimizer to generate correct execution plans:

    1- The statistics must be available at table and partition level for indexes and tables.
    2- There should be foreign key constraint in CASH_BALANCES table for accounts_id column.

    If due to performance reasons, we are unable to provide above statistics to optimizer, then we should use hints for correct execution plan.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points