Skip to main content
LinkedIn
Copied!

Table of Contents

Using Query Inspector to improve SQL queries

Version:

Only available versions of this content are shown in the dropdown

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

You need the PegaRULES:DatabaseAdministrator role to access the Query Inspector. For more information, see Adding a role to an access group.

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 Configure System Database Query 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.

Did you find this content helpful?

Have a question? Get answers now.

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

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

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

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us