Tracking the SQL statement execution process and related knowledge development in oracle, oraclesql

Tracking the SQL statement execution process and related knowledge development in oracle, oraclesql

select * from v$sqlarea;

select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00';

Each record in the query result of this method displays a query statement, and only SQL _text can be queried for less than 1000 characters. The excess will be truncated.
Improvement:

Select * from v $ sqlarea where first_load_time> '2017-11-2010: 30: 00' and SQL _text like 'insert % '; // further Filter

Use fulltext if you want to display the excess 1000.

Knowledge Development:

1. V $ SQLTEXT

This view contains the complete text of SQL statements in the Shared pool. An SQL statement may be divided into multiple blocks and stored in multiple records.
Note: V $ SQLAREA only contains the first 1000 characters.

Common columns in V $ SQLTEXT
L HASH_VALUE: Hash Value of the SQL statement
L ADDRESS: ADDRESS of the SQL statement in SGA
L SQL _TEXT: SQL text.
L PIECE: SQL statement block number

Connection column in V $ SQLTEXT:

Column View Joined Column (s)

HASH_VALUE, address v $ SQL, V $ SESSION HASH_VALUE, ADDRESS
HASH_VALUE. address v $ SESSION SQL _HASH_VALUE, SQL _ADDRESS

Example: If hash_value: 3111103299 is known, query the SQL statement:

select * from v$sqltextwhere hashvalue= '3111103299'order by piece

2. V $ SQLAREA

This view keeps track of all the shared cursor instances in the shared pool. Each SQL statement in the shared pool corresponds to a column. This view is very important in analyzing SQL statement resource usage.

V $ SQLAREA information Column

L HASH_VALUE: Hash Value of the SQL statement.
L ADDRESS: the ADDRESS of the SQL statement in SGA.
These two columns are used to identify SQL statements. Sometimes, the two statements may have the same hash value. At this time, the SQL statement must be used together with the ADDRESS to confirm.
L PARSING_USER_ID: the user who parses the first CURSOR by a statement.
L VERSION_COUNT: Number of cursor statements
L KEPT_VERSIONS:
L SHARABLE_MEM: Total number of shared memory used by cursor
L PERSISTENT_MEM: Total number of resident memory used by cursor
L RUNTIME_MEM: Total number of runtime memory used by cursor.
L SQL _TEXT: the text of an SQL statement (up to 1000 characters of the statement can be saved ).
L MODULE, ACTION: When DBMS_APPLICATION_INFO is used, the session parses the information of the first cursor.
Other common columns in V $ SQLAREA
L SORTS: Number of sort statements
L CPU_TIME: CPU time when the statement is parsed and executed
L ELAPSED_TIME: the time when the statement is parsed and executed.
L parse_cils: Number of resolution calls (soft and hard) of the statement
L EXECUTIONS: number of statements executed
L INVALIDATIONS: Number of cursor failures of a statement
L LOADS: number of statements loaded (loaded)
L ROWS_PROCESSED: Total number of columns returned by the statement

Connection column in V $ SQLAREA
Column View Joined Column (s)
HASH_VALUE, address v $ SESSION SQL _HASH_VALUE, SQL _ADDRESS
HASH_VALUE, address v $ SQLTEXT, V $ SQL, V $ OPEN_CURSOR HASH_VALUE, ADDRESS
SQL _TEXT V $ DB_OBJECT_CACHE NAME

Example:
A. view the SQL statements that consume the most resources:

SELECT hash_value, executions, buffer_gets, disk_reads, parse_callsFROM V$SQLAREAWHERE buffer_gets > 10000000 OR disk_reads > 1000000ORDER BY buffer_gets + 100 * disk_reads DESC ;

B. view the resource consumption of an SQL statement:

SELECT hash_value, buffer_gets, disk_reads, executions, parse_callsFROM V$SQLAREAWHERE hash_Value = 228801498 AND address = hextoraw( 'CBD8E4B0' );

C. Search for the first 10 SQL statements with poor performance:

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ;  SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;

EXECUTIONS indicates the total number of EXECUTIONS of the same SQL statement, SORTS indicates the number of SORTS, and DISK_READS indicates the number of physical reads.

3. v $ SQL

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: child cursor

user A: select * from tbluser 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.tblselect * 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:
L SQL _TEXT: first 1000 characters of SQL text
L SHARABLE_MEM: size of the occupied shared memory (unit: byte)
L PERSISTENT_MEM: Fixed memory size during the life cycle (unit: byte)
L RUNTIME_MEM: Fixed memory size during the execution period
L SORTS: number of completed sorting tasks
L LOADED_VERSIONS: displays whether the context heap is loaded, and 1 is 0 No
L OPEN_VERSIONS: displays whether the Sub-cursor is locked. 1 is 0 or not.
L USERS_OPENING: Number of users executing statements
L FETCHES: the number of FETCHES of SQL statements.
L EXECUTIONS: number of EXECUTIONS since it was loaded into the cache Library
L USERS_EXECUTING: Number of users executing statements
L LOADS: number of times an object has been loaded
L FIRST_LOAD_TIME: The first loading time.
L INVALIDATIONS: Invalid Number of times
L parse_cils: Number of resolution calls
L DISK_READS: Number of disk reads
L BUFFER_GETS: Number of read cache segments
L ROWS_PROCESSED: parses the total number of columns returned by the SQL statement.
L COMMAND_TYPE: Command type code
L OPTIMIZER_MODE: optimizer model of SQL statements
L OPTIMIZER_COST: the query cost provided by the optimizer.
L PARSING_USER_ID: the ID of the first resolved user
L PARSING_SCHEMA_ID: Plan ID of the first resolution
L KEPT_VERSIONS: indicates whether the current sub-cursor is marked as resident memory using the DBMS_SHARED_POOL package
L ADDRESS: current cursor parent handle ADDRESS
L TYPE_CHK_HEAP: Current heap type check description
L HASH_VALUE: Hash Value of the parent statement in the cache Database
L PLAN_HASH_VALUE: The Execution Plan expressed by numerical values.
L CHILD_NUMBER: Number of child cursors
L MODULE: In the first parsing, the MODULE name set by DBMS_APPLICATION_INFO.SET_MODULE is called.
L ACTION: During the first parsing, the ACTION name set by DBMS_APPLICATION_INFO.SET_ACTION is called.
L SERIALIZABLE_ABORTS: Number of times the transaction fails to be serialized
L OUTLINE_CATEGORY: If outline is applied during the interpretation of cursor, this column displays all types of outline; otherwise, this column is empty.
L CPU_TIME: CPU usage time (unit: milliseconds), such as resolution, execution, and retrieval)
L ELAPSED_TIME: Time consumed for parsing, execution, and retrieval (unit: milliseconds)
L OUTLINE_SID: outline session ID
L CHILD_ADDRESS: subcursor address
L SQLTYPE: indicates the SQL language version used by the current statement.
L REMOTE: indicates whether the cursor is a REMOTE image (Y/N)
L OBJECT_STATUS: Object status (VALID or INVALID)
L IS_OBSOLETE: when there are too many sub-game targets, it indicates whether the cursor is discarded (Y/N)

Original article address:
Http://hi.baidu.com/clebean/item/73297be5da8cba0e8d3ea8e7
Http://blog.sina.com.cn/s/blog_8019d3c10100rte3.html

How Does oracle track the execution of an SQL statement?

Create a table to record
Explain plan SET statement_id = 'name' FOR (here is the statement you want to debug)

SELECT
A. OPERATION,
OPTIONS,
OBJECT_NAME,
OBJECT_TYPE,
ID,
PARENT_ID
FROM
PLAN_TABLE
WHERE
STATEMENT_ID = 'name'
ORDER
Id;

The ID 'name' is an identifier. You can retrieve multiple fields by yourself. The following describes the explanations of each field (the format may be incorrect. You can copy it and check it later ):

Field Name field type meaning
The value of the optimal STATEMENT_ID parameter specified in the STATEMENT_ID VARCHAR2 (30) explain PLAN statement. If SET STATEMENT_ID is not used in the explain PLAN statement, the value is SET to NULL.

REMARKS VARCHAR2 (80) Comments associated with each step of the explain Plan can be up to 80 bytes

OPERATION VARCHAR2 (30) Name of the internal OPERATION performed by each step in the first row generated by a STATEMENT. The possible values of this column are as follows: DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT

OPTIONS VARCHAR2 (30) variants of the operations described in the OPERATION Column

OBJECT_NODE VARCHAR2 (128) is used to access the database link of the object. The database link name can describe the order of output operations for local queries that are executed in parallel.

OBJECT_OWNER VARCHAR2 (30) Name the owner of schema containing tables or Indexes

OBJECT_NAME VARCHAR2 (30) Name of the table or index

OBJECT_INSTANCE INTEGER indicates the sequence number given based on the order in which the object appears in the original statement. The processing sequence of the original statement text is expanded from left to right from outside to outside. view

OBJECT_TYPE VARCHAR2 (30) modifier used to provide descriptive information about an object, such as the NON-UNIQUE of an index

OPTIMIZER VARCHAR2 (255) Current OPTIMIZER Mode

Id integer number assigned to each step of the Execution Plan

PARENT_ID INTEGER: ID of the next execution step of the operation on the output of the ID step

Position integer indicates the processing order of the steps with the same PARENT_ID.

Cost integer estimates the operation overhead value based on the overhead method of the optimizer. For statements that use the rule-based method, this column is empty. This column value has no specific measurement unit. It is only used for comparison. weight of execution planning overhead size

Cardinality integer ...... remaining full text>

How can Oracle easily track executed SQL statements like SQLSERVER?

V $ session works with v $ SQL. query these two views.

SELECT * FROM v $ session a, v $ SQL B
WHERE a. SQL _ID = B. SQL _ID (+)