PL/vSQL loop runs dynamic SQL many times inside one statement, creating huge internal executions and monitoring growth; batch/limit or redesign to set-based SQL to reduce it.
Environment
-
Vertica Analytics Database
-
Workloads using PL/vSQL (or other procedural SQL blocks) that build and execute dynamic SQL in loops
Situation
-
Monitoring/history data (for example, “query consumption” / “execution records”) grows extremely fast.
-
A small number of executions account for the majority of the growth.
-
The workload looks like one outer statement, but the system shows many internal executions associated with it.
-
Customers may report:
-
Rapid growth of monitoring/history storage
-
Increased overhead when querying monitoring/history
-
Performance impact during or after running the procedural script
-
