Demantra Performance - Table sales_data Reorg High Recommendation steps (Doc ID 2400167.1)

Last updated on MARCH 06, 2023

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------
Demantra Performance

Executed Gather stats

Collaborator log is suggesting to run reorg.

Run the following SQL to evaluate how table reorg might affect Primary Key (PK) based access:

SELECT ui.index_name,trunc((ut.num_rows/ui.clustering_factor)/(ut.num_rows/ut.blocks),2)
FROM user_indexes ui, user_tables ut, user_constraints uc
WHERE ui.table_name=ut.table_name
AND ut.table_name=uc.table_name
AND ui.index_name=uc.index_name
AND UC.CONSTRAINT_TYPE='P'
AND ut.table_name=upper('sales_data');

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. completed Re-org and gather Statistics
2. collaborator.log has recommendation for Reorg


Also for version 7.3.1.3 and above, it shows in log_table_reorg:
High recommendation: Run table_reorg.reorg at row level on table SALES_DATA

Cause

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