performance issue after upgrade to oracle 11.2.0.1 linux 32 bit
lsllcm
I have one insteresting performance issue about 11.2.0.1. Any comments
are appreciated.
When I use A JOIN B and A JOIN C, the optimizer choose index scan on
table C.
When I use A JOIN B and B JOIN C, the optimizer choose full table
scan
on table C.
Below is test case:
Case 1: When I use A JOIN B and A JOIN C, the optimizer choose index
scan on table C.
SELECT X.SERV_PROV_CODE,
X.B1_PER_ID1,
X.B1_PER_ID2,
X.B1_PER_ID3,
SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
WHERE S.SERV_PROV_CODE = 'SACRAMENTO'
AND S.SET_ID = 'CONNIE'
AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
AND S.B1_PER_ID1 = X.B1_PER_ID1
AND S.B1_PER_ID2 = X.B1_PER_ID2
AND S.B1_PER_ID3 = X.B1_PER_ID3
AND F.B1_PER_ID1 = S.B1_PER_ID1
AND F.B1_PER_ID2 = S.B1_PER_ID2
AND F.B1_PER_ID3 = S.B1_PER_ID3
AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
AND S.REC_STATUS = X.REC_STATUS
AND F.REC_STATUS = X.REC_STATUS
AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
X.PAYMENT_FEEITEM_STATUS IS NULL)
GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3
Elapsed: 00:00:00.06
Plan hash value: 305769021
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-
Rows | OMem | 1Mem | Used-Mem
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | |
40516 | 745K| 745K| 668K (0)
| 2 | NESTED LOOPS |
| | | |
| 3 | NESTED LOOPS | |
40516 | | |
| 4 | NESTED LOOPS | |
85 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
24 | | |
|* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX |
24 | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| F4FEEITEM |
3 | | |
|* 8 | INDEX RANGE SCAN | F4FEEITEM_PK |
1 | | |
|* 9 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK |
1 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM |
475 | | |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("S"."REC_STATUS"='A')
6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')
7 - filter("F"."REC_STATUS"='A')
8 - access("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."B1_PER_ID1"="S"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="S"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="S"."B1_PER_ID3")
9 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3" AND
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR")
filter("F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR")
10 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR
"X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND
"X"."REC_STATUS"='A'))
Note
-----
- Warning: basic plan statistics not available. These are only
collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or
system level
55 rows selected.
Elapsed: 00:00:04.05
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
Case 2: When I use A JOIN B and B JOIN C, the optimizer choose full
table scan on table C.
Plan hash value: 2372462434
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-
Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | |
1 | 745K| 745K| 660K (0)|
|* 2 | HASH JOIN | |
95859 | 744K| 744K| 1185K (0)|
| 3 | NESTED LOOPS |
| | | | |
| 4 | NESTED LOOPS | |
95859 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
24 | | | |
|* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX |
24 | | | |
|* 7 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK |
1 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM |
3921 | | | |
|* 9 | TABLE ACCESS FULL | F4FEEITEM |
1173K| | | |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"F"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"F"."REC_STATUS"="X"."REC_STATUS")
5 - filter("S"."REC_STATUS"='A')
6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')
7 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3")
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
8 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR
"X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND
"X"."REC_STATUS"='A'))
9 - filter(("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."REC_STATUS"='A'))
Note
-----
- Warning: basic plan statistics not available. These are only
collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or
system level
54 rows selected.
Elapsed: 00:00:02.80
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
Thanks
lsllcm
lsllcm
I have put the 10053 trace here along with query
1. Good plan
SELECT X.SERV_PROV_CODE,
X.B1_PER_ID1,
X.B1_PER_ID2,
X.B1_PER_ID3,
SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
WHERE S.SERV_PROV_CODE = 'SACRAMENTO'
AND S.SET_ID = 'CONNIE'
AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
AND S.B1_PER_ID1 = X.B1_PER_ID1
AND S.B1_PER_ID2 = X.B1_PER_ID2
AND S.B1_PER_ID3 = X.B1_PER_ID3
AND F.B1_PER_ID1 = S.B1_PER_ID1
AND F.B1_PER_ID2 = S.B1_PER_ID2
AND F.B1_PER_ID3 = S.B1_PER_ID3
AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
AND S.REC_STATUS = X.REC_STATUS
AND F.REC_STATUS = X.REC_STATUS
AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
X.PAYMENT_FEEITEM_STATUS IS NULL)
GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3
/
from 10053 trace file
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
"F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
"X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
"B1_PER_ID1","X"."B1_PER_ID2" "B1_PER_ID2","X"."B1_PER_ID3"
"B1_PER_ID3",SUM(NVL("X"."FEE_ALLOCATION",0)) "GF_FEE" FROM
"ACCELA"."SETDETAILS" "S","ACCELA"."X4PAYMENT_FEEITEM"
"X","ACCELA"."F4FEEITEM" "F" WHERE "S"."SERV_PROV_CODE"='SACRAMENTO'
AND "S"."SET_ID"='CONNIE' AND "S"."REC_STATUS"='A' AND
"S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE" AND
"F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."B1_PER_ID1"="S"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="S"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="S"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"S"."REC_STATUS"="X"."REC_STATUS" AND
"F"."REC_STATUS"="X"."REC_STATUS" AND
("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL) AND
"X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY
"X"."SERV_PROV_CODE","X"."B1_PER_ID1","X"."B1_PER_ID2","X"."B1_PER_ID3"
kkoqbc: optimizing query block SEL$1 (#1)
:
call(in-use=12596, alloc=32736), compile(in-use=149332,
alloc=161508), execution(in-use=2864, alloc=4060)
2. Poor plan
SELECT X.SERV_PROV_CODE,
X.B1_PER_ID1,
X.B1_PER_ID2,
X.B1_PER_ID3,
SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
WHERE S.SERV_PROV_CODE = 'SACRAMENTO'
AND S.SET_ID = 'CONNIE'
AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
AND S.B1_PER_ID1 = X.B1_PER_ID1
AND S.B1_PER_ID2 = X.B1_PER_ID2
AND S.B1_PER_ID3 = X.B1_PER_ID3
AND F.B1_PER_ID1 = X.B1_PER_ID1
AND F.B1_PER_ID2 = X.B1_PER_ID2
AND F.B1_PER_ID3 = X.B1_PER_ID3
AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
AND S.REC_STATUS = X.REC_STATUS
AND F.REC_STATUS = X.REC_STATUS
AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
X.PAYMENT_FEEITEM_STATUS IS NULL)
GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3
/
from 10053 trace file
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
"B1_PER_ID1","X"."B1_PER_ID2" "B1_PER_ID2","X"."B1_PER_ID3"
"B1_PER_ID3",SUM(NVL("X"."FEE_ALLOCATION",0)) "GF_FEE" FROM
"ACCELA"."SETDETAILS" "S","ACCELA"."X4PAYMENT_FEEITEM"
"X","ACCELA"."F4FEEITEM" "F" WHERE "S"."SERV_PROV_CODE"='SACRAMENTO'
AND "S"."SET_ID"='CONNIE' AND "S"."REC_STATUS"='A' AND
"S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE" AND
"F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"S"."REC_STATUS"="X"."REC_STATUS" AND
"F"."REC_STATUS"="X"."REC_STATUS" AND
("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL) AND
"X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY
"X"."SERV_PROV_CODE","X"."B1_PER_ID1","X"."B1_PER_ID2","X"."B1_PER_ID3"
kkoqbc: optimizing query block SEL$1 (#1)
:
call(in-use=12532, alloc=32736), compile(in-use=140036,
alloc=149244), execution(in-use=2720, alloc=4060)
Charles Hooper
> Hi Charles,
>
> I have put the 10053 trace here along with query
> 1. Good plan
> from 10053 trace file
> Final query after transformations:******* UNPARSED QUERY IS *******
> SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
> "F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
> MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
> MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
> (INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
> (INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
(snip)
> 2. Poor plan
(snip)
> from 10053 trace file
> Final query after transformations:******* UNPARSED QUERY IS *******
> SELECT "X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
(snip)
Notice in the good plan all of the OPT_ESTIMATE hints. It appears
that someone used the (extra cost) SQL Tuning Advisor feature in the
database to create a SQL profile for one of the SQL statements, but
not the other. This essentially added hints to the SQL statement to
help the optimizer correct cardinality and selectivity problems which
caused poor performance problems in the past. See this link for a
quick explanation:
http://jonathanlewis.wordpress.com/2007/02/11/profiles/
Longer descriptions are available from the documentation:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10822/tdppt_sqltune.htm
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/sql_tune.htm
Compare the WHERE clauses between the Good execution:
WHERE
"S"."SERV_PROV_CODE"='SACRAMENTO'
AND "S"."SET_ID"='CONNIE'
AND "S"."REC_STATUS"='A'
AND "S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE"
AND "F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE"
AND "S"."B1_PER_ID1"="X"."B1_PER_ID1"
AND "S"."B1_PER_ID2"="X"."B1_PER_ID2"
AND "S"."B1_PER_ID3"="X"."B1_PER_ID3"
AND "F"."B1_PER_ID1"="S"."B1_PER_ID1"
AND "F"."B1_PER_ID2"="S"."B1_PER_ID2"
AND "F"."B1_PER_ID3"="S"."B1_PER_ID3"
AND "F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR"
AND "S"."REC_STATUS"="X"."REC_STATUS"
AND "F"."REC_STATUS"="X"."REC_STATUS"
AND ("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL)
AND "X"."SERV_PROV_CODE"='SACRAMENTO'
AND "F"."SERV_PROV_CODE"='SACRAMENTO'
AND "X"."REC_STATUS"='A'
AND "F"."REC_STATUS"='A'
And the Bad execution:
S.SERV_PROV_CODE = 'SACRAMENTO'
AND S.SET_ID = 'CONNIE'
AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
AND S.B1_PER_ID1 = X.B1_PER_ID1
AND S.B1_PER_ID2 = X.B1_PER_ID2
AND S.B1_PER_ID3 = X.B1_PER_ID3
AND F.B1_PER_ID1 = X.B1_PER_ID1
AND F.B1_PER_ID2 = X.B1_PER_ID2
AND F.B1_PER_ID3 = X.B1_PER_ID3
AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
AND S.REC_STATUS = X.REC_STATUS
AND F.REC_STATUS = X.REC_STATUS
AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
X.PAYMENT_FEEITEM_STATUS IS NULL)
You will probably notice that these generated predicates appear in the
Good execution plan due to transitive closure - I do not know why they
do not appear in the bad pan (maybe an altered optimizer parameter?):
AND "X"."SERV_PROV_CODE"='SACRAMENTO'
AND "F"."SERV_PROV_CODE"='SACRAMENTO'
AND "X"."REC_STATUS"='A'
AND "F"."REC_STATUS"='A'
As an experiment, test the performance of this SQL statement which
uses the hints and generated predicates from the fast execution:
SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
"F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
X.SERV_PROV_CODE,
X.B1_PER_ID1,
X.B1_PER_ID2,
X.B1_PER_ID3,
SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
WHERE
S.SERV_PROV_CODE = 'SACRAMENTO'
AND S.SET_ID = 'CONNIE'
AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
AND S.B1_PER_ID1 = X.B1_PER_ID1
AND S.B1_PER_ID2 = X.B1_PER_ID2
AND S.B1_PER_ID3 = X.B1_PER_ID3
AND F.B1_PER_ID1 = X.B1_PER_ID1
AND F.B1_PER_ID2 = X.B1_PER_ID2
AND F.B1_PER_ID3 = X.B1_PER_ID3
AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
AND S.REC_STATUS = X.REC_STATUS
AND F.REC_STATUS = X.REC_STATUS
AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
X.PAYMENT_FEEITEM_STATUS IS NULL)
AND "X"."SERV_PROV_CODE"='SACRAMENTO'
AND "F"."SERV_PROV_CODE"='SACRAMENTO'
AND "X"."REC_STATUS"='A'
AND "F"."REC_STATUS"='A'
GROUP BY
X.SERV_PROV_CODE,
X.B1_PER_ID1,
X.B1_PER_ID2,
X.B1_PER_ID3;
If the SQL statement completes as quickly as the Good execution,
remove the following transitive closure generated predicates from the
WHERE clause and test again:
AND "X"."SERV_PROV_CODE"='SACRAMENTO'
AND "F"."SERV_PROV_CODE"='SACRAMENTO'
AND "X"."REC_STATUS"='A'
AND "F"."REC_STATUS"='A'
If performance is still slow, and you are licensed to use the SQL
Tuning Advisor, try to create a SQL Profile for the original slow
version of the query to essentially lock the hinted cardinality and
selectivity hints into the SQL statement.
--
Suggestions from anyone else?
--
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
lsllcm
Thanks Charles,
I have checked sql profiles, there is no sql profiles in the db, below
sql returns no rows
select name, status
from dba_sql_profiles
where status = 'ENABLED'
;
I have tried below sql, the execution plan is same as bad plan
Thanks
lsllcm
Randolf Geist
> You will probably notice that these generated predicates appear in the
> Good execution plan due to transitive closure - I do not know why they
> do not appear in the bad pan (maybe an altered optimizer parameter?):
> AND "X"."SERV_PROV_CODE"='SACRAMENTO'
> AND "F"."SERV_PROV_CODE"='SACRAMENTO'
> AND "X"."REC_STATUS"='A'
> AND "F"."REC_STATUS"='A'
Charles,
it looks like the generated predicates appear in both traces I think,
the "good" and the "bad" one.
To the OP: You might encounter something along the lines I've outlined
here: http://oracle-randolf.blogspot.com/2009/10/multi-column-joins.html
Depending on the way you perform multi-column joins, the multi-column
sanity checks built into recent versions of the CBO might get bypassed
or not - that is one possible explanation, but with the amount of
information provided, these are only guesses.
Note that in both cases, the "good" and the "bad", you're not really
joining A->B->B->C or A->B->A->C, but both are different mixtures,
because if these join predicates:
Statement 1) AND F.REC_STATUS = X.REC_STATUS
Statement 2) AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
which you would need to turn into
Statement 1) AND F.REC_STATUS = S.REC_STATUS
Statement 2) AND F.SERV_PROV_CODE = X.SERV_PROV_CODE
to have achieved what you've described.
Can you confirm that these are actually the PK/FK relations between
those tables, no spurious join predicates on non-FK/PK columns?
You should run the statement with STATISTICS_LEVEL = ALL (or the
GATHER_PLAN_STATISTICS hint) to compare the estimates to the actual
cardinalities - you might have correlated columns among the join
columns that are not recognized by the optimizer by default.
It seems to be odd that you get these OPT_ESTIMATE hints but no SQL
Profile seems to be enabled - may be this is something new in 11.2,
but usually you should see a corresponding note in the "Notes" section
of an DBMS_XPLAN.DISPLAY output and in the 10053 trace file (e.g. a
reference to a plan found in the SPM - SQL Plan Management feature
introduced in 11.1). May be these hints get introduced by an Outline
or SQL Baseline from SPM.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the upcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684
Charles Hooper
Randolf,
Thanks for the correction - I think I just need a taller monitor. It
appears that I compared the optimizer's transformed version of the SQL
statement that executed efficiently with the original version of the
SQL statement submitted by the OP that executed slowly.
lsllcm,
Please double-check to make certain that a SQL profile does not exist
for the good performing query. I created a test here (with 11.1.0.7,
but I could have used 11.2.0.1) with a SQL statement that executed
slowly due to a couple problems (inaccurate statistics on one of the
tables was the primary problem). The 10053 trace for my test query
included the following:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VM_NWVW_1"."$vm_col_2" "TOP_LEVEL_PART_ID",DECODE(DECODE(DECODE
(DECODE(DECODE(DECODE(NVL("VM_NWVW_1"."$vm_col_20",'NONE'), ...
I then used the SQL tuning (DBMS_SQLTUNE) features in Enterprise
Manager to "fix" the performance of the SQL statement. Once finished,
I accepted the changed execution plan, flushed the shared pool, and
created another 10053 trace for the SQL statement. The 10053 trace
contained the following:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (JOIN ("PL" "P" "R2" "R")
SCALE_ROWS=23.197274 ) OPT_ESTIMATE (JOIN ("PL" "R2" "R")
SCALE_ROWS=23.197274 ) OPT_ESTIMATE (JOIN ("P" "R2" "R")
SCALE_ROWS=25.482612 ) OPT_ESTIMATE (INDEX_FILTER "R6" "SYS_C0011548"
SCALE_ROWS=30.516792 ) OPT_ESTIMATE (INDEX_FILTER "R6"
"X_REQUIREMENT_5" SCALE_ROWS=30.516792 ) OPT_ESTIMATE (INDEX_SKIP_SCAN
"R6" "X_REQUIREMENT_5" SCALE_ROWS=0.000985 ) OPT_ESTIMATE
(INDEX_SKIP_SCAN "R6" "SYS_C0011548" SCALE_ROWS=0.000985 )
OPT_ESTIMATE (TABLE "R6" SCALE_ROWS=32.231755 ) ...
You will notice that much like your good performing SQL statement, my
transformed SQL statement also contains many similar hints added by
the SQL tuning task.
For SQL profiles to work, the text of the SQL statements much match
exactly (if I recall correctly, SQL profiles will tolerate varying
amounts of white space). That would explain why you see a different
plan when you "JOIN B and A JOIN C" than you do when you "JOIN B and B
JOIN C" - the text of the two SQL statements does not match exactly,
so an existing SQL profile would apply to only one of the SQL
statements.
It is a bit odd that you did not see a difference in the performance
with all of the hints appended from the good performing version of the
SQL statement. If I recall correctly, the queries actually produce
many more rows than what the optimizer is predicting, so you might be
experiencing the problem identified by Randolf.
lsllcm
>
> - 显示引用的文字 -
Hi Randolf,
Thanks for your comments:
I have tried the hidden parameter, the execution plan is as same as
bad plan.
Table SETDETAILS, X4PAYMENT_FEEITEM, F4FEEITEM does not have PK/FK
relationship. But they have below relationship.
Table SETDETAILS and X4PAYMENT_FEEITEM has same columns
(SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3)
Table SETDETAILS and F4FEEITEM has same columns (SERV_PROV_CODE,
B1_PER_ID1, B1_PER_ID2, B1_PER_ID3)
Table X4PAYMENT_FEEITEM and F4FEEITEM has same columns
(SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3,FEEITEM_SEQ_NBR)
Another note: in oracle 10.2.0.4, the execution plan is as same as
good plan. After test on 11.2.0.1, the execution plan is changed.
We need to rewrite the sql for 11.2.0.1. We want to find the root
cause. Because maybe there are many other queries has such issue.
Below are test case:
SQL> SELECT /*+ opt_param('_optimizer_join_sel_sanity_check', 'false')
*/
2 X.SERV_PROV_CODE,
3 X.B1_PER_ID1,
4 X.B1_PER_ID2,
5 X.B1_PER_ID3,
6 SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
7 FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
8 WHERE
9 S.SERV_PROV_CODE = 'SACRAMENTO'
10 AND S.SET_ID = 'CONNIE'
11 AND S.REC_STATUS = 'A'
12 AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
13 AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
14 AND S.B1_PER_ID1 = X.B1_PER_ID1
15 AND S.B1_PER_ID2 = X.B1_PER_ID2
16 AND S.B1_PER_ID3 = X.B1_PER_ID3
17 AND F.B1_PER_ID1 = X.B1_PER_ID1
18 AND F.B1_PER_ID2 = X.B1_PER_ID2
19 AND F.B1_PER_ID3 = X.B1_PER_ID3
20 AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
21 AND S.REC_STATUS = X.REC_STATUS
22 AND F.REC_STATUS = X.REC_STATUS
23 AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
24 X.PAYMENT_FEEITEM_STATUS IS NULL)
25 AND "X"."SERV_PROV_CODE"='SACRAMENTO'
26 AND "F"."SERV_PROV_CODE"='SACRAMENTO'
27 AND "X"."REC_STATUS"='A'
28 AND "F"."REC_STATUS"='A'
29 GROUP BY
30 X.SERV_PROV_CODE,
31 X.B1_PER_ID1,
32 X.B1_PER_ID2,
33 X.B1_PER_ID3
34 /
Elapsed: 00:00:13.43
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID d6g1xv7mjuymy, child number 0
-------------------------------------
SELECT /*+ opt_param('_optimizer_join_sel_sanity_check', 'false') */
X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2,
X.B1_PER_ID3, SUM(NVL(X.Fee_Allocation, 0)) GF_FEE FROM
SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F WHERE
S.SERV_PROV_CODE = 'SACRAMENTO' AND S.SET_ID = 'CONNIE' AND
S.REC_STATUS = 'A' AND S.SERV_PROV_CODE = X.SERV_PROV_CODE AND
F.SERV_PROV_CODE = S.SERV_PROV_CODE AND S.B1_PER_ID1 = X.B1_PER_ID1
AND S.B1_PER_ID2 = X.B1_PER_ID2 AND S.B1_PER_ID3 = X.B1_PER_ID3
AND F.B1_PER_ID1 = X.B1_PER_ID1 AND F.B1_PER_ID2 = X.B1_PER_ID2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
AND F.B1_PER_ID3 = X.B1_PER_ID3 AND F.FEEITEM_SEQ_NBR =
X.FEEITEM_SEQ_NBR AND S.REC_STATUS = X.REC_STATUS AND
F.REC_STATUS = X.REC_STATUS AND (X.PAYMENT_FEEITEM_STATUS !=
'VOIDED' OR X.PAYMENT_FEEITEM_STATUS IS NULL) AND
"X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY X.SERV_PROV_
Plan hash value: 2372462434
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-
Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | | |
| 1 | HASH GROUP BY | |
1 | 745K| 745K| 1148K (0)|
|* 2 | HASH JOIN | |
21543 | 744K| 744K| 1426K (0)|
| 3 | NESTED LOOPS |
| | | | |
| 4 | NESTED LOOPS | |
95522 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
24 | | | |
|* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX |
24 | | | |
|* 7 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK |
1 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM |
3907 | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
|* 9 | TABLE ACCESS FULL | F4FEEITEM |
1173K| | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"F"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"F"."REC_STATUS"="X"."REC_STATUS")
5 - filter("S"."REC_STATUS"='A')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')
7 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3")
filter("S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE")
8 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR
"X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND
"X"."REC_STATUS"='A' AND
"S"."REC_STATUS"="X"."REC_STATUS"))
9 - filter(("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."REC_STATUS"='A'))
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only
collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or
system level
59 rows selected.
Elapsed: 00:00:05.65
lsllcm
Thanks for your comments, below are bad plan with
gather_plan_statistics.
SQL> SELECT /*+ gather_plan_statistics */
Elapsed: 00:00:13.39
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2t5d84xnrdhzm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ X.SERV_PROV_CODE,
X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3,
SUM(NVL(X.Fee_Allocation, 0)) GF_FEE FROM SETDETAILS S,
X4PAYMENT_FEEITEM X, F4FEEITEM F WHERE S.SERV_PROV_CODE =
'SACRAMENTO' AND S.SET_ID = 'CONNIE' AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE AND F.SERV_PROV_CODE =
S.SERV_PROV_CODE AND S.B1_PER_ID1 = X.B1_PER_ID1 AND
S.B1_PER_ID2
= X.B1_PER_ID2 AND S.B1_PER_ID3 = X.B1_PER_ID3 AND F.B1_PER_ID1
=
X.B1_PER_ID1 AND F.B1_PER_ID2 = X.B1_PER_ID2 AND F.B1_PER_ID3 =
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
X.B1_PER_ID3 AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR AND
S.REC_STATUS = X.REC_STATUS AND F.REC_STATUS = X.REC_STATUS AND
(X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR X.PAYMENT_FEEITEM_STATUS IS
NULL) AND "X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1,
X.B1_PER
Plan hash value: 2372462434
------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | 4 |00:00:13.27 | 34058 | 33097 | |
| |
| 1 | HASH GROUP BY | |
1 | 1 | 4 |00:00:13.27 | 34058 | 33097 | 745K| 745K|
1165K (0)|
|* 2 | HASH JOIN | |
1 | 91293 | 13 |00:00:13.98 | 34058 | 33097 | 744K| 744K|
1427K (0)|
| 3 | NESTED LOOPS | |
1 | | 28 |00:00:00.01 | 62 | 0 | |
| |
| 4 | NESTED LOOPS | |
1 | 95522 | 44 |00:00:00.01 | 27 | 0 | |
| |
|* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
1 | 24 | 12 |00:00:00.01 | 6 | 0 | |
| |
|* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX |
1 | 24 | 12 |00:00:00.01 | 3 | 0 | |
| |
|* 7 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK |
12 | 1 | 44 |00:00:00.01 | 21 | 0 | |
| |
|* 8 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM |
44 | 3907 | 28 |00:00:00.01 | 35 | 0 | |
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 9 | TABLE ACCESS FULL | F4FEEITEM |
1 | 1173K| 1174K|00:00:10.84 | 33996 | 33097 | |
| |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"F"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"F"."REC_STATUS"="X"."REC_STATUS")
5 - filter("S"."REC_STATUS"='A')
6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')
7 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lsllcm
Thanks for your comments, below are good plan with
gather_plan_statistics.
SQL> SELECT /*+ gather_plan_statistics */
2 X.SERV_PROV_CODE,
3 X.B1_PER_ID1,
4 X.B1_PER_ID2,
5 X.B1_PER_ID3,
6 SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
7 FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
8 WHERE
9 S.SERV_PROV_CODE = 'SACRAMENTO'
10 AND S.SET_ID = 'CONNIE'
11 AND S.REC_STATUS = 'A'
12 AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
13 AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
14 AND S.B1_PER_ID1 = X.B1_PER_ID1
15 AND S.B1_PER_ID2 = X.B1_PER_ID2
16 AND S.B1_PER_ID3 = X.B1_PER_ID3
17 AND F.B1_PER_ID1 = S.B1_PER_ID1
18 AND F.B1_PER_ID2 = S.B1_PER_ID2
19 AND F.B1_PER_ID3 = S.B1_PER_ID3
20 AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
21 AND S.REC_STATUS = X.REC_STATUS
22 AND F.REC_STATUS = X.REC_STATUS
23 AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
24 X.PAYMENT_FEEITEM_STATUS IS NULL)
25 AND "X"."SERV_PROV_CODE"='SACRAMENTO'
26 AND "F"."SERV_PROV_CODE"='SACRAMENTO'
27 AND "X"."REC_STATUS"='A'
28 AND "F"."REC_STATUS"='A'
29 GROUP BY
30 X.SERV_PROV_CODE,
31 X.B1_PER_ID1,
32 X.B1_PER_ID2,
33 X.B1_PER_ID3
34 /
Elapsed: 00:00:00.26
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4tc7hc9r2t25s, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ X.SERV_PROV_CODE,
X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3,
SUM(NVL(X.Fee_Allocation, 0)) GF_FEE FROM SETDETAILS S,
X4PAYMENT_FEEITEM X, F4FEEITEM F WHERE S.SERV_PROV_CODE =
'SACRAMENTO' AND S.SET_ID = 'CONNIE' AND S.REC_STATUS = 'A'
AND S.SERV_PROV_CODE = X.SERV_PROV_CODE AND F.SERV_PROV_CODE =
S.SERV_PROV_CODE AND S.B1_PER_ID1 = X.B1_PER_ID1 AND
S.B1_PER_ID2
= X.B1_PER_ID2 AND S.B1_PER_ID3 = X.B1_PER_ID3 AND F.B1_PER_ID1
=
S.B1_PER_ID1 AND F.B1_PER_ID2 = S.B1_PER_ID2 AND F.B1_PER_ID3 =
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S.B1_PER_ID3 AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR AND
S.REC_STATUS = X.REC_STATUS AND F.REC_STATUS = X.REC_STATUS AND
(X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR X.PAYMENT_FEEITEM_STATUS IS
NULL) AND "X"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."SERV_PROV_CODE"='SACRAMENTO' AND "X"."REC_STATUS"='A' AND
"F"."REC_STATUS"='A' GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1,
X.B1_PER
Plan hash value: 305769021
------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | 4 |00:00:00.02 | 77 | 3 | |
| |
| 1 | HASH GROUP BY | |
1 | 60951 | 4 |00:00:00.02 | 77 | 3 | 745K| 745K|
1360K (0)|
| 2 | NESTED LOOPS | |
1 | | 13 |00:00:00.01 | 77 | 3 | |
| |
| 3 | NESTED LOOPS | |
1 | 60951 | 13 |00:00:00.01 | 65 | 3 | |
| |
| 4 | NESTED LOOPS | |
1 | 129 | 27 |00:00:00.01 | 33 | 3 | |
| |
|* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS |
1 | 24 | 12 |00:00:00.01 | 6 | 0 | |
| |
|* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX |
1 | 24 | 12 |00:00:00.01 | 3 | 0 | |
| |
|* 7 | TABLE ACCESS BY INDEX ROWID| F4FEEITEM |
12 | 5 | 27 |00:00:00.01 | 27 | 3 | |
| |
|* 8 | INDEX RANGE SCAN | F4FEEITEM_PK |
12 | 1 | 27 |00:00:00.01 | 19 | 0 | |
| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 9 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK |
27 | 1 | 13 |00:00:00.01 | 32 | 0 | |
| |
|* 10 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM |
13 | 473 | 13 |00:00:00.01 | 12 | 0 | |
| |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("S"."REC_STATUS"='A')
6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE')
7 - filter("F"."REC_STATUS"='A')
8 - access("F"."SERV_PROV_CODE"='SACRAMENTO' AND
"F"."B1_PER_ID1"="S"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="S"."B1_PER_ID2" AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"F"."B1_PER_ID3"="S"."B1_PER_ID3")
filter("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE")
9 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR")
filter(("S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR"))
10 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR
"X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND "X"."REC_STATUS"='A' AND
"S"."REC_STATUS"="X"."REC_STATUS" AND
"F"."REC_STATUS"="X"."REC_STATUS"))
Thanks
lsllcm
lsllcm
>
> - 显示引用的文字 -
Hi Charles,
Thanks for your comments.
Maybe I miss something, I use below sql to check again. there is no
records returned. Is there other method to check sql profile.
I did not use dbms_sqltune package to create sql profile. Maybe 11g
create it automatically.
SQL>
SQL> select name, status
2 from dba_sql_profiles
3 ;
NAME STATUS
------------------------------ --------
Thanks
lsllcm
lsllcm
I have put both 10053 trace for both execution plan as below url,
could you help have a look?
http://docs.google.com/View?id=ddqzpk2x_4cmpn7tfv
http://docs.google.com/View?id=ddqzpk2x_5db7q59fw
Thanks
lsllcm
Charles Hooper
Thanks for posting all of the information. I looked at the 10053
trace files. The one identifying characteristic of the fast executing
SQL statement is the almost immediate substitution of the original SQL
statement with a hinted version of the SQL statement. This is from
the 10053 trace for the fast execution:
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=4.000000 ) OPT_ESTIMATE (TABLE
"F" MIN=27.000000 ) OPT_ESTIMATE (INDEX_SCAN "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (INDEX_FILTER "F" "F4FEEITEM_PK"
MIN=27.000000 ) OPT_ESTIMATE (TABLE "X" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_SCAN "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_FILTER "X" "X4PAYMENT_FEEITEM_PK" MIN=13.000000 ) */
"X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
"B1_PER_ID1","X"."B1_PER_ID2" "B1_PER_ID2","X"."B1_PER_ID3"
"B1_PER_ID3",SUM(NVL("X"."FEE_ALLOCATION",0)) "GF_FEE" FROM
"test"."SETDETAILS" "S","test"."X4PAYMENT_FEEITEM"
"X","test"."F4FEEITEM" "F" WHERE "S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE' AND "S"."REC_STATUS"='A' AND
"S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE" AND
"F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."B1_PER_ID1"="S"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="S"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="S"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"S"."REC_STATUS"="X"."REC_STATUS" AND
"F"."REC_STATUS"="X"."REC_STATUS" AND
("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL) GROUP BY
"X"."SERV_PROV_CODE","X"."B1_PER_ID1","X"."B1_PER_ID2","X"."B1_PER_ID3"
JE: cfro: SETDETAILS objn:652770 col#:1 dfro:F4FEEITEM dcol#:1
JE: cfro: SETDETAILS objn:74851 col#:1 dfro:X4PAYMENT_FEEITEM dcol#:
1
This is the same section from the slow execution:
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "X"."SERV_PROV_CODE" "SERV_PROV_CODE","X"."B1_PER_ID1"
"B1_PER_ID1","X"."B1_PER_ID2" "B1_PER_ID2","X"."B1_PER_ID3"
"B1_PER_ID3",SUM(NVL("X"."FEE_ALLOCATION",0)) "GF_FEE" FROM
"test"."SETDETAILS" "S","test"."X4PAYMENT_FEEITEM"
"X","test"."F4FEEITEM" "F" WHERE "S"."SERV_PROV_CODE"='SACRAMENTO' AND
"S"."SET_ID"='CONNIE' AND "S"."REC_STATUS"='A' AND
"S"."SERV_PROV_CODE"="X"."SERV_PROV_CODE" AND
"F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND
"S"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"S"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"S"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."B1_PER_ID1"="X"."B1_PER_ID1" AND
"F"."B1_PER_ID2"="X"."B1_PER_ID2" AND
"F"."B1_PER_ID3"="X"."B1_PER_ID3" AND
"F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND
"S"."REC_STATUS"="X"."REC_STATUS" AND
"F"."REC_STATUS"="X"."REC_STATUS" AND
("X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED' OR
"X"."PAYMENT_FEEITEM_STATUS" IS NULL) GROUP BY
"X"."SERV_PROV_CODE","X"."B1_PER_ID1","X"."B1_PER_ID2","X"."B1_PER_ID3"
JE: cfro: SETDETAILS objn:652770 col#:1 dfro:F4FEEITEM dcol#:1
JE: cfro: SETDETAILS objn:74851 col#:1 dfro:X4PAYMENT_FEEITEM dcol#:
1
*Something* is triggering the SQL statement replacement early in the
10053 trace, but I am not able to determine the source of the change.
DBA_SQL_PROFILES should show SQL profiles for the SQL statement - I
can't explain why you do not see any rows when querying that view.
SQL plan baselines, I believe, are another possibility (looking back
at Randolf's post, he has already mentioned this).
From:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/optplanmgmt.htm
"In Oracle Database 11g, an automatically configured task runs the SQL
Tuning Advisor during a maintenance window. This automatic SQL tuning
task targets high-load SQL statements as identified by the execution
performance data collected in the Automatic Workload Repository (AWR)
snapshots. The automatic SQL tuning task implements the SQL profile
recommendations made by the SQL tuning advisor. Thus, the database
automatically adds tuned plans to the SQL plan baselines of the
identified high-load SQL statements."
The above quote seems to indicate that the database attempts to
automatically "fix" itself when it finds a slow executing SQL
statement...
An explanation of SQL plan baselines from the book "Troubleshooting
Oracle Performance":
http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA291#v=onepage&q=&f=false
Parts 1-3 (of 4) about SQL plan baselines from the Oracle development
group:
http://optimizermagic.blogspot.com/2009_01_01_archive.html
The above web page shows the following SQL statement to check for SQL
plan baselines:
select sql_text, plan_name, enabled, accepted from
dba_sql_plan_baselines;
Randolf's suggestion to display the actual number of rows to the
estimated cardinalities was a good suggestion. The fast executing
plan includes the following:
| E-Rows | A-Rows | A-Time |
-------------------------------
| | 4 |00:00:00.02 |
| 60951 | 4 |00:00:00.02 |
| | 13 |00:00:00.01 |
| 60951 | 13 |00:00:00.01 |
| 129 | 27 |00:00:00.01 |
| 24 | 12 |00:00:00.01 |
| 24 | 12 |00:00:00.01 |
| 5 | 27 |00:00:00.01 |
| 1 | 27 |00:00:00.01 |
| 1 | 13 |00:00:00.01 |
| 473 | 13 |00:00:00.01 |
-------------------------------
The slow executing plan includes the following:
| E-Rows | A-Rows | A-Time |
-------------------------------
| | 4 |00:00:13.27 |
| 1 | 4 |00:00:13.27 |
| 91293 | 13 |00:00:13.98 |
| | 28 |00:00:00.01 |
| 95522 | 44 |00:00:00.01 |
| 24 | 12 |00:00:00.01 |
| 24 | 12 |00:00:00.01 |
| 1 | 44 |00:00:00.01 |
| 3907 | 28 |00:00:00.01 |
| 1173K| 1174K|00:00:10.84 |
-------------------------------
Except for the full table scan operation (the last operation in the
slow plan), the actual number of rows returned is significantly less
than the estimated number of rows. That probably explains why the
slow plan uses a full table scan on a 1.17 million row table rather
than an index range scan to return the 27 rows from that table.
The only suggestion that I have at this time is to check
dba_sql_plan_baselines. You could use a series of hints to prevent
the full table scan of F4FEEITEM, and to prevent the hash join
operation, but I suspect that this is not a path you would want to
take.
Jonathan Lewis and Randolf have both spent much more time working with
10053 trace files than I have - one of them might know of another
event number that should be enabled with a 10053 trace to explain why
the SQL statement in the fast execution was replaced immediately. I
suspect that other contributors to this group would also have ideas to
explain the substitution.
lsllcm
> "In Oracle Database 11g, an automatically configured task runs the SQL
> Tuning Advisor during a maintenance window. This automatic SQL tuning
> task targets high-load SQL statements as identified by the execution
> performance data collected in the Automatic Workload Repository (AWR)
> snapshots. The automatic SQL tuning task implements the SQL profile
> recommendations made by the SQL tuning advisor. Thus, the database
> automatically adds tuned plans to the SQL plan baselines of the
> identified high-load SQL statements."
>
> The above quote seems to indicate that the database attempts to
> automatically "fix" itself when it finds a slow executing SQL
> statement...
>
> An explanation of SQL plan baselines from the book "Troubleshooting
Hi Charles,
Thanks for your following up the issue. I guess I have stoped
automatically sql plan job (sql tuning advisor in view
dba_autotask_task).
I checked below three views:
SQL> select what from dba_jobs;
WHAT
--------------------------------------------------------------------------------
wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
wwv_flow_mail.push_queue(wwv_flow_platform.get_preference
('SMTP_HOST_ADDRESS'),w
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
SQL> select job_name,program_name from dba_scheduler_jobs;
JOB_NAME PROGRAM_NAME
------------------------------
--------------------------------------------------------------------------------
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE
RSE$CLEAN_RECOVERABLE_SCRIPT
FGR$AUTOPURGE_JOB
BSLN_MAINTAIN_STATS_JOB BSLN_MAINTAIN_STATS_PROG
DRA_REEVALUATE_OPEN_FAILURES
HM_CREATE_OFFLINE_DICTIONARY
ORA$AUTOTASK_CLEAN ORA$AGE_AUTOTASK_DATA
FILE_WATCHER FILE_WATCHER_PROGRAM
PURGE_LOG PURGE_LOG_PROG
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB
RLM$SCHDNEGACTION
RLM$EVTCLEANUP
14 rows selected
SQL> select client_name,task_name from dba_autotask_task;
CLIENT_NAME
TASK_NAME
----------------------------------------------------------------
----------------------------------------------------------------
sql tuning advisor
AUTO_SQL_TUNING_PROG
auto optimizer stats collection
gather_stats_prog
auto space advisor
auto_space_advisor_prog
There is no sql plan job now.
I also checked the view dba_sql_plan_baselines, there is also no
records
Because we maybe meet many this kind of issue, we don't want to use
sql plan method to handle it.
Thanks
lsllcm
Randolf Geist
You might want to read this:
http://jonathanlewis.wordpress.com/2009/12/16/adaptive-optimisation/
which very likely explains what makes the difference between the
"good" and the "bad" plan on 11.2.0.1 and why you're not able to find
any baselines, SQL Profiles etc. and still get these OPT_ESTIMATE
hints.
It doesn't explain why you can't reproduce the "good" performance when
you manually add these OPT_ESTIMATE hints, but since the statements
differ in their join predicates and you haven't shown us what you get
exactly when adding these hints I'm not entirely sure about this.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684