Analyze resource usage of SQL statements

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: the user who parses the first cursor FOR THE STATEMENT
Version_count: Number of cursor statements
Kept_versions:
Sharable_memory: Total number of shared memory used by cursor
Persistent_memory: Total number of resident memory used by cursor
Runtime_memory: 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 sort statements
Cpu_time: CPU time when the statement is parsed and executed
Elapsed_time: the time when the statement is parsed and executed.
Parse_cils: Number of resolution calls (soft and hard) of the statement
Executions: number of statements executed
Invalidations: Number of cursor failures of a statement
Loads: number of statements loaded (loaded)
Rows_processed: Total number of columns returned by the statement

Column view joined column (s) in V $ sqlarea)
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:
1. view the SQL statements that consume the most resources:
Select hash_value, executions, buffer_gets, disk_reads, parse_cils
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_cils
From v $ sqlarea
Where hash_value = 228801498 and address = hextoraw ('cbd8e4b0 ');

 Find the first 10 SQL statements with poor performanceSelect * 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 sorting times, 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 performanceSelect 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 in the Shared Pool and their related information
-- 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
-- Buffer_gets the number of READ memory times caused by running this statement on all sub-cursors
-- Hit_radio hit rate
-- Reads_per_run: Number of read/write disks executed each time

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 QuerySelect B. Username username, A. disk_reads reads,a.exe cutions exec,
A. disk_reads/decode(a.executions,, a.exe cutions) 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
Order by A. disk_reads DESC;