Oracle V$sqlarea Parse SQL statement uses resource conditions to confirm if a variable is bound

-How to determine if a binding variable exists in the system:
Start by creating a table that holds the data you've collated:
CREATE table T1 as select Sql_text from V$sqlarea;
----V$sqlarea This view keeps track of shared cursor in all shared pool,
--Each SQL statement in the shared pool corresponds to a column. This view is important in analyzing the use of SQL statement resources.
/**
See the SQL that consumes 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 + * 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 top 10 poor performance SQL statements
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 how many times the same SQL statement was executed, sorts represents the number of sorts, and disk_reads represents the amount of physical reads.
Analyze poor-performance sql:
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;
Querying SQL statements that have already been resolved in a shared pool and their related information
--executions execution of all child cursors This statement number of times
--disk_reads the number of read disks that are caused by all child cursors running this statement
--buffer_gets the number of read memory that is caused by all child cursors running this statement
--hit_radio hit rate
--reads_per_run number of Read and write disks per execution

Generally speaking, the higher the Executions,buffer_gets,hit_radio, the more memory is read, the less disk is the ideal state, so the higher the better
The other two higher reads the disk the more times, therefore the low point is good
Select the most resource-intensive 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>10000


Add a field to the table:
ALTER TABLE T1 add sql_text_wo_constants varchar2 (1000);
To create a function remove_constants:
Create or Replace function
Remove_constants (P_query in varchar2) return VARCHAR2 as
L_query long;
L_char varchar2 (1);
L_in_quotes Boolean default false;
Begin
For I in 1.. Length (P_query) loop
L_char: =substr (p_query,i,1);
if (L_char = "" and l_in_quotes) then
L_in_quotes= false;
else if (L_char = "and not l_in_quotes.) Then
l_in_quotes= true;
l_query:=l_query| | ' #‘;
End If;
if (not l_in_quotes) then
L_query: = l_query| | L_char;
End If;
End Loop;
L_query: = Translate (l_query, ' 0123456789 ', ');
For I in 0..8 loop
L_query: = replace (L_query,lpad (' @ ', 10-i, ' @ '), ' @ ');
L_query: = replace (L_query,lpad (', 10-i, '), ');
End Loop;
return Upper (L_query);
End
/
----Below is how to use this function
After you have processed the data in the V$sql view with remove_constants, update to the T1 table:
Update T1 Set sql_text_wo_constants = Remove_constants (Sql_text);
--Find out the SQL statements with different predicate conditions and how many times they are executed
Select Sql_text_wo_constants,count (*) from T1
GROUP BY Sql_text_wo_constants have Count (*) >100 order by 2;
---executes one SQL 1000 times with one loop, with only a different predicate per execution:
Ed
Begin
For I in 1..1000 loop
Execute immediate ' select *from t where rm= ' | | I
End Loop;
End

Select Sql_text_wo_constants,count (*) from T1
GROUP BY Sql_text_wo_constants
Having Count (*) >100
Order by 2;

V$sqlarea

This view keeps track of the shared cursor in all shared pool, and each SQL statement in Sharedpool corresponds to a column. This view is important in analyzing the use of SQL statement resources.

Information columns in the V$sqlarea

The hash value of the Hash_value:sql statement.
The address of the Address:sql statement in the SGA.
These two columns are used to identify SQL statements, and sometimes two different statements may have the same hash value. At this point, the SQL statement must be confirmed with address together.
PARSING_USER_ID: the user who resolves the first cursor for the statement
Version_count: Number of statement cursor
Kept_versions:
Total shared memory used by Sharable_memory:cursor
Total number of resident memory used by Persistent_memory:cursor
The total number of run-time memory used by Runtime_memory:cursor.
The text of the Sql_text:sql statement (maximum only the first 1000 characters of the statement can be saved).
Module,action: Information when the session parses the first cursor when using Dbms_application_info

Other common columns in the V$sqlarea

Sorts: number of sorts of statements
Cpu_time: The CPU time the statement was parsed and executed
Elapsed_time: The shared time at which statements are parsed and executed
Parse_calls: Number of parsing calls (soft, hard) for statements
Executions: Number of executions of statements
Invalidations: Cursor invalidation number of statements
LOADS: The number of statements loaded (loaded)
Rows_processed: The total number of columns returned by the statement

Connection columns 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:
1. View the SQL that consumes the most resources:
SQL code

1. SELECT hash_value, executions, buffer_gets, Disk_reads, Parse_calls
2. From V$sqlarea
3. WHERE buffer_gets > 10000000 OR disk_reads > 1000000
4. ORDER by Buffer_gets + * Disk_reads DESC;

2. View the resource consumption of an SQL statement:
SQL code

1. SELECT Hash_value, Buffer_gets, disk_reads, executions, parse_calls
2. From V$sqlarea
3. WHERE Hash_value = 228801498 and address = Hextoraw (' cbd8e4b0 ');

Find the top 10 poor performance SQL statements
SQL code

1. SELECT * FROM (select Parsing_user_id,executions,sorts,command_type,disk_reads,sql_text from V$sqlarea
2. ORDER by Disk_reads DESC) where rownum<10;

Description
Executions indicates how many times the same SQL statement was executed, sorts represents the number of sorts, and disk_reads represents the amount of physical reads.
Disk_reads number
The sum of the number of disk reads over all childcursors

Sorts number
Sum of the number of sorts that were do for all the childcursors

Executions number
Total number of executions, totalled through all the childcursors
Analyze poor-performance SQL
SQL code

1. SELECT executions, disk_reads, Buffer_gets,
2. ROUND ((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,
3. ROUND (disk_reads/executions,2) Reads_per_run,
4. Sql_text
5. From V$sqlarea
6. WHERE executions>0
7. and Buffer_gets >0
8. and (Buffer_gets-disk_reads)/buffer_gets < 0.8

Querying SQL statements that have already been resolved in a shared pool and their related information
--executions execution of all child cursors This statement number of times
--disk_reads the number of read disks that are caused by all child cursors running this statement
--buffer_gets the number of read memory that is caused by all child cursors running this statement
--hit_radio hit rate
--reads_per_run number of Read and write disks per execution

Generally speaking, the higher the Executions,buffer_gets,hit_radio, the more memory is read, the less disk is the ideal state, so the higher the better
The other two higher reads the disk the more times, therefore the low point is good

Select the most resource-intensive query
SQL code

1. Select B.username username,a.disk_reads reads,a.executions exec,
2. A.disk_reads/decode (a.executions,0,1,a.executions) Rds_exec_ratio,
3. A.sql_text statement
4. From V$sqlarea A,dba_users b
5. where a.parsing_user_id=b.user_id
6. and a.disk_reads>100000

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle V$sqlarea Parse SQL statement uses resource conditions to confirm whether a variable is bound