Oracle uses v $ sqlarea and v $ SQL to query the most resource-consuming tutorials, oraclesqlarea

Oracle uses v $ sqlarea and v $ SQL to query the most resource-consuming tutorials, oraclesqlarea

How to query the most resource-consuming data in Oracle using v $ sqlarea and v $ SQL

v$sqlarea,v$sql

Query the most resource-consuming query from V $ SQLAREA

select b.username username,a.disk_reads reads,    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,    a.sql_text Statementfrom  v$sqlarea a,dba_users bwhere a.parsing_user_id=b.user_id and a.disk_reads > 100000order by a.disk_reads desc;

Replace the disk_reads column with the buffer_gets column to obtain information about the SQL statements that occupy the most memory.

V $ SQL is the parsed SQL statement in the memory shared SQL area.

List the five most frequently used queries:

select sql_text,executionsfrom (select sql_text,executions,   rank() over    (order by executions desc) exec_rank   from v$sql)where exec_rank <=5;

Top 5 SQL statements that consume the most disk reads:

select disk_reads,sql_textfrom (select sql_text,disk_reads,   dense_rank() over     (order by disk_reads desc) disk_reads_rank   from v$sql)where disk_reads_rank <=5;

Find the query that requires a large number of buffer read (logical read) operations:

select buffer_gets,sql_textfrom (select sql_text,buffer_gets,   dense_rank() over     (order by buffer_gets desc) buffer_gets_rank   from v$sql)where buffer_gets_rank<=5;

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

HASH_VALUE: the Hash value of the SQL statement. 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. PARSING_USER_ID: VERSION_COUNT of the first cursor user parsed by the statement: Number of cursor statements KEPT_VERSIONS: SHARABLE_MEMORY: Total shared memory used by the cursor PERSISTENT_MEMORY: the total number of resident memory used by the cursor RUNTIME_MEMORY: the total number of runtime memory used by cursor. SQL _TEXT: the text of an SQL statement (up to 1000 characters can be saved ). MODULE, ACTION: the information of session parsing the first cursor when DBMS_APPLICATION_INFO is used

Other common columns in V $ SQLAREA

SORTS: number of statements sorted CPU_TIME: CPU time when the statement is parsed and executed ELAPSED_TIME: the time when the statement is parsed and executed. parse_cils: the number of statements parsed and called (soft and hard) times EXECUTIONS: number of statement executions INVALIDATIONS: Number of cursor failures of the statement LOADS: number of statements loaded (loaded) 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_ADDRESSHASH_VALUE, ADDRESS V$SQLTEXT, V$SQL, V$OPEN_CURSOR HASH_VALUE, ADDRESSSQL_TEXT V$DB_OBJECT_CACHE NAME

Example:

1. view the SQL statements that consume the most resources:

    SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls       FROM V$SQLAREA       WHERE buffer_gets > 10000000 OR disk_reads > 1000000       ORDER BY buffer_gets + 100 * disk_reads DESC;  

2. view the resource consumption of an SQL statement:

    SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls       FROM V$SQLAREA       WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');  

Find 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       order BY disk_reads DESC )where ROWNUM<10 ;  

Note:

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.

DISK_READS NUMBER The sum of the number of disk reads over all child cursors

Sorts number Sum of the number of sorts that were done for all the child cursors

Executions number Total number of executions, totalled over all the child cursors

Analyze SQL statements with poor performance

    SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,        ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,        SQL_TEXT        FROM V$SQLAREA        WHERE EXECUTIONS>0        AND BUFFER_GETS >0        AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8  

Query the parsed SQL statements and related information in the shared pool.

-- EXECUTIONS: number of times this statement is executed for all sub-game targets -- number of disk reads caused by running this statement on all sub-cursors of DISK_READS -- number of times that read memory is generated when all sub-cursors of BUFFER_GETS run this statement -- Hit_radio number of read/write disks per operation

In general, the higher the EXECUTIONS, BUFFER_GETS, and Hit_radio, the higher the READ memory and the smaller the disk, the better.

The higher the two instances, the more disk reads, so the lower the point is better.

Select the most resource-consuming Query

    select b.username username,a.disk_reads reads,a.executions exec,           a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,           a.sql_text statement           from v$sqlarea a,dba_users b           where a.parsing_user_id=b.user_id           and a.disk_reads>100000  

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.

Common columns in V $ SQLTEXT

HASH_VALUE: Hash value of an SQL statement ADDRESS: ADDRESS of an SQL statement in SGA SQL _TEXT: SQL text. PIECE: SQL statement block number

Connection column in V $ SQLTEXT

Column                     View         Joined Column(s)HASH_VALUE, ADDRESS   V$SQL, V$SESSION   HASH_VALUE, ADDRESSHASH_VALUE. ADDRESS   V$SESSION    SQL_HASH_VALUE, SQL_ADDRESS

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

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