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

# SHOW STATEMENT HINTS

The `SHOW STATEMENT HINTS` [statement](/docs/stable/sql-statements.md) lists the [statement hints](/docs/stable/cost-based-optimizer.md#statement-hints) that have been created for a specific SQL statement fingerprint using the `information_schema.crdb_rewrite_inline_hints()`, `information_schema.crdb_set_session_variable_hint()`, `information_schema.crdb_pin_plan_gist()`, and `information_schema.crdb_pin_plan_gram()` [built-in functions](/docs/stable/functions-and-operators.md#system-repair-functions).

## Required privileges

Users must have the [`VIEWCLUSTERMETADATA`](/docs/stable/security-reference/authorization#supported-privileges) privilege to run `SHOW STATEMENT HINTS`.

## Synopsis

![show\_statement\_hints syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/show_statement_hints.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=f976b0922e1fffc91c8f547b6636c57a)

## Parameters

| Parameter | Description |
| - | - |
| `string_or_placeholder` | The SQL statement fingerprint to show hints for. This can be a string literal (such as `'SELECT * FROM users WHERE city = _'`) or a SQL parameter placeholder (such as `$1`) for use in prepared statements. |

### Options

| Option | Value | Description |
| - | - | - |
| `DETAILS` | N/A | Include hint-specific information in JSON format. |

## Response

The following fields are returned:

| Column | Type | Description |
| - | - | - |
| `row_id` | `INT` | A unique ID. |
| `fingerprint` | `STRING` | The SQL statement fingerprint that the hint applies to. |
| `hint_type` | `STRING` | Hint type. `REWRITE INLINE HINTS` indicates a rewritten inline hint. `SET VARIABLE` indicates a session variable override. [`PINNED PLAN`](/docs/stable/cost-based-optimizer#pin-query-plans) indicates a pinned query plan. |
| `database` | `STRING` | The database the hint applies to. `NULL` if the hint applies to all databases. |
| `enabled` | `BOOL` | Whether the hint is enabled. |
| `created_at` | `TIMESTAMPTZ` | The timestamp when the hint was created. |
| `details` | `JSONB` | When the [`DETAILS`](#options) option is specified, hint-specific information in JSON format. For `REWRITE INLINE HINTS`, this includes the donor SQL fingerprint with hints that will be applied. For `SET VARIABLE`, this includes the session variable name and value. For [`PINNED PLAN`](/docs/stable/cost-based-optimizer#pin-query-plans), this includes the [PlanGram](/docs/stable/cost-based-optimizer.md#plangrams) that describes the pinned plan shapes. |

## Examples

### Show hints for a statement

To show all hints for a specific statement fingerprint:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW STATEMENT HINTS FOR $$ SELECT * FROM users WHERE city = _ $$;
```

```
        row_id        |            fingerprint             |      hint_type       | database | enabled |          created_at
----------------------+------------------------------------+----------------------+----------+---------+--------------------------------
  1143470380756697089 | SELECT * FROM users WHERE city = _ | REWRITE INLINE HINTS | NULL     | t       | 2026-01-21 21:11:06.782818+00
(1 row)
```

### Show hints with detailed information

To include hint-specific details in the output:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW STATEMENT HINTS FOR $$ SELECT * FROM users WHERE city = _ $$ WITH DETAILS;
```

```
        row_id        |            fingerprint             |      hint_type       | database | enabled |          created_at           |                              details
----------------------+------------------------------------+----------------------+----------+---------+-------------------------------+--------------------------------------------------------------------
  1143470380756697089 | SELECT * FROM users WHERE city = _ | REWRITE INLINE HINTS | NULL     | t       | 2026-01-21 21:11:06.782818+00 | {"donorSql": "SELECT * FROM users@users_city_idx WHERE city = _"}
(1 row)
```

This example shows a `REWRITE INLINE HINTS` type hint. For a `SET VARIABLE` hint, the details would include the session variable name and value:

```
        row_id        |            fingerprint             | hint_type    | database | enabled |          created_at           |                       details
----------------------+------------------------------------+--------------+----------+---------+-------------------------------+------------------------------------------------------
  1167741942826958849 | SELECT * FROM users WHERE city = _ | SET VARIABLE | NULL     | t       | 2026-04-17 14:42:39.7001+00   | {"variableName": "vectorize", "variableValue": "on"}
(1 row)
```

For a [`PINNED PLAN`](/docs/stable/cost-based-optimizer#pin-query-plans) hint, the details would include the [PlanGram](/docs/stable/cost-based-optimizer.md#plangrams) that describes the pinned plan shapes:

```
        row_id        |            fingerprint             |  hint_type  | database | enabled |          created_at           |                                                                                                         details
----------------------+------------------------------------+-------------+----------+---------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1192441097581395969 | SELECT * FROM users WHERE city = _ | PINNED PLAN | NULL     |    t    | 2026-07-13 20:29:03.547593+00 | {"planGram": "root: n0; n0: (Scan Table=\"users\" Index=\"users_pkey\" HasConstraint=\"true\" HasLimit=\"false\") | (PlaceholderScan Table=\"users\" Index=\"users_pkey\" HasConstraint=\"true\" HasLimit=\"false\");"}
(1 row)
```

## See also

* [Statement hints](/docs/stable/cost-based-optimizer.md#statement-hints)
* [Index hints](/docs/stable/table-expressions.md#force-index-selection)
* [Join hints](/docs/stable/cost-based-optimizer.md#join-hints)
* [SQL Statements](/docs/stable/sql-statements.md)