This discussion is archived
12 Replies Latest reply: Oct 31, 2013 2:32 AM by JohnWatson RSS

Performance issue

user10858330 Newbie
Currently Being Moderated

Hello everyone,

i have been facing a very strange issue on production server. we are on Release 11.2.0.3.0 windows platform. Application user complaint that reports are taking too much time to complete. i have checked the server and find out there are some stats which were missing. i have gather both schema and database stats and run the same report again . it improved a bit, but not much as we were expected. i looked at execution plan where i could see two or three Nested loop join scanning large table with index range scan. it seems problematic therefore, i ran advisor against the statement. it came with potentially better execution plan improvement was 94% i have implemented and run the statement again. it actually remove all need loop join with hash and full table scan. The report were taking 2hr come in 5 mints. But when i ran other reports it again using nested loop join and taking long time to complete. As i run Tuning Advisor it come in between 1 to 5 mints. I have already gather all statistics. My question is why Optimizer is not taking optimal Plan? i know Hint can force to do so but i have no access to change application code. Can anyone suggest me what i need to check?  

  • 1. Re: Performance issue
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    post statements and plans.

     

    Best regards,

      Nikolay

  • 2. Re: Performance issue
    JohnWatson Guru
    Currently Being Moderated

    You could have bad or missing system statistics. If you query SYS.AUX_STATS$ do you get  lots of nulls for PVAL1, or dates from ancient history for DSTART and DSTOP ?

  • 3. Re: Performance issue
    user10858330 Newbie
    Currently Being Moderated

    Plan hash value: 1808844320

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                      |                      |    42 |  4368 | 19861   (1)| 00:04:39 |       |       |        |      |            |
    |*  1 |  COUNT STOPKEY                                        |                      |       |       |            |          |       |       |        |      |            |
    |   2 |   PX COORDINATOR                                      |                      |       |       |            |          |       |       |        |      |            |
    |   3 |    PX SEND QC (ORDER)                                 | :TQ10005             |    42 |  4368 | 19861   (1)| 00:04:39 |       |       |  Q1,05 | P->S | QC (ORDER) |
    |*  4 |     COUNT STOPKEY                                     |                      |       |       |            |          |       |       |  Q1,05 | PCWC |            |
    |   5 |      VIEW                                             |                      |    42 |  4368 | 19861   (1)| 00:04:39 |       |       |  Q1,05 | PCWP |            |
    |*  6 |       SORT GROUP BY                                   |                      |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,05 | PCWP |            |
    |   7 |        PX RECEIVE                                     |                      |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,05 | PCWP |            |
    |   8 |         PX SEND RANGE                                 | :TQ10004             |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,04 | P->P | RANGE      |
    |   9 |          SORT GROUP BY                                |                      |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,04 | PCWP |            |
    |  10 |           PX RECEIVE                                  |                      |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,04 | PCWP |            |
    |  11 |            PX SEND HASH                               | :TQ10003             |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,03 | P->P | HASH       |
    |  12 |             SORT GROUP BY                             |                      |    42 |  3486 | 19861   (1)| 00:04:39 |       |       |  Q1,03 | PCWP |            |
    |* 13 |              HASH JOIN OUTER                          |                      |  4868 |   394K| 19860   (1)| 00:04:39 |       |       |  Q1,03 | PCWP |            |
    |* 14 |               HASH JOIN OUTER                         |                      |  4868 |   337K| 19722   (1)| 00:04:37 |       |       |  Q1,03 | PCWP |            |
    |  15 |                PX RECEIVE                             |                      |       |       |            |          |       |       |  Q1,03 | PCWP |            |
    |  16 |                 PX SEND HASH                          | :TQ10000             |       |       |            |          |       |       |  Q1,00 | P->P | HASH       |
    |  17 |                  NESTED LOOPS                         |                      |       |       |            |          |       |       |  Q1,00 | PCWP |            |
    |  18 |                   NESTED LOOPS                        |                      |  4868 |   280K| 19714   (1)| 00:04:37 |       |       |  Q1,00 | PCWP |            |
    |  19 |                    NESTED LOOPS                       |                      |  4842 |   198K|  2223   (1)| 00:00:32 |       |       |  Q1,00 | PCWP |            |
    |  20 |                     PX PARTITION RANGE ALL            |                      |   563 | 18579 |    32   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWC |            |
    |  21 |                      TABLE ACCESS BY LOCAL INDEX ROWID| PEP01V1_DG0          |   563 | 18579 |    32   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |
    |  22 |                       BITMAP CONVERSION TO ROWIDS     |                      |       |       |            |          |       |       |  Q1,00 | PCWP |            |
    |* 23 |                        BITMAP INDEX SINGLE VALUE      | MINDX_PEP01V1_DG0_0  |       |       |            |          |     1 |     6 |  Q1,00 | PCWP |            |
    |  24 |                     PARTITION RANGE ALL               |                      |     9 |    81 |     4   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |
    |  25 |                      TABLE ACCESS BY LOCAL INDEX ROWID| PEP01V1_JT           |     9 |    81 |     4   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |
    |* 26 |                       INDEX RANGE SCAN                | MINDX_PEP01V1_JT_DG0 |     9 |       |     3   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |
    |  27 |                    PARTITION RANGE ALL                |                      |     1 |       |     3   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |
    |* 28 |                     INDEX RANGE SCAN                  | PKINDX_PEP01V1_DG2   |     1 |       |     3   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |
    |  29 |                   TABLE ACCESS BY LOCAL INDEX ROWID   | PEP01V1_DG2          |     1 |    17 |     4   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWP |            |
    |  30 |                PX RECEIVE                             |                      | 16730 |   196K|     7   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
    |  31 |                 PX SEND HASH                          | :TQ10001             | 16730 |   196K|     7   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
    |  32 |                  PX BLOCK ITERATOR                    |                      | 16730 |   196K|     7   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |
    |  33 |                   TABLE ACCESS FULL                   | PEP01V1_VBC1000      | 16730 |   196K|     7   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
    |  34 |               PX RECEIVE                              |                      |   402K|  4711K|   138   (1)| 00:00:02 |       |       |  Q1,03 | PCWP |            |
    |  35 |                PX SEND HASH                           | :TQ10002             |   402K|  4711K|   138   (1)| 00:00:02 |       |       |  Q1,02 | P->P | HASH       |
    |  36 |                 PX BLOCK ITERATOR                     |                      |   402K|  4711K|   138   (1)| 00:00:02 |       |       |  Q1,02 | PCWC |            |
    |  37 |                  TABLE ACCESS FULL                    | PEP01V1_VBC200       |   402K|  4711K|   138   (1)| 00:00:02 |       |       |  Q1,02 | PCWP |            |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       1 - filter(ROWNUM<=10001)
       4 - filter(ROWNUM<=10001)
       6 - filter(ROWNUM<=10001)
      13 - access(NVL("PEP01V1_VBC200"."VBC200_CELLULAR_MSISDN"(+),' ')=NVL("PEP01V1_DG2"."CUSTOMER_CELLULAR_PHONE_NUMBER",' '))
      14 - access(NVL("PEP01V1_VBC1000"."VBC1000_CELLULAR_MSISDN"(+),' ')=NVL("PEP01V1_DG2"."CUSTOMER_CELLULAR_PHONE_NUMBER",' '))
      23 - access("PEP01V1_DG0"."LINE_TYPE"='Customer Info')
      26 - access("PEP01V1_DG0"."DG0_ID"="PEP01V1_JT"."DG0_ID")
      28 - access("PEP01V1_DG2"."DG2_ID"="PEP01V1_JT"."DG2_ID")



    After Running Advisor execution plan changed


    plan FOR succeeded.
    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                         
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1956972868                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                         
    | Id  | Operation                                              | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                                                                                                         
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                         
    |   0 | SELECT STATEMENT                                       |                     |    42 |  4368 |       | 95215   (2)| 00:22:14 |       |       |        |      |            |                                                                                                                         
    |*  1 |  COUNT STOPKEY                                         |                     |       |       |       |            |          |       |       |        |      |            |                                                                                                                         
    |   2 |   PX COORDINATOR                                       |                     |       |       |       |            |          |       |       |        |      |            |                                                                                                                         
    |   3 |    PX SEND QC (ORDER)                                  | :TQ10007            |    42 |  4368 |       | 95215   (2)| 00:22:14 |       |       |  Q1,07 | P->S | QC (ORDER) |                                                                                                                         
    |*  4 |     COUNT STOPKEY                                      |                     |       |       |       |            |          |       |       |  Q1,07 | PCWC |            |                                                                                                                         
    |   5 |      VIEW                                              |                     |    42 |  4368 |       | 95215   (2)| 00:22:14 |       |       |  Q1,07 | PCWP |            |                                                                                                                         
    |*  6 |       SORT GROUP BY                                    |                     |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,07 | PCWP |            |                                                                                                                         
    |   7 |        PX RECEIVE                                      |                     |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,07 | PCWP |            |                                                                                                                         
    |   8 |         PX SEND RANGE                                  | :TQ10006            |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,06 | P->P | RANGE      |                                                                                                                         
    |   9 |          SORT GROUP BY                                 |                     |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,06 | PCWP |            |                                                                                                                         
    |  10 |           PX RECEIVE                                   |                     |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,06 | PCWP |            |                                                                                                                         
    |  11 |            PX SEND HASH                                | :TQ10005            |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,05 | P->P | HASH       |                                                                                                                         
    |  12 |             SORT GROUP BY                              |                     |    42 |  3486 |       | 95215   (2)| 00:22:14 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |* 13 |              HASH JOIN RIGHT OUTER                     |                     |    37M|  2991M|       | 94793   (1)| 00:22:08 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  14 |               PX RECEIVE                               |                     |   402K|  4711K|       |   138   (1)| 00:00:02 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  15 |                PX SEND BROADCAST                       | :TQ10001            |   402K|  4711K|       |   138   (1)| 00:00:02 |       |       |  Q1,01 | P->P | BROADCAST  |                                                                                                                         
    |  16 |                 PX BLOCK ITERATOR                      |                     |   402K|  4711K|       |   138   (1)| 00:00:02 |       |       |  Q1,01 | PCWC |            |                                                                                                                         
    |  17 |                  TABLE ACCESS FULL                     | PEP01V1_VBC200      |   402K|  4711K|       |   138   (1)| 00:00:02 |       |       |  Q1,01 | PCWP |            |                                                                                                                         
    |* 18 |               HASH JOIN RIGHT OUTER                    |                     |    37M|  2558M|       | 94617   (1)| 00:22:05 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  19 |                PX RECEIVE                              |                     | 16730 |   196K|       |     7   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  20 |                 PX SEND BROADCAST                      | :TQ10002            | 16730 |   196K|       |     7   (0)| 00:00:01 |       |       |  Q1,02 | P->P | BROADCAST  |                                                                                                                         
    |  21 |                  PX BLOCK ITERATOR                     |                     | 16730 |   196K|       |     7   (0)| 00:00:01 |       |       |  Q1,02 | PCWC |            |                                                                                                                         
    |  22 |                   TABLE ACCESS FULL                    | PEP01V1_VBC1000     | 16730 |   196K|       |     7   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                         
    |* 23 |                HASH JOIN                               |                     |    37M|  2126M|   258M| 94573   (1)| 00:22:05 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  24 |                 PX RECEIVE                             |                     |    37M|   606M|       | 23211   (1)| 00:05:25 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  25 |                  PX SEND HASH                          | :TQ10003            |    37M|   606M|       | 23211   (1)| 00:05:25 |       |       |  Q1,03 | P->P | HASH       |                                                                                                                         
    |  26 |                   PX BLOCK ITERATOR                    |                     |    37M|   606M|       | 23211   (1)| 00:05:25 |     1 |     6 |  Q1,03 | PCWC |            |                                                                                                                         
    |  27 |                    TABLE ACCESS FULL                   | PEP01V1_DG2         |    37M|   606M|       | 23211   (1)| 00:05:25 |     1 |     6 |  Q1,03 | PCWP |            |                                                                                                                         
    |  28 |                 PX RECEIVE                             |                     |    37M|  1505M|       | 39530   (1)| 00:09:14 |       |       |  Q1,05 | PCWP |            |                                                                                                                         
    |  29 |                  PX SEND HASH                          | :TQ10004            |    37M|  1505M|       | 39530   (1)| 00:09:14 |       |       |  Q1,04 | P->P | HASH       |                                                                                                                         
    |* 30 |                   HASH JOIN                            |                     |    37M|  1505M|       | 39530   (1)| 00:09:14 |       |       |  Q1,04 | PCWP |            |                                                                                                                         
    |  31 |                    PX RECEIVE                          |                     |     4 |   132 |       |    32   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |            |                                                                                                                         
    |  32 |                     PX SEND BROADCAST                  | :TQ10000            |     4 |   132 |       |    32   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |                                                                                                                         
    |  33 |                      PX PARTITION RANGE ALL            |                     |     4 |   132 |       |    32   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWC |            |                                                                                                                         
    |  34 |                       TABLE ACCESS BY LOCAL INDEX ROWID| PEP01V1_DG0         |     4 |   132 |       |    32   (0)| 00:00:01 |     1 |     6 |  Q1,00 | PCWP |            |                                                                                                                         
    |  35 |                        BITMAP CONVERSION TO ROWIDS     |                     |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |                                                                                                                         
    |* 36 |                         BITMAP INDEX SINGLE VALUE      | MINDX_PEP01V1_DG0_0 |       |       |       |            |          |     1 |     6 |  Q1,00 | PCWP |            |                                                                                                                         
    |  37 |                    PX BLOCK ITERATOR                   |                     |   117M|  1007M|       | 39383   (1)| 00:09:12 |     1 |     6 |  Q1,04 | PCWC |            |                                                                                                                         
    |  38 |                     TABLE ACCESS FULL                  | PEP01V1_JT          |   117M|  1007M|       | 39383   (1)| 00:09:12 |     1 |     6 |  Q1,04 | PCWP |            |                                                                                                                         
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                         
                                                                                                                                                                                                                                                                                                                
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
    ---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                
       1 - filter(ROWNUM<=10001)                                                                                                                                                                                                                                                                                
       4 - filter(ROWNUM<=10001)                                                                                                                                                                                                                                                                                
       6 - filter(ROWNUM<=10001)                                                                                                                                                                                                                                                                                
      13 - access(NVL("PEP01V1_VBC200"."VBC200_CELLULAR_MSISDN"(+),' ')=NVL("PEP01V1_DG2"."CUSTOMER_CELLULAR_PHONE_NUMBER",' '))                                                                                                                                                                                
      18 - access(NVL("PEP01V1_VBC1000"."VBC1000_CELLULAR_MSISDN"(+),' ')=NVL("PEP01V1_DG2"."CUSTOMER_CELLULAR_PHONE_NUMBER",' '))                                                                                                                                                                              
      23 - access("PEP01V1_DG2"."DG2_ID"="PEP01V1_JT"."DG2_ID")                                                                                                                                                                                                                                                 
      30 - access("PEP01V1_DG0"."DG0_ID"="PEP01V1_JT"."DG0_ID")                                                                                                                                                                                                                                                 
      36 - access("PEP01V1_DG0"."LINE_TYPE"='Customer Info')                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                
    Note                                                                                                                                                                                                                                                                                                        
    -----                                                                                                                                                                                                                                                                                                       
       - SQL profile "SYS_SQLPROF_014203d5f1680003" used for this statement                                                                                                                                                                                                                                     

    61 rows selecte

  • 4. Re: Performance issue
    user10858330 Newbie
    Currently Being Moderated
    SNAME                      PNAME                           PVAL1 PVAL2                                                                                                                                                                                                                                                   

    ------------------------------ ------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SYSSTATS_INFO              STATUS                                COMPLETED                                                                                                                                                                                                                                                 
    SYSSTATS_INFO              DSTART                                10-29-2013 05:46                                                                                                                                                                                                                                          
    SYSSTATS_INFO              DSTOP                                 10-29-2013 07:46                                                                                                                                                                                                                                          
    SYSSTATS_INFO              FLAGS                               0                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              CPUSPEEDNW                       1834                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              IOSEEKTIM                          10                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              IOTFRSPEED                       4096                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              SREADTIM                        8.611                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              MREADTIM                        1.511                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              CPUSPEED                         2036                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              MBRC                                0                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              MAXTHR                      235339776                                                                                                                                                                                                                                                           
    SYSSTATS_MAIN              SLAVETHR                      2639872                                                                                                                                                                                                                                                           

     

    13 rows selected

     

  • 5. Re: Performance issue
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    please format the plans so they be readable. Switch to advanced editor and put them into Courier New (after making sure that they are readable with monospace fonts, e.g. by pasting them into notepad).

     

    Best regards,

      Nikolay

  • 6. Re: Performance issue
    JohnWatson Guru
    Currently Being Moderated

    The system stats have 0 for the MBRC, is it possible that you gathered them at a time when there were no full table scan operations? Or have you tried to set the db_file_multiblock_read_count parameter? From the exec plans, it looks as though you have a manually specified degree of parallelism, either hinted in the statements or by decorating the tables or indexes?

     

    I would try:

    clear any manual parallel settings

    remove db_file_multiblock_read_count if it is set

    run the Resource Manager's I/O calibration

    disable the profile

    gather system stats again, with a complete workload

    enable parallel_degree_policy=auto

     

    run the query a few times to let cardinality feedback kick in, and see what the optimizer comers up with.

  • 7. Re: Performance issue
    user10858330 Newbie
    Currently Being Moderated

    I have not set db_file_multiblock_read_count parameter it is set on default value 64.

    Parallel setting is manual.Moreover, table indexes all created with parallel degree 4

    would setting the parameter manual to auto will fix the issue? my concern is if my schema and database statistic are available why CBO choosing bad execution plan. Moreover, i have once again gather workload system stats by following command.

    exec dbms_stats.gather_system_stats(gathering_mode => 'interval',interval => 30)

    Between these 30 mints i' will  execute resource intensive queries. let you know the values of  SYSSTATS_MAIN';

  • 8. Re: Performance issue
    Taral Journeyer
    Currently Being Moderated

    Did you set profiles for your statement through advisor. If so that might be the case

     

    Note                                                                                                                                                                                                                                                                                                       

    -----                                                                                                                                                                                                                                                                                                      

       - SQL profile "SYS_SQLPROF_014203d5f1680003" used for this statement                                                                                                                                                                                                                                    

  • 9. Re: Performance issue
    user10858330 Newbie
    Currently Being Moderated

    if you read my post i have mentioned that when i ran query forst time it will choose bad execution plan, As i attached a profile with the statement same query return in few mints instead of hours. i have collect all stats system schema, database but CBO still getting bad plan for queries here is my system stats

     

    START_TIME                  END_TIME                      MAX_IOPS   MAX_MBPS  MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS

    ------------------------------- ------------------------------- ---------- ---------- ---------- ---------- ------------------

    30-OCT-13 10.56.14.088000000 AM 30-OCT-13 11.01.14.665000000 AM    290    109     57      9              2

     

    PNAME                           PVAL1

    ------------------------------ ----------

    CPUSPEEDNW                       1834
    IOSEEKTIM                          10
    IOTFRSPEED                       4096
    SREADTIM                        8.611
    MREADTIM                        1.511
    CPUSPEED                         2036
    MBRC                               64
    MAXTHR                      235339776
    SLAVETHR                      2639872

     

    9 rows selected

     

     

    db_file_multiblock_read_count =64

    Parallel_Degree_Policy=auto;

    Note:

    The value of MBRC is not set. i have gather stats many time. lastly i have Set the MBRC to your chosen db_file_multiblock_read_count

    begin

     

    Dbms_Stats.Set_System_Stats('MBRC',64);

    end;

  • 10. Re: Performance issue
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    it's possible that your queries happen to be too complex for the optimizer -- e.g. there could be issues with selectivity estimates because of predicated correlates or some skewed distributions which cannot be adequately described by histograms. Offline optimization can sometimes identify such issues and create SQL profiles to provide additional information to the optimizer.

     

    If that's the case here, then you either have to go through such queries one by one (if there are not too many of them), or try and find something in common between them.

     

    Also, we can try and identify why the optimizer is having issues here, but we need more information for this than just explain plan. Since you're using the tuning advisor and your version is 11.2, you can run SQL real-time monitor -- it's simpler than tracing.

     

    Best regards,

      Nikolay

  • 11. Re: Performance issue
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You could "set" the execution plan using SQL Plan Management.  See Oracle Support Note "How to Use SQL Plan Management (SPM) - Example Usage [ID 456518.1]"

     

    Hemant K Chitale


  • 12. Re: Performance issue
    JohnWatson Guru
    Currently Being Moderated

    As I said before: do not set the db_file_multiblock_read_count parameter. And I would not set MBRC. Let Uncle Oracle work out what is best. And I also said, run the Resource Manager calibration: automatic degree of parallelism doesn't work without. it. And remove the parallel decoration of the tables and indexes.

     

    But in general, it is very difficult to tune invisible SQL. The problem could be a badly written query.

Legend

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