Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

Using Query Inspector to improve SQL queries

Updated on July 8, 2022

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).

Before you begin: You need the PegaRULES:DatabaseAdministrator role to access the Query Inspector. For more information, see Adding a role to an access group.
Note: Pega Cloud Services provides Query Runner, Query Inspector, and Schema Tools in Pega Platform for clients to manage their database. These database tools are not available to on-premises clients.
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.
  1. In the header of Dev Studio, click ConfigureSystemDatabaseQuery Inspector.
  2. Select the database from the Choose your database list.
  3. Enter or paste an SQL query into the Enter or paste statement to evaluate here field.
  4. Click Generate explain plan.

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us