Re: Query ASH for Undo Blocks

To be honest, that date_col1 filter looks highly selective and might not be helping your index at all. Either way, it being the second column means your third is not going be used in accessing the index ( https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-indexing/ ) so you probably want it last (if at all). Moving this will definitely help limit the amount of blocks you read from the index and therefore help reduce number of blocks that may need undo applied.

Thanks,
Andrew

On Mon, 20 Jun 2022 at 16:13, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> “The index used to be:
> ON Table1 ("ID", "Date_col1", "ID2")
>
> I had it changed to
>
> ON Table1 ("ID", "Date_col1", "ID2", status) “
>
>
>
> If you make this index the single column ID,
>
>
>
> then
>
>
>
> 3 - access("ID"=:B3 AND "Date_col1"<=GREATEST(:B1,LOCALTIMESTAMP(6))
>
>
>
> has
>
> ID CNT % count
> 20962881 92,39
>
> fewer rows in the index to evaluate, **probably** meaning having to
> examine the table row value instead of the index value for ID2 and status
> is worth the trip.
>
>
>
> I would suggest that is worth a test since you don’t need to change the
> data or the query, just change the index OR add the new one (and do stats,
> so the CBO will see how small it is).
>
>
>
> Good luck,
>
>
>
> mwf
>
>
>
> *From:* Lothar Flatz [mailto:l.flatz_at_bluewin.ch]
> *Sent:* Monday, June 20, 2022 10:18 AM
> *To:* mwf_at_rsiz.com; jlewisoracle_at_gmail.com; 'Oracle L'
> *Subject:* Re: Query ASH for Undo Blocks
>
>
>
> Hi Mark,
>
> true as CASE "status" WHEN 2 THEN :B1 WHEN 3 THEN LOCALTIMESTAMP(6) END
> does evaluate to null for any status not in (2,3). You can not create an
> index on that expression as the bind variable is only known at run time.
> However you still had to combine the single column indexes (bitmap
> conversion from rowids).
> I would rather list partition the index on status. However like this query
> is written now we will not become happy whatever we do.
>
> Thanks
>
> Lothar
>
> Am 20.06.2022 um 15:52 schrieb Mark W. Farnham:
>
> both columns smell like columns where it should be reviewed if a “final”
> value should be changed to YOUR DEFINITION value of NULL.
>
>
>
> For example, if 5 is “I’m done with you, leave me alone” for status, then
> an index on status becomes very small and self pruning.
>
>
>
> ID2 likewise seems like a single column index would be tiny, so having to
> filter the other column after just getting THAT tiny index range scan would
> be an enormous improvement.
>
>
>
>
>
> Good luck,
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Lothar Flatz
> *Sent:* Monday, June 20, 2022 9:25 AM
> *To:* jlewisoracle_at_gmail.com; Oracle L
> *Subject:* Re: Query ASH for Undo Blocks
>
>
>
> Hi Jonathan,
>
> a.)
> COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS
> STATUS 6 0 1
> ID2 2 24584135 1
>
> STATUS COUNT
> 5 20910785
> 9 1684772
> 3 37284
> 6 31258
> 2 5525
> 4 51
>
> select count(*) from tab1 where Date_col1> LOCALTIMESTAMP(6);
> COUNT(*)
> 30899
>
> select count(*) from tab1 where Date_col1 <= LOCALTIMESTAMP(6);
>
> COUNT(*)
> 23313
>
>
> Date_col1 is not null because
>
> select count(*) from tab1 where Date_col1
> <=GREATEST(NULL,LOCALTIMESTAMP(6));
>
> COUNT(*)
> 0
>
>
>
> b.) Don't know how I should do this. v$segstat / v$segment_statistics have
> no relation to specific session.
> Whenever v$segstat / v$segment_statistics changes I could not track
> this back the source.
> Since I can not look at my statement in isolation (e.g. on a test db)
> I would have all db activity in my stats.
> Even if I could, I would need concurrent activity to get shed some
> light on undo applied.
> I guess what I could do is using Tanels snapper though. The undo chain
> counter might do the trick.
>
> In my understanding since ID2 is Null -> ID2 = ID2 which effectively
> means :"ID2 is not null".
> Thus, this is a filter due 3 valued logic because of the comparison
> operator. I am pretty sure that is not what the developer indented.
> I guess this is this silly coding trick with conditional conditions.
>
>
> c.) Sure, I know. The point is that I am lacking the privileges. (Not
> that I did not ask..)
> Timestamp by itself is not a good enough reason not to be captured.
> It is sampled in anydata and can be converted back to timestamp.
>
> d.)
> The index is for sure not very good with respect to the query.
>
>
> ID CNT % count
> 20962881 92,39
> 524 1229211 5,42
> 800 91407 0,4
> 553 71571 0,32
> 815 59699 0,26
> 507 48167 0,21
> 521 34961 0,15
> 805 26855 0,12
> 893 25108 0,11
> 818 24173 0,11
> 895 18537 0,08
> 854 15793 0,07
> 546 14880 0,07
> 523 13995 0,06
>
> Thanks
>
>
>
> Am 18.06.2022 um 10:52 schrieb Jonathan Lewis:
>
> A couple more thoughts:
>
>
>
> a) what does the status column look like - number of distinct values,
> pattern of 2's and 3's, number of nulls.
>
>
>
> b) You could check v$segstat / v$segment_statistics over an interval and
> check the number of executions/buffer gets for the statement then see if
> the changed in buffer gets over the interval is mostly due to the table
> blocks or index blocks. Since you update only one row then if the buffer
> gets are mostly index that suggests the "id2" predicate is eliminating the
> data (because it's null), if the buffer gets are mostly table it's
> probably about the status (and maybe that should be in the index).
>
>
>
> c) If you enable sql_trace at level 4 for that SQL_ID for a few executions
> the trace file will report the peeked value for the timestamp variable each
> time. See https://jonathanlewis.wordpress.com/2019/05/03/occurence/ for
> getting at the SQL_ID from outside any session that's running it.
>
>
>
> d) I do wonder if the very small number of distinct values for id means
> every id has a huge number of entries in the index which HAVE to be
> examined before being eliminated by the id2 predicate (what does the result
> of "select id, count(*)" look like ?
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Fri, 17 Jun 2022 at 15:47, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
> Lothar,
>
>
>
> What's num_rows for the table and for the index?
>
>
>
> Surely id2 is also a column in the index since it appears as a filter on
> the index range scan
>
>
>
> What's the actual query - has Oracle taken the predicate like the one that
> appears as the filter predicate to the "table access" operation and
> constructed the date_col1 predicate that appears as an access predicate on
> the "index range scan" operation.
>
>
>
> Which version of Oracle ? It's possible that the :B1 is not captured
> because it's a timestamp and there's a bit of code missing. I have a note
> dated some time in 2006 which shows that bind variables are not reported in
> trace files if they are nvarchar2() it's possible that the same happens
> with timestamps and other newer data types.
>
>
>
> Regards
>
> JOnathan Lewis
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Fri, 17 Jun 2022 at 15:04, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> Hi Jonathan,
>
> On the very database I am limited in rights, therefore I can not provide
> all the Information you asked for.
> In particular there is no SQL Monitor, one execution runs to fast and I
> can not switch it on.
> The statement is rather simple (I changed all Names):
>
>
> -------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> -------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | 2
> (100)| |
> | 1 | FOR UPDATE | | |
> | | |
> |* 2 | TABLE ACCESS BY INDEX ROWID| Tab1 | 1 | 42 |
> 2 (0)| 00:00:01 |
> |* 3 | INDEX RANGE SCAN | Index1 | 1 | |
> 2 (0)| 00:00:01 |
>
> -------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("Date_col1"<=CASE "status" WHEN 2 THEN :B1 WHEN 3 THEN
> LOCALTIMESTAMP(6) END )
> 3 - access("ID"=:B3 AND "Date_col1"<=GREATEST(:B1,LOCALTIMESTAMP(6))
> )
> filter("ID2"=COALESCE(:B2,"ID2"))
>
> Here the Stats of a batch run
>
> Stat Name Statement Per Execution % Snap
> ---------------------------------------- ---------- -------------- -------
> Elapsed Time (ms) 3.9098E+08 45.7 3.2
> CPU Time (ms) 3.8841E+08 45.4 4.5
> Executions 8,546,245 1.0 0.0
> Buffer Gets 3.2236E+10 3,772.0 6.9
> Disk Reads 364,449 0.0 0.0
> Parse Calls 187,232 0.0 0.0
> Rows 6,441,767 0.8 N/A
> User I/O Wait Time (ms) 328,217 0.0 0.0
> Cluster Wait Time (ms) 0 0.0 0.0
> Application Wait Time (ms) 117 0.0 0.0
> Concurrency Wait Time (ms) 1,693,881 0.2 0.1
> Invalidations 0 N/A N/A
> Version Count 1,536 N/A N/A
> Sharable Mem(KB) 64,108 N/A N/A
>
> -------------------------------------------------------------
> ASH says time is spent:
>
> SQL_PLAN_OPERATION SQL_PLAN_OPTIONS SQL_PLAN_LINE_ID
> SECONDS % seconds
> CPU RANGE SCAN 3
> 415160 97
> CPU BY INDEX ROWID
> 2 6100 1
> CPU
> 1 3170 1
> buffer busy waits
> 1 1310 0
> CPU
> 480 0
> read by other session BY INDEX ROWID
> 2 220 0
> db file sequential read BY INDEX ROWID
> 2 100 0
> latch: cache buffers chains BY INDEX ROWID
> 2 90 0
> buffer busy waits BY INDEX ROWID
> 2 70 0
> db file scattered read
> 1 40 0
> latch: cache buffers chains RANGE SCAN
> 3 30 0
> latch: cache buffers chains
> 1 30 0
>
> This is the Index Definition:
>
> INDEX_NAME COMPRESSION BLEVEL LEAF_BLOCKS DISTINCT_KEYS
> AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
> Index1 DISABLED 3 112885
> 6290 17 102
>
> col stats
> COLUMN_NAME
> NUM_DISTINCT NUM_NULLS NUM_BUCKETS
> --------------------------------------------------------------------------------------------------------------------------------
> ------------ ---------- -----------
> ID
>
> 56 37349080 1
> Date_col1
>
> 2628 39004885 1
>
> Bind Variable B2 is always Null btw. B1 is never captured. (Do you know
> why? I wonder if the reason might be that it is used in an expression.)
>
> These Numbers are from a Test DB. Here I got about 200 Buffers per
> execution. Question: where does the rest come from?
>
> --------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers | Reads |
>
> --------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 49M| |
> 42M|15:17:46.13 | 17G| 12889 |
> | 1 | FOR UPDATE | | 49M| |
> 42M|15:17:46.13 | 17G| 12889 |
> |* 2 | TABLE ACCESS BY INDEX ROWID| Tab1 | 49M| 2 |
> 57M|13:45:46.36 | 16G| 12874 |
> |* 3 | INDEX RANGE SCAN | Index1 | 49M| 22 |
> 12G|07:27:46.63 | 10G| 1536 |
>
> --------------------------------------------------------------------------------------------------------------
>
> Thanks
>
> Lothar
>
>
> Am 13.06.2022 um 20:13 schrieb Jonathan Lewis:
>
>
>
> What's the scale of the problem? i.e. how long does the query run, how
> complex is the query/plans, how much of the time relates to the index range
> scan, how many buffer gets does that index range scan step take, how many
> do you think it should take. How frequently doe the query run, how
> repeatable is the experiment?
>
>
>
> How are you working out that that index range scan is doing more buffer
> gets than you expect - have you enabled SQL Monitor for the SQL_Id?
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
> On Mon, 13 Jun 2022 at 11:35, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> 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
>
>
>
>
>
>
>
>

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