I made a mistake while pasting statistics. The query has the exact same plan
hash value in both systems, which is 3209742519.
The load profile is different in production and the load test environment in
that the number of lines per contract in the latter is more. But, whatever we
are testing in the load test environment is going to eventually start running
in production in about two weeks, which means that we will most likely start
seeing the same issue in production as well. We have a SEV-1 open with Oracle
at the moment and their development is looking into the issue. I am attaching a
file with the complete statement and statistics from TKP in case someone is
interested in looking into this information.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] ;
Sent: Wednesday, May 03, 2017 4:03 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>;
contact@xxxxxxxx
Subject: Re: Buffer Gets question
Furthermore, while it's a clue based on far too little information, a larger
number of rows for a smaller number of buffer gets has the flavour of Oracle
switching to a hash join to get more data to avoid using a nested loop to
acquire a "large" result set .
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Stefan Koehler <contact@xxxxxxxx>
Sent: 03 May 2017 07:43
To: Amir.Hameed@xxxxxxxxx; 'ORACLE-L'
Subject: Re: Buffer Gets question
Hello Amir,
1) Your plan hash value is different between production and load test
environment (3209742519 vs. 209742519)
2) 8237 avg buffer gets per execution vs. 70500 avg buffer gets per execution
in regard to 1313 avg rows per execution vs. 90 avg rows per execution shows
that you are doing something enormously different in both environments
3) But besides all this you also can have the same plan hash value but
enormously different performance (e.g. different predicate section, hash
collisions with scalar subquery caching for different data, etc.)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
SQL ID: 8752wtxdch988 Plan Hash: 3209742519
SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPL QPH) index(QPA
QP_PRICING_ATTRIBUTES_N5) dynamic_sampling(1) l_list_cur */
QPLAT_PROD.LINE_INDEX, QPL.LIST_LINE_ID, QPL.ARITHMETIC_OPERATOR,
QPL.OPERAND, QPL.PRICING_GROUP_SEQUENCE, QPL.LIST_LINE_TYPE_CODE,
QPL.PRICING_PHASE_ID, QPL.AUTOMATIC_FLAG, QPL.INCOMPATIBILITY_GRP_CODE,
QPL.PRICE_BY_FORMULA_ID, QPL.PRODUCT_PRECEDENCE, QPL.PRIMARY_UOM_FLAG,
QPL.MODIFIER_LEVEL_CODE, QPL.LIST_LINE_NO, QPA.PRODUCT_UOM_CODE,
QPA.EXCLUDER_FLAG, QPH.LIST_TYPE_CODE, QPH.ROUNDING_FACTOR,
QPH.LIST_HEADER_ID LIST_HEADER_ID, QPLAT_PROD.CONTEXT
PRODUCT_ATTRIBUTE_CONTEXT, QPLAT_PROD.ATTRIBUTE PRODUCT_ATTRIBUTE,
QPLAT_PROD.VALUE_FROM PRODUCT_ATTR_VALUE_FROM, QPLAT_PROD.ATTRIBUTE_LEVEL
PRODUCT_ATTRIBUTE_LEVEL, QPLAT_PROD.ATTRIBUTE_TYPE PRODUCT_ATTRIBUTE_TYPE,
QPLAT_PROD.APPLIED_FLAG, NULL PRICING_ATTRIBUTE_CONTEXT, NULL
PRICING_ATTRIBUTE, NULL PRICING_ATTR_VALUE_FROM, NULL
PRICING_SETUP_VALUE_FROM, NULL PRICING_SETUP_VALUE_TO, NULL
PRICING_ATTRIBUTE_LEVEL, NULL PRICING_ATTRIBUTE_TYPE, NULL
PRICING_OPERATOR_TYPE, NULL PRICING_ATTRIBUTE_DATATYPE ,QPL.BREAK_UOM_CODE ,
QPL.BREAK_UOM_CONTEXT ,QPL.BREAK_UOM_ATTRIBUTE ,QPL.PRICE_BREAK_TYPE_CODE
FROM
QP_NPREQ_LINES_TMP QPLINES, QP_NPREQ_LINE_ATTRS_TMP QPLAT_PROD,
QP_PRICING_ATTRIBUTES QPA, QP_LIST_LINES QPL, QP_LIST_HEADERS_B QPH,
QP_PRICE_REQ_SOURCES_V QPRS WHERE QPA.PRICING_PHASE_ID = :B1 AND
QPA.QUALIFICATION_IND IN (:B5 , :B4 ) AND QPLAT_PROD.CONTEXT =
QPA.PRODUCT_ATTRIBUTE_CONTEXT AND QPLINES.LIST_PRICE_OVERRIDE_FLAG IN ('N',
'O') AND (QPLINES.PRICE_FLAG = :B3 OR (QPLINES.PRICE_FLAG = :B10 AND :B9 =
:B3 )) AND QPLINES.LINE_TYPE_CODE <> :B8 AND QPLINES.VALIDATED_FLAG = :B3
AND QPLINES.QUALIFIERS_EXIST_FLAG IN (:B3 ,:B7 ) AND QPLAT_PROD.ATTRIBUTE =
QPA.PRODUCT_ATTRIBUTE AND QPLAT_PROD.VALUE_FROM = QPA.PRODUCT_ATTR_VALUE
AND QPA.EXCLUDER_FLAG = :B7 AND QPLAT_PROD.PRICING_STATUS_CODE = :B6 AND
QPH.LIST_HEADER_ID = QPL.LIST_HEADER_ID AND QPA.LIST_HEADER_ID =
QPLINES.PRICE_LIST_HEADER_ID AND QPA.LIST_LINE_ID = QPL.LIST_LINE_ID AND
QPL.QUALIFICATION_IND IN (:B5 , :B4 ) AND QPLINES.PRICING_EFFECTIVE_DATE
BETWEEN NVL(TRUNC(QPH.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)
AND NVL(TRUNC(QPH.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE) AND
NVL(QPLINES.START_DATE_ACTIVE_FIRST,TRUNC(SYSDATE)) BETWEEN
NVL(QPH.START_DATE_ACTIVE_FIRST, NVL(QPLINES.START_DATE_ACTIVE_FIRST,
TRUNC(SYSDATE) )) AND NVL(QPH.END_DATE_ACTIVE_FIRST,
NVL(QPLINES.START_DATE_ACTIVE_FIRST, TRUNC(SYSDATE) )) AND
NVL(QPLINES.START_DATE_ACTIVE_SECOND,TRUNC(SYSDATE)) BETWEEN
NVL(QPH.START_DATE_ACTIVE_SECOND, NVL(QPLINES.START_DATE_ACTIVE_SECOND,
TRUNC(SYSDATE) )) AND NVL(QPH.END_DATE_ACTIVE_SECOND,
NVL(QPLINES.START_DATE_ACTIVE_SECOND, TRUNC(SYSDATE) )) AND
NVL(QPLINES.ACTIVE_DATE_FIRST_TYPE,'X') = NVL(QPH.ACTIVE_DATE_FIRST_TYPE,
NVL(QPLINES.ACTIVE_DATE_FIRST_TYPE,'X')) AND
NVL(QPLINES.ACTIVE_DATE_SECOND_TYPE,'X') = NVL(QPH.ACTIVE_DATE_SECOND_TYPE,
NVL(QPLINES.ACTIVE_DATE_SECOND_TYPE,'X')) AND
QPLINES.PRICING_EFFECTIVE_DATE BETWEEN NVL(TRUNC(QPL.START_DATE_ACTIVE),
QPLINES.PRICING_EFFECTIVE_DATE) AND NVL(TRUNC(QPL.END_DATE_ACTIVE),
QPLINES.PRICING_EFFECTIVE_DATE) AND QPH.CURRENCY_CODE =
QPLINES.CURRENCY_CODE AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE
AND QPLINES.REQUEST_TYPE_CODE = QPRS.REQUEST_TYPE_CODE AND QPH.ACTIVE_FLAG =
:B3 AND QPLAT_PROD.ATTRIBUTE_TYPE = :B2 AND QPLAT_PROD.LINE_INDEX =
QPLINES.LINE_INDEX AND QPL.PRICING_PHASE_ID = :B1 AND
(INSTR(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) UNION ALL SELECT /*+ ORDERED
USE_NL(QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA
QP_PRICING_ATTRIBUTES_N5) index(qplat_pric qp_preq_line_attrs_tmp_N2)
dynamic_sampling(1) l_list_cur */ QPLAT_PROD.LINE_INDEX, QPL.LIST_LINE_ID,
QPL.ARITHMETIC_OPERATOR, QPL.OPERAND, QPL.PRICING_GROUP_SEQUENCE,
QPL.LIST_LINE_TYPE_CODE, QPL.PRICING_PHASE_ID, QPL.AUTOMATIC_FLAG,
QPL.INCOMPATIBILITY_GRP_CODE, QPL.PRICE_BY_FORMULA_ID,
QPL.PRODUCT_PRECEDENCE, QPL.PRIMARY_UOM_FLAG, QPL.MODIFIER_LEVEL_CODE,
QPL.LIST_LINE_NO, QPA.PRODUCT_UOM_CODE, QPA.EXCLUDER_FLAG,
QPH.LIST_TYPE_CODE, QPH.ROUNDING_FACTOR, QPH.LIST_HEADER_ID LIST_HEADER_ID,
QPLAT_PROD.CONTEXT PRODUCT_ATTRIBUTE_CONTEXT, QPLAT_PROD.ATTRIBUTE
PRODUCT_ATTRIBUTE, QPLAT_PROD.VALUE_FROM PRODUCT_ATTR_VALUE_FROM,
QPLAT_PROD.ATTRIBUTE_LEVEL PRODUCT_ATTRIBUTE_LEVEL,
QPLAT_PROD.ATTRIBUTE_TYPE PRODUCT_ATTRIBUTE_TYPE, QPLAT_PROD.APPLIED_FLAG,
QPLAT_PRIC.CONTEXT PRICING_ATTRIBUTE_CONTEXT, QPLAT_PRIC.ATTRIBUTE
PRICING_ATTRIBUTE, QPLAT_PRIC.VALUE_FROM PRICING_ATTR_VALUE_FROM,
QPA.PRICING_ATTR_VALUE_FROM PRICING_SETUP_VALUE_FROM,
QPA.PRICING_ATTR_VALUE_TO PRICING_SETUP_VALUE_TO,
QPLAT_PRIC.ATTRIBUTE_LEVEL PRICING_ATTRIBUTE_LEVEL,
QPLAT_PRIC.ATTRIBUTE_TYPE PRICING_ATTRIBUTE_TYPE,
QPA.COMPARISON_OPERATOR_CODE PRICING_OPERATOR_TYPE,
QPA.PRICING_ATTRIBUTE_DATATYPE PRICING_ATTRIBUTE_DATATYPE ,
QPL.BREAK_UOM_CODE ,QPL.BREAK_UOM_CONTEXT ,QPL.BREAK_UOM_ATTRIBUTE ,
QPL.PRICE_BREAK_TYPE_CODE FROM QP_NPREQ_LINES_TMP QPLINES,
QP_NPREQ_LINE_ATTRS_TMP QPLAT_PROD, QP_PRICING_ATTRIBUTES QPA,
QP_NPREQ_LINE_ATTRS_TMP QPLAT_PRIC, QP_LIST_LINES QPL, QP_LIST_HEADERS_B
QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPA.PRICING_PHASE_ID = :B1 AND
QPA.QUALIFICATION_IND IN (:B12 ,:B11 ) AND QPLAT_PROD.CONTEXT =
QPA.PRODUCT_ATTRIBUTE_CONTEXT AND QPLINES.LIST_PRICE_OVERRIDE_FLAG IN ('N',
'O') AND (QPLINES.PRICE_FLAG = :B3 OR (QPLINES.PRICE_FLAG = :B10 AND :B9 =
:B3 )) AND QPLINES.LINE_TYPE_CODE <> :B8 AND QPLINES.VALIDATED_FLAG = :B3
AND QPLINES.QUALIFIERS_EXIST_FLAG IN (:B3 ,:B7 ) AND
QPLINES.PRICING_ATTRS_EXIST_FLAG = :B3 AND QPLAT_PROD.ATTRIBUTE =
QPA.PRODUCT_ATTRIBUTE AND QPLAT_PROD.VALUE_FROM = QPA.PRODUCT_ATTR_VALUE
AND QPA.EXCLUDER_FLAG = :B7 AND QPLAT_PROD.PRICING_STATUS_CODE = :B6 AND
QPA.PRICING_ATTRIBUTE_CONTEXT IS NOT NULL AND QPLAT_PRIC.CONTEXT =
QPA.PRICING_ATTRIBUTE_CONTEXT AND QPLAT_PRIC.ATTRIBUTE =
QPA.PRICING_ATTRIBUTE AND (QPLAT_PRIC.VALUE_FROM =
QPA.PRICING_ATTR_VALUE_FROM OR QPA.COMPARISON_OPERATOR_CODE IN ('NOT =',
:B14 )) AND QPLAT_PRIC.ATTRIBUTE_TYPE = :B13 AND QPLAT_PRIC.
PRICING_STATUS_CODE = :B6 AND QPLAT_PRIC.LINE_INDEX = QPLAT_PROD.LINE_INDEX
AND QPH.LIST_HEADER_ID = QPL.LIST_HEADER_ID AND QPA.LIST_HEADER_ID =
QPLINES.PRICE_LIST_HEADER_ID AND QPA.LIST_LINE_ID = QPL.LIST_LINE_ID AND
QPL.QUALIFICATION_IND IN (:B12 ,:B11 ) AND QPLINES.PRICING_EFFECTIVE_DATE
BETWEEN NVL(TRUNC(QPH.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)
AND NVL(TRUNC(QPH.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE) AND
NVL(QPLINES.START_DATE_ACTIVE_FIRST,TRUNC(SYSDATE)) BETWEEN
NVL(QPH.START_DATE_ACTIVE_FIRST, NVL(QPLINES.START_DATE_ACTIVE_FIRST,
TRUNC(SYSDATE) )) AND NVL(QPH.END_DATE_ACTIVE_FIRST,
NVL(QPLINES.START_DATE_ACTIVE_FIRST, TRUNC(SYSDATE) )) AND
NVL(QPLINES.START_DATE_ACTIVE_SECOND,TRUNC(SYSDATE)) BETWEEN
NVL(QPH.START_DATE_ACTIVE_SECOND, NVL(QPLINES.START_DATE_ACTIVE_SECOND,
TRUNC(SYSDATE) )) AND NVL(QPH.END_DATE_ACTIVE_SECOND,
NVL(QPLINES.START_DATE_ACTIVE_SECOND, TRUNC(SYSDATE) )) AND
NVL(QPLINES.ACTIVE_DATE_FIRST_TYPE,'X') = NVL(QPH.ACTIVE_DATE_FIRST_TYPE,
NVL(QPLINES.ACTIVE_DATE_FIRST_TYPE,'X')) AND
NVL(QPLINES.ACTIVE_DATE_SECOND_TYPE,'X') = NVL(QPH.ACTIVE_DATE_SECOND_TYPE,
NVL(QPLINES.ACTIVE_DATE_SECOND_TYPE,'X')) AND
QPLINES.PRICING_EFFECTIVE_DATE BETWEEN NVL(TRUNC(QPL.START_DATE_ACTIVE),
QPLINES.PRICING_EFFECTIVE_DATE) AND NVL(TRUNC(QPL.END_DATE_ACTIVE),
QPLINES.PRICING_EFFECTIVE_DATE) AND QPH.CURRENCY_CODE =
QPLINES.CURRENCY_CODE AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE
AND QPLINES.REQUEST_TYPE_CODE = QPRS.REQUEST_TYPE_CODE AND QPH.ACTIVE_FLAG =
:B3 AND QPLAT_PROD.ATTRIBUTE_TYPE = :B2 AND QPLAT_PROD.LINE_INDEX =
QPLINES.LINE_INDEX AND QPL.PRICING_PHASE_ID = :B1 AND
(INSTR(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) ORDER BY 1,2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1714 1.10 1.08 0 0 0 0
Fetch 1714 1582.29 1606.13 4360 120837211 0 154148
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3429 1583.40 1607.22 4360 120837211 0 154148
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (APPS) (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
40 40 40 SORT ORDER BY (cr=62588 pr=3166 pw=0
time=17950323 us cost=44 size=3564 card=2)
40 40 40 UNION-ALL (cr=62588 pr=3166 pw=0
time=17950163 us)
0 0 0 NESTED LOOPS (cr=22 pr=0 pw=0 time=113 us
cost=21 size=1639 card=1)
0 0 0 NESTED LOOPS (cr=22 pr=0 pw=0 time=112 us
cost=21 size=1639 card=1)
0 0 0 NESTED LOOPS (cr=22 pr=0 pw=0 time=112
us cost=20 size=1626 card=1)
0 0 0 NESTED LOOPS (cr=22 pr=0 pw=0 time=112
us cost=19 size=1614 card=1)
0 0 0 NESTED LOOPS (cr=22 pr=0 pw=0 time=111
us cost=18 size=1541 card=1)
0 0 0 NESTED LOOPS (cr=22 pr=0 pw=0
time=109 us cost=16 size=1451 card=1)
4 4 4 NESTED LOOPS (cr=4 pr=0 pw=0 time=59
us cost=12 size=1396 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PREQ_LINES_TMP_T (cr=2 pr=0 pw=0 time=37 us cost=3 size=1161 card=1)
1 1 1 INDEX RANGE SCAN
QP_PREQ_LINES_TMP_N3 (cr=1 pr=0 pw=0 time=20 us cost=2 size=0 card=1)(object id
1459273)
4 4 4 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PREQ_LINE_ATTRS_TMP_T (cr=2 pr=0 pw=0 time=22 us cost=9 size=235 card=1)
4 4 4 INDEX RANGE SCAN
QP_PREQ_LINE_ATTRS_TMP_N1 (cr=1 pr=0 pw=0 time=14 us cost=1 size=0
card=82)(object id 1459274)
0 0 0 INLIST ITERATOR (cr=18 pr=0 pw=0
time=48 us)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PRICING_ATTRIBUTES (cr=18 pr=0 pw=0 time=42 us cost=4 size=55 card=1)
0 0 0 INDEX RANGE SCAN
QP_PRICING_ATTRIBUTES_N5 (cr=18 pr=0 pw=0 time=38 us cost=3 size=0
card=1)(object id 1409342)
0 0 0 TABLE ACCESS BY INDEX ROWID
QP_LIST_LINES (cr=0 pr=0 pw=0 time=0 us cost=2 size=90 card=1)
0 0 0 INDEX UNIQUE SCAN QP_LIST_LINES_PK
(cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 1409194)
0 0 0 TABLE ACCESS BY INDEX ROWID
QP_LIST_HEADERS_B (cr=0 pr=0 pw=0 time=0 us cost=1 size=73 card=1)
0 0 0 INDEX UNIQUE SCAN QP_LIST_HEADERS_B_PK
(cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 1409129)
0 0 0 TABLE ACCESS BY INDEX ROWID
QP_PTE_REQUEST_TYPES_B (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
0 0 0 INDEX UNIQUE SCAN
QP_PTE_REQUEST_TYPES_B_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0
card=1)(object id 1409375)
0 0 0 INDEX UNIQUE SCAN
QP_PTE_SOURCE_SYSTEMS_U2 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object
id 1409405)
0 0 0 TABLE ACCESS BY INDEX ROWID
QP_PTE_SOURCE_SYSTEMS (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
40 40 40 NESTED LOOPS (cr=62566 pr=3166 pw=0
time=17949983 us cost=23 size=1925 card=1)
40 40 40 NESTED LOOPS (cr=62526 pr=3166 pw=0
time=17949870 us cost=23 size=1925 card=1)
40 40 40 NESTED LOOPS (cr=62522 pr=3166 pw=0
time=17949769 us cost=22 size=1912 card=1)
40 40 40 NESTED LOOPS (cr=62478 pr=3166 pw=0
time=17949557 us cost=21 size=1900 card=1)
40 40 40 NESTED LOOPS (cr=62468 pr=3166 pw=0
time=17949238 us cost=20 size=1827 card=1)
40 40 40 NESTED LOOPS (cr=62396 pr=3157 pw=0
time=17931815 us cost=18 size=1737 card=1)
288560 288560 288560 NESTED LOOPS (cr=62392 pr=3157 pw=0
time=16716986 us cost=16 size=1504 card=1)
4 4 4 NESTED LOOPS (cr=4 pr=0 pw=0
time=41 us cost=12 size=1398 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PREQ_LINES_TMP_T (cr=2 pr=0 pw=0 time=21 us cost=3 size=1163 card=1)
1 1 1 INDEX RANGE SCAN
QP_PREQ_LINES_TMP_N2 (cr=1 pr=0 pw=0 time=10 us cost=2 size=0 card=2)(object id
1459272)
4 4 4 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PREQ_LINE_ATTRS_TMP_T (cr=2 pr=0 pw=0 time=18 us cost=9 size=235 card=1)
4 4 4 INDEX RANGE SCAN
QP_PREQ_LINE_ATTRS_TMP_N1 (cr=1 pr=0 pw=0 time=10 us cost=1 size=0
card=82)(object id 1459274)
288560 288560 288560 INLIST ITERATOR (cr=62388 pr=3157
pw=0 time=16659584 us)
288560 288560 288560 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PRICING_ATTRIBUTES (cr=62388 pr=3157 pw=0 time=16592664 us cost=4 size=106
card=1)
432840 432840 432840 INDEX RANGE SCAN
QP_PRICING_ATTRIBUTES_N5 (cr=6354 pr=3157 pw=0 time=16147601 us cost=3 size=0
card=1)(object id 1409342)
40 40 40 TABLE ACCESS BY INDEX ROWID BATCHED
QP_PREQ_LINE_ATTRS_TMP_T (cr=4 pr=0 pw=0 time=1102439 us cost=2 size=233 card=1)
288560 288560 288560 INDEX RANGE SCAN
QP_PREQ_LINE_ATTRS_TMP_N2 (cr=3 pr=0 pw=0 time=665443 us cost=1 size=0
card=1)(object id 1459275)
40 40 40 TABLE ACCESS BY INDEX ROWID
QP_LIST_LINES (cr=72 pr=9 pw=0 time=17393 us cost=2 size=90 card=1)
40 40 40 INDEX UNIQUE SCAN QP_LIST_LINES_PK
(cr=58 pr=3 pw=0 time=16801 us cost=1 size=0 card=1)(object id 1409194)
40 40 40 TABLE ACCESS BY INDEX ROWID
QP_LIST_HEADERS_B (cr=10 pr=0 pw=0 time=289 us cost=1 size=73 card=1)
40 40 40 INDEX UNIQUE SCAN QP_LIST_HEADERS_B_PK
(cr=9 pr=0 pw=0 time=89 us cost=0 size=0 card=1)(object id 1409129)
40 40 40 TABLE ACCESS BY INDEX ROWID
QP_PTE_REQUEST_TYPES_B (cr=44 pr=0 pw=0 time=179 us cost=1 size=12 card=1)
40 40 40 INDEX UNIQUE SCAN
QP_PTE_REQUEST_TYPES_B_U1 (cr=4 pr=0 pw=0 time=67 us cost=0 size=0
card=1)(object id 1409375)
40 40 40 INDEX UNIQUE SCAN
QP_PTE_SOURCE_SYSTEMS_U2 (cr=4 pr=0 pw=0 time=72 us cost=0 size=0
card=1)(object id 1409405)
40 40 40 TABLE ACCESS BY INDEX ROWID
QP_PTE_SOURCE_SYSTEMS (cr=40 pr=0 pw=0 time=81 us cost=1 size=13 card=1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
40 SORT (ORDER BY)
40 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
4 NESTED LOOPS
1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF
'QP_PREQ_LINES_TMP_T' (TABLE (TEMP))
1 INDEX (RANGE SCAN) OF 'QP_PREQ_LINES_TMP_N3'
(INDEX)
4 TABLE ACCESS (BY INDEX ROWID BATCHED) OF
'QP_PREQ_LINE_ATTRS_TMP_T' (TABLE (TEMP))
4 INDEX (RANGE SCAN) OF
'QP_PREQ_LINE_ATTRS_TMP_N1' (INDEX)
0 INLIST ITERATOR
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID
BATCHED) OF 'QP_PRICING_ATTRIBUTES' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'QP_PRICING_ATTRIBUTES_N5' (INDEX)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_LIST_LINES' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_LIST_LINES_PK' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_LIST_HEADERS_B' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_LIST_HEADERS_B_PK' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_PTE_REQUEST_TYPES_B' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_PTE_REQUEST_TYPES_B_U1' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_PTE_SOURCE_SYSTEMS_U2' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_PTE_SOURCE_SYSTEMS' (TABLE)
40 NESTED LOOPS
40 NESTED LOOPS
40 NESTED LOOPS
40 NESTED LOOPS
40 NESTED LOOPS
40 NESTED LOOPS
288560 NESTED LOOPS
4 NESTED LOOPS
1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF
'QP_PREQ_LINES_TMP_T' (TABLE (TEMP))
1 INDEX (RANGE SCAN) OF
'QP_PREQ_LINES_TMP_N2' (INDEX)
4 TABLE ACCESS (BY INDEX ROWID BATCHED) OF
'QP_PREQ_LINE_ATTRS_TMP_T' (TABLE (TEMP))
4 INDEX (RANGE SCAN) OF
'QP_PREQ_LINE_ATTRS_TMP_N1' (INDEX)
288560 INLIST ITERATOR
288560 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID BATCHED) OF 'QP_PRICING_ATTRIBUTES' (TABLE)
432840 INDEX MODE: ANALYZED (RANGE SCAN) OF
'QP_PRICING_ATTRIBUTES_N5' (INDEX)
40 TABLE ACCESS (BY INDEX ROWID BATCHED) OF
'QP_PREQ_LINE_ATTRS_TMP_T' (TABLE (TEMP))
288560 INDEX (RANGE SCAN) OF
'QP_PREQ_LINE_ATTRS_TMP_N2' (INDEX)
40 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_LIST_LINES' (TABLE)
40 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_LIST_LINES_PK' (INDEX (UNIQUE))
40 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_LIST_HEADERS_B' (TABLE)
40 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_LIST_HEADERS_B_PK' (INDEX (UNIQUE))
40 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_PTE_REQUEST_TYPES_B' (TABLE)
40 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_PTE_REQUEST_TYPES_B_U1' (INDEX (UNIQUE))
40 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'QP_PTE_SOURCE_SYSTEMS_U2' (INDEX (UNIQUE))
40 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'QP_PTE_SOURCE_SYSTEMS' (TABLE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4360 0.08 18.91
Disk file operations I/O 3 0.00 0.00
latch: cache buffers chains 1 0.00 0.00
********************************************************************************
