My Oracle Support Banner

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

Last updated on MAY 20, 2021

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.50 and later
Information in this document applies to any platform.


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';


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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