Re: Buffer Gets question

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Thu, 4 May 2017 10:11:45 +0200
Message-ID: <64836f04-4afe-7766-0a12-86d73dced602_at_bluewin.ch>


Hi Stefan,

agreed. The file is a mess, hard to read. On a first glance the estimation does look ok. Probably the hints are not needed. I also get very suspicious when I see such a long line of hints. I do not remember on positive case on that.
When you look at the sharp drop of retrieved rows (288560 to 40) I get the impression that there is an index missing supporting a join. The inefficiency seems to be in the access to table QP_PRICING_ATTRIBUTES . It is also possible that join conditions are messed up for that table.

Regards

Lothar

On 03.05.2017 17:22, Stefan Koehler wrote:
> 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: _at_OracleSK
> Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
>
>> "Hameed, Amir" <Amir.Hameed_at_xerox.com> 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.
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 




--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 04 2017 - 10:11:45 CEST

Original text of this message