The plan cache view contains the status record of a plan cache, execution statistics of plans, and plan information.
(g)v$plan_cache_stat
(g)v$plan_cache_stat records the status of each plan cache, and each plan cache has a record entry in this view.
| Field | Type | Description |
|---|---|---|
| tenant_id | bigint(20) | The ID of the tenant. |
| svr_ip | varchar(32) | The IP address of the server. |
| svr_port | bigint(20) | The port number of the server. |
| sql_num | bigint(20) | The number of SQL queries cached in the plan cache. |
| mem_used | bigint(20) | The memory usage of the plan cache. |
| access_count | bigint(20) | The number of times the plan cache is accessed. |
| hit_count | bigint(20) | The number of times the plan cache is hit. |
| hit_rate | bigint(20) | The number of times the plan cache is hit. |
| plan_num | bigint(20) | The number of plans in the plan cache. |
| mem_limit | bigint(20) | The upper limit of the memory usage of the plan cache. |
| hash_bucket | bigint(20) | The number of buckets in the hash map of the plan cache. |
| stmtkey_num | bigint(20) | The number of the stmt_key fields in the plan cache. |
(g)v$plan_cache_plan_stat
(g)v$plan_cache_plan_stat records the specific information of all plans in a plan cache, and the general execution statistics of each plan. Each plan has a record entry in the view.
| Field | Type | Description |
|---|---|---|
| tenant_id | bigint(20) | The ID of the tenant. |
| svr_ip | varchar(32) | The IP address of the server. |
| svr_port | bigint(20) | The port number of the server. |
| plan_id | bigint(20) | The ID of the plan. |
| sql_id | varchar(32) | The ID of the SQL query. |
| type | bigint(20) | The type of the plan. Valid values: * 1: local plan * 2: remote plan * 3: distributed plan |
| db_id | bigint(20) unsigned | The ID of the database. |
| is_bind_sensitive | bigint(20) | Indicates whether Adaptive Cursor Sharing (ACS) needs to be enabled for the plan. |
| is_bind_aware | bigint(20) | Indicates that ACS is enabled for the plan. |
| statement | varchar(4096) | The parameterized SQL statement. |
| query_sql | varchar(65536) | The original SQL statement used to query data when the plan is loaded for the first time. |
| sys_vars | varchar(4096) | The value of the system variable that affects the plan. |
| plan_hash | bigint(20) | The hash value of the plan. |
| first_load_time | timestamp(6) | The time when the plan was loaded for the first time. |
| schema_version | bigint(20) | The version of the schema. |
| merged_version | bigint(20) | The version of the merged plan corresponding to the current cached plan. |
| last_active_time | timestamp(6) | The time when the plan was last executed. |
| avg_exe_usec | bigint(20) | The average execution time of the plan. |
| slowest_exe_time | timestamp(6) | The time consumed for the slowest execution. |
| slowest_exe_usec | bigint(20) | The timestamp of the slowest execution. |
| slow_count | bigint(20) | The number of times that the plan causes a slow query. |
| hit_count | bigint(20) | The number of times the plan cache is hit. |
| plan_size | bigint(20) | The size of the plan. |
| executions | bigint(20) | The number of times the plan is executed. |
| disk_reads | bigint(20) | The total number of physical read operations that are performed. |
| direct_writes | bigint(20) | The total number of writes to the disk. |
| buffer_gets | bigint(20) | The total number of logical read operations that are performed. |
| application_wait_time | bigint(20) unsigned | The total period of time consumed for executing events of the APPLICATION type. |
| concurrency_wait_time | bigint(20) unsigned | The total period of time consumed for executing events of the CONCURRENCY type. |
| user_io_wait_time | bigint(20) unsigned | The total period of time consumed for executing events of the USER_IO type. |
| rows_processed | bigint(20) | The number of result rows selected or the number of rows modified by executing the ALTER TABLE statement. |
| elapsed_time | bigint(20) unsigned | The time consumed from the reception of the execution requests to the end of all executions. |
| cpu_time | bigint(20) unsigned | The CPU time consumed for all executions. |
| large_querys | bigint(20) | The number of slow queries. |
| delayed_large_querys | bigint(20) | The number of slow queries that are put to the slow query queue. |
| outline_version | bigint(20) | The version of the outline. |
| outline_id | bigint(20) | The ID of the outline. The value -1 indicates a plan that is not generated by binding an outline. |
| outline_data | varchar(65536) | The information about the outline corresponding to the plan. |
| acs_sel_info | varchar(65536) | The selection probability space corresponding to the current ACS-enabled plan. |
| table_scan | tinyint(4) | Indicates whether the query is a primary key scan. |
| evolution | bool | Indicates whether the plan is evolving. |
| evo_executions | bigint(20) | The number of plan evolutions. |
| evo_cpu_time | bigint(20) unsigned | The total CPU time consumed for plan evolution. |
| timeout_count | bigint(20) | The number of timeouts. |
| ps_stmt_id | bigint(20) | The ID of the PREPARE statement. |
(g)v$plan_cache_plan_explain
(g)v$plan_cache_plan_explain records the execution plans of an SQL statement in the plan cache. Notice
To query data in the
gv$plan_cache_plan_explaintable, you must set parameters such as IP, port,tenant_id, andplan_id.To query data in the
v$plan_cache_plan_explaintable, you must set parameters such astenant_id, andplan_id.
| Field | Type | Description |
|---|---|---|
| TENANT_ID | bigint(20) | The ID of the tenant. |
| IP | varchar(32) | The IP address of the server. |
| PORT | bigint(20) | The port number of the server. |
| PLAN_ID | bigint(20) | The ID of the plan. |
| OPERATOR | varchar(128) | The name of the operator. |
| NAME | varchar(128) | The name of the table. |
| ROWS | bigint(20) | The estimated number of result rows. |
| COST | bigint(20) | The estimated cost. |
| PROPERTY | varchar(256) | The information about the corresponding operator. |