Learning Dynamic Performance Table
Third article -(1)-v$sq 2007.5.25
A specific SQL statement is stored in the v$sql.
A statement can map multiple cursor, because the cursor that the object refers to can have different users (such as Example 1). If there is more than one cursor (child cursor) present, the V$sqlarea provides the collection information for all cursors.
Example 1 :
Here are the following child cursor
User A:select * from TBL
User B:select * from TBL
People think that these two statements are not the same ah, there may be many people will say is the same, but I tell you not necessarily, then why?
This tbla seems to be the same, but not necessarily oh, one is a user, one is a B user, then their execution plan analysis code differences may be big oh, change the wording of people understand:
SELECT * FROM A.tbl
SELECT * FROM B.tbl
On individual cursor, v$sql can be used. The view contains cursor-level data. Used when attempting to locate a session or user to parse the cursor.
The Plan_hash_value column stores the cursor execution plan as a numeric representation. Can be used to compare execution plans. Plan_hash_value lets you easily identify whether two execution plans are the same without having to compare them in one row.
V$sql the column description in:
- Sql_text:sql the first 1000 characters of a text
- Sharable_mem: The amount of shared memory occupied (in bytes)
- PERSISTENT_MEM: Fixed memory size (in bytes) for the lifetime
- RUNTIME_MEM: Fixed memory size during the execution period
- Sorts: number of completed sorts
- Loaded_versions: Shows whether the context heap is loaded, 1 is 0 no
- Open_versions: Shows whether the child cursor is locked, 1 is 0 no
- Users_opening: Number of users executing statements
- The fetch number of the FETCHES:SQL statement.
- Executions: number of executions since it was loaded into the cache library
- Users_executing: Number of users executing statements
- LOADS: Number of times an object has been loaded
- First_load_time: Initial loading time
- Invalidations: Invalid number of times
- Parse_calls: Number of parse calls
- Disk_reads: Number of Read disks
- Buffer_gets: Number of Read buffers
- rows_processed: Resolves the total number of columns returned by the SQL statement
- Command_type: Command Type Code
- Optimizer model for OPTIMIZER_MODE:SQL statements
- Optimizer_cost: The cost of this query given by the optimizer
- PARSING_USER_ID: First resolved user ID
- PARSING_SCHEMA_ID: The first resolved plan ID
- Kept_versions: Indicates whether the current child cursor is marked as resident memory using the Dbms_shared_pool package
- Address: The current cursor parent handle addresses
- Type_chk_heap: Current Heap type check description
- Hash_value: The hash value of the parent statement in the cache library
- Plan_hash_value: A numeric representation of the execution plan.
- Child_number: Number of child cursors
- MODULE: The first parsing of this statement is done by calling Dbms_application_info. The module name of the Set_module setting.
- ACTION: The first parse of this statement is by calling Dbms_application_info. Set_action the name of the action set.
- Serializable_aborts: Transaction failed to serialize number of times
- Outline_category: If OUTLINE is applied during the interpretation of the cursor, then this column will show OUTLINE categories, otherwise the columns are empty
- Cpu_time: CPU Usage time (units, milliseconds) for parse/execute/Get
- Elapsed_time: Elapsed time (units, milliseconds) for parsing/executing/acquiring
- Outline_sid:outline Session ID
- Child_address: Child cursor Address
- SqlType: Indicates the SQL language version used by the current statement
- Remote: Indicates whether the cursor is an image (y/n)
- Object_status: Object state (VALID or INVALID)
- Is_obsolete: Indicates whether the cursor is discarded when the number of child cursors is too large (y/n)
Third article -(2)-v$sql_plan 2007.5.28
This view provides a way to examine execution plans for the cursor that is executed and is still in the cache.
In general, the information provided in this view is very similar to the printed EXPLAIN plan, but the EXPLAIN plan shows a theoretical scheme that is not necessarily used at the time of execution, but V$sql_plan includes the actual plan used. The execution plan obtained from the explain plan statement can be different from the specific execution plan, because the cursor may be compiled with different session parameter values (for example, Hash_area_size).
V$sql_plan The data can be:
- Confirm the current execution plan
- Identify the effects of creating table indexes
- Find the access path that the cursor includes (for example, a full table query or a range index query)
- Identify the optimal selection of indexes
- Decide whether to optimize the selection of the detailed execution plan (e.g., nested loops join) as the developer wishes.
This view can also be used as a key mechanism in planning comparisons. Plan comparisons are typically used when the following changes occur:
- Delete and create a new index
- Executing parse statements on database objects
- modifying initial parameter values
- Switch from rule-based to cost-based optimization mode
- After upgrading an application or database to a new version
If the previous plan is still in (for example, selecting a record from V$sql_plan and saving it to an Oracle table for reference), then it is possible to identify how the performance of an SQL statement changes after the execution of the plan.
Attention:
Oracle strongly recommends that you collect optimization statistics using dbms_stats packages rather than analyze. This package allows you to collect statistical items in parallel, collect the complete statistics of the partitioned objects (partitioned objects), and better adjust your statistical collection methods in other ways. Here, the Cost-based Optimizer will eventually use the statistics items that were collected by Dbms_stats. Browse the oracle9i supplied PL/SQL package and type reference for more information about this package.
However, you must use the Analyze statement instead of the dbms_stats for statistical collection, not involving the Cost-based optimizer, as:
• Use the Validate or list CHAINED rows clause
• Collect information on the freelist blocks.
V$sql_plan common columns in:
In addition to some new columns, this view includes almost all of the plan_table columns, and the columns that are also present in plan_table have the same values:
- ADDRESS: Current cursor parent handle position
- Hash_value: The hash value of the parent statement in the library cache. Both the address and Hash_value columns can be used to connect V$sqlarea query cursor-specific information.
- Child_number: The child cursor sequence address,hash_value that uses this execution plan and child_number can be used to connect V$SQL query child cursor information.
- Operation: The name of the internal action performed in each step, for example: TABLE ACCESS
- OPTIONS: Describes the variants of the column operation in action, for example: full
- Object_node: Database link used to access objects the name of a local query that uses parallel execution can describe the order of output in an operation.
- object#: Number of table or indexed objects
- Object_owner: The name of the schema that contains the table or index for which it is an owner
- object_name: Table or index name
- OPTIMIZER: Executes the default optimization mode for the first column of the plan, for example, CHOOSE. For example, a business is a storage database that tells you if the object is optimized.
- ID: The ordinal number assigned to each step in the execution plan.
- PARENT_ID: The ID of the next execution step that operates on the output of the ID step.
- DEPTH: Business tree depth (or level).
- POSITION: The corresponding processing order for operations with the same parent_id.
- The Cost:cost-based method optimizes the evaluation of the operational overhead, and if the statement uses rule-based mode, this column will be empty.
- Cardinality: The evaluation of the number of rows accessed based on the cost-based mode.
- BYTES: The evaluation of the bytes produced according to the cost-based mode operation.
- Other_tag: Description of the contents of the other columns.
- Partition_start: The start partition in the range access partition.
- Partition_stop: The stop partition in the range access partition.
- PARTITION_ID: Calculates the number of steps for the column values Partition_start and Partition_stop
- Other: Additional information is the implementation of step details for user reference.
- Distribution: A method for allocating columns from a production server to a consumer server for parallel queries
- Cpu_cost: Evaluation of CPU operating overhead based on cost-based mode. If the statement uses the Rule-based method, this column is empty.
- Io_cost: Based on the evaluation of the cost-based mode I/O operation overhead. If the statement uses the Rule-based method, this column is empty.
- Temporary space occupancy Assessment for temp_space:cost-based mode operations (sort or hash-join). If the statement uses the Rule-based method, this column is empty.
- Access_predicates: Indicates to locate the column in the access structure, for example, at the beginning or end of a range index query.
- Filter_predicates: Indicates the filter column before the data is generated.
The CONNECT by operation produces a depth column replacement level pseudo-column, which is sometimes used to help indent plan_table data in SQL scripts
V$sql_plan the connection column in
Columns Address,hash_value and Child_number are used to connect v$sql or V$sqlarea to obtain cursor-specific information, for example, Buffer_get, or to connect V$sqltext to get the full SQL statement.
Column View Joined column (s)
Address, Hash_value V$sqlarea address, Hash_value
Address,hash_value,child_number v$sq Address,hash_value,child_number
Address, Hash_value V$sqltext address, Hash_value
Confirm SQL Optimization plan for statements
The following statement displays an execution plan for the specified SQL statement. Viewing the execution plan for an SQL statement is the first step in tuning an optimized SQL statement. The SQL statement that is queried to execute the plan is identified by the Hash_value and address columns of the statement. Two-Step execution:
1.SELECT Sql_text, Address, hash_value from V$sql
WHERE sql_text like '%tag% ';
Sql_text ADDRESS Hash_value
-------- -------- ----------
82157784 1224822469
2.SELECT operation, Options, object_name, cost from V$sql_plan
WHERE address = ' 82157784 ' and hash_value = 1224822469;
Operation OPTIONS object_name Cost
-------------------- ------------- ------------------ ----
SELECT STATEMENT 5
SORT
AGGREGATE
HASH JOIN 5
TABLE ACCESS FUL Departments 2
TABLE ACCESS FUL EMPLOYEES 2
Learning Dynamic Performance Table (3)--v$sql&v$sql_plan
