Large TCHK Allocations in Shared Pool

I’ve noticed large shared pool memory allocations which were caused by a create table as select (CTAS) on a complex view on an Oracle 12.2 database:

create table t as select * from complex_view ;

column tchk format 999999
column sh format 999999
column pers format 999999
select typecheck_mem/1024 TCHK, sharable_mem/1024 SH, 
	persistent_mem/1024 PERS 
	from v$sql 
	where sql_text like '%complex%' ;
	
   TCHK      SH    PERS
------- ------- -------
 384114   67055    5408

The statement has occupied 384 MB just for the typecheck (TCHK) memory. The same allocations have been missing when the query runs standalone.

select /*+ ALL_ROWS */ * from complex_view ;
 
   TCHK      SH    PERS
------- ------- -------
      0    6855    4482

The TCHK allocations have also been observed when running a very simple test case:

create table test_table (a number) ;

create table t as select * from test_table ;

   TCHK      SH    PERS
------- ------- -------
    126      37      71

select * from test_table ;

  TCHK      SH    PERS
------- ------- -------
      0      19       6

Furthermore, the CHK allocations have been confirmed by a SGA heap dump processed by Tanel Poder’s heapdump_analyzer:

  Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
  ---------- ------- ------------ ----------------- ----------------- -----------------
      101400   25       4056 ,    SQLA^8012fc2a,         freeable,  TCHK^8012fc2a  
       25056    6       4176 ,    SQLA^8012fc2a,         freeable,  TCHK^8012fc2a  
        8112    2       4056 ,    SQLA^8012fc2a,         freeable,  qbcqtcHTHeap   
        4056    1       4056 ,    SQLA^8012fc2a,         recreate,  TCHK^8012fc2a  

By counting user stacks within the Oracle SGA memory allocation functions with DTrace we can find out functions which cause most memory allocation calls.

#pragma D option dynvarsize=64m

pid$target::kghalo:entry,
pid$target::kghalf:entry,
pid$target::kghalp:entry
{
  @ustacks[ustack(100)] = count();
  @total = count() ;
}

CTAS has made an order of magnitude more memory allocation calls than the query alone, 26’471 and 1’576 respectively, even for such a simple query, .

The function kksFullTypeCheck stands out in the flame graph as a significant contributor to the sga memory allocation calls:

Note: The flame graph is 1.41M large. It might take some time to open the file. Click here to open the file in a new browser tab.

This function has never been invoked when the select was executed.

Finally, I measured the typecheck memory consumption in 11.2.0.3.7 for ctas. It allocated 86K compared to 126K in 12.2.

Last but not least, if the problem becomes excesive, you can first create the table and then insert the rows with a insert into table select … as a workaround.

Related articles:

Large Memory Allocations due to Query Parsing