> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Analysis

export const Ocient = "Ocient®";

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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
EXPLAIN DEBUG SELECT * FROM names;
```

*Output*

```none Text theme={null}
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 SQL theme={null}
-- 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 SQL theme={null}
EXPLAIN PIPELINE SELECT * FROM names;
```

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

```sql SQL theme={null}
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 SQL theme={null}
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](/sql-reference)