SQL performance - full table scan v. index scan on PS_BEN_PROG_PARTIC

PeopleSoft HCM Payroll for NA 9.2 image 46 Tax Update 23B

PeopleTools 8.56.19

Oracle Database Standard Edition 12.2

The same SQL runs in two different environments with different timing. The issue that brought me here is that the SQL filled up PSTEMP. I added datafiles, which allowed the SQL to complete, but took over 40 minutes. I have searched Oracle Support without success. Any help is appreciated. Thanks. -Steve

We have run stats:

EXEC DBMS_STATS.gather_table_stats('SYSADM','PS_BEN_PROG_PARTIC');

exec dbms_stats.gather_index_stats('SYSADM','PSABEN_PROG_PARTIC');

exec dbms_stats.gather_index_stats('SYSADM','PS_BEN_PROG_PARTIC');