The PeopleSoft DBA Blog: PeopleSoft PS/Query: Finding Users' Cartesian Joins

https://blog.psftdba.com/2024/11/psquery-cartesian.html?spref=tw

Many PeopleSoft users like its ad hoc query tool because they can write their own queries directly on the system, without having to learn to write structured query language (SQL), or getting a developer to write it for them.? ?

What is the Problem?

It is easy for users to create poor queries, that either don't work as intended or can run for long periods, even indefinitely, consuming resources without ever producing results.? This can consume significant amounts of CPU, and in the cloud, that is mostly what you pay for!? The effect can be mitigated with the database's resource manager, but it is better not to do it in the first place.

One cause of long-running queries that I come across is missing join criteria leading the database to perform Cartesian Merge Joins.? I should stress that not all Cartesian joins are evil.? For example, in some data warehouse queries (e.g. GL nVision reporting), it can be a very effective strategy to Cartesian join dimension tables before visiting the fact table, especially if you can use Bloom filter a full scan on the fact table.? It works well with parallel query, and on engineered systems this can also be pushed down to the storage cells.

Finding Execution Plans with Cartesian Joins

The linked blog post shows

• How to profile database time by execution plan from ASH for SQL statements from PS/Queries run on the Process Scheduler using the PSQUERY application engine program on a process scheduler.?
• How to find PS/Queries that may lack join criteria between parent and child records, and also just those that have been run recently.