This presentation is part of the Designing and Building for Performance Self-Study Course.

Transcript

DB Time (PEGA0007) is triggered when a call to the PegaRULES database exceeds a certain threshold (typically 500ms). 
Queries that take a long time to run, especially in development, will likely run even longer in a production environment when hundreds of users are accessing the database at the same time.
There are many root causes for why a query might take too long to run.

• Indexing a table:  There may be a need to add an index to a table to make the query plan more efficient
• SQL syntax:  The syntax of the generated SQL may be too complex for the DBMS to generate an efficient query access plan
• Data filtering:  The filtering of data may not be fine enough and you are trying to return too many rows
• Record locking:  There may be contention on the database server due to record locking
• Database server capacity:  The capacity of the database server may be impacted by other, unrelated processing such as backups, reports, or reorganizations
• Data returns:  You are returning too much data, such as very large BLOBs, via the pzPvStream column

Use this view to see the exact query syntax, as well as any bind variables to the SQL, and determine the exact data this query is trying to retrieve. When it is difficult to determine the root cause visually:

• Re-run this SQL in an interactive tool, such as SQL*PLUS, TOAD, DBArtisan or Enterprise Manager, to recreate the execution event, or
• Provide this SQL to a DBA for more detailed analysis of the query plan generated

To put this alert into perspective, the MyAlerts tool provides the "Last Step" and "Trace List," which links to the exact rules and sequence of steps that led up to this alert event.  Often times, fixing a poorly running query is done by modifying the rules that led up to and were used to generate the SQL.