Query Analysis

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

EXPLAIN [ PROTO | JSON | DEBUG ] query

This table defines the explain plan formats.

Examples

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

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.

EXPLAIN DEBUG SELECT * FROM names;

Output

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

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

Examples

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

EXPLAIN PIPELINE SELECT * FROM names;

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

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.

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.

Related Links

SQL Reference