Shulou(Shulou.com)06/01 Report--

Oracle high resource consumption SQL statement location

Http://www.ecdoer.com/post/oracle-highcost-sql-locate.html

The most commonly used system views for Oracle SQL statement resource consumption monitoring are v$sql, v$sqlarea, v$sqltext, and v$session. In this article, we first understand the functions and differences of these views, then understand how to locate high resource-consuming SQL statements, and finally understand the specific meaning of each view field.

Functions and differences of related system views

V$sql and v$sqlarea are basically the same, recording SQL statistics in the shared SQL zone (share pool), such as memory consumption, IO (physical disk read and logical memory read), sort operations, hash ID, and so on. The difference is that v$sql keeps an entry for each SQL, while in v$sqlarea, according to sql_text (note that the first 1000 characters of the current SQL pointer are stored here, that is, the SQL recorded here may be incomplete! Group by the statistics column to sum (), and calculate the number of sub-pointers through version_count.

However, SQL statements with the same sql_text may have completely different meanings in the database. For example, there are two users in the database, User1 and User2, each with a data table EMP. Then when two users issue the query select count (*) from emp;, each accesses the table EMP in their own SCHEMA, and the contents of the two tables are different, so their resource consumption must be different. At this point, the two identical SQL statistics will be available in v$sql, while in v$sqlarea, the two pointers with the same sql_text will be merged, and the statistics such as execution times, DISK_READS, BUFFER_GETS and other statistics will be sum, and version_count will be displayed as 2, which is the aggregation effect of v$sqlarea.

There is no statistics in v$sqltext, but it stores complete SQL statements and their hash ID, and so on.

For these three, we can use the 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, as do the other two.

Through the above three statements, it can be found that the V$SQL data source X$KGLCURSOR_CHILD, in fact, the data is still derived from X$KGLCURSOR_CHILD_SQLID, while the V$SQLAREA data source X$KGLCURSOR_CHILD_SQLID is essentially the result of grouping and summarizing X$KGLCURSOR_CHILD according to fields such as sql_id, and V$SQLTEXT data source X$KGLNA.

V$session is mainly used to determine session-related information, such as using SID and SERIAL# to uniquely determine a session (SID may be duplicated), session owner user name USERNAME, session state (active: executing SQL statements, inactive: waiting for operation, killed: killed), which client initiated the session (MACHINE, TERMINAL), what SQL is being executed (determined by SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER) With these, you can know with the help of v$sqltext), even what the last executed SQL is (determined by PREV_SQL_ADDRESS, etc.), lock waiting for relevant information (such as location table, file, block, locked row), and so on.

High resource consumption SQL search location

1) check the SQL that reads too much hard disk or may consume too much memory:

Select sql_text, disk_reads, buffer_gets, parsing_schema_name, executions

From v$sqlarea

Order by disk_reads desc

Note: it is impossible to find out the amount of memory consumed by each SQL from V$sqlarea alone, but we can indirectly reflect the SQL statements that may consume a large amount of memory with the help of disk reads, and then check it with the help of execution plans (such as v$sql_plan view).

Use the system view v$sqlarea, where disk_reads is the number of disk reads and the main field, and the remaining fields are reference fields. Where buffer_gets is the number of in-memory reads, parsing_schema_name is the first compiler schema name (usually the same as the user name), and executions is the number of statements executed.

It should be noted that the sql_text in v$sqlarea may be incomplete, and if complete is needed, you need to use hash_value or sql_id combined with v$sqltext to view the analysis.

2) View the SQL with multiple execution times

Select sql_text, executions, parsing_schema_name

From v$sqlarea

Order by executions desc

3) check the sorted SQL

Select sql_text, sorts, parsing_schema_name

From v$sqlarea

Order by sorts desc

The place should also involve Library Cache hit rate, memory hit rate and so on. For the time being, please see the reproduced content "Summary of various hit rates and utilization rates related to Oracle tuning".

Important fields of dependent view

V$sqlarea

V$sql and v$sqlarea are basically similar, but v$sqlarea is more commonly used, so only the commonly used fields of v$sqlarea are described as follows (translated by personal reference to the official Oracle documentation, because it is the latest version, it will be somewhat different from that on the network):

The first 1000 characters of the SQL_TEXT:SQL statement; all characters of the SQL_FULLTEXT:SQL statement; SQL_ID: the unique identification ID of the SQL parent cursor cached in the cache (library cache) (note, similar to hash_value, but hash_value is 4bytes and sql_id is 8bytesdsqlSecretid may replace hash_value later); the amount of shared memory occupied by the SHARABLE_MEM:SQL statement and its child cursors PERSISTENT_MEM: the fixed amount of memory occupied during the life cycle of the open SQL statement (including child cursors); RUNTIME_MEM: the fixed amount of memory occupied during cursor execution; SORTS: the number of sorting caused by statement execution; VERSION_COUNT: the total number of child cursors parented by this statement in the cache; LOADED_VERSIONS: the number of child cursors of the statement context heap (KGL heap 6) loaded in the cache OPEN_VERSIONS: the number of child cursors opened under the parent cursor; USERS_OPENING: the number of users who opened the child cursor; the number of fetch of the FETCHES:SQL statement; EXECUTIONS: the total number of times the SQL statement was executed, including all child cursors; USERS_EXECUTING: the total number of users who executed all child cursors of the statement; LOADS: the total number of times the statement was loaded; FIRST_LOAD_TIME: the time when the parent cursor was first loaded (compiled) PARSE_CALLS: the number of calls to resolve all child cursors under the parent cursor; DISK_READS: the number of disk reads 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 Ihand O wait time PLSQL_EXEC_TIME:PLSQL execution time; ROWS_PROCESSED: the total number of rows processed by the SQL statement; OPTIMIZER_COST: the cost given by this query optimization; PARSING_USER_ID: the user who parsed the parent statement for the first time ID;PARSING_SCHEMA_ID: the ID;PARSING_SCHEMA_NAME that parses the statement SCHEMA for the first time: the NAME;KEPT_VERSIONS that parses the SCHEMA of the statement: indicates whether the current child cursor is marked as resident memory using the DBMS_SHARED_POOL package ADDRESS: the current cursor parent handle (a unique address number that points to the cursor); HASH_VALUE: the hash value of the statement in library cache; PLAN_HASH_VALUE: the hash value of the execution plan, based on which you can determine whether the two execution plans are the same (instead of comparing each line per character); CPU_TIME: the CPU time spent parsing, executing, and fetch (taking values) of the statement ELAPSED_TIME: the elapsed time of parsing, execution, and fetch (value) of the statement; LAST_ACTIVE_TIME: the time when the query plan was last executed; LOCKED_TOTAL: the number of times all child cursors were locked;'

V$sqltext

ADDRESS: the current cursor parent handle (a unique address number that points to the cursor); HASH_VALUE: the cursor (child cursor) has a unique hash value in the library cache; SQL_ID: a unique identification value of the SQL in the cache cursor; COMMAND_TYPE:SQL statement types, such as select, insert, update, etc.; PIECE: sort the number of fragments of SQL text SQL_TEXT: contains a small piece of SQL text characters in a complete SQL (these fragments need to be combined for a complete SQL statement)

V$session

SADDR:session address; SID:session identification value, which is often associated with serial# to uniquely determine a session (sometimes SID will be reused when killing a process, resulting in manslaughter. While serial is added but not repeated, sid is a unique key in the current session of the same instance, while sid, serial# is unique key in all session throughout the life of instance); SERIAL#: session sequence number, which is used to uniquely identify a session when one session ends and another session reuses the SID of that session; AUDSID: audit session ID, you can query the sid,select sid from v$session where audsid=userenv ('sessionid') of the current session through audsid PADDR: process address, associated with the addr field of v$process. Through this, you can query the corresponding session;USER#: of the process, which is the same as the user_id,Oracle in dba_users. The user# of the internal process is 0; the user name of the session owner, which is equal to the username of the internal process of username,Oracle in dba_users, is empty; COMMAND: the type of SQL statement being executed, such as 1 is create table, 3 is select, etc. OWNERID: if the column value is 2147483644, the value is invalid, otherwise it is used for session migration, parallelism, etc.; TADDR:Address of transaction state object;LOCKWAIT: identifies whether the current query is in lock waiting state, empty means no wait; STATUS: identifies session status, Active is executing SQL statement, inactive wait operation, killed is marked as kill; SERVER: server type, DEDICATED dedicated, SHARED sharing, etc.; SCHEMA#:SCHEMA ID value, schema# of Oracle internal process is 0 SCHEMANAME:SCHEMA user name for Oracle internal processes: sys;OSUSER: client operating system user name; PROCESS: client operating system process ID;MACHINE: operating system machine name; TERMINAL: operating system terminal name; PROGRAM: operating system application name, such as EXE or sqlplus.exe;TYPE: session type, such as BACKGROUND or USER;SQL_ADDRESS: use with SQL_HASH_VALUE to identify the SQL statement being executed SQL_HASH_VALUE: used with SQL_ADDRESS to identify the executing SQL statement; SQL_ID: the identity ID;SQL_CHILD_NUMBER of the executing SQL statement: the child ID;FIXED_TABLE_SEQUENCE of the executing SQL statement: a numeric value that is incremented when the session completes a user call, that is, if the session hangs, it does not increase. So you can use this field to monitor session performance since a certain point in time. For example, an session that had a value of 10000 in this field an hour ago and now 20000 indicates that its user call is more frequent within an hour, so you can focus on the performance statistics of this session. The object_id of the table where the locked row of ROW_WAIT_OBJ#: is located. Associate it with the object_id in dba_object to get the datafile id of the locked table name;ROW_WAIT_FILE#: row, and associate it with the file# of v$datafile to get the block of datafile name;ROW_WAIT_BLOCK#: locked and the current row of ID;ROW_WAIT_ROW#: locked. LOGON_TIME: login time

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.