Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 9 Nov 2009 06:38:10 -0800 (PST)
Message-ID: <0d9ec43b-cd7a-4ea7-98e3-b25565c8643f_at_n35g2000yqm.googlegroups.com>

On Nov 8, 11:20 am, lsllcm <lsl..._at_gmail.com> wrote:
> Below sql still runs slow regardless optimzer mode is FIRST_ROWS_100
> and ALL_ROWS
>
> SELECT *
> FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE,
> A.B1_PER_ID1 AS B1_PER_ID1,
> A.B1_PER_ID2 AS B1_PER_ID2,
> A.B1_PER_ID3 AS B1_PER_ID3,
> A.B1_PER_GROUP,
> A.B1_PER_TYPE,
> A.B1_PER_SUB_TYPE,
> A.B1_PER_CATEGORY,
> A.B1_SPECIAL_TEXT,
> A.B1_CREATED_BY_ACA,
> A.R3_STD_TIME_CLASS_CODE,
> A.B1_STANDARD_TIME,
> A.B1_EVENT_CODE,
> A.B1_REF_ID,
> A.B1_APPL_STATUS,
> A.REC_FUL_NAM,
> A.REC_STATUS,
> A.PROJECT_NBR,
> A.B1_ALT_ID,
> A.B1_TRACKING_NBR,
> A.B1_APPL_STATUS_DATE,
> A.B1_REF_ID1,
> A.B1_REF_ID2,
> A.B1_REF_ID3,
> A.B1_Q_UD1,
> A.B1_Q_UD2,
> A.B1_Q_UD3,
> A.B1_Q_UD4,
> A.APP_STATUS_GROUP_CODE,
> A.B1_MODULE_NAME,
> A.B1_FILE_DD,
> A.REC_DATE,
> A.B1_REPORTED_DATE,
> A.B1_APPL_CLASS,
> A.B1_APP_TYPE_ALIAS,
> A.B1_CREATED_BY,
> A.B1_INITIATED_BY_PRODUCT,
> B.APP_STATUS,
> B.APP_STATUS_DATE,
> B.DISPOSITION,
> B.DISPOSITION_DATE,
> B.TOTAL_FEE,
> B.TOTAL_PAY,
> B.PERCENT_COMPLETE,
> B.BALANCE,
> B.BALANCE_DATE,
> B.HOUSE_COUNT,
> B.BUILDING_COUNT,
> B.PUBLIC_OWNED,
> B.CONST_TYPE_CODE,
> B.ACTION,
> B.GA_AGENCY_CODE,
> B.GA_BUREAU_CODE,
> B.GA_DIVISION_CODE,
> B.GA_OFFICE_CODE,
> B.GA_SECTION_CODE,
> B.GA_GROUP_CODE,
> B.GA_FNAME,
> B.GA_MNAME,
> B.GA_LNAME,
> B.B1_APPL_SUB_STATUS,
> B.B1_SHORT_NOTES,
> B.B1_CLOSED_DATE,
> B.B1_CLOSEDBY,
> B.B1_REPORTED_CHANNEL,
> B.B1_ASGN_DEPT,
> B.B1_ASGN_STAFF,
> B.B1_PRIORITY,
> B.B1_SEVERITY,
> B.B1_ASGN_DATE,
> B.TOTAL_JOB_COST,
> B.B1_CLOSED_DEPT,
> B.B1_COMPLETE_BY,
> B.B1_COMPLETE_DEPT,
> B.B1_COMPLETE_DATE,
> B.B1_SCHEDULED_DATE,
> B.B1_CREATOR_DEPT,
> B.PM_SCHEDULE_SEQ,
> B.B1_EST_PROD_UNITS,
> B.B1_ACTUAL_PROD_UNITS,
> B.B1_EST_COST_PER_UNIT,
> B.B1_COST_PER_UNIT,
> B.B1_EST_JOB_COST,
> B.B1_PROD_UNIT_TYPE,
> B.B1_CREATED_BY AS B1CREATEDBY,
> B.B1_TRACK_START_DATE,
> B.B1_ESTIMATED_DUE_DATE,
> B.B1_IN_POSSESSION_TIME,
> B.C6_ANONYMOUS_FLAG,
> B.C6_REFERENCE_TYPE,
> B.C6_APPEARANCE_DAYOFWEEK,
> B.C6_APPEARANCE_DD,
> B.C6_BOOKING_FLAG,
> B.C6_DFNDT_SIGNATURE_FLAG,
> B.C6_ENFORCE_OFFICER_ID,
> B.C6_ENFORCE_OFFICER_NAME,
> B.C6_INFRACTION_FLAG,
> B.C6_INSPECTOR_ID,
> B.C6_MISDEMEANOR_FLAG,
> B.C6_OFFN_WITNESSED_FLAG,
> B.C6_INSPECTOR_NAME,
> B.C6_ENFORCE_DEPT,
> B.C6_INSPECTOR_DEPT,
> B.APP_STATUS_REASON,
> B.FIRST_ISSUED_DATE,
> B.UNDISTRIBUTED_JOB_COST,
> B.B1_VAL_MULTIPLIER,
> B.B1_VAL_EXTRA_AMT,
> G.EXPIRATION_CODE,
> G.EXPIRATION_STATUS,
> G.EXPIRATION_DATE,
> G.EXPIRATION_INTERVAL,
> G.EXPIRATION_INTERVAL_UNITS,
> G.GRACE_PERIOD_INTERVAL,
> G.GRACE_PERIOD_INTERVAL_UNITS,
> G.PENALTY_INTERVAL,
> G.PENALTY_INTERVAL_UNITS,
> G.NUM_PENALTY_PERIODS,
> G.PENALTY_PERIOD,
> G.RENEWAL_FEE_FUNCTION,
> G.RENEWAL_FEE_CODE,
> G.PENALTY_FEE_FUNCTION,
> G.PENALTY_FEE_CODE,
> G.PAY_PERIOD_GROUP,
> G.UDF1,
> G.UDF2,
> G.UDF3,
> G.UDF4,
> C.B1_WORK_DESC
> FROM B1PERMIT A
> LEFT OUTER JOIN BPERMIT_DETAIL B ON A.SERV_PROV_CODE =
> B.SERV_PROV_CODE
> AND A.B1_PER_ID1 =
> B.B1_PER_ID1
> AND A.B1_PER_ID2 =
> B.B1_PER_ID2
> AND A.B1_PER_ID3 =
> B.B1_PER_ID3
> LEFT OUTER JOIN BWORKDES C ON A.SERV_PROV_CODE =
> C.SERV_PROV_CODE
> AND A.B1_PER_ID1 = C.B1_PER_ID1
> AND A.B1_PER_ID2 = C.B1_PER_ID2
> AND A.B1_PER_ID3 = C.B1_PER_ID3
> LEFT OUTER JOIN B1_EXPIRATION G ON A.SERV_PROV_CODE =
> G.SERV_PROV_CODE
> AND A.B1_PER_ID1 =
> G.B1_PER_ID1
> AND A.B1_PER_ID2 =
> G.B1_PER_ID2
> AND A.B1_PER_ID3 =
> G.B1_PER_ID3,
> B3CONTRA L
> WHERE A.REC_STATUS = 'A'
> AND A.B1_PER_ID3 NOT LIKE '#%'
> AND (A.B1_APPL_CLASS = 'COMPLETE' OR A.B1_APPL_CLASS IS
> NULL)
> AND A.SERV_PROV_CODE = L.SERV_PROV_CODE
> AND A.B1_PER_ID1 = L.B1_PER_ID1
> AND A.B1_PER_ID2 = L.B1_PER_ID2
> AND A.B1_PER_ID3 = L.B1_PER_ID3
> AND A.SERV_PROV_CODE = :spc
> AND L.B1_LICENSE_NBR = :p1
> AND L.B1_LICENSE_TYPE = :p2
> ORDER BY A.B1_FILE_DD DESC)
> WHERE ROWNUM < 101

Randolf is of course correct that the Access and Filter predicates for the plans would be very helpful.

It appears that this may be a case were Bug 6845871 - Suboptimal plan from ROWNUM predicate is actually helping 10.2.0.4 and 11.1.0.6 derive a better plan than 11.2.0.1, but it seems that the 10.2.0.4 and 11.1.0.6 plan is behaving like "WHERE ROWNUM < 1" is specified rather than "WHERE ROWNUM < 101".

Hopefully, the following re-print of your posted plans will not have word-wrapping problems:
10g and 11.1.0.6 db

---

| Id  | Operation                          | Name                 |

Rows |

---

|   0 | SELECT STATEMENT                   |
|     1 |
|*  1 |  COUNT STOPKEY                     |
|       |
|   2 |   VIEW                             |
|     1 |
|*  3 |    SORT ORDER BY STOPKEY           |
|     1 |
|   4 |     NESTED LOOPS OUTER             |
|     1 |
|   5 |      NESTED LOOPS OUTER            |
|     1 |
|   6 |       NESTED LOOPS OUTER           |
|     1 |
|   7 |        NESTED LOOPS                |
|     1 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| B3CONTRA
|     1 |
|*  9 |          INDEX RANGE SCAN          | B3CONTRA_BUS_NAM2_IX
|     1 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| B1PERMIT
|     1 |
|* 11 |          INDEX UNIQUE SCAN         | B1PERMIT_PK
|     1 |
|  12 |        TABLE ACCESS BY INDEX ROWID | BWORKDES
|     4 |
|* 13 |         INDEX UNIQUE SCAN          | BWORKDES_PK
|     1 |
|  14 |       TABLE ACCESS BY INDEX ROWID  | BPERMIT_DETAIL
|     1 |
|* 15 |        INDEX UNIQUE SCAN           | BPERMIT_DETAIL_PK
|     1 |
|  16 |      TABLE ACCESS BY INDEX ROWID   | B1_EXPIRATION
|     2 |
|* 17 |       INDEX RANGE SCAN             | B1_EXPIRATION_PK
|     1 |
---------------------------------------------------------------------------

10g and 11.1.0.6 db(cont)

---------­-------------------------------
| Rows  | Bytes | Cost (%CPU)| Time     |
---------­-------------------------------
|     1 |  4157 |     6  (17)| 00:00:01 |
|       |       |            |          |
|     1 |  4157 |     6  (17)| 00:00:01 |
|     1 |   616 |     6  (17)| 00:00:01 |
|     1 |   616 |     5   (0)| 00:00:01 |
|     1 |   447 |     3   (0)| 00:00:01 |
|     1 |   319 |     2   (0)| 00:00:01 |
|     1 |   235 |     1   (0)| 00:00:01 |
|     1 |    48 |     0   (0)| 00:00:01 |
|     1 |       |     0   (0)| 00:00:01 |
|     1 |   187 |     1   (0)| 00:00:01 |
|     1 |       |     0   (0)| 00:00:01 |
|     4 |   336 |     1   (0)| 00:00:01 |
|     1 |       |     0   (0)| 00:00:01 |
|     1 |   128 |     1   (0)| 00:00:01 |
|     1 |       |     0   (0)| 00:00:01 |
|     2 |   338 |     2   (0)| 00:00:01 |
|     1 |       |     1   (0)| 00:00:01 |
---------­-------------------------------

Notice how the ROWNUM predicate was apparently pushed far into the view. Now compare with 11.2.0.1:
11.2.0.1

---

+--------

| Id  | Operation                          | Name              |  Rows

|

---

+--------

| 0   | SELECT STATEMENT                   |                   |
|
| 1   |  COUNT STOPKEY                     |                   |
|
| 2   |   VIEW                             |                   |   123
|
| 3   |    NESTED LOOPS OUTER              |                   |   123
|
| 4   |     NESTED LOOPS OUTER             |                   |   123
|
| 5   |      NESTED LOOPS OUTER            |                   |   123
|
| 6   |       NESTED LOOPS                 |                   |   123
|
| 7   |        TABLE ACCESS BY INDEX ROWID | B1PERMIT          |  501K
|
| 8   |         INDEX FULL SCAN DESCENDING | B1PERMIT_FILEDD_IX|  4114
|
| 9   |        INDEX RANGE SCAN            | B3CONTRA_PK       |     1
|
| 10  |       TABLE ACCESS BY INDEX ROWID  | BPERMIT_DETAIL    |     1
|
| 11  |        INDEX UNIQUE SCAN           | BPERMIT_DETAIL_PK |     1
|
| 12  |      TABLE ACCESS BY INDEX ROWID   | B1_EXPIRATION     |     1
|
| 13  |       INDEX RANGE SCAN             | B1_EXPIRATION_PK  |     1
|
| 14  |     TABLE ACCESS BY INDEX ROWID    | BWORKDES          |     1
|
| 15  |      INDEX UNIQUE SCAN             | BWORKDES_PK       |     1

|

---

+--------
11.2.0.1 (Cont)

+-----------------------------------+
|  Rows | Bytes | Cost  | Time      |
+-----------------------------------+
|       |       |   10K |           |
|       |       |       |           |
|   123 |  499K |   10K |  00:02:01 |
|   123 |   71K |   10K |  00:02:01 |
|   123 |   61K |  9903 |  00:02:59 |
|   123 |   43K |  9779 |  00:02:58 |
|   123 |   25K |  9533 |  00:02:55 |
|  501K |   76M |  1359 |  00:00:17 |
|  4114 |       |    37 |  00:00:01 |
|     1 |    48 |     2 |  00:00:01 |
|     1 |   158 |     2 |  00:00:01 |
|     1 |       |     1 |  00:00:01 |
|     1 |   146 |     2 |  00:00:01 |
|     1 |       |     1 |  00:00:01 |
|     1 |    79 |     2 |  00:00:01 |
|     1 |       |     1 |  00:00:01 |
+-----------------------------------+

This OTN thread might provide a little more information about why it appears in the 10.2.0.4 and 11.1.0.6 plans that "WHERE ROWNUM < 1" was specified:
http://forums.oracle.com/forums/thread.jspa?messageID=3774308

Of course, it could also have to do with shared cursors and bind variables from a previous execution. Suggestions: Use a /*+ gather_plan_statistics */ hint in the SQL statement to allow DBMS_XPLAN to retrieve the predicted number of rows and the actual number of rows. The SQL statement with the hint in place would start like this:
SELECT /*+ gather_plan_statistics */ *
  FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE, You might also want to gather a 10053 trace for the SQL statement executed on 11.1.0.6 and 11.2.0.1 and then compare the two trace files.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Nov 09 2009 - 08:38:10 CST