---
title: Query Analysis
slug: Nq2_dVjRT0KbvvJgWiGsd
description: Perform query analysis in Ocient to optimize performance and understand data patterns.
docTags: 
createdAt: Fri Jul 22 2022 13:26:54 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:42 GMT+0000 (Coordinated Universal Time)
---

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.&#x20;

**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**&#x20;

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.&#x20;

This example shows the optimized plan for the query `SELECT * FROM names` in DEBUG format, which produces a more compact execution plan.&#x20;

```sql
EXPLAIN DEBUG SELECT * FROM names;
```

*Output*

```none
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.&#x20;

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.&#x20;

## Related Links

[SQL Reference](<./././SQL Reference.md>)
