Using Query Inspector to improve SQL queries
You can use Query Inspector to generate an EXPLAIN PLAN to help tune a SELECT SQL query for your internal Pega Platform databases on Pega Cloud.
An EXPLAIN PLAN is the internal sequence of steps that the SQL query uses to tune the SQL query, which is useful for improving query efficiency. Use the Query Inspector to enter or paste an SQL query and generate an EXPLAIN PLAN.The Query Inspector is intended only to access specific rows and not a lot of data (that is, it is intended only for very directed queries) and it limits results to 500 lines. For bulk data export, use the Pega Report or Business Intelligence Exchange (BIX).
The EXPLAIN
PLAN output organizes actions taken by the SELECT statement in
order of execution sequence. An explain plan outputs the following:
- The operation taken.
- The name of the table on which the SELECT statement performed the operation.
- The number of rows on which the SELECT statement performed the operation.
- The cost (CPU usage) of the SELECT statement execution.
- The length of the rows upon which the SELECT statement executed upon.
The types of operations for SELECT statements include the following.
- FULL Table scans that read the entire table.
- JOIN methods that explains the sequence of tables joined together.
- Nested loops that assesses rows in a table more efficiently based on the assessment of a previous table.
- INDEX SCAN and SEQUENCE SCANS that pull from multiple values within a table.
- In the header of Dev Studio, click .
- Select the database from the Choose your database list.
- Enter or paste an SQL query into the Enter or paste statement to evaluate here field.
- Click Generate explain plan.
Previous topic Exporting previously run query results Next topic Improving database batch processing performance