Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
From: lsllcm <lsllcm_at_gmail.com>
Date: Tue, 10 Nov 2009 07:22:54 -0800 (PST)
Message-ID: <1763fe0a-a4ab-498e-8cbe-645e97e963f0_at_g22g2000prf.googlegroups.com>
Thanks Charles and Randolf
SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.SERV_PROV_CODE
"A"."B1_PER_ID2"="L"."B1_PER_ID2" AND
"A"."B1_PER_ID2"="G"."B1_PER_ID2" AND
"A"."B1_PER_ID3"="G"."B1_PER_ID3")
SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.SERV_PROV_CODE
"A"."B1_PER_ID2"="B"."B1_PER_ID2" AND "A"."B1_PER_ID3"="B"."B1_PER_ID3") PLAN_TABLE_OUTPUT
2. below are first K rows in 10053 trace file in 11.2.0.1 version, I am not familiar with 10053 trace file, I will spend some time to research it.
The trace file is about 2m, it is too long to paste here. The forum has no upload file feature.
specified:
http://forums.oracle.com/forums/thread.jspa?messageID=3774308
Date: Tue, 10 Nov 2009 07:22:54 -0800 (PST)
Message-ID: <1763fe0a-a4ab-498e-8cbe-645e97e963f0_at_g22g2000prf.googlegroups.com>
Thanks Charles and Randolf
-----------------------item 1 ---------------------------------
1. I include the Predicate Information here, from gather plan
statistics, the estimated rows in 11.1.0.6 is less than 11.2.0.1.
-------------------------11.1.0.6 execution
plan------------------------------------------------ Elapsed: 00:00:03.55 SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ; PLAN_TABLE_OUTPUT
SQL_ID 4h3u67mxcfa6m, child number 0
SELECT /*+ gather_plan_statistics */ * 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,
PLAN_TABLE_OUTPUT
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_
Plan hash value: 1735401230
| Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-
PLAN_TABLE_OUTPUT
|* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:00.02 | 1971 | | | | 2 | VIEW | | 1 | 9 | 2 |00:00:00.02 | 1971 | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 9 | 2 |00:00:00.02 | 1971 | 2048 | 2048 | 2048 (0 | 4 | NESTED LOOPS OUTER | | 1 | 9 | 2 |00:00:00.02 | 1971 | | | | 5 | NESTED LOOPS OUTER | | 1 | 9 | 2 |00:00:00.02 | 1963 | | | | 6 | NESTED LOOPS OUTER | | 1 | 9 | 2 |00:00:00.02 | 1955 | | | | 7 | NESTED LOOPS | | 1 | 9 | 2 |00:00:00.02 | 1954 | | | |* 8 | INDEX FAST FULL SCAN | B3CONTRA_PK | 1 | 9 | 2 |00:00:00.02 | 1946 | | | |* 9 | TABLE ACCESS BY INDEX ROWID| B1PERMIT | 2 | 1 | 2 |00:00:00.01 | 8 | | | |* 10 | INDEX UNIQUE SCAN | B1PERMIT_PK | 2 | 1 | 2 |00:00:00.01 | 6 | | |
PLAN_TABLE_OUTPUT
| 11 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 2 | 1 | 0 |00:00:00.01 | 1 | | | |* 12 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 2 | 1 | 0 |00:00:00.01 | 1 | | | | 13 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 2 | 1 | 2 |00:00:00.01 | 8 | | | |* 14 | INDEX UNIQUE SCAN | BWORKDES_PK | 2 | 1 | 2 |00:00:00.01 | 6 | | | | 15 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 2 | 1 | 2 |00:00:00.01 | 8 | | | |* 16 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 2 | 1 | 2 |00:00:00.01 | 6 | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
1 - filter(ROWNUM<101)
3 - filter(ROWNUM<101)
8 - filter(("L"."B1_LICENSE_NBR"=:P1 AND "L"."B1_LICENSE_TYPE"=:P2
AND "L"."SERV_PROV_CODE"=:SPC))
9 - filter((("A"."B1_APPL_CLASS" IS NULL OR
"A"."B1_APPL_CLASS"='COMPLETE') AND "A"."REC_STATUS"='A'))
10 - access("A"."SERV_PROV_CODE"=:SPC 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")
filter("A"."B1_PER_ID3" NOT LIKE '#%')
12 - access("G"."SERV_PROV_CODE"=:SPC 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")
14 - access("C"."SERV_PROV_CODE"=:SPC 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")
PLAN_TABLE_OUTPUT
16 - access("B"."SERV_PROV_CODE"=:SPC 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")
58 rows selected.
Elapsed: 00:00:00.17
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
D:\>
-------------------------11.2.0.1 execution
plan------------------------------------------------ Elapsed: 00:00:53.55 SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ; PLAN_TABLE_OUTPUT
SQL_ID 4h3u67mxcfa6m, child number 0
SELECT /*+ gather_plan_statistics */ * 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,
PLAN_TABLE_OUTPUT
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_
Plan hash value: 3893163705
| Id | Operation | Name |Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:19.43 | 1067K| 1506 | |* 1 | COUNT STOPKEY | | 1 | | 3 |00:00:19.43 | 1067K| 1506 | | 2 | VIEW | | 1 | 123 | 3 |00:00:19.43 | 1067K| 1506 | | 3 | NESTED LOOPS OUTER | | 1 | 123 | 3 |00:00:19.43 | 1067K| 1506 | | 4 | NESTED LOOPS OUTER | | 1 | 123 | 3 |00:00:20.24 | 1067K| 1498 | | 5 | NESTED LOOPS OUTER | | 1 | 123 | 3 |00:00:20.24 | 1067K| 1498 | | 6 | NESTED LOOPS | | 1 | 123 | 3 |00:00:20.05 | 1067K| 1488 | |* 7 | TABLE ACCESS BY INDEX ROWID| B1PERMIT | 1 | 513K| 515K|00:00:22.49 | 153K| 1256 | |* 8 | INDEX FULL SCAN DESCENDING| B1PERMIT_FILEDD_IX | 1 | 4114 | 517K|00:00:05.99 | 4331 | 334 | |* 9 | INDEX RANGE SCAN | B3CONTRA_PK | 515K| 1 | 3 |00:00:10.67 | 913K| 232 |
PLAN_TABLE_OUTPUT
| 10 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 3 | 1 | 3 |00:00:00.22 | 13 | 10 | |* 11 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 3 | 1 | 3 |00:00:00.10 | 8 | 5 | | 12 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 3 | 1 | 0 |00:00:00.01 | 2 | 0 | |* 13 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 3 | 1 | 0 |00:00:00.01 | 2 | 0 | | 14 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 3 | 1 | 3 |00:00:00.20 | 11 | 8 | |* 15 | INDEX UNIQUE SCAN | BWORKDES_PK | 3 | 1 | 3 |00:00:00.12 | 8 | 5 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
1 - filter(ROWNUM<101)
7 - filter((("A"."B1_APPL_CLASS" IS NULL OR
"A"."B1_APPL_CLASS"='COMPLETE') AND "A"."REC_STATUS"='A' AND
"A"."B1_PER_ID3" NOT LIKE '#%'))
8 - access("A"."SERV_PROV_CODE"=:SPC)
filter("A"."SERV_PROV_CODE"=:SPC)
9 - access("L"."SERV_PROV_CODE"=:SPC 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 "L"."B1_LICENSE_NBR"=:P1 AND
"L"."B1_LICENSE_TYPE"=:P2)
filter("L"."B1_LICENSE_TYPE"=:P2)
11 - access("B"."SERV_PROV_CODE"=:SPC 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") PLAN_TABLE_OUTPUT
13 - access("G"."SERV_PROV_CODE"=:SPC 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")
15 - access("C"."SERV_PROV_CODE"=:SPC 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")
60 rows selected.
Elapsed: 00:00:02.12
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
------------------------------item
2-----------------------------------
2. below are first K rows in 10053 trace file in 11.2.0.1 version, I am not familiar with 10053 trace file, I will spend some time to research it.
The trace file is about 2m, it is too long to paste here. The forum has no upload file feature.
First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 73255.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 32739848.00 First K Rows: K = 100.00, N = 90790.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 32739848.00 First K Rows: K = 100.00, N = 90790.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 112700.00Thanks for the link, I am reading on it. 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
------------------------------item
3---------------------------------------
specified:
http://forums.oracle.com/forums/thread.jspa?messageID=3774308
Thanks
lsllcm
Received on Tue Nov 10 2009 - 09:22:54 CST