Oracle教程 2023-04-07

Oracle 视图 DBA_SQL_PLAN_DIRECTIVES 官方解释，作用，如何使用详细说明

Oracle视图DBA_SQL_PLAN_DIRECTIVES用于查看系统中的SQL执行计划指令信息。它由Oracle数据库提供，其中包含各种计划指令的内容，用于查询SQL语句的执行计划，以及用于每个指令的消耗。

使用方法:

1. 运行以下语句，以查看当前计划指令信息：

SELECT * FROM DBA_SQL_PLAN_DIRECTIVES;

2. 如果要查看特定的SQL计划指令，可以使用WHERE子句：

SELECT * FROM DBA_SQL_PLAN_DIRECTIVES
WHERE directive_name = ‘DIRECTIVE_NAME’;

3. 指令计划指令可以根据计划指令值进行排序，例如：

SELECT * FROM DBA_SQL_PLAN_DIRECTIVES ORDER BY directive_value;

DBA_SQL_PLAN_DIRECTIVES displays information about the SQL plan directives in the system.

Column	Datatype	NULL	Description
`DIRECTIVE_ID`	`NUMBER`	`NOT NULL`	The identifier of the SQL plan directive
`TYPE`	`VARCHAR2(16)`		The type of the SQL plan directive: `DYNAMIC SAMPLING`: SQL plan directive `DYNAMIC SAMPLING RESULT`: Dynamic sampling query results. This value appears only in Oracle Database 12c Release 2 (12.2.0.1) and later releases. `UNKNOWN`: Unknown
`ENABLED`	`VARCHAR2(3)`		Indicates whether the SQL plan directive is enabled. Possible values: `YES`: The SQL plan directive is enabled. `NO`: The SQL plan directive is not enabled.
`STATE`	`VARCHAR2(10)`		The state of the SQL plan directive. Possible values include: `SUPERSEDED`: This value indicates that the corresponding column or groups have an extension or histogram, or that another SQL plan directive exists that can be used for the directive. `USABLE`: This value indicates that the SQL plan directive is usable for the optimizer.
`AUTO_DROP`	`VARCHAR2(3)`		If `YES`, the SQL plan directive gets dropped when unused beyond `SPD_RETENTION_WEEKS`
`REASON`	`VARCHAR2(36)`		The reason for creating the SQL plan directive
`CREATED`	`TIMESTAMP(6)`		The creation timestamp of the SQL plan directive
`LAST_MODIFIED`	`TIMESTAMP(6)`		The timestamp of most recent modification of the SQL plan directive
`LAST_USED`	`TIMESTAMP(9)`		The timestamp of most recent usage of the SQL plan directive
`NOTES`	`XMLTYPE`		Extra information about the SQL plan directive