Learning Dynamic Performance Table (III)-(1)-V $ SQL

Learning Dynamic Performance Table 3-(1)-V $ SQL

V $ SQL stores specific SQL statements.

One statement can map multiple cursor because the cursor referred to by the object can have different users (for example, 1 ). If multiple cursor (sub-cursor) exist, provide set information for all cursor in V $ sqlarea.
Example 1:
Here we will introduce the following child cursor
User A: Select * From TBL
User B: Select * From TBL
Do you think these two statements are the same? Many may say they are the same, but I tell you not necessarily. Why?
This tbla looks the same, but not necessarily. One is for user a and the other is for user B. In this case, their execution plan analysis code may differ greatly, now you can understand the following:
Select * from A. TBL
Select * from B. TBL

On individual cursor, V $ SQL can be used. This view contains cursor-level data. Used when attempting to locate a session or user to analyze cursor.
The plan_hash_value column stores the cursor execution plan represented by numerical values. Can be used to compare execution plans. Plan_hash_value allows you to easily identify whether two execution plans are the same without comparing one row or one line.

Description of columns in V $ SQL:
SQL _text: The first 1000 characters of SQL text
Sharable_mem: size of the occupied shared memory (unit: byte)
Persistent_mem: Fixed memory size during the life cycle (unit: byte)
Runtime_mem: Fixed memory size during the execution period
Sorts: number of completed sorting tasks
Loaded_versions: displays whether the context heap is loaded, and 1 is 0 No
Open_versions: displays whether the Sub-cursor is locked, and 1 is 0 No
Users_opening: Number of users executing statements
Fetches: the number of fetches of SQL statements.
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: The first loading time.
Invalidations: Number of invalid times
Parse_cils: Number of resolution calls
Disk_reads: Number of disk reads
Buffer_gets: Number of read cache segments
Rows_processed: parses the total number of columns returned by the SQL statement.
Command_type: Command type code
Optimizer_mode: optimizer model of SQL statements
Optimizer_cost: the query cost provided by the optimizer.
Parsing_user_id: The first resolved user ID
Parsing_schema_id: Plan ID of the first resolution
Kept_versions: indicates whether the current sub-cursor is marked as resident memory using the dbms_shared_pool package
Address: current cursor parent handle address
Type_chk_heap: description of current heap type check
Hash_value: the hash value of the parent statement in the cache database.
Plan_hash_value: Number of execution plans.
Child_number: Number of child cursors
Module: In the first parsing, the module name set by dbms_application_info.set_module is called.
Action: The action name set by dbms_application_info.set_action is called for the first parsing of this statement.
Serializable_aborts: Number of times the transaction failed to be serialized
Outline_category: If outline is applied during the interpretation of cursor, this column displays all types of outline. Otherwise, this column is empty.
Cpu_time: CPU usage time (unit: milliseconds) such as resolution, execution, and retrieval)
Elapsed_time: Time consumed for parsing, execution, and retrieval (unit: milliseconds)
Outline_sid: Outline session ID
Child_address: subcursor address
Sqltype: indicates the SQL language version used by the current statement.
Remote: indicates whether the cursor is a remote image (y/N)
Object_status: Object status (valid or invalid)
Is_obsolete: when there are too many sub-game targets, it indicates whether the cursor is discarded (y/N)