Query Analysis - Ocient Documentation

You can analyze queries by commands that instruct Ocient® to return plan-related information for a database administrator or analyst to evaluate. The results of these commands are vital in performance optimization activities on an Ocient System. Ocient supports SQL DDL commands to terminate running queries and distributed tasks. These commands provide administrative control over query execution and are distinct from programmatic cancellation methods in JDBC and pyocient drivers.

EXPLAIN

EXPLAIN shows the optimized query plan. The database optimizes the query normally, but it does not execute. Instead, the database returns the query plan in the specified format. Syntax

SQL

EXPLAIN [ PROTO | JSON | DEBUG ] query

Parameter	Data type	Description
`query`	string	A valid SELECT statement on a database table.

This table defines the explain plan formats.

Format	Description
PROTO	Includes the same information as the JSON format, but it is presented in a human-readable format.
JSON	The default option. Returns the optimized query plan in JSON format.
DEBUG	The DEBUG format is more compact but does not include some information about each operator.

Examples This example shows the optimized plan for the query SELECT * FROM names in JSON format.

SQL

EXPLAIN SELECT * FROM names;

This EXPLAIN example generates a lengthy output showing the execution plan for the query. This execution plan shows how the database scans the tables referenced by the statement and calculates the memory usage and total execution cost. This example shows the optimized plan for the query SELECT * FROM names in DEBUG format, which produces a more compact execution plan.

SQL

EXPLAIN DEBUG SELECT * FROM names;

Output

Text

ROOT_OP @ (0x7eae71515c40), ID: 94921393, md: 2, Out (1): time_x |
  REORDER_OP @ (0x7f4c19ee1c40), ID: 94921392, md: 0, Out (1): time_x | no-op reorder
    RENAME_OP @ (0x7f49b1bc2c40), ID: 94921390, md: 2, Out (1): time_x | Old2New: (loc.time_x -> time_x)
      GATHER_OP @ (0x7f49b7886a40), ID: 94921389, md: 2, Out (1): loc.time_x |
        GATHER_OP @ (0x7f49b78866c0), ID: 94921388, md: 2, Out (1): loc.time_x |
          RANDOM_SHUFFLE_OP @ (0x7f486825ba40), ID: 94921394, md: 2, Out (1): loc.time_x |
            INDEX_OP @ (0x7f49b792f040), ID: 94921387, md: 1, Out (1): loc.time_x |

EXPLAIN PIPELINE

EXPLAIN PIPELINE shows the pipeline the system uses for compiling a query. The system compiles the pipeline for the specified segments, and if no segment is specified, it picks random segments with the tables. To explain a pipeline, you must be a system-level user or possess the SELECT TABLE privilege for the table used in the query. Syntax

SQL

-- To show pipeline for a random segment:
EXPLAIN PIPELINE query

-- To show pipeline for a specific segment:
EXPLAIN PIPELINE FOR SEGMENT 'storage_id' ON query

-- To show pipeline for multiple specific segments:
EXPLAIN PIPELINE FOR SEGMENT IN ('storage_id' [, ...]) ON query

Parameter	Data type	Description
`query`	string	A valid SELECT statement on a database table.
`storage_id`	string	A valid segment storage identifier.

Examples This example shows the pipeline on the query SELECT * FROM names for a random segment.

SQL

EXPLAIN PIPELINE SELECT * FROM names;

This example shows the pipeline for the segment with the identifier 529a2e9d-d06c-46cd-a93b-624d3bed1c08.

SQL

EXPLAIN PIPELINE FOR SEGMENT '529a2e9d-d06c-46cd-a93b-624d3bed1c08'
    ON SELECT * FROM names;

This example shows the pipeline for segments with these identifiers: 529a2e9d-d06c-46cd-a93b-624d3bed1c08, 378eac93-2e3d-4ce6-bf1f-d8b7a78fe585.

SQL

EXPLAIN PIPELINE FOR SEGMENT IN (
    '529a2e9d-d06c-46cd-a93b-624d3bed1c08',
    '378eac93-2e3d-4ce6-bf1f-d8b7a78fe585')
    ON SELECT * FROM names;

All the examples generate lengthy output that includes the execution plan for the query for the specified segments. This execution plan shows how the database scans the tables referenced by the statement and calculates the memory usage and total execution cost.

PLAN EXECUTE

You can execute a previously generated query plan directly, bypassing the query optimizer. This execution is useful for replaying specific execution plans during performance analysis or debugging. The Ocient JDBC CLI supports two forms of plan execution:

PLAN EXECUTE FILE — Read the plan from a local file, which is recommended for large plans.
PLAN EXECUTE INLINE — Provide the plan text directly at the command line.

For syntax and usage details, see PLAN EXECUTE FILE and PLAN EXECUTE INLINE. SQL Reference

​EXPLAIN

​EXPLAIN PIPELINE

​PLAN EXECUTE

​Related Links

EXPLAIN

EXPLAIN PIPELINE

PLAN EXECUTE

Related Links