All Products
Search
Document Center

Plan cache view

Last Updated: Jun 18, 2021

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_explain table, you must set parameters such as IP, port, tenant_id, and plan_id.

  • To query data in the v$plan_cache_plan_explain table, you must set parameters such as tenant_id, and plan_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.