1. V $ SQL %-2
V $ SQL _plan,
1. This view provides a way to check the execution plans of the executed cursor that is still in the cache. Generally, the information provided by this view is very similar to the displayed explain plan. However, the explain Plan shows a theoretical plan and is not necessarily used during execution, however, V $ SQL _plan includes the actually used plan.
Common columns in V $ SQL _planExcept for some newly added columns, this view includes almost all plan_table columns, which also exist in plan_table and have the same value:
Address: current cursor parent handle location
Hash_value: the hash value of the parent statement in the library cache.
The address and hash_value columns can be used to connect v $ sqlarea to query cursor-specific information.
Child_number: Number of subcursor using this execution plan
The column address, hash_value, and child_number can be used to connect v $ SQL to query the subcursor information.
Operation: name of the internal operation performed in each step, for example, table access
Options: description of the operation variant of the column, for example, full
Object_node: the name of the database link used to access the object. This column can describe the order of output operations for local queries that are executed in parallel.
Object #: Number of table or index objects
Object_owner: name of the owner of the schema containing tables or indexes.
Object_name: name of the table or index
Optimizer: the default optimization mode of the first column in the execution plan; for example, choose. For example, if a business is a storage database, it will tell you whether the objects are optimized.
ID: The serial number assigned to each step in the execution plan.
Parent_id: ID of the next execution step for the output of the ID step.
Depth: Business tree depth (or level ).
Position: The processing order of operations with the same parent_id.
Cost: evaluate the operation overhead optimized by cost-based. If the statement uses the rule-based method, this column will be empty.
Cardinality: Evaluate the number of rows accessed by the cost-based operation.
Bytes: Evaluate the bytes produced by cost-based operations ,.
Other_tag: Description of other columns.
Partition_start: the start partition in the range access partition.
Partition_stop: Stop partition in the range access partition.
Partition_id: number of steps for calculating the value of partition_start and partition_stop
Other: other information is the execution step details for your reference.
Distribution: stores methods for allocating columns from production servers to consumption servers for parallel queries.
Cpu_cost: Evaluate the CPU operation overhead based on the cost-based method. If the statement uses the rule-based method, this column is empty.
Io_cost: The cost of I/O operations based on the cost-based method. If the statement uses the rule-based method, this column is empty.
Temp_space: Evaluate the temporary space occupation of the cost-based operation (sort or hash-join. If the statement uses the rule-based method, this column is empty.
Access_predicates: Specifies the column to locate in the access structure, for example, the start or end position in the range index query.
Filter_predicates: Specifies the filtering column before data is generated. The connect by Operation generates a depth column to replace the level pseudo column, which is sometimes used in SQL scripts to help indent plan_table data 2,
V $ SQL _plan
Connection column inColumn address, hash_value, and child_number are used to connect v $ SQL or V $ sqlarea to obtain cursor-specific information, such as buffer_get, or connect v $ sqltext to obtain the complete SQL statement. Column view joined column (s)
Address, hash_value v $ sqlarea address, hash_value
Address, hash_value, child_number v $ SQL address, hash_value, child_number
Address, hash_value v $ sqltext address, hash_value 3. Oracle strongly recommends that you use the dbms_stats package instead of analyze to collect optimization statistics. This package allows you to collect statistical items in parallel, collect the complete statistics of partition objects (partitioned objects), and better adjust your statistical collection methods through other methods. Here, the cost-based optimizer will eventually use the statistics collected by dbms_stats. Example: Execution Plan of the queried SQL statement: 1. select SQL _text, address, hash_value from V $ SQL where SQL _text like '% tag %'; 2. Select Operation, options, object_name, cost from V $ SQL _plan where address = '??? 'And hash_value = ???;
