Re: Query ASH for Undo Blocks

Hello

It's true that I haven't used it too often to tell you if it's reliable or not but I have the following script in my library

select

     decode(current_obj#
            ,0
            ,'undo block'
            ,-1
            ,'cpu'
            ,current_obj#) cur_obj

                  and  to_date('&date_from', 'ddmmyyyy hh24:mi:ss')

> We normally check the undo read percentage from Ash history by verifying
> how many samples are with current_obj# as 0. If it's non zero then should
> be actual table/index block but not undo. And -1 points to on CPU. But yes
> it won't say if it's 'consistent read related undo' or 'delayed block
> cleanout related undo'. For that we need to refer the run time session
> statistics as Nenad pointed out.
>
> On Mon, 13 Jun 2022, 4:32 pm Noveljic Nenad, <nenad.noveljic_at_vontobel.com>
> wrote:
>
>> I think yes, but it's only useful in the case when the buffer gets result
>> in read wait events. But what if all the undo blocks needed are already
>> cached?
>>
>> You can check if the session statistics for "%undo records applied" is
>> increasing:
>> https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9542989000346736054
>>
>> Alternatively, you can trace the Oracle C function kcbgtcr with bpftrace.
>>
>> Best regards,
>>
>> Nenad
>>
>>
>>
>>
>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On
>> Behalf Of Lothar Flatz
>> Sent: Montag, 13. Juni 2022 12:36
>> To: oracle-l_at_freelists.org
>> Subject: Query ASH for Undo Blocks
>>
>> *** E-Mail from outside Vontobel: Do not click on links or open
>> attachments unless you know the content is safe. ***
>>
>>
>> Hi,
>>
>> there is a plan with an Index Range scan that generates way more
>> buffer_gets than you would expect.
>> Would it be correct to query active session history using CURRENT_FILE#
>> and CURRENT_BLOCK# to check against DBA_EXTENTS to find out if some buffer
>> gets are coming from undo?
>> The query does work, but i am not sure if the result is showing the
>> indented answer.
>>
>> Thanks
>>
>> Lothar
>>
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>> ____________________________________________________
>> Please consider the environment before printing this e-mail.
>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>
>> Important Notice
>>
>> This message is intended only for the individual named. It may contain
>> confidential or privileged information. If you are not the named addressee
>> you should in particular not disseminate, distribute, modify or copy this
>> e-mail. Please notify the sender immediately by e-mail, if you have
>> received this message by mistake and delete it from your system.
>> Without prejudice to any contractual agreements between you and us which
>> shall prevail in any case, we take it as your authorization to correspond
>> with you by e-mail if you send us messages by e-mail. However, we reserve
>> the right not to execute orders and instructions transmitted by e-mail at
>> any time and without further explanation.
>> E-mail transmission may not be secure or error-free as information could
>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>> processing of incoming e-mails cannot be guaranteed. All liability of
>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>> is excluded. You are advised that urgent and time sensitive messages should
>> not be sent by e-mail and if verification is required please request a
>> printed version.
>> Please note that all e-mail communications to and from the Vontobel Group
>> are subject to electronic storage and review by Vontobel Group. Unless
>> stated to the contrary and without prejudice to any contractual agreements
>> between you and Vontobel Group which shall prevail in any case,
>> e-mail-communication is for informational purposes only and is not intended
>> as an offer or solicitation for the purchase or sale of any financial
>> instrument or as an official confirmation of any transaction.
>> The legal basis for the processing of your personal data is the
>> legitimate interest to develop a commercial relationship with you, as well
>> as your consent to forward you commercial communications. You can exercise,
>> at any time and under the terms established under current regulation, your
>> rights. If you prefer not to receive any further communications, please
>> contact your client relationship manager if you are a client of Vontobel
>> Group or notify the sender. Please note for an exact reference to the
>> affected group entity the corporate e-mail signature. For further
>> information about data privacy at Vontobel Group please consult
>> www.vontobel.com <https://www.vontobel.com>.
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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