Payroll balance analyses timing out for relatively small data sets

Kevin Sheehan

I recently noticed that my Payroll Balance OTBI analyses have started to time out. I'm aware that the Payroll Balances tables have few indices and are subject to performance issues, and at first I thought that maybe my data set for the particular report was just too large. However, after running a comparison between a lower environment and production, I'm finding a vast discrepancy in performance.

As a test, I created an analysis to return two balances (Gross Pay and Gross Earnings - Core Relationship Run Dimension) for 39 employees across 26 payrolls (1/1/24 - 12/31/24). Following Oracle's recommendations, I filtered on both Payroll Actions.Effective Date and Balance Value Details.Effective Date. In a lower environment (which has the same data for that period), the analysis completed in 36s and returned 1815 lines. In our Production environment, it exceeds 30 minutes/times out. If I reduce the number of people down to 10 or 20 (which returns 500-1000 lines), it works, though it takes much longer than I would expect.

I have opened an SR and they said that our POD health check is showing Green/Good. They asked me to follow these steps, which I did:

1. **Gather Statistics**  
This step provides information about the distribution and storage of data in tables, columns, indexes, and partitions. This information helps optimize query performance by assisting the query optimizer in making decisions about the execution plan.  
**Path:** HOME > TOOLS > Scheduler Processes > Schedule New Process > Gather Table Statistics.

2. **Clear BI Publisher Cache**  
Access the following link: [https://[podname].oracle.com/analytics](https://[podname].oracle.com/analytics).  
Navigate to: Administration > Publisher > Manage Publisher > System Maintenance > Manage Cache.  
- Click on "Clear Object Cache."  
- Click on "Clear Metadata Cache."

3. **Clear OTBI Cache**  
Go to Administration > Issue SQL and enter the SQL command: `Call SAPurgeAllCache();` then click "Issue SQL." You should see a message indicating that "Operation SAPurgeAllCache succeeded!"

4. **Reload Files and Metadata in OTBI**  
Navigate to Administration > Maintenance and Troubleshooting.  
Click "Reload Files and Metadata."  
*Note: This process may take a few minutes to complete. Please be patient until the message changes from "Reloading, please wait…" to "Reload XML message files, refresh server metadata, and clear caches."

5. **Log Out and Log Back In**  
It is important to log out gracefully and then log back in.

### Important Note:  
The initial period after these actions may feel sluggish as the cache and metadata are warmed up and resynced. However, this will not cause any harm, and performance should return to normal within a few minutes.

After that didn't help, they said to refer to the the attached documentation for the Payroll Balances Real Time subject area, which I've done. Has anyone encountered an issue like this? Any advice?

OTBI_PSR_Technote_V4.pdf