E-TR: In TREEMAINT AE Process the Delete on PSTREENODE Causes Creation of Empty Data Blocks (Doc ID 2778143.1)

Last updated on AUGUST 26, 2024

Applies to:

Symptoms

The Tree maintenance process TREEMAINT creates empty data blocks in PSTREENODE table in the database, this causes a big chunk of the database memory to get totally blocked, and that eventually impacts overall performance.

Below SQL can be run to find the empty blocks :
 
Select table_name,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB'
"FRAGMENTED_SPACE", round((round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*8/1024)),2))*100) "percentage"
from dba_tables WHERE table_name='PSTREENODE';

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.