Re: Query ASH for Undo Blocks

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Mon, 20 Jun 2022 17:07:58 +0200
Message-ID: <660f0354-2189-e552-90d0-f9b84ee70c37_at_bluewin.ch>

Hi Jonathan,

I tried to verify that based on 1400 captured binds:

with binds as
(SELECT /*+ materialize */
     val,
     COUNT(*) cnt
FROM

     (
         SELECT
             to_number(value_string) val
         FROM
             dba_hist_sqlbind
         WHERE
             sql_id = '&&sql_id'
             AND name = ':B3'
             AND value_string IS NOT NULL
         UNION ALL
         SELECT
             to_number(value_string)
         FROM
             v$sql_bind_capture
         WHERE
             sql_id = '&&sql_id'
             AND name = ':B3'
             AND value_string IS NOT NULL
     )

GROUP BY
     val
ORDER BY
     cnt DESC),
row_cnt as (
SELECT
     id,
     COUNT(*) cnt
FROM
     tab1
GROUP BY
     id)
select  sum(binds_cnt * row_cnt_cnt)/sum(  row_cnt_cnt) from (
select binds.val, binds.cnt  binds_cnt , row_cnt.cnt row_cnt_cnt from binds, row_cnt
where val= id
order by row_cnt.cnt desc)
/

Well, the result was 52. Anything wrong with my query?

Thanks

Lothar

Am 20.06.2022 um 16:13 schrieb Jonathan Lewis:
>
> This last discrepancy could be explained by the having some variation
> in the number of rows per ID. If there are a couple of IDs with N
> times the typical number rows then (potentially) there will be N times
> as many queries for each of those value, and each one has to work
> through N rows, giving an N^2  impact on the buffer gets. What does
> "select id, count(*)" look like ?
>
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 20 Jun 2022 at 11:24, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
>
> As you can see we have 54 buffer/execution.
>
> Still during the batch run there seems to be something not
> explained.  Even if just the first column in the index is used we
> have 112885  Leaf blocks devided by 58 distinct keys = 2015 + 4
> =2019 buffer on average and not 3700 as indicated below.
> Thus, it looks like things were different on production and undo
> chain seems a logical candidate.
>

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

Received on Mon Jun 20 2022 - 17:07:58 CEST