RE: Buffer Gets question
- From: Stefan Koehler <contact@xxxxxxxx>
- To: Amir.Hameed@xxxxxxxxx, 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 May 2017 17:22:15 +0200 (CEST)
Hey Amir,
We have a SEV-1 open with Oracle at the moment and their development is
looking into the issue.
So it is accepted as some kind of bug?
I am attaching a file with the complete statement and statistics from TKP in
case someone is interested in looking into this information.
Can you please provide this output in DBMS_XPLAN ("SQL> select * from table
(DBMS_XPLAN.DISPLAY_CURSOR('&1',&2,'ALL ALLSTATS OUTLINE PEEKED_BINDS
LAST'));") format? To be honest i am too lazy to manually correlate each
execution plan line for E-Rows and A-Rows. In addition it seems like there is
more going on behind (e.g. views?).
However when looking at your SQL statement one thing is eye-catching:
- /*+ ORDERED USE_NL(QPLAT_PROD QPA QPL QPH) index(QPA
QP_PRICING_ATTRIBUTES_N5) dynamic_sampling(1) l_list_cur */
- /*+ 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 */
Can you please tell us what you are trying to do here? Every time i see
something like this it is very suspicious that you tried to do a NL join order
like "QPLAT_PROD -> QPA -> QPL -> QPH" but this is not how the hint works:
https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/
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
"Hameed, Amir" <Amir.Hameed@xxxxxxxxx> hat am 3. Mai 2017 um 14:37
geschrieben:
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
