Query Performance Issue ! SQL*Plus v/s Oracle Reports…

Have editted on 1st March to include the Execution Plan of the Modified Query. See at the end.

Recently, got a mail from one of my customer on a Query Performance Issue. The Customer mentioned that the Query, when executed from SQL*Plus, takes around 10 Seconds, whereas, when the pasted in Oracle Reports and run through Concurrent Manager, it runs from around 30-40 minutes and is finally killed. The Customer also confirmed that the Query is exactly same i.e it is purely a Copy-Paste of the Query that was run from SQL*Plus and that the Bind Type & Length too are same. The run time plan for both the executions are totally different, with the plan of SQL*Plus better than the plan generated by Oracle Reports. The Question was, “Why is the plan different between SQL*Plus and Oracle Reports ?”

As usual, I asked for a 10053 trace for both the executions, which is one of the best way in investigating Optimizer Calculations. The 10053 trace revealed some important facts about the Query Performance, and therefore helped resolve the issue. Before we dive into the investigation, let me paste the Query and both the Execution Plans.

This is the Query.

SELECT /*+ INDEX(WDD WSH_DELIVERY_DETAILS_TI4) */
             WDD.ORGANIZATION_ID, 
             COUNT(*) LPNS,
             TRP.TRIP_ID,
             RC.CUSTOMER_NUMBER,
             RC.CUSTOMER_NAME, 
             RAA.ADDRESS1 TO_ADDRESS1, 
             RAA.CITY RAA.STATE TO_CITY_STATE, 
             HLA.DESCRIPTION FROM_DESC, 
             HLA.ADDRESS_LINE_1||' '||HLA.TOWN_OR_CITY||' '||HLA.TOWN_OR_CITY)  FROM_ADDRESS1_CITY, 
             WDA.DELIVERY_ID, 
             WDA.PARENT_DELIVERY_DETAIL_ID,
             WDD.DELIVERY_DETAIL_ID, 
             WDD1.CONTAINER_NAME    CONTAINER_NAME, 
             SUM(WDD.REQUESTED_QUANTITY) REQUESTED_QUANTITY, 
             WDD.REQUESTED_QUANTITY_UOM, 
             SUM(ROUND(WDD.GROSS_WEIGHT,0)) GROSS_WEIGHT, 
             WDD.WEIGHT_UOM_CODE,
             WT.VEHICLE_NUM_PREFIX ||' '||WT.VEHICLE_NUMBER VEHICLE_NUMBER,
             MSI.SEGMENT1 ITEM_CODE, 
	     MSI.ATTRIBUTE10 PRT_DESC 
FROM 	apps.WSH_DELIVERY_DETAILS WDD,
     	apps.WSH_DELIVERY_DETAILS WDD1,
        apps.WSH_DELIVERY_ASSIGNMENTS WDA ,
        apps.WSH_DELIVERY_ASSIGNMENTS WDA1,    
        apps.WSH_TRIPS WT, 
        apps.MTL_SYSTEM_ITEMS MSI, 
	TI_RA_ADDRESSES_V RAA,
	TI_RA_CUSTOMERS_V RC ,
        apps.HR_LOCATIONS_ALL HLA,
    	(SELECT DISTINCT  T.TRIP_ID, WDA.DELIVERY_ID 
    	FROM	apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
          	apps.WSH_DELIVERY_LEGS DL,
          	apps.WSH_TRIP_STOPS PICKUP_STOP,  
             	apps.WSH_TRIPS T
	WHERE	WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  = : P_TRIPID_FR 
AND 	TRP.TRIP_ID <= : P_TRIPID_TO 
GROUP BY WDD.ORGANIZATION_ID , TRP.TRIP_ID , RC.CUSTOMER_NUMBER , RC.CUSTOMER_NAME , RAA.ADDRESS1 , 
RAA.CITY , RAA.STATE , HLA.DESCRIPTION , HLA.ADDRESS_LINE_1 , HLA.TOWN_OR_CITY , WDA.DELIVERY_ID , 
WT.VEHICLE_NUM_PREFIX , WT.VEHICLE_NUMBER , WDA.PARENT_DELIVERY_DETAIL_ID , WDD.DELIVERY_DETAIL_ID , 
WDD1.CONTAINER_NAME , WDD.REQUESTED_QUANTITY_UOM , WDD.WEIGHT_UOM_CODE , MSI.SEGMENT1 , MSI.ATTRIBUTE10

The Runtime Plan of the Query when Executed from SQL*Plus.

-----------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                        | Rows  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                             |       |
|   1 |  SORT GROUP BY                                  |                             |     1 |
|   2 |   VIEW                                          | VM_NWVW_1                   |     1 |
|   3 |    SORT UNIQUE                                  |                             |     1 |
|*  4 |     FILTER                                      |                             |       |
|   5 |      NESTED LOOPS                               |                             |       |
|   6 |       NESTED LOOPS                              |                             |     1 |
|   7 |        NESTED LOOPS                             |                             |     1 |
|   8 |         NESTED LOOPS                            |                             |     1 |
|   9 |          NESTED LOOPS                           |                             |     5 |
|  10 |           NESTED LOOPS                          |                             |     5 |
|  11 |            NESTED LOOPS                         |                             |     5 |
|  12 |             NESTED LOOPS                        |                             |     5 |
|  13 |              NESTED LOOPS OUTER                 |                             |    27 |
|  14 |               NESTED LOOPS OUTER                |                             |    27 |
|  15 |                NESTED LOOPS                     |                             |    27 |
|  16 |                 NESTED LOOPS                    |                             |  1908 |
|  17 |                  NESTED LOOPS                   |                             |   427 |
|  18 |                   NESTED LOOPS                  |                             |   127 |
|  19 |                    MERGE JOIN                   |                             |    72 |
|  20 |                     TABLE ACCESS BY INDEX ROWID | WSH_TRIP_STOPS              |   187 |
|* 21 |                      INDEX RANGE SCAN           | WSH_TRIP_STOPS_N1           |   187 |
|* 22 |                     SORT JOIN                   |                             |    72 |
|  23 |                      TABLE ACCESS BY INDEX ROWID| WSH_TRIPS                   |    72 |
|* 24 |                       INDEX RANGE SCAN          | WSH_TRIPS_U1                |    72 |
|  25 |                    TABLE ACCESS BY INDEX ROWID  | WSH_DELIVERY_LEGS           |     2 |
|* 26 |                     INDEX RANGE SCAN            | WSH_DELIVERY_LEGS_N2        |     2 |
|* 27 |                   INDEX RANGE SCAN              | WSH_DELIVERY_ASSIGNMENTS_N1 |     3 |
|  28 |                  TABLE ACCESS BY INDEX ROWID    | WSH_DELIVERY_ASSIGNMENTS    |     4 |
|* 29 |                   INDEX RANGE SCAN              | WSH_DELIVERY_ASSIGNMENTS_N1 |     4 |
|* 30 |                 TABLE ACCESS BY INDEX ROWID     | WSH_DELIVERY_DETAILS        |     1 |
|* 31 |                  INDEX RANGE SCAN               | WSH_DELIVERY_DETAILS_TI4    |     1 |
|* 32 |                INDEX RANGE SCAN                 | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
|  33 |               TABLE ACCESS BY INDEX ROWID       | WSH_DELIVERY_DETAILS        |     1 |
|* 34 |                INDEX UNIQUE SCAN                | WSH_DELIVERY_DETAILS_U1     |     1 |
|  35 |              TABLE ACCESS BY INDEX ROWID        | MTL_SYSTEM_ITEMS_B          |     1 |
|* 36 |               INDEX UNIQUE SCAN                 | MTL_SYSTEM_ITEMS_B_U1       |     1 |
|  37 |             TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCOUNTS            |     1 |
|* 38 |              INDEX UNIQUE SCAN                  | HZ_CUST_ACCOUNTS_U1         |     1 |
|  39 |            TABLE ACCESS BY INDEX ROWID          | HZ_LOCATIONS                |     1 |
|* 40 |             INDEX UNIQUE SCAN                   | HZ_LOCATIONS_U1             |     1 |
|  41 |           TABLE ACCESS BY INDEX ROWID           | HZ_PARTY_SITES              |     1 |
|* 42 |            INDEX RANGE SCAN                     | HZ_PARTY_SITES_N2           |     1 |
|* 43 |          TABLE ACCESS BY INDEX ROWID            | HZ_CUST_ACCT_SITES_ALL      |     1 |
|* 44 |           INDEX RANGE SCAN                      | HZ_CUST_ACCT_SITES_N1       |     1 |
|  45 |         TABLE ACCESS BY INDEX ROWID             | HZ_PARTIES                  |     1 |
|* 46 |          INDEX UNIQUE SCAN                      | HZ_PARTIES_U1               |     1 |
|* 47 |        INDEX UNIQUE SCAN                        | HR_LOCATIONS_PK             |     1 |
|  48 |       TABLE ACCESS BY INDEX ROWID               | HR_LOCATIONS_ALL            |     1 |
-----------------------------------------------------------------------------------------------

Runtime Plan of the Query when Executed from Oracle Reports.

  
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                                       | Name                        | Rows  |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                |                             |       |
  |   1 |  SORT ORDER BY                                  |                             |     1 |
  |   2 |   SORT GROUP BY                                 |                             |     1 |
  |   3 |    VIEW                                         | VM_NWVW_1                   |     1 |
  |   4 |     SORT UNIQUE                                 |                             |     1 |
  |*  5 |      FILTER                                     |                             |       |
  |   6 |       NESTED LOOPS                              |                             |       |
  |   7 |        NESTED LOOPS                             |                             |     1 |
  |   8 |         NESTED LOOPS                            |                             |     1 |
  |   9 |          NESTED LOOPS                           |                             |     4 |
  |  10 |           NESTED LOOPS                          |                             |     4 |
  |  11 |            NESTED LOOPS                         |                             |     1 |
  |  12 |             NESTED LOOPS OUTER                  |                             |     1 |
  |  13 |              NESTED LOOPS OUTER                 |                             |     1 |
  |  14 |               NESTED LOOPS                      |                             |     1 |
  |* 15 |                HASH JOIN                        |                             |     1 |
  |  16 |                 TABLE ACCESS FULL               | HZ_CUST_ACCT_SITES_ALL      | 36983 |
  |* 17 |                 HASH JOIN                       |                             | 23952 |
  |  18 |                  TABLE ACCESS FULL              | HZ_PARTY_SITES              | 76710 |
  |* 19 |                  HASH JOIN                      |                             | 22674 |
  |  20 |                   TABLE ACCESS FULL             | HZ_LOCATIONS                | 72598 |
  |* 21 |                   HASH JOIN                     |                             | 22737 |
  |  22 |                    TABLE ACCESS FULL            | HZ_CUST_ACCOUNTS            | 14291 |
  |* 23 |                    HASH JOIN                    |                             | 22801 |
  |  24 |                     TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B          | 11073 |
  |* 25 |                      INDEX RANGE SCAN           | MTL_SYSTEM_ITEMS_B_N16      | 11073 |
  |* 26 |                     HASH JOIN                   |                             |   130K|
  |  27 |                      TABLE ACCESS FULL          | HR_LOCATIONS_ALL            |   919 |
  |* 28 |                      TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |   130K|
  |* 29 |                       INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_TI4    |   143K|
  |* 30 |                TABLE ACCESS BY INDEX ROWID      | WSH_DELIVERY_ASSIGNMENTS    |     1 |
  |* 31 |                 INDEX RANGE SCAN                | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
  |* 32 |               INDEX RANGE SCAN                  | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
  |  33 |              TABLE ACCESS BY INDEX ROWID        | WSH_DELIVERY_DETAILS        |     1 |
  |* 34 |               INDEX UNIQUE SCAN                 | WSH_DELIVERY_DETAILS_U1     |     1 |
  |  35 |             TABLE ACCESS BY INDEX ROWID         | HZ_PARTIES                  |     1 |
  |* 36 |              INDEX UNIQUE SCAN                  | HZ_PARTIES_U1               |     1 |
  |* 37 |            INDEX RANGE SCAN                     | WSH_DELIVERY_ASSIGNMENTS_N1 |     4 |
  |  38 |           TABLE ACCESS BY INDEX ROWID           | WSH_DELIVERY_LEGS           |     1 |
  |* 39 |            INDEX RANGE SCAN                     | WSH_DELIVERY_LEGS_N1        |     1 |
  |* 40 |          TABLE ACCESS BY INDEX ROWID            | WSH_TRIP_STOPS              |     1 |
  |* 41 |           INDEX UNIQUE SCAN                     | WSH_TRIP_STOPS_U1           |     1 |
  |* 42 |         INDEX UNIQUE SCAN                       | WSH_TRIPS_U1                |     1 |
  |  43 |        TABLE ACCESS BY INDEX ROWID              | WSH_TRIPS                   |     1 |
  -----------------------------------------------------------------------------------------------

Since the investigation is around the Cardinality of each of the row source, I have removed other information (irrelevant for this discussion) from the execution plan. The Query contains a Subquery with alias as TRP. The Tables queried in the Subquery Drives the Good Plan, whereas, the Subquery is unnested and joined at a later stage in the Problematic Plan (Step 37-43). In the Problematic Plan, the Driving table is HR_LOCATIONS_ALL, which is hash joined to WSH_DELIVERY_DETAILS. The Investigation was around the change in the Execution Path. One of the primary goal of Optimizer is to calculate the Number of rows to be returned by each of the Filter Predicate and then evaluate the Access Patch, including the Driving Table, based on the predicate that fetches the least number of rows. It then also evaluates the Join Method, which is to consider Nested Loop Join for small set of rows or Hash Join / Sort Merge Join for larger set of rows.

The text of the TRP Subquery is pasted below

    	(SELECT DISTINCT  T.TRIP_ID, WDA.DELIVERY_ID 
    	FROM	apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
          	apps.WSH_DELIVERY_LEGS DL,
          	apps.WSH_TRIP_STOPS PICKUP_STOP,  
             	apps.WSH_TRIPS T
	WHERE	WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  <=  : P_TRIPID_TO )  TRP

The Good Plan for this subquery is a Sort Merge Join on WSH_TRIPS & WSH_TRIP_STOPS. Both the tables are accessed via an Index on TRIP_ID. Note that though the range predicate is on TRIP_ID of WSH_TRIPS, the column is joined to TRIP_ID of WSH_TRIP_STOPS and therefore, the optimizer scans an Index on these columns to get the relevant rows from the table. The Optimizer calculation says 72 rows from WSH_TRIPS and 187 rows from WSH_TRIP_STOPS, which are merged join to get final 72 rows from the two tables. This calculation is based on the Input values that Optimized peeked into during the hard parse. For the same set of input values, we have a 10053 trace of the Bad Plan and therefore, I immediately concentrated on the cardinality calculation for the two tables mentioned here. Surprisingly, the first caveat that I observed in the 10053 trace was missing Bind Peeked Data and this was enough to get into the Optimizer calculations.

The Peeked Bind Values section of the 10053 trace shows following information.

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=72 off=0
  No bind buffers allocated
 Bind#1
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=24
  No bind buffers allocated
 Bind#2
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=48
  No bind buffers allocated
 Bind#3
  No oacdef for this bind.
 Bind#4
  No oacdef for this bind.

When the Query was executed from SQL*Plus, the values for the Binds were populated and were visible in the Peeked Bind values section of 10053 trace. The missing information means, the Optimizer would fall back to the default calculation for a range predicate. From the trace, the computed cardinality for the two tables, WSH_TRIPS & WSH_TRIP_STOPS, are as under :

Access path analysis for WSH_TRIP_STOPS
***************************************
Access path analysis for WSH_TRIP_STOPS
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for WSH_TRIP_STOPS[PICKUP_STOP] 

  Table: WSH_TRIP_STOPS  Alias: PICKUP_STOP
    Card: Original: 4666397.000000  Rounded: 11666  Computed: 11665.99  Non Adjusted: 11665.99

Access path analysis for WSH_TRIPS
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for WSH_TRIPS[T] 

  Table: WSH_TRIPS  Alias: T
    Card: Original: 1802653.000000  Rounded: 4507  Computed: 4506.63  Non Adjusted: 4506.63

In the absence of the actual values provided to the Bind Variables, the Optimizer computed the Cardinality based on defaults, which in this case is 0.25% (5% for >= and 5% of = : P_TRIPID_FR and TRIP_ID <= : P_TRIPID_TO is too high. Another predicate in the Outer query is ORGANIZATION_ID and based on this predicate, optimizer generates a plan with the smallest rowsource first. The smallest rowsource is HR_LOCATIONS_ALL with 919 rows and is joined to WSH_DELIVERY_DETAILS table, which is accessed via hinted index WSH_DELIVERY_DETAILS_TI4.

To check and confirm this behaviour, I executed an EXPLAIN PLAN FOR for this query, without setting the values for the bind variables, from SQL*Plus. With no values to the Bind, the EXPLAIN PLAN FOR computed the BAD plan from SQL*Plus. Therefore, now I was sure that the problem occurs when the peeked values to the bind are not available. The next concern was “Why Oracle Report does not peek into the Bind Variables before actually generating the plan ?”.

I assume this to be a default behaviour, where the query is parsed first and then binded. Unfortunately, there is not much written on this. A better option is to write a query in an efficient way or hint the query. To resolve the issue, I modified the query and this modified version worked perfectly well on both the environments i.e. SQL*Plus and Oracle Reports. The modification was in the Subquery TRP. The change is pasted below.

    (SELECT TRIP_ID, DELIVERY_ID FROM 
    (SELECT T.TRIP_ID, WDA.DELIVERY_ID, row_number() over(partition by t.trip_id, wda.delivery_id) rnum 
                FROM   apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
                                apps.WSH_DELIVERY_LEGS DL,
                                apps.WSH_TRIP_STOPS PICKUP_STOP,  
                                apps.WSH_TRIPS T
                WHERE   WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  <=  : P_TRIPID_TO)
     WHERE RNUM=1)  TRP

The modification made sure that the TRP subquery becomes the Driving Query. The relevant portion of the execution plan post modification is attached below. The calculation for WSH_TRIPS is still at 0.25% and is therefore computed as 4507, even then, the Optimizer takes this as a Driving Table, which is what we wanted. This again leads to a common argument, which is, Optimizer is a piece of Code that works on Statistics. It is the way we write a query that dominates the Optimizer.


|* 19 |                  WINDOW SORT PUSHED RANK         |                             | 26689 |
|* 20 |                   FILTER                         |                             |       |
|  21 |                    NESTED LOOPS                  |                             | 26689 |
|  22 |                     NESTED LOOPS                 |                             |  7968 |
|  23 |                      NESTED LOOPS                |                             |  4516 |
|* 24 |                       INDEX RANGE SCAN           | WSH_TRIPS_U1                |  4507 |
|  25 |                       TABLE ACCESS BY INDEX ROWID| WSH_TRIP_STOPS              |     1 |
|* 26 |                        INDEX RANGE SCAN          | WSH_TRIP_STOPS_N1           |     1 |
|  27 |                      TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_LEGS           |     2 |
|* 28 |                       INDEX RANGE SCAN           | WSH_DELIVERY_LEGS_N2        |     2 |
|* 29 |                     INDEX RANGE SCAN             | WSH_DELIVERY_ASSIGNMENTS_N1 |     3 |

  20 - filter(:P_TRIPID_FR=:P_TRIPID_FR AND "T"."TRIP_ID"=:P_TRIPID_FR AND "PICKUP_STOP"."TRIP_ID"<=:P_TRIPID_TO))
  28 - access("DL"."PICK_UP_STOP_ID"="PICKUP_STOP"."STOP_ID")
  29 - access("WDA"."DELIVERY_ID"="DL"."DELIVERY_ID")
       filter("WDA"."DELIVERY_ID" IS NOT NULL)

Exadata v/s Solid State Disk ! Performance & Scalability Comparison.

During one of my Exadata Presentation, one of the attendees asked about the Performance & Scalability Difference between EXADATA and SSD. He mentioned that the Performance POC for one of their critical application on SSD and Exadata gave similar results. Before replying to his query, I asked him “What was the Average CPU Utilization during both the POC’s ?”. While the answer to my question was interesting, thought of writing a short blog on this debate and at the end will be the response by the attendee to my question.

Lot has been written on the comparison between the two. For example, visit asktom.oracle.com and Kevin Closson’s blog. I will just write on my reply with a brief explanation.

EXADATA is one of the biggest breakthrough in achieving fast computing and therefore, clearly overpowers other technologies around it. While other technologies might give you a similar performance, but investing only for performance is not the only Organization Goal. The primary reason, which I feel, Exadata scores over others is the Scalability Factor. With that said, while Exadata gives Extreme Performance, it improves the Scalabiity as well. SSD and any other technologies are Hardware components, whereas, Exadata is a combination of Hardware and Intelligent Software and this combination makes it Faster and Scalable.

In an Oracle Database or an Application deployed on top of Oracle, Latches are considered to be a Performance and Scalability Inhibitor. Every Block access (into the cache or from the cache), requires Latch and if these latch gets are in access, these excessive visits to the cache will burn the CPU power thus impacting the performance and scalability. Exadata has changed this approach, by introducing a faster hardware that scans the required blocks and an Intelligent Software on top of it, to filter only the required column data directly to the Private Memory Area (PGA) of the User Process. This Intelligence bypasses the buffer cache thus avoiding a costlier step of Latching. This has a huge impact on the CPU Cycles and therefore, if the Database Server has enough CPU Power available, this additional CPU Power can be translated to Scalability.

Usually, in an Oracle Database on a traditional (non-exadata) storage, the blocks are read from the Disk to the Buffer Cache and then the required column data is filtered and fetched to the Private Memory of the User Process. Reading a Block from Disk to Cache, filtering the required data from Cache involves CPU. All of these consume CPU Cycles on the Database Server. As mentioned, in an Exadata Storage, the combination of Hardware and Software works together to eliminate the costlier from the Database Server. From this explanation, it is quite evident that Exadata is not only improves performance, but also provides scalability. All of this is achieved without making any changes in the application. However, further optimization in an Exadata is also possible.

Now to the reply on my Question “What was the Average CPU Utilization during both the POC’s ?”. The reply was that through out the POC, the CPU Utilization on an Exadata Machine was always far below than the POC on SSD. Solid State Disk’s would provided faster access to the blocks that are read from the disk, but will not eliminate the costlier steps of latching the blocks from the Database Server. Therefore, savings in CPU Cycles is not expected. Infact, it may worsen the performance.

In one of the paragraph above, I mentioned about the steps as a part of row fetching that an Oracle Database, on an non-exadata storage, performs on behalf of a User. This is, a block is read from Disk to the Cache and then the required columns and row data is fetched to the Private Memory area, which is to the PGA, of the User Process. This contradicts to the steps that I mentioned in one of my previous blog on Consistent Gets. This blog rectifies the error in my assumption on the steps performed and mentioned in the said previous blog.

SQL*Plus Connection Slow ! Library Cache Lock…..

Oracle Database 11g introduced a new security feature, which is meant to protect a users’ password. Refer to section “What are the Oracle Password built-in Password Protections ?” of Chapter 3 of Oracle Database Security Guide. The document says, if a user tries to log in to the database more than 3 times with an Invalid Password, Oracle Database delays each login after third try. Most of them are unware of this new Security feature, and therefore, this sometimes creates a panic situation and an unexpected escalations to Oracle Support.

Recently, one of my customer reported a slow database connection to one of their Application Schema. The connections to other schemas, except the problematic one, were going through perfectly fine. The application connects to the Database through Weblogic Server and the problem summary mentioned that while the Weblogic Server is able to connect successfully (with zero delay) to the same schema on UAT, it takes around 30 seconds to connect to the same user on production. Therefore, concern over an issue with Database Server was raised. A severity 1 SR was raised and operating system truss output for the problematic and non-problematic user was uploaded as well. The code path of both the output was exactly same, except for the time taken for the problematic user.

One simple logic to understand was, that if it is an Oracle Server Issue, then it should cause the delay in connection for all the Schema’s and not just to one. Therefore, there had to be some problem with this Schema. It was thoroughly checked whether there are any AUDITING or ON-LOGON Triggers on this Schema. Database Audit Trail was enabled for all the users and therefore, this was not an exception for this schema. There were no ON-LOGON triggers on this User. Therefore, what was causing this delay was a big question.

Customer noticed this due to the fact that they have couple of scheduled cron jobs that runs every 30 seconds. These jobs usually takes, 5-10 seconds to complete, but since last few days, they have observed these overlapping. While Investigating this issue, I noticed that when we issue sqlplus username/password, the session waits on Library Cache Lock for around 15-20 seconds and then authenticates the user. This was the case with the scheduled jobs as well. Each of these jobs were waiting on the latch. Why should an SQL*Plus command wait on Library Cache Lock ? For other users, it could immediately authenticate.

In this case, Database Audit Trail, which inserts a record in aud$ came to our rescue. In the aud$ table, we noticed that for last 3-4 days, the number of failed attempts had increased dramatically. Further, it was also revealed that these connections from our the Application Servers configured at a DR site and were pointing to the production database server using wrong password. The error message reported in the aud$ was 1017, which is “Invalid Username / Password”.

The library cache lock wait is seen due to the fact that the account status gets updated due to incorrect login and is maintained in the library cache. This is to prevent password guessing attack as there’s a sleep in the code when incorrect login attempts exceed count of 3. These sleeps cause the latch, as the process holding it is yet to release the lock. While this behaviour is a Security enhancements, it can be disabled by setting event 28401.

India Localization ! R12 Upgrade Performance Issues…..

First, let me wish my readers a very Happy & Prosperous New Year.

While the heading of this blog relates to India Localization and Performance Issues post R12 Upgrade, the solution that I talk about actually translates to Effective Query Writing. One of my customer is in a final phase of Apps R12 Upgrade and is facing some slowness in a Test Environment. While I am working on all of these, I thought of writing on One Issue at a time, as and when, these are resolved. The database is 11gR2. Slowness in Matched Receipt Application was reported and an 10046 Trace file was sent to me for analysis. From the trace file, I could see 2 Queries that were consuming most of the CPU time and resolving these would bring down the execution time. The Queries are mentioned below :

SELECT wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
FROM	wsh_new_deliveries wnd, 
	wsh_delivery_assignments wda, 
	wsh_delivery_details wdd, 
	jai_om_oe_so_lines jsl 
WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
AND 	wnd.organization_id = :1 
AND 	wda.delivery_id = wnd.delivery_id 
AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
AND 	wdd.source_code = 'OE'  
AND 	wdd.organization_id = :2 
AND 	wdd.source_header_id = jsl.header_id 
AND  	wdd.source_line_id = jsl.line_id 
AND 	((:3 = 'UN MATCH' 
AND 	EXISTS (select ref_line_id 
		from 	JAI_CMN_MATCH_RECEIPTS 
		where 	ref_line_id = wda.delivery_detail_id 
		AND 	order_invoice ='O' 
		and 	ship_status IS NULL) ) OR
  	(:4 = 'MATCH' 
  	AND NOT EXISTS (select ref_line_id 
  			from 	JAI_CMN_MATCH_RECEIPTS 
  			where 	ref_line_id = wda.delivery_detail_id 
  			AND  	order_invoice ='O' 
  			and 	ship_status IS NULL) ) ) 
  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
  GROUP BY wda.delivery_id ;

SELECT ott.name, ott.transaction_type_id
FROM 	oe_transaction_types_tl ott, 
	oe_order_headers_all oh, 
	oe_order_lines_all ol,
  	jai_om_oe_so_lines jsl 
WHERE 	(NAME LIKE :b1) 
AND 	( ott.transaction_type_id = oh.order_type_id 
AND 	oh.header_id = ol.header_id 
AND 	ol.header_id = jsl.header_id 
AND 	ol.line_id = jsl.line_id 
AND 	ol.ship_from_org_id = :2 
AND	ol.sold_to_org_id = nvl(:3, ol.sold_to_org_id ) ) 
GROUP BY ott.name, ott.transaction_type_id

Since the solution for both the Queries are same, to make this smaller, a detailed explanation on Query 1 wll dominate the blog. The Execution Plan of Query 1 is pasted below and the problematic part is marked as bold (and arrow marked, in case, some browser does not display characters in BOLD). I have also mentioned the run time values of the Bind Variables from v$sql_bind_capture. These values usually help to run the query from SQL*Plus and validate the working of a query.

Bind Capture Value
------------------
SQL_ID        CHILD_ADDRESS    NAME         POSITION DATATYPE_STRING      VALUE_STRING
------------- ---------------- ---------- ---------- -------------------- --------------------
byzqm6cf3j226 07000009D7614120 :1                  1 NUMBER               52
byzqm6cf3j226 07000009D7614120 :2                  2 NUMBER               52
byzqm6cf3j226 07000009D7614120 :3                  3 CHAR(32)             MATCH
byzqm6cf3j226 07000009D7614120 :4                  4 CHAR(32)             MATCH
byzqm6cf3j226 07000009D7614120 :5                  5 CHAR(32)             NULL
byzqm6cf3j226 07000009D7614120 :6                  6 NUMBER               NULL

Plan hash value: 2965431451

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |       |       | 64721 (100)|          |
|   1 |  SORT GROUP BY                     |                             |     1 |    67 |            |          |
|   2 |   CONCATENATION                    |                             |       |       |            |          |
|*  3 |    FILTER                          |                             |       |       |            |          |
|*  4 |     FILTER                         |                             |       |       |            |          |
|   5 |      NESTED LOOPS                  |                             |     1 |    67 | 64704  (61)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                             |     1 |    55 | 64704  (61)| 00:00:01 |
|   7 |        NESTED LOOPS                |                             |     7 |   294 | 64691  (61)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |     7 |   217 | 64677  (61)| 00:00:01 |<--------
|*  9 |          INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_TI6    |   142K|       |  4194  (91)| 00:00:01 |<--------
|* 10 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     1 |    11 |     2  (50)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     0   (0)|          |
|* 12 |        INDEX RANGE SCAN            | WSH_NEW_DELIVERIES_TI1      |     1 |    13 |     2  (50)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN             | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |     0   (0)|          |
|* 14 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 16 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 18 |    FILTER                          |                             |       |       |            |          |
|* 19 |     FILTER                         |                             |       |       |            |          |
|  20 |      NESTED LOOPS                  |                             |     1 |    67 |    16  (50)| 00:00:01 |
|  21 |       NESTED LOOPS                 |                             |     1 |    54 |    14  (50)| 00:00:01 |
|  22 |        NESTED LOOPS                |                             |     1 |    43 |    12  (50)| 00:00:01 |
|* 23 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |     1 |    31 |    12  (50)| 00:00:01 |
|* 24 |          INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_N2     |     9 |       |     6  (50)| 00:00:01 |
|* 25 |         INDEX RANGE SCAN           | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |     0   (0)|          |
|* 26 |        TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS    |     1 |    11 |     2  (50)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN           | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     0   (0)|          |
|* 28 |       INDEX RANGE SCAN             | WSH_NEW_DELIVERIES_TI1      |     1 |    13 |     2  (50)| 00:00:01 |
|* 29 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 30 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 31 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 32 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   3 - filter(((:B4='UN MATCH' AND  IS NOT NULL) OR (:B3='MATCH' AND  IS NULL)))
   4 - filter(:B1 IS NULL)
   8 - filter(("WDD"."SOURCE_CODE"='OE' AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL AND
              "WDD"."CUSTOMER_ID"=NVL(:B2,"WDD"."CUSTOMER_ID")))
   9 - access("WDD"."ORGANIZATION_ID"=:B5)
  10 - filter("WDA"."DELIVERY_ID" IS NOT NULL)
  11 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
  12 - access("WND"."ORGANIZATION_ID"=:B6 AND "WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  13 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  14 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  15 - access("REF_LINE_ID"=:B1)
  16 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  17 - access("REF_LINE_ID"=:B1)
  18 - filter(((:B4='UN MATCH' AND  IS NOT NULL) OR (:B3='MATCH' AND  IS NULL)))
  19 - filter(:B1 IS NOT NULL)
  23 - filter(("WDD"."ORGANIZATION_ID"=:B5 AND "WDD"."SOURCE_CODE"='OE' AND
              "WDD"."CUSTOMER_ID"=NVL(:B2,"WDD"."CUSTOMER_ID")))
  24 - access("WDD"."SOURCE_HEADER_ID"=:B1)
  25 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  26 - filter("WDA"."DELIVERY_ID" IS NOT NULL)
  27 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
  28 - access("WND"."ORGANIZATION_ID"=:B6 AND "WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  29 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  30 - access("REF_LINE_ID"=:B1)
  31 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  32 - access("REF_LINE_ID"=:B1)

WSH_DELIVERY_DETAIL is the diriving Table, which is scanned via an Index on ORGANIZATION_ID (Index WSH_DELIVERY_DETAILS_T16). See Step 9 from the Predicate Information Section.

Analysis

The Logical Reads of this query is around 0.23 Million per Execution. For Analysis, it was important to validate whether the Driving Table and Access Path chosen by the optimizer is Optimal. Therefore, I first targetted Step 8 & 9 of the Execution plan, which is the driving step. Step 9 says an Index Scan and the estimated rows from this step is around 142k. This means, after scanning and filtering the data for an Organization_ID, the optimizer estimates around 142k rows will be returned and passed to the next step, which is to visit the table to filter additional non-indexed predicates. From Step 8, it can be seen that these non-indexed predicates are SOURCE_CODE, CUSTOMER_ID and SOURCE_HEADER_ID. Once the non-indexed predicates are applied, the optimizer estimates 7 rows to be returned by the final step 8. Based on this, the next action, which is to come out with a Optimal Access Path. In this case, it is a Nested Loop Join. Only 7 rows out of 142K seemed to be a problem and therefore, the next step was to validate the optimizer calculation. Following table shows the Statistics that Optimizer takes into consideration and next we shall manually run the formula to check the calculation.

## Number of Rows in the Table

SQL> @table_stats
Enter value for 1: WSH_DELIVERY_DETAILS
old   2: where table_name=upper('&1')
new   2: where table_name=upper('WSH_DELIVERY_DETAILS')

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO DEGREE
------------------------------ --- ---------- ---------- --------- --- ----------
WSH                            NO    11148400     673435 14-DEC-11 YES          1

## Column Statistics (Only relevant columns specified in the Query are displayed)

SQL> @column_stats
Enter value for 1: WSH
old   3: where owner='&1'
new   3: where owner='WSH'
Enter value for 2: WSH_DELIVERY_DETAILS
old   4: and   table_name='&2'
new   4: and   table_name='WSH_DELIVERY_DETAILS'

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
CUSTOMER_ID                            9250      31395 .000108108 NONE
ORGANIZATION_ID                          78          0 .012820513 NONE
SOURCE_CODE                               2          0         .5 NONE
SOURCE_HEADER_ID                    1171510    1090944 8.5360E-07 NONE

The table has around 11 Million Rows. The Optimizer Calculation for Number of Rows expected from Index Scan is = 11148400*1/78 (Num_Distinct of Organization_Id) = 142928.905 = 142k. This matches the calculation. Step 8 is a filter from non-indexed columns used in the WHERE clause. Therefore, it is 142928*1/9250*1/2 = 7.72 = 7 (9250 for Customer_id and 2 for Source_code). Optimizer, in this case, has not considered source_header_id in the computation, as this column is also used in the JOIN Predicate, else the expected rows would have gone down further. This miscalculation of 7 Rows is enough to generate a wrong execution plan, particularly if the values against the bind variables :5 and :6 is NULL. Again, let us revisit the WHERE predicate of the query that FILTERS a condition on Customer_id & Source_code.


AND 	wdd.source_code = 'OE'  <-- Non Indexed Column 
AND 	wdd.organization_id = :2 <--- Indexed Column (Bind Value passed as 52)
  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) <--- Non Indexed Column (Bind Value passed as NULL)

As mentioned in my previous paragraph, the NULL values means a a condition wdd.customer_id = wdd.customer_id, but Optimizer takes this predicate into calculation and therefore, misbehaves. While better indexing strategy or more detailed statistics would help Optimizer calculate nearly accurate cardinality, this can have an impact on other queries as well. Therefore, a better strategy would be to write the query, as per best practices, which would generate an Optimal Execution Plan.

The change would be, since the values for :5 & :6, which is for customer_id and source_header_id respectively, is passed as NULL, these predicates are unwanted in the query. When the Query was executed, with these 2 columns removed, the execution time and the I/O’s reduced. However, we cannot always assume that these will be NULL and therefore, the query needs to be written based on the runtime choice. In this case, since there are 2 columns, which could be either NULL or NOT NULL, there would be 4 Combinations by way of which this query can be written. These Combination will be :>/p>

   1. Customer_ID is NULL and Source_header_id is NULL
   2. Customer_id is NULL and Source_header_id is NOT NULL
   3. Customer_Id is NOT NULL and Source_header_id is NULL
   4. Customer_Id is NOT NULL and Source_header_id is NOT NULL

Since the choice is between 2 values (NULL & NOT NULL), the number of permutation and combination would depend on the number of columns. In this case, 2^2 = 4. If there are 3 columns, it will be 2^3=8. Following table shows the change in the WHERE clause to accommodate the 4 Combinations mentioned above.

## MODIFICATION 1 both customer_id and source_header_id is null

SELECT /*+ MODIFICATION 1 REMOVED BOTH NULL COLUMNS */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
--	  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
--	  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
	  GROUP BY wda.delivery_id;

## MODIFICATION 2 customer_id is null and source_header_id is not null

SELECT /*+ MODIFICATION 2 CUSTOMER ID IS NULL AND SOURCE ID IS NOT NULL */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
--	  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
	  AND 	wdd.source_header_id = :5 
	  GROUP BY wda.delivery_id;

## MODIFICATION 4 customer_id is not null and source_header_id is not null

SELECT /*+ MODIFICATION 3 BOTH ARE NOT NULL COLUMNS */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
	  AND 	wdd.customer_id = :5 
	  AND 	wdd.source_header_id = :6 
	  GROUP BY wda.delivery_id;

## MODIFICATION 4 customer_id is not null and source_header_id is null

SELECT /*+ MODIFICATION 4 CUSTOMER_ID is NOT NULL */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
	  AND 	wdd.customer_id = :5
--	  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
	  GROUP BY wda.delivery_id;

The change require an IF..THEN..ELSE loop to check for the values of the Bind Variables at Run Time and execute the query that is relevant. With this change, the Optimizer calculated correct cardinality based on the predicates actually used by the User and came up with a perfect execution plan. Below is the plan, when the query was run at the customer site for both values as null.

Plan hash value: 466680131

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |       |       |       |   114K(100)|          |
|   1 |  SORT GROUP BY                   |                             |     1 |    62 |       |   114K (59)| 00:00:01 |
|*  2 |   FILTER                         |                             |       |       |       |            |          |
|   3 |    NESTED LOOPS                  |                             |   511 | 31682 |       |   114K (59)| 00:00:01 |
|*  4 |     HASH JOIN                    |                             |   511 | 25550 |  2400K|   114K (59)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS        | 64471 |  1636K|       | 62327  (59)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN           | WSH_DELIVERY_DETAILS_TI6    |   142K|       |       |  4194  (91)| 00:00:01 |
|   7 |      NESTED LOOPS                |                             |       |       |       |            |          |
|   8 |       NESTED LOOPS               |                             | 72604 |  1701K|       | 48471  (55)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | WSH_NEW_DELIVERIES_TI1      | 16146 |   204K|       |  2197  (91)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N1 |     5 |       |       |     0   (0)|          |
|  11 |       TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     4 |    44 |       |     4  (50)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN             | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |       |     0   (0)|          |
|* 13 |    TABLE ACCESS BY INDEX ROWID   | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |       |     8  (50)| 00:00:01 |
|* 14 |     INDEX RANGE SCAN             | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |       |     6  (50)| 00:00:01 |
|* 15 |    TABLE ACCESS BY INDEX ROWID   | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |       |     8  (50)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN             | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |       |     6  (50)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(((:B3='UN MATCH' AND  IS NOT NULL) OR (:B4='MATCH' AND  IS NULL)))
   4 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
   5 - filter(("WDD"."SOURCE_CODE"='OE' AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL))
   6 - access("WDD"."ORGANIZATION_ID"=:B2)
   9 - access("WND"."ORGANIZATION_ID"=:B1)
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  10 - access("WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter("WDA"."DELIVERY_ID" IS NOT NULL)
  12 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  13 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  14 - access("REF_LINE_ID"=:B1)
  15 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  16 - access("REF_LINE_ID"=:B1)

With the same indexes, but slight modification in the Query, the optimizer has come up with a plan that does a HASH Join (as against Nested Loop Join) based on the number of rows expected from WSH_DELIVERY_DETAILS Table.

This modification was applied via an application patch, which resolved the Match Receipt Application Performance Issues. The benefit was due to the reduced number of Logical Reads.

How Challenging is the task of a Performance Optimization Expert ? A real life example…

It is a well known fact that, most of the times, a Performance Optimization Expert is usually involved in Reactive mode. An Ongoing Performance Issue is easy to diagnose, but what if a severe performance issue was reported a day before and this caused a Crash of a production system ?

In case of an Issue that occured in the past, while most of the experts call for AWR reports and rely on ASH data available, but in my opinion, the analysis and recommendation based on AWR and ASH reports does not guarantee 100% accuracy. To prove this point, I have a real life scenario to share.

One of my customers added another node to an existing 2 node RAC production setup. Post completion of this activity, the system was released to the end users. In this case, end users are the customers across the globe. In the afternoon, severe slowness was reported and before anyone could analyze the issue, all the Application Servers / Web Servers Crashed and had to be restarted. There was a severe escalation and everyone pointed the finger of blame on the addition of a 3rd Node, as this was the only change in the entire production system. The Database Administrators also mentioned that while investigating the issue, they could observe severe “Cursor:pin” related waits. There were around 400+ sessions waiting for the “Cursor:pin” and the queries that were stuck were running successfully on other 2 nodes. Therefore, it was assumed to be a BUG or some issue related to 3rd Node. Since the Application architecture involved connections thru connection pool, when all the connections pool got exhausted, severe slowness and then a crash was reported.

A detailed investigation was initiated the next day. AWR report for the problem period and the ASH reports also revealed that almost 90% of the time the sessions were waiting on “Cursor:Pin” and the other 2 nodes the AWR was clean. While we were still investigating the issue, one of the DBA also mentioned that all other queries were working absolutely fine and, the problem only existed with the two tables as the queries on these 2 objects were waiting on the Mutexes and the executions of these queries were ZERO. The executions of the queries on these 2 objects were 1000+. Further, he also mentioned that they tried running the queries on these 2 objects from SQL*Plus, even these hung and had to be cancelled using CTRL-C. This raised some suspicion, as why the problem was only limited to 2 Objects and therefore, we started concentrating more these 2 Objects.

Analysis

These 2 Objects were actually a Synonym to 2 remote tables and 90% of the application modules get data from the remote database, using Database Link. Does it mean that during the problem period, there was some connectivity issue between the two Servers ? Let us simulate this with an example.

To simulate this, I created a database link between my 11g and 10g instance and then created a Synonym on EMP table, which resides in 10g Database. Further, to simulate production load, I created a database procedure that selects a row from this remote table and scheduled around 1000 sessions using DBMS_JOB. With the 10g Listener UP and Running, the job runs perfectly fine, therefore, I stopped the listener of 10g Database, as if there was some problem with the connectivity and re-scheduled the job.


connect vivek/vivek
create database link o10g connect to vivek identified by vivek using 'orcl10g';
create synonym emp_remote for emp@o10g;

create or replace procedure test_load as
e_name  varchar2(30);
l_eno	number;
begin
  l_eno:=round(dbms_random.value(7000,8000),0);
  select ename into e_name from emp_remote where empno=l_eno;
end;
/

alter session set nls_date_format='Dd-MM-YYYY HH24:MI:SS';

select sysdate from dual;

SYSDATE
-------------------
15-11-2011 00:45:42

## I Scheduled the Jobs to run at 00 Hours & 47 Minutes

declare
l_job	number;
begin
   for i in 1..1000
     loop

       dbms_job.submit(l_job,'test_load;',trunc(sysdate)+00/24+47/(24*60));
       commit;
     end loop;
end;
/

Once the Jobs started, the wait events and the execution of the query hung on “Cursor:Pin” are as under :

       SID EVENT                                  P1         P2         P3 SQL_ID
---------- ------------------------------ ---------- ---------- ---------- -------------
         1 pmon timer                            300          0          0
         4 DIAG idle wait                          5          1       1000
        10 Streams AQ: qmn coordinator id          0          0          0
        13 Streams AQ: qmn slave idle wai          1          0          0
        15 Streams AQ: waiting for time m          0          0          0
        26 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        28 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        30 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        32 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        34 library cache lock             1269367872 1235709864          2 f20gyq4m5fh3c
        35 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        42 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        47 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        49 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        51 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
        52 library cache lock             1269367872 1235628808          2 f20gyq4m5fh3c
        54 library cache lock             1269367872 1269117072          2 f20gyq4m5fh3c
       125 VKRM Idle                               0          0          0
       126 VKTM Logical Idle Wait                  0          0          0
       127 DIAG idle wait                          5          1       1000
       131 smon timer                            300          0          0
       136 Space Manager: slave idle wait          0          0          0
       139 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       142 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       143 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       149 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       153 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       154 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       155 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       156 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       170 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       172 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c
       175 library cache lock             1269367872 1234978972          2 f20gyq4m5fh3c
       176 cursor: pin S wait on X         643252332   10682368     327680 f20gyq4m5fh3c

SQL> @sql_sqlid
Enter value for sql: f20gyq4m5fh3c
old   1: select sql_id, sql_text, executions, buffer_gets from v$sqlarea where sql_id='&sql'
new   1: select sql_id, sql_text, executions, buffer_gets from v$sqlarea where sql_id='f20gyq4m5fh3c'

SQL_ID        SQL_TEXT                                           EXECUTIONS BUFFER_GETS
------------- -------------------------------------------------- ---------- -----------
f20gyq4m5fh3c SELECT ENAME FROM EMP_REMOTE WHERE EMPNO=:B1                0           0

This was the exact behaviour that was reported on the 3rd Node production setup. I compared the alert.log of my Database and the production 3rd Node and the common observation was an entry in both the environment (this is from my database). In production, the error was related to tns timeout.


Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl10g)(CID=(PROGRAM=oracle)(HOST=localhost.localdomain)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 15-NOV-2011 01:01:30
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12541

TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511

TNS-00511: No listener
    nt secondary err code: 111
    nt OS err code: 0

At this point, when we discussed this with the customer, one of the Application team member admitted that as a part of the checklist, they missed out on Opening of a Port from the database server to the Server hosting the remote database and this was realized in the afternoon. They were not aware of the implication it had on the production setup.

In this case, the AWR & ASH reports revealed “Cursor:Pin” waits but were not sufficient in revealing the actual cause of the issue. It required further analysis, therefore, I usually insist on production access to diagnose a Performance Issue whether it was in the past or is an ongoing issue.

AIOUG Annual Event Sangam 2011

All India Oracle User Group is back with its Annual Conference and this time, it is back to Bangalore. The event, Sangam 2011, will be held on 9th and 10th of December 2011. This time, Arup Nanda will be presenting 2 half days session. Apart from Arup, there are several other speakers who would be presenting and this again makes the event an Interesting one to attend. This time though I am not presenting, I will be attending both the days as it gives me an oppurtunity to meet up the Oracle Community. Visit http://www.aioug.org to register for the event. See you in Bangalore 🙂

parse count (failures) – How do I find queries causing failures ?

In my last blog on Hard Parses and failures posted on September 3, 2011, I missed to write about the ways to find the Queries causing “parse count (failures)”. I received few mails from some of readers wanting to know on how to get these. Since these queries fail, these are not stored in the Shared Pool and therefore, it is not possible to get these from v$ views. Secondly, since these are not stored in the Shared Pool, everytime these queries are executed, these are hard parsed, thus causing unwanted contention on Library Cache latches.

A possible way to get these is 10046 traces. I used these traces to get the Queries that were causing these failures to occur. Yesterday, I was working at another customer site, where I could see some failures happening. These are again around 25% to 30% of the total Hard Parses. Therefore, if we eliminate these, total hard parses / second would come down. Following query helps me check, whether the statistics related to hard parses are alarming.

set linesize 132
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select sysdate, name, value from v$sysstat where name like 'parse%';
exec dbms_lock.sleep(10);
select sysdate, name, value from v$sysstat where name like 'parse%';

I execute a query against v$sysstat, wait for 10 seconds and re-execute the same. Therefore, the incremental figure is for 10 seconds duration. From this, it is very easy to compute the parses/second ratio. Following is the output of the query taken yesterday :

SQL> @hard_parse

Session altered.

Elapsed: 00:00:00.00

SYSDATE             NAME                                                                  VALUE
------------------- ---------------------------------------------------------------- ----------
20-09-2011 10:28:14 parse time cpu                                                       117213
20-09-2011 10:28:14 parse time elapsed                                                   390923
20-09-2011 10:28:14 parse count (total)                                                54294681
20-09-2011 10:28:14 parse count (hard)                                                   766713
20-09-2011 10:28:14 parse count (failures)                                               177555
20-09-2011 10:28:14 parse count (describe)                                                  204

6 rows selected.

Elapsed: 00:00:00.03

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.00

SYSDATE             NAME                                                                  VALUE
------------------- ---------------------------------------------------------------- ----------
20-09-2011 10:28:24 parse time cpu                                                       117216
20-09-2011 10:28:24 parse time elapsed                                                   390969
20-09-2011 10:28:24 parse count (total)                                                54303954
20-09-2011 10:28:24 parse count (hard)                                                   767011
20-09-2011 10:28:24 parse count (failures)                                               177637
20-09-2011 10:28:24 parse count (describe)                                                  204

6 rows selected.

Elapsed: 00:00:00.02
SQL> select 767011-766713, 177637-177555 from dual;

767011-766713 177637-177555
------------- -------------
          298            82

In this case, the Hard Parses are 29.8 per second and failures are 8.2, which is around 28% of the total hard parses. To check for the queries causing these, I generated enabled 10046 trace at system level, during less concurrency time (non-peak hours), and issued grep -i “PARSE ERROR” *.trc. This gave me the name of the trace files and from these traces, it was very easy to figure out the queries that were failing. In the trace file, search for PARSE ERROR and you will get the query, alongwith the reason for its failure, which is ora-942 (table or view does not exists), as can be seen in err column.

grep -i "PARSE ERROR" *.trc
RAC1_ora_1966688.trc:PARSE ERROR #1:len=157 dep=0 uid=38 oct=3 lid=38 tim=2611876001420 err=942
RAC1_ora_1966688.trc:PARSE ERROR #1:len=157 dep=0 uid=38 oct=3 lid=38 tim=2611876029670 err=942
RAC1_ora_1966688.trc:PARSE ERROR #5:len=95 dep=1 uid=0 oct=3 lid=0 tim=2611876430154 err=942

Do we need to take care of Hard Parses only when Library Cache related Latches are on top ?

I just concluded a 3 months Production Optimization Project. The problem summary was an overall slowness of the production system, which is a three tier architecture. The Production System includes a 2 Node 10.2.0.5 Database with 4 Application Servers configured with Connection Pool. As with all my Performance Project, this time too I started off with a one round of discussion with all the stake holders. These stake holders manage the production system and were called upon to express their opinion on the overall slowness. While everyone voiced together that the slowness is consistent the entire day, they also mentioned that they are confident that the issue is with the Oracle Application Server and the way it manages the Connection Pool. The rationale behind this assumption was the Connection Pool Statistics, which says that as the load increases, the number of Connection Pools configured increase drastically and are not freed until the load comes down. The peak starts at 11 am and, since the production system is slow, the load comes down by 9 pm. The business hours of the customer is 9 am to 5 pm.

Initial investigation revealed that with Automatic Shared Memory Management enabled, the Shared Pool had grown upto 40GB. This is pretty high. I have not seen any configuration that requires such a huge Shared Pool. This was surely a cause of concern and therefore, this was further drilled down to one of a serious bug in the application. This was Hard Parse.

An Application or Database flooded with Unshared Application queries is considered as an Unscalable Application. Slowly or Gradually, as the User Load increase, the performance issues are bound to come. In this case, the Hard Parse / Second ratio was around 180 per second. While there were many queries that were using Literals, the most surprising part was the Hard Parse failures, which were almost 68% of the total Hard Parses. Therefore, once a patch to fix the queries that were using Literals was applied, there was no significant improvement.

The most challenging part of this assignment was the reluctance from the Application Team to accept the Hard Parses or the Parse failure to be an Issue. The debate from the Application Team was that reducing the Hard Parses would not yield any benefit as they do not see any Library Cache related latches on top. They have been taught to see the AWR reports and look to the top events. Reducing the number of waits of the top most component seemed to be on top of the list for Application team. While this is true, to some extent, but in case of Hard Parses, this would not work.

This is the reason, the title of my blog is “Do we really need to take care of Hard Parses only when Library Cache related Latches are on top ?”. In this case, the Hard Parses can be easily related to the contention that we observed on the Connection Pool. Usually, the response time of a Connection Pool or a Shared Server Architecture should be less than a Second. If this goes high, we might see a slowness or contention on Connection Pool, as other users may have to wait for the pool to be released. This again explains, why the number of connections pools increased as the load increases.

Hard Parses are a performance killer, also called as a Silent Killer. Many a times, Hard Parses does not cause significant waits on Library Cache latches. It depends on the resources available at the Database Server. In this case, it was a 2 Node RAC with 56 CPU’s on each machine. The Hardware configuration was enough and therefore, the contention was not that significant to cause Latch Free waits. As we all know, Hard Parse takes more time than a Soft Parse or a No Parse and this resulted in a signifcant waits or contention on Connection Pool. Also, as mentioned above, Hard Parses are Silent Killer. With that said, if these are not taken care of at a initial stage, this can bring down the Production System (as it did in the case of my Customer).

To summarize, Hard Parses are to be addressed even if these are not visible as a Latch Contention. In case of my customer, as soon as the Hard Parses and Parse failures were fixed, the performance of the production system improved drastically and was consistent through out the day. The users were able to enter their transactions in the business hours. Further, the contention on the connection pool came down as well.

What has triggered this Sudden Performance Degradation ? A Generic Question…

It is a well known fact that most of the time, a Performance Tuning Consultant is involved for a Reactive Support which is due to a severe performance issue. During such involvement, one of the generic question that a Performance Tuning Expert or Consultant has to answer is “What has triggered this Sudden Performance Degradation ?” or a Comment like “Oracle Optimizer is an Issue as it triggered this Degradation and everything came back to normalcy after reverting the Optimizer Stats”. The challenge for the Consultant is to prove that Optimizer is not an Issue and it is the Application behaviour or Indexing Strategy that has caused this issue. This Strategy acted as a slow poison and then a point reached when it finally gave up. During one of my presentation at All India Oracle User Group, I presented on Developing a Scalable Application and this was one of my slide, which I felt would be of use for my readers as well.

In this blog, I will answer to the Question and Comment mentioned above. This would be one of the case that can trigger a sudden degradation. The test case will be around Optimizer Statistics. I have been asked this question by the Customers and concerns were raised over the way Optimizer works or use these Statistics. Some of the Concerns raised are :

  1. Why do I generate fresh Stats when I am Satisfied by the Application Performance on current Stats ?
  2. I generated Statistics over the weekend and suddenly the performance degraded. Finally, I reverted back the statistics and everything came back to normalcy.
  3. What is the cause of this Sudden Degradation ?

While lot has been written on Concern 1 (the way Optimizer works and the change in Optimizer behaviour in 10g, I will answer Concern 2, and Concern 3 will be automatically answered. As usual, all this will be well supported and demonstrated by way of a Test Case.

SCENARIO

A Customer gathered Statistics on all the production tables. As per the policy, these statistics are gathered every weekend since last couple of years. On Monday, suddenly the Users started complaining about the slowness. The Onsite DBA’s had a look at the system and complained about the I/O issue. Further investigations revealed that the Execution plans for some of the critical application queries has changed from Index Scan to Full Table Scan. What has changed between Friday and Monday? The DBA’s realised that the only change is Optimizer Statistics. In such cases, if it is possible to identify the suspected table and its stats, then it is very easy to revert the stats of this table else the option left out is to revert the entire statistics and bring back the stats of Friday. Both the fallback plan has its own Implications. After reverting the Statistics, the Application Performance comes back to normalcy. Based on the cause of the Issue and the change to get back to normalcy, it is quite evident that the finger of blame would be on Oracle Optimizer. The Customer then decided not to gather fresh statistics unless the Optimizer Issue is resolved.

While there are various reasons that can cause a Slowness post-fresh statistics generation, one of the top most cause is Inefficient Indexing. If the Indexing Strategy of an Application is not optimal, the performance of that application would show a sudden degradation. Usually, Performance drop is not linear. It is always exponential, when the utilization reaches a threshold where it is difficult to handle the extra (minor) growth, this minor growth is enough to cause a sudden degradation.

TEST CASE & EXPLANATION

To Demonstrate the impact of In-efficient Indexing, we need to make certain assumption. In this case, let us assume that the Production System has just gone live and the production tables were without any rows, except the Master Tables. For our demonstration, I will create a table and populate some data (initially 10 days) and run an application query against that table. The table is setup to create 10 Customer ID’s and each of these customers have 5 transactions a day. We will run an application query to get the product_name and sale_value for a Customer for a day. This query should return 5 rows from initial 500 rows (10 Days x 5 Transactions a day x 10 Customers).

Next, as the system become 60 days old, the same application query is run. In our case, even if the volume increase, the number of rows return by the query remains unchanged. We shall see the significant impact of this Increase in Volume on the Performance and this impact was all of a sudden.

Table Creation Script - Inserts 10 days Data, which means the production is 10 days old

exec dbms_random.seed(0);
drop table vivek_test;
create table vivek_test as
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by level<=10) ;

Next we Create an Index on CustID and Gather Statistics

create index vivek_test_idx on vivek_test(custid);
exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',cascade=>true,method_opt=>'for all columns size 1');

Once this is done, we execute our Application Query to get data for 09th June 2011

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

    CUSTID PRODUCT_NAME         SALE_VALUE
---------- -------------------- ----------
         1 rMLTDXxxqXOZnqYRJwIn       1380
         1 AOcitwrWNXCxPHzIIIxw       3850
         1 hpalFVIprLxIHDrRgqHb       2287
         1 vkfKdaFoCaHgBCQAFgCY       4364
         1 DAlVYefHAurNoryMikSJ       1125

Runtime Plan

select sql_id, sql_text, buffer_gets, executions, version_count
from v$sqlarea where sql_text like 'select /*+ vivek%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
2t8mwg94j89h8 select /*+ vivek */ custid, product_name, sale_val          10          2             1
              ue from  vivek_test where custid=1 and   sale_date
               between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
              to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  2t8mwg94j89h8, child number 0
-------------------------------------
select /*+ vivek */ custid, product_name, sale_value from  vivek_test where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60)

Plan hash value: 2623397998

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     4 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |     1 |    35 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |    50 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:B1,'MM-DD-YYYY HH24:MI:SS')=TO_DATE(:B1,'MM-DD-YYYY HH24:MI:SS') AND
              "SALE_DATE"<=TO_DATE(:B1,'MM-DD-YYYY HH24:MI:SS')+1-.0000115740740740740740740740740740
              7407407407))
   3 - access("CUSTID"=1)

The Runtime Plan in this case shows that the Application Query has used an Index on Custid, which is usually an appropriate approach in an OLTP system. The Interesting fact here is a Throwaway, which is, 50 Rows were fetched from an Index and only 1 row was fetched from the Table Access. In this case, 49 rows were THROWNAWAY. The Actual THROWAWAY rows are 45, but again, 45 rows out of 50 rows is 90% of the rows are discarded and this is very bad.

THE REAL PERFORMANCE ISSUE

User Continue to use the system and it is now 60 days old. As per the policy, after the 60th day, fresh Statistics were gathered. Further, the DBA’s also take the backup of the old stats before generating new. As the data is entered, users also continue to execute the application query.

truncate table vivek_test;
exec dbms_random.seed(0);

insert into vivek_test
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');

exec dbms_stats.create_stat_table(user,'STATS');
exec dbms_stats.export_table_stats(user,'VIVEK_TEST',stattab=>'stats');

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

Run time Plan
Plan hash value: 2623397998
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    18 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |    18   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |   300 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Check for the Throwaway percentage now, which has gone up considerably. Let us pump in 5 (for 5 working days) days data, and as per the policy, generate fresh statistics. We already have a backup of the previously generated Statistics in STATS table.

truncate table vivek_test;
exec dbms_random.seed(0);

insert into vivek_test
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

Run time Plan
Plan hash value: 1579208797

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |    18 (100)|          |
|*  1 |  FILTER            |            |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| VIVEK_TEST |    15 |   525 |    18   (0)| 00:00:01 |
---------------------------------------------------------------------------------

In just 4-5 days, the plan of the query changed from Index Scan to Full Table Scan. Further, the I/O’s of the query also increased from 10 (when it started) to 22 and then now to 63 per execution. The Cost of the Query remains same. In high concurrency, these Full table Scan’s would surely have an impact on the overall performance. Let us now revert the statistics from the backup table and check for the query performance.

Current Stats
new   2: where table_name='VIVEK_TEST'

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
VIVEK                          NO        3250         60 13-JUN-11 YES

Elapsed: 00:00:00.45
SQL> exec dbms_stats.import_table_stats(user,'VIVEK_TEST',stattab=>'STATS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04

Reverted Stats
OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
VIVEK                          NO        3000         60 13-JUN-11 YES

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

Runtime Plan
Plan hash value: 2623397998

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    18 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |    18   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |   300 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

After the Statistics were reverted, the application query regenerated the Better plan. This was enough to raise a doubt over the behaviour of Optimizer. On my test database, the actual issue occured on the 61st day. In your case, this may not be the case. For this example, I will also demonstrate you a way to find out on which day the Optimizer will change the plan.

The test case and demonstration clearly explains that the issue here is not because of the Optimizer Statistics, but is because of Inefficient Indexing, which is also evident from the Throwaway percentage mentioned above. To prove this point, I will create a composite index on Custid, sale_date. I will generate a fresh statistics, which will for 65 days. Once the Application query is run, I will pump in 365 days worth of data, regenerate fresh statistics and check for the Execution Plan (and throwaway percentage).

drop index vivek_test_idx;
create index vivek_test_idx on vivek_test(custid, sale_date);
exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',cascade=>true,method_opt=>'for all columns size 1');
variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
2t8mwg94j89h8 select /*+ vivek */ custid, product_name, sale_val           6          1             1
              ue from  vivek_test where custid=1 and   sale_date
               between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
              to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60
              )

Plan hash value: 2623397998
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     5 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |    16 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

The Production is now a year old, with new Index
truncate table vivek_test;
exec dbms_random.seed(0);

insert into vivek_test
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
2t8mwg94j89h8 select /*+ vivek */ custid, product_name, sale_val           6          1             1
              ue from  vivek_test where custid=1 and   sale_date
               between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
              to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60
              )

Plan hash value: 2623397998
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     5 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |    15 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

With the New Index, the performance is consistent and the performance matrix (I/O’s and throwaway percentage) are stable too. This answers the concern raised by the Customers “What is the cause of Sudden Degration ?”. In most of the cases, the change in behaviour is due to Application. You may notice that the change in number of rows from 3000 to 3250 (a difference of 250 rows) could cause the performance degradation. In this case, the number of blocks remained unchanged. As mentioned earlier, in my test case, it was the 61st day that triggered the plan change, which means, addition of just 50 rows. On an actual OLTP production system, the number of rows inserted on a day into a table is huge.

You may run the following pl/sql block on your test database to check the day on which the execution plan of above query would change.

drop index vivek_test_idx;
create index vivek_test_idx on vivek_test(custid);
This is for 120 Days and Statement Id from plan_table corresponds to the day.

declare
s_date	varchar2(30):=to_char(trunc(sysdate)-1,'MM-DD-YYYY');
e_date  varchar2(30):=to_char(trunc(sysdate)+1-1/(24*60*60),'MM-DD-YYYY');
l_stat	varchar2(1000);
begin
  execute immediate 'delete from plan_table';
  for i in 1..120
  loop
   execute immediate 'truncate table vivek_test';
   insert into vivek_test 
   with main as
   (select mod(rownum,10) custid,
           round(dbms_random.value(1000,7000),0) sale_value,
           dbms_random.string('A',20) product_name
   from    all_objects a
   where rownum<=50)
   select custid, s_date sale_date, sale_value, product_name
   from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');
   l_stat:= 'explain plan set statement_id '||''''||i||''''||' for select custid, sale_value, sale_date from vivek_test where custid=1
            and   sale_date between to_date(:b2,'||''''||'MM-DD-YYYY HH24:MI:SS'||''''||') 
            and to_date(:b3,'||''''||'MM-DD-YYYY HH24:MI:SS'||''''||')';
    execute immediate l_stat using s_date, e_date;
--    dbms_output.put_line(l_stat);
   commit;
  end loop;
end;
/
column operation for a20
column options for a20
select statement_id, operation, options from plan_table
where operation='TABLE ACCESS'
order by to_number(statement_id);

STATEMENT_ID                   OPERATION            OPTIONS
------------------------------ -------------------- --------------------
1                              TABLE ACCESS         BY INDEX ROWID
2                              TABLE ACCESS         BY INDEX ROWID
3                              TABLE ACCESS         BY INDEX ROWID

56                             TABLE ACCESS         BY INDEX ROWID
57                             TABLE ACCESS         BY INDEX ROWID
58                             TABLE ACCESS         BY INDEX ROWID
59                             TABLE ACCESS         BY INDEX ROWID
60                             TABLE ACCESS         BY INDEX ROWID
61                             TABLE ACCESS         FULL <---- From 61st Day, the Plan Changed
62                             TABLE ACCESS         FULL
63                             TABLE ACCESS         FULL
64                             TABLE ACCESS         FULL

120                            TABLE ACCESS         FULL

120 rows selected.

Exadata v/s Traditional Storage

My previous blog was on Exadata and I demonstrated the use of Oracle API’s to simulate Exadata Performance on a Traditional Storage. I am now all set to witness the actual performance benefits that Exadata Storage can provide, when compared to other storages. Currently, I am engaged for the same customer where I presented a Simulation that looked encouraging and this time, I am involved in a Benchmarking of an Entire Application Performance on the Exadata Box. The Application is a mix of OLTP and Batch processes and the results are awaited in next few days time. Would share some of the interesting facts soon.