Oracle SQL resource consumption related views

first, the common view description

Oracle SQL statement resource consumption monitoring, the most commonly used system views are:

V$sql

V$sqlarea

V$sqltext

V$session

V$sql and V$sqlarea are basically the same, documenting the SQL statistics in the shared SQL Zone (share pool), such as memory consumption, IO (physical disk read and logical memory reads), sorting operations, hash IDs, and so on. The difference is that V$sql keeps an entry for each SQL, while V$sqlarea is based on sql_text (note that it stores the first 1000 characters of the current SQL pointer, which means that the SQL logged here may be incomplete!). ) for group BY, the Statistics column is sum (), and the number of child pointers is computed by Version_count.

However, the same SQL statement in text (Sql_text) may have a completely different meaning in the database. For example, there are two users in the database User1 and User2, these two users each have a data sheet emp. Then when two users issue queries for select COUNT (*) from EMP, each accesses the table EMP in its schema, and the contents of both tables are different so its resource consumption must be different. At this point, there will be two identical SQL statistics in V$sql, and in V$sqlarea, the same 2 pointers will be merged, the number of executions, disk_reads, buffer_gets and other statistics will accumulate (sum), The Version_count will be shown as 2, which is the V$sqlarea aggregation effect.

There are no statistics in v$sqltext , but the complete SQL statement and its hash ID are stored.

For these three, we can use view v$fixed_view_definition to view the source table of the view, as follows:

SELECT view_definition from v$fixed_view_definition WHERE view_name= ' gv$sql ';

SELECT view_definition from v$fixed_view_definition WHERE view_name= ' Gv$sqlarea ';

SELECT view_definition from v$fixed_view_definition WHERE view_name= ' Gv$sqltext ';

Note: The view is named V$sql but the source of the view is Gv$sql, so use gv$sql directly, and the other two are the same.

Through the above 3 statements can be found that v$sql data source x$kglcursor_child, in fact, the data is still derived from X$kglob, and v$sqlarea data source X$kglcursor_child_sqlid essence is the X$kglcursor_ The child is grouped together according to fields such as sql_id, and the V$sqltext data source X$kglna.

V$session is primarily used to determine session-related information, such as using SIDS and serial# to uniquely determine a session (SID may be duplicated), session owner user name username, session state (active: Executing SQL statement, inactive: Wait for operation, killed: killed), which client the session was initiated by (machine, TERMINAL), what SQL is being executed (determined by sql_address, Sql_hash_value, sql_id, Sql_child_number , which can be known with the help of V$sqltext), or even what SQL was last executed (determined by prev_sql_address, etc.), the lock waits for information such as the table, file, block, locked line, etc.

High resource consumption SQL lookup locator

1) View more SQL that reads hard disk or consumes more memory:

Select Sql_text, Disk_reads, buffer_gets, parsing_schema_name, executions

From V$sqlarea

ORDER BY disk_reads Desc;

Note: The amount of memory consumed by each SQL cannot be isolated from V$sqlarea, but we can use disk reads to indirectly reflect potentially large memory-intensive SQL statements, and then use the execution plan (such as the V$sql_plan view) to view them.

With the system view V$sqlarea, where Disk_reads is the number of disk reads and the primary field, the remaining fields are reference fields. Where Buffer_gets is the number of memory reads, Parsing_schema_name is the first compiler pattern name (typically the same as the user name), executions is the number of statement executions.

It is important to note that the Sql_text in V$sqlarea may not be complete, and if it needs to be complete, it needs to be hash_value or sql_id combined with V$sqltext to see the analysis.

2) View more executions of SQL

Select Sql_text, executions, parsing_schema_name

From V$sqlarea

ORDER BY executions Desc;

3) View the SQL with multiple sorts

Select Sql_text, sorts, parsing_schema_name

From V$sqlarea

order BY sorts Desc;

The service should also involve the library cache hit rate, memory hit rate and so on, not summarized.

4) View the top 10 SQL statements with the most total consumed time
SELECT *
From (select v.sql_id,
V.child_number,
V.sql_text,
V.elapsed_time,
V.cpu_time,
V.disk_reads,
Rank () Over (order by v.elapsed_time Desc) Elapsed_rank
From V$sql v) a
where Elapsed_rank <= 10;

5) View the top 10 SQL statements with the most CPU consumption time
SELECT *
From (select v.sql_id,
V.child_number,
V.sql_text,
V.elapsed_time,
V.cpu_time,
V.disk_reads,
Rank () Over (order by v.cpu_time Desc) Elapsed_rank
From V$sql v) a
where Elapsed_rank <= 10;

6) View the top 10 SQL statements that consume disk read most
SELECT *
From (select v.sql_id,
V.child_number,
V.sql_text,
V.elapsed_time,
V.cpu_time,
V.disk_reads,
Rank () Over (order by v.disk_reads Desc) Elapsed_rank
From V$sql v) a
where Elapsed_rank <= 10;

Note: If you perform the above search hint ra-00942:table or view does not exist

This experience hint does not mean that there are no V$sql objects in the DB instance, but that your current user does not have permissions. The solution is to give the current user the V$sqlarea query permission

Sql>grant Select any dictionary to myuser;

650) this.width=650; "title=" qq20170808161914.jpg "src=" https://s4.51cto.com/wyfs02/M01/9D/FB/wKioL1mJgPzyH_ Araabn5wfyjxi467.jpg-wh_500x0-wm_3-wmp_4-s_216741007.jpg "alt=" Wkiol1mjgpzyh_araabn5wfyjxi467.jpg-wh_50 "/>

Related view important fields

V$sqlarea

V$sqlareav$sql and V$sqlarea are basically similar, and v$sqlarea is more commonly used, so only the V$sqlarea characters commonly used section of the description, as follows (personal reference to Oracle Official document translation, due to the latest version, so there will be some discrepancies on the network):

Sql_text: The first 1000 characters of a SQL statement;

Sql_fulltext: All characters of the SQL statement;

sql_id: The unique identification ID of the SQL parent cursor that is cached in the cache (library cache) (note, similar to Hash_value, but Hash_value is 4bytes and sql_id is 8bytes,sql_ ID more accurate later may replace Hash_value);

Sharable_mem: The amount of shared memory used by the SQL statement and its child cursors;

Persistent_mem: The amount of fixed memory (including child cursors) occupied by opening the lifetime of the SQL statement;

Runtime_mem: The amount of fixed memory occupied by the cursor during execution;

Sorts: The number of orders caused by the execution of the statement;

Version_count: The total number of child cursors in the cache with the statement as the parent statement;

loaded_versions: The number of child cursors loaded in the cache for this statement context heap (KGL heap 6);

open_versions: Number of child cursors opened under the parent cursor;

users_opening: Number of users to open child cursors;

fetches: Fetch number of SQL statement;

executions: The number of total executions of the SQL statement including all child cursors;

users_executing: The total number of users who have executed all child cursors of the statement;

LOADS: The total number of times the statement was loaded;

first_load_time: The time the parent cursor was first loaded (compiled);

parse_calls: The number of resolution calls for all child cursors under the parent cursor;

disk_reads: The number of read disks caused by the statement through all child cursors;

direct_writes: The number of direct writes caused by the statement through all child cursors;

buffer_gets: The number of read caches caused by the statement through all child cursors;

application_wait_time: Application wait time;

user_io_wait_time: User I/O wait time;

plsql_exec_time: Plsql execution time;

rows_processed: The total number of rows processed by the SQL statement;

optimizer_cost: This query optimizes the number of costs that are given;

parsing_user_id: The user ID of the parent statement is parsed for the first time;

parsing_schema_id: The ID of the SCHEMA of the statement is parsed for the first time;

parsing_schema_name: The NAME of the SCHEMA that parses the statement;

kept_versions: Indicates whether the current child cursor is marked as resident memory using the Dbms_shared_pool packet;

address: The current cursor parent handle (a unique address number that points to the cursor);

Hash_value: The statement hash value in the library cache;

plan_hash_value: The HASH value of the execution plan, which can be used to determine whether the two execution plans are the same (instead of the way each character is compared per line);

cpu_time: The CPU time consumed by the statement parsing, executing, and fetching (taking value);

elapsed_time: The time elapsed for parsing, executing, and fetching fetch (value) of the statement;

last_active_time: The time of the last execution of the query plan;

locked_total: The number of times that all child cursors are locked;

V$sqltext

address: The current cursor parent handle (a unique address number that points to the cursor);

Hash_value: The cursor (child cursor) is the only hash value in the library cache;

sql_id: A unique identifier value for the SQL in the cache cursor;

command_type: SQL statement type, such as SELECT, INSERT, UPDATE, etc.;

PIECE: The number of fragments of the sorted SQL text;

sql_text: Contains a small chunk of SQL text characters in a full SQL (to have the fragments combined for full SQL statements);

V$session

SADDR: Session address;

SID: Session identity value, often with serial# union uniquely determine a session (in the kill process, sometimes the SID will be reused, resulting in manslaughter.) While serial will increase but not repeat, SID is a unique key in the current session of the same instance, while Sid, Serial# is a unique key in all sessions throughout instance lifetime);

serial#: The session sequence number, which is used to uniquely identify a session when one session ends and another session reuses the SID for that session;

Audsid: Audit session ID, you can query the current session by Audsid Sid,select SID from V$session where Audsid=userenv (' SessionID ');

paddr: Process Address, association v$process addr field, through this can query to process corresponding session;

user#: The user# of user_id,oracle internal process with dba_users is 0;

USERNAME: The session owner user name, which is equal to the USERNAME of the username,oracle internal process in dba_users;

COMMAND: The type of SQL statement being executed, such as 1 for CREATE TABLE, 3 for Select, and so on;

ownerid: If the column value is 2147483644, the value is invalid, otherwise the value is used for session migration, parallelism, etc.;

taddr: Address of Transaction state object;

lockwait: Identifies whether the current query is in a lock-wait state, or null to indicate no wait;

Status: Identify session state, active is executing SQL statement, inactive wait operation, killed is labeled as kill;

Server: Types of servers, dedicated private, shared sharing, etc.;

schema#: Schema ID value, the schema# of Oracle internal processes is 0;

SCHEMANAME: Schema user name, Oracle internal process for SYS;

Osuser: Client operating system user name;

Process: Client operating system processes ID;

Machine: Operating system name;

TERMINAL: Operating system terminal name;

Program: Operating system Application name, such as EXE or Sqlplus.exe;

Type: Session types, such as background or user;

sql_address: Working with Sql_hash_value to identify the SQL statement being executed;

Sql_hash_value: Working with Sql_address to identify the SQL statement being executed;

sql_id: The identity ID of the SQL statement being executed;

sql_child_number: Child ID of the SQL statement being executed;

fixed_table_sequence: A value added when the session completes a user call, that is, if the session hangs, it does not increase. You can therefore monitor session performance from a point in time based on this field. For example, the value of this field for a session one hours ago is 10000, and now is 20000, which indicates that the user call is more frequent within one hours and can focus on the performance statistics of the session.

row_wait_obj#: The object_id of the table on which the row is locked, and the OBJECT_ID Association in Dba_object, can get the table name that is locked;

row_wait_file#: the datafile ID of the locked row, and the file# Association in V$datafile can get datafile name;

row_wait_block#: block ID that is locked;

row_wait_row#: The current row being locked;

logon_time: Login time;

This article is from the "59090939" blog, please be sure to keep this source http://59090939.blog.51cto.com/6338052/1954557

Oracle SQL resource consumption related views