Re: Query ASH for Undo Blocks

Hi Guys,

thanks a lot Lok, Mohamed and Nenad. Unfortunately that index is almost all cached.
Thus I have only 2 physical read both from undo.... I can see some evidence that we have a undo issue, but can not proof it.

Thanks

Lothar
Am 13.06.2022 um 13:39 schrieb Mohamed Houri:
>
> 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
>    , count(1)
> from
>      gv$active_session_history
> where
>    sample_time between to_date('&date_from', 'ddmmyyyy hh24:mi:ss')
>                   and  to_date('&date_from', 'ddmmyyyy hh24:mi:ss')
> and event = '*db file sequential read*'
> and sql_id = '&sql_id'
> group by current_obj#
> order by 2 asc;
>
> For a wait event = *db file sequential read*
> -- if current_obj = 0 then this means you are reading from undo
> block(useful to check read consistency)
> -- if current_obj = -1 then this means you are working on cpu
>
> Best regards
> Mohamed Houri
>
> Le lun. 13 juin 2022 à 13:21, Lok P <loknath.73_at_gmail.com> a écrit :
>
> 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
> <http://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