oracle v$sqlarea 分析SQL语句使用资源情况 确认是否绑定变量

时间：2022-02-25 16:05:56

-如何确定系统中是否存在绑定变量的情况：
首先创建一个表，用于存放整理过得数据：
create table t1 as select sql_text from v$sqlarea;
----V$SQLAREA本视图持续跟踪所有shared pool中的共享cursor，
--在shared pool中的每一条SQL语句都对应一列。本视图在分析SQL语句资源使用方面非常重要。
/**
*1.查看消耗资源最多的SQL：
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.查看某条SQL语句的资源消耗：
 SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls  
FROM V$SQLAREA  
WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');  
查找前10条性能差的sql语句 
 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表示同一条SQL语句一共执行了多少次，SORTS表示排序的次数，DISK_READS表示物理读的数量。
分析性能差的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  ;
查询共享池中已经解析过的SQL语句及其相关信息 
--EXECUTIONS 所有子游标的执行这条语句次数 
--DISK_READS 所有子游标运行这条语句导致的读磁盘次数 
--BUFFER_GETS 所有子游标运行这条语句导致的读内存次数 
--Hit_radio 命中率 
--Reads_per_run 每次执行读写磁盘数

笼统的说EXECUTIONS，BUFFER_GETS，Hit_radio越高表示读内存多，磁盘少是比较理想的状态，因此越高越好 
另外两个越高读磁盘次数越多，因此低点好 
选出最占用资源的查询 :
 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

给表增加一个字段：
alter table t1 add sql_text_wo_constants varchar2(1000);
创建函数 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;
 /
 ----下面是如何使用这个函数
 将v$sql视图中的数据用remove_constants处理后，更新到t1表中：
 update t1 set sql_text_wo_constants = remove_constants(sql_text);
 --查出除了谓语条件不同的SQL语句和它们的执行次数
 select sql_text_wo_constants,count(*) from t1 
 group by sql_text_wo_constants having count(*)>100 order by 2;
 ---使用一个循环执行1000次某条SQL，每次执行时只有谓语不同：
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

　　本视图持续跟踪所有shared pool中的共享cursor，在sharedpool中的每一条SQL语句都对应一列。本视图在分析SQL语句资源使用方面非常重要。

V$SQLAREA中的信息列

HASH_VALUE：SQL语句的Hash值。 
ADDRESS：SQL语句在SGA中的地址。 
这两列被用于鉴别SQL语句，有时，两条不同的语句可能hash值相同。这时候，必须连同ADDRESS一同使用来确认SQL语句。 
PARSING_USER_ID：为语句解析第一条CURSOR的用户 
VERSION_COUNT：语句cursor的数量 
KEPT_VERSIONS： 
SHARABLE_MEMORY：cursor使用的共享内存总数 
PERSISTENT_MEMORY：cursor使用的常驻内存总数 
RUNTIME_MEMORY：cursor使用的运行时内存总数。 
SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。 
MODULE,ACTION：使用了DBMS_APPLICATION_INFO时session解析第一条cursor时的信息

V$SQLAREA中的其它常用列

SORTS: 语句的排序数 
CPU_TIME: 语句被解析和执行的CPU时间 
ELAPSED_TIME: 语句被解析和执行的共用时间 
PARSE_CALLS: 语句的解析调用(软、硬)次数 
EXECUTIONS: 语句的执行次数 
INVALIDATIONS: 语句的cursor失效次数 
LOADS: 语句载入(载出)数量 
ROWS_PROCESSED: 语句返回的列总数

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

示例： 
1.查看消耗资源最多的SQL：

Sql代码  
  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 + 100 * disk_reads DESC;

2.查看某条SQL语句的资源消耗：

Sql代码  
  1. SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
  2. FROM V$SQLAREA
  3. WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');

查找前10条性能差的sql语句

Sql代码  
  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 ;

说明: 
EXECUTIONS表示同一条SQL语句一共执行了多少次，SORTS表示排序的次数，DISK_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 done for all the childcursors

EXECUTIONS NUMBER 
Total number of executions, totalled over all the childcursors 
分析性能差的sql

Sql代码  
  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

查询共享池中已经解析过的SQL语句及其相关信息 
--EXECUTIONS 所有子游标的执行这条语句次数 
--DISK_READS 所有子游标运行这条语句导致的读磁盘次数 
--BUFFER_GETS 所有子游标运行这条语句导致的读内存次数 
--Hit_radio 命中率 
--Reads_per_run 每次执行读写磁盘数

笼统的说EXECUTIONS，BUFFER_GETS，Hit_radio越高表示读内存多，磁盘少是比较理想的状态，因此越高越好 
另外两个越高读磁盘次数越多，因此低点好

选出最占用资源的查询

Sql代码  
  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

版权声明：本文为博主原创文章，未经博主允许不得转载。

相关文章