Re: Re: Re: Re: segment covers more blocks than needed
Date: Wed, 29 Jan 2020 15:21:58 +0100 (CET)
Message-ID: <1956760737.27991.1580307718662_at_bluewin.ch>
Hi Jonathan,
thanks, that is very helpfull. We have now analyzed a couple of longrunning Statements from SQL Monitoring. In each case dynamic sampling was causing an issue due missestimation. I do believe this behaviour classifies as a bug. If the segment is not empty, only blocks with rows should be considered for DS. BTW.: is possible to query the LHWM?
Regards
Lothar
----Ursprüngliche Nachricht----
Von : jonathan_at_jlcomp.demon.co.uk
Datum : 29/01/2020 - 14:25 (MZ)
An : oracle-l_at_freelists.org
Betreff : Re: Re: Re: segment covers more blocks than needed
I've run up a test - creating an empty table with a single 8MB extent, inserted a few rows into it (one block's worth) which went in at block 1395 of file 22 (the segment started at block 1152) for 1024 blocks, and ensured that there were no stats on the table.
Running with a hint /*+ dynamic_sampling(small_table 4) */ .... with two inequality predicates, enabling 10046 and 10053, and pre-flushing the library cache and shared pool I got dynamic sample with the following trace information:
- Executed dynamic sampling query: level : 4 sample pct. : 25.347913 actual sample size : 0 filtered sample card. : 0 orig. card. : 82171 block cnt. table stat. : 1006 block cnt. for sampling: 1006 max. sample block cnt. : 256 sample block cnt. : 255 min. sel. est. : 0.00250000
WAIT #140340155855888: nam='db file sequential read' ela= 407 file#=22 block#=1152 blocks=1 obj#=137880 tim=412547814186 WAIT #140340155855888: nam='db file sequential read' ela= 308 file#=22 block#=1168 blocks=1 obj#=137880 tim=412547814536 WAIT #140340155855888: nam='db file scattered read' ela= 614 file#=22 block#=1153 blocks=15 obj#=137880 tim=412547815237 FETCH #140340155855888:c=873,e=1627,p=17,cr=21,cu=0,mis=0,r=1,dep=1,og=1,plh=904293527,tim=412547815326 STAT #140340155855888 id=1 cnt=1 pid=0 pos=1 obj=0 op='RESULT CACHE 83x5y77kq960c3by8u9dan074m (cr=21 pr=17 pw=0 str=1 time=1624 us)'STAT #140340155855888 id=2 cnt=1 pid=1 pos=1 obj=0 op='SORT AGGREGATE (cr=21 pr=17 pw=0 str=1 time=1590 us)' STAT #140340155855888 id=3 cnt=0 pid=2 pos=1 obj=137880 op='TABLE ACCESS SAMPLE SMALL_TABLE (cr=21 pr=17 pw=0 str=1 time=1580 us cost=36 size=373919 card=4109)'
So it looks as if Oracle has used the segment size as the table blocks (in the absence of the stats) to estimate the sample size it has to take. Then the blocks it has examined are: 1152 (first block in table, L1 bitmap), 1168 (L2 bitmap), 1153 - 1167 (the remaining 15 L1 bitmaps). I suspect it's then used the bitmaps to discover that the block addresses it has generated for the random sample happen to contain no data (no formatted blocks) without ever looking at a single data block.
I think somewhere in one of the articles I cited (or maybe on a forum or elsewhere on the listserver) I've got a suggestion that when you create a small table that takes a big extent as its first extent you should do an "alter table move" as soon as you have a little data in it as this rebuilds the table with the data in the first few blocks and sets a truthful HWM. I don't think I realised at the time that failing to do this (at least as a one-off) could result in a bit of a disaster with dynamic sampling.
Regards
Jonathan Lewis
From: l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 29 January 2020 11:04
To: Jonathan Lewis
Cc: Oracle-L_at_freelists.org
Subject: Re: Re: Re: segment covers more blocks than needed
Hi Jonathan,
it is a underestimate.
Find the runtimestats below. The Statement has not finished. It would go on for hours.
The missestimate is in line 5, the real number of rows is 20.
Runtime Stats does not reflect it, because Statement Needs to run longer.
Sorry for the mess, I can not set a fixed font in the web email Interface.
Thanks
Lothar
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 | 0 |
| 1 | HASH GROUP BY | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 |
| 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 |
| 3 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 |
|* 5 | TABLE ACCESS FULL | TABLE1 | 1 | 1 | 1 |00:00:00.01 | 15 | 0 |
| 6 | BUFFER SORT | | 1 | 468M| 0 |00:00:00.01 | 0 | 0 |
| 7 | PARTITION RANGE ALL | | 1 | 468M| 418M|00:03:26.59 | 2616K| 2613K|
| 8 | TABLE ACCESS FULL | TABLE2 | 841 | 468M| 418M|00:03:23.68 | 2616K| 2613K|
|* 9 | INDEX UNIQUE SCAN | TABLE2_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |* 10 | TABLE ACCESS BY INDEX ROWID| TABLE2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | ---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - filter(("VERS"."DWH_VALID_TO">SYSDATE_at_! AND "VERS"."DWH_VALID_FROM"<=SYSDATE@!))
9 - access("HEAD"."DWH_HEAD_ID"="VERS"."DWH_HEAD_ID")
filter("H"."FK_PARTNER_ID"="HEAD"."DWH_HEAD_ID")
10 - filter("HEAD"."DWH_UNLOAD_DATE" IS NULL)
Note
- dynamic statistics used: dynamic sampling (level=4)
----Ursprüngliche Nachricht----
Von : jonathan_at_jlcomp.demon.co.uk
Datum : 29/01/2020 - 11:49 (MZ)
An : l.flatz_at_bluewin.ch
Cc : Oracle-L_at_freelists.org
Betreff : Re: Re: segment covers more blocks than needed
Lothar,
Can you give an example of exactly what you mean by bad estimates. Is it a significant over-estimate, or a zero estimate, or one of the "guess" figures ?
Regards
Jonathan Lewis
From: l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 29 January 2020 10:38
To: Jonathan Lewis
Cc: Oracle-L_at_freelists.org
Subject: Re: Re: segment covers more blocks than needed
Hi Jonathan,
thanks for the explaination.
I have tested the Segment and indeed the populated blocks are somewhere in the extent and not at the start of the Segment.
Where I think Things goes wrong is that the blocks at the start of the Segment are even considered for dynamic sampling.
I believe the dynamic sampling arrives at the Conclusion that the segement is empty.
We have tested with serveral Levels of dynamic sampling . Anything below 3 works, because DS is not done.
Any Level between 3-6 Fails to generate a correct estimate.
I wonder why empty blocks are not ignored by DS.
Can you shed some light on it?
Regards
Lothar
----Ursprüngliche Nachricht----
Von : jonathan_at_jlcomp.demon.co.uk
Datum : 28/01/2020 - 17:00 (MZ)
An : Oracle-L_at_freelists.org
Betreff : Re: segment covers more blocks than needed
ASSM formats blocks on demand in chunks of 16 consecutive blocks. But the blocks aren't necessarily going to be the ones near the start of the extent (and when I've looked - only in passing - at cases where the segment size is 1MB or 8MB the first 16 blocks seem to be at, or close to, the end of the extent.
The tablescan is then done from the start of segment to the "Low highwater mark (LHWM)" in unit of db_file_multiblock_read_count, then in units of multiples of 16 blocks s between the LHWM and the High highwater mark (HHWM) - where the LHWM is nominally the point up to which the segment is formatted with no unformatted gaps below it. (If you're unlucky Oracle could think that LHWM is right at the start of the segment when it's really thousands of blocks into the segment - but I haven't seen anything that extreme recently).
https://jonathanlewis.wordpress.com/2013/07/30/assm-2/ https://jonathanlewis.wordpress.com/2010/07/19/fragmentation-3/#more-4147
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 28 January 2020 15:50
To: Oracle-L_at_freelists.org
Subject: segment covers more blocks than needed
Hi,
I am no classical DBA, thus this question might be trivia for youl.
This issue causes serious misestomate when dynamic_sampling >= 4 is used.
We got a DWH with many small tables where stats are showing 502 blocks in DBA_TABLES.
Empty blocks is 0.
The Point is that there must be blocks which must (almost?) empty.
502 Blocks Matches with the initial size of the Segment.
We did a testcase. We created am empty table. No blocks when querying dba_tables of course.
After we inserted the first row (deferred_segment_creation = true) , again 502 appeared in dba_tables.
DBMS_SPACE features this Output:
L/SQL-Prozedur erfolgreich abgeschlossen.
Unformatted Blocks = 486
Unformatted Bytes = 7962624
FS1 Bytes (at least 0 to 25% free space) = 0 FS1 Blocks(at least 0 to 25% free space) = 0 FS2 Bytes (at least 25 to 50% free space)= 0 FS2 Blocks(at least 25 to 50% free space)= 0 FS3 Bytes (at least 50 to 75% free space) = 0 FS3 Blocks(at least 50 to 75% free space) = 0 FS4 Bytes (at least 75 to 100% free space) = 262144 FS4 Blocks(at least 75 to 100% free space)= 16Full Blocks in segment = 0
Full Bytes in segment = 0
Segment_space_managment is AUTO. Tablespace has a uniform extent. Runtime Stats show 28 buffers on FTS, proving that the HWM is not set after the first block. Avg_row_len is 200. Remember this is only 1 row.
What is going on here?
Regards
Lothar
Full Bytes in segment = 0Full Blocks in segment = 0FS4 Blocks(at least 75 to 100% free space)= 63FS4 Bytes (at least 75 to 100% free space) = 1032192FS3 Blocks(at least 50 to 75% free space) = 1FS3 Bytes (at least 50 to 75% free space) = 16384FS2 Blocks(at least 25 to 50% free space)= 0FS2 Bytes (at least 25 to 50% free space)= 0FS1 Blocks(at least 0 to 25% free space) = 0FS1 Bytes (at least 0 to 25% free space) = 0Unformatted Bytes = 7176192Unformatted Blocks = 438
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 29 2020 - 15:21:58 CET