Oracle SQL
  • LICENSE

Why you dont want to set _partition_large_extents FALSE

Posted on May 4, 2017 by Roger MacNicol Posted in oracle, SmartScan, undocumented

I’ve seen some blogs recommending that _partition_large_extents be set to FALSE for a variety of space conserving reasons without the authors thinking about the negative impact this is going to have on Smart Scan. Large Extents cause an INITIAL allocation of 8 MB and a NEXT allocation of 1 MB and they have been the default for table spaces on Exadata since 11.2.0.2. You can verify that large extents are in use by a given table or partition by:

Select segment_flags 
From sys_dba_segs 
where segment_name = <table_name> 
and owner = <schema_name>;

The segment flag bit for large extents is 0x40000000.

This pattern of allocation is design to work optimally with Smart Scan because Smart Scan intrinsically works in 1 MB chunks.  Reads of ASM allocation units are split into maximum 1 MB chunks to be passed to the filter processing library to have their blocks sliced and diced to create the synthetic blocks that contain only the rows and columns of interest to return to the table scan driver. When less than 1 MB gets allocated at a time to a segment and then the next contiguous blocks gets allocated to a different  segment, each separate run of blocks will be read by a different MBR. Each run will be passed separately to Smart Scan and we get sub-optimal chunks to work on increasing both the overhead of processing and the number of round trips needed to process the table scan. The design of Smart Scan is predicated on scooping up contiguous runs of data from disk for efficient processing.

This matters particularly for HCC data and for chained rows.

Hybrid Columnar Compression 

We made two improvements to HCC row-based processing in 12.1.0.1. The first was the introduction of an Order By clause on index-access plans that guaranteed any compression unit would only be decompressed once. The second was 256 KB formatting of extents. Prior to 12c, a Fetch By Rowid would read the first block into the buffer cache in order to get the list of blocks in that Compression Unit and then read the remaining blocks into the buffer cache using the exact read-ahead count. This guaranteed a minimum of two physicals reads to retrieve one row. By formatting 256 KB at a time to the segment, we know a priori that we can issue the first read for all the blocks from the RDBA to next 256 KB alignment thus the majority of Compress For Query reads can be done with a single physical read.

However this formatting can only happen when large segments are used.  You can verify that 256 KB formatting is being used by:

Select flag 
From GV$FILESPACE_USAGE 
where tablespace_id = <tsnumber>
  and rfno = <relative_fno>;

The flag bit for 256 KB formatting is 0x08. Note: 256 KB formatting requires a minimum 8 KB block size not by undoing this critical table scan optimization.

Chained Rows

When Smart Scan processes chained rows all it can see is the 1 MB chunk it is given. It has no ability to initiate a disk I/O to retrieve chained row pieces that lie outside that 1 MB chunk (even if it could the chances that the required row piece is on the same cell is relatively low). We changed the direct load path in 11.2.0.3 to work with the natural 1 MB alignment of Smart Scan so that direct loaded rows would never cross a 1 MB boundary. However as of 12.1.0.2 we have not yet changed Updates to keep all the row pieces inside a 1 MB chunk nor have we taught Alter Table Shrink Space Compact to obey the 1 MB alignment rule.

Keeping 1 MB chunk contiguous for the same segment maximizes the chances that after non-bulk inserts and updates Smart Scan is able to see all the row pieces needed to satisfy the column references and not have to send the block back to RDBMS for buffer cache chained row processing. Using 11.2.0.1 style 64 KB allocations maximizes the chances Smart Scan will not be able to process those blocks . Just saying …

Conclusion

Getting Exadata for its table scan performance but then setting  “_partition_large_extents” = FALSE is like buying a BMW M5 and insisting on filling it with 87 octane. The correct answer to conserving space on small tables is to over-ride the default by explicitly setting INITIAL and NEXT in the Create Table statement.

Roger MacNicol

Oracle Data Storage Technology 

HCC oracle Roger MacNicol row chaining SmartScan undocumented oracle
« Why are HCC stats being bumped when Smart Scanning row major data in 12.2
Working around heatbeat issues caused by tracing or by regexp »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL