RE: "Row Source Operation" section in TKP

I was able to pull bind values from DBA_HIST_SQLBIND view for the bad run. But, my question was more from the standpoint of clarifying my understanding of the section.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lothar Flatz Sent: Friday, September 16, 2016 9:57 AM To: oracle-l_at_freelists.org
Subject: Re: "Row Source Operation" section in TKP

Well, I would say "who cares which executions stats it is?". What you need is the statistic of the execution that went wrong. It would be good to have the bind variables for that execution. Given that, you can repeat your bad execution any time and create runtime stats.

Regards

Lothar
On 16.09.2016 15:42, Stefan Koehler wrote:
> Hey Amir,
>
>> So, it seems that the Row Source Operation section in TKP only
>> reports statistics from the first run? I was under the impression that this section shows cumulative value from all executions?
> It depends on how you enabled the extended SQL trace. Exemplary based
> on DBMS_MONITOR
> (http://docs.oracle.com/database/121/ARPLS/d_monitor.htm#ARPLS67178):
> plan_stat - Frequency at which we dump row source statistics. Value should be 'NEVER', 'FIRST_EXECUTION' (equivalent to NULL) or 'ALL_EXECUTIONS'
>
> Please check out Christian's blog post for the different row source statistic options, if you have enabled SQL trace by event and level:
> https://antognini.ch/2012/08/event-10046-full-list-of-levels/
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
>> "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 16. September 2016 um 15:22 geschrieben:
>>
>> I need some clarification on my understanding of the Row Source
>> Operation section in the TKP output file. The following statistics
>> show that the statement was executed 27 times and it ended up doing
>> over 300k IOs. However, the Row Source Operation section of the
>> statement shows that only one row was scanned from the OE_ORDER_HEADERS_N5 index. When I looked at the raw trace file, I could see that the first 10-12 runs of the statement were very quick and only one of the last few runs ran longer and did most of the IOs. So, it seems that the Row Source Operation section in TKP only reports statistics from the first run? I was under the impression that this section shows cumulative value from all executions?
>>
>> call count cpu elapsed disk query current rows
>> ------- ------ -------- ---------- ---------- ---------- ----------
>> ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 27 63.79 796.81 328824
>> 344593 0 0 Fetch 27 0.00 0.00 0 0 0 25
>> ------- ------ -------- ---------- ---------- ---------- ----------
>> ---------- total 55 63.79 796.81 328824 344593 0 25
>>
>>
>>
>> Rows (1st) Rows (avg) Rows (max) Row Source Operation
>> ---------- ---------- ----------
>> ---------------------------------------------------
>> 1 1 1 FILTER (cr=4 pr=0 pw=0 time=58 us)
>> 1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)
>> 1 1 1 TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (cr=4 pr=0
>> pw=0 time=49 us cost=47 size=19 card=1)
>> 1 1 1 INDEX RANGE SCAN OE_ORDER_HEADERS_N5 (cr=3 pr=0 pw=0 time=26 us
>> cost=3 size=0 card=73)(object id 164369)
>>
>> Thanks
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--

--
http://www.freelists.org/webpage/oracle-l

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Fri Sep 16 2016 - 16:06:15 CEST