(转载)oracle的v$sqlarea表
[V$SQLAREA]
本视图持续跟踪所有shared pool中的共享cursor,在shared pool中的每一条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:
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');
3.查找前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表示物理读的数量。
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
分析性能差的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>100000
关于v$sql/v$sqlarea/v$sqltext的区别参考链接:
v$sqltext 存储的是完整的SQL,SQL被分割 SQL> desc v$sqltext Name Null? Type ----------------------------------------- -------- ---------------------------- ADDRESS RAW(4) --------- HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql COMMAND_TYPE NUMBER PIECE NUMBER ---------- 分片之后的顺序编号 SQL_TEXT VARCHAR2(64) -------------- 注意长度 v$sqlarea --------- 存储的SQL 和一些相关的信息,比如累计的执行次数,逻辑读,物理读等统计信息 SQL> desc v$sqlarea Name Null? Type ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000) SHARABLE_MEM NUMBER PERSISTENT_MEM NUMBER RUNTIME_MEM NUMBER SORTS NUMBER VERSION_COUNT NUMBER LOADED_VERSIONS NUMBER OPEN_VERSIONS NUMBER USERS_OPENING NUMBER FETCHES NUMBER EXECUTIONS NUMBER USERS_EXECUTING NUMBER LOADS NUMBER FIRST_LOAD_TIME VARCHAR2(38) INVALIDATIONS NUMBER PARSE_CALLS NUMBER DISK_READS NUMBER BUFFER_GETS NUMBER ROWS_PROCESSED NUMBER COMMAND_TYPE NUMBER OPTIMIZER_MODE VARCHAR2(25) PARSING_USER_ID NUMBER PARSING_SCHEMA_ID NUMBER KEPT_VERSIONS NUMBER ADDRESS RAW(4) HASH_VALUE NUMBER MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER SERIALIZABLE_ABORTS NUMBER CPU_TIME NUMBER ELAPSED_TIME NUMBER IS_OBSOLETE VARCHAR2(1) CHILD_LATCH NUMBER v$sql ---------- 存储的是具体的SQL 和执行计划相关信息,实际上,v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息 SQL> desc v$sql Name Null? Type ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000) SHARABLE_MEM NUMBER PERSISTENT_MEM NUMBER RUNTIME_MEM NUMBER SORTS NUMBER LOADED_VERSIONS NUMBER OPEN_VERSIONS NUMBER USERS_OPENING NUMBER FETCHES NUMBER EXECUTIONS NUMBER USERS_EXECUTING NUMBER LOADS NUMBER FIRST_LOAD_TIME VARCHAR2(38) INVALIDATIONS NUMBER PARSE_CALLS NUMBER DISK_READS NUMBER BUFFER_GETS NUMBER ROWS_PROCESSED NUMBER COMMAND_TYPE NUMBER OPTIMIZER_MODE VARCHAR2(10) OPTIMIZER_COST NUMBER PARSING_USER_ID NUMBER PARSING_SCHEMA_ID NUMBER KEPT_VERSIONS NUMBER ADDRESS RAW(4) TYPE_CHK_HEAP RAW(4) HASH_VALUE NUMBER PLAN_HASH_VALUE NUMBER CHILD_NUMBER NUMBER ---------- 注意这个 MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER SERIALIZABLE_ABORTS NUMBER OUTLINE_CATEGORY VARCHAR2(64) CPU_TIME NUMBER ELAPSED_TIME NUMBER OUTLINE_SID NUMBER -------------- 注意这里跟 outline 有关 CHILD_ADDRESS RAW(4) SQLTYPE NUMBER REMOTE VARCHAR2(1) OBJECT_STATUS VARCHAR2(19) LITERAL_HASH_VALUE NUMBER LAST_LOAD_TIME VARCHAR2(38) IS_OBSOLETE VARCHAR2(1) CHILD_LATCH NUMBER 另外注意这个 QL> desc v$sql_plan Name Null? Type ----------------------------------------- -------- ---------------------------- ADDRESS RAW(4) HASH_VALUE NUMBER CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段 OPERATION VARCHAR2(60) OPTIONS VARCHAR2(60) OBJECT_NODE VARCHAR2(20) OBJECT# NUMBER OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(64) OPTIMIZER VARCHAR2(40) ID NUMBER PARENT_ID NUMBER DEPTH NUMBER POSITION NUMBER SEARCH_COLUMNS NUMBER COST NUMBER CARDINALITY NUMBER BYTES NUMBER OTHER_TAG VARCHAR2(70) PARTITION_START VARCHAR2(10) PARTITION_STOP VARCHAR2(10) PARTITION_ID NUMBER OTHER VARCHAR2(4000) DISTRIBUTION VARCHAR2(40) CPU_COST NUMBER IO_COST NUMBER TEMP_SPACE NUMBER ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) 实际上,看起来同样的一句SQL ,往往具有不同的执行计划 如果是不同的数据库用户,那么相应的涉及的 对象 可能都不一样,注意v$sql 中 OBJECT# NUMBER OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(64) OPTIMIZER VARCHAR2(40) 即使是相同的数据库用户,若 session 的优化模式、session 级的参数 等不一样,执行计划也能不同。所以即使相同的sql,也可能具有不同的执行计划! v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接 ADDRESS RAW(4) HASH_VALUE NUMBER CHILD_NUMBER NUMBER 而v$SQLAREA 忽略了 执行计划 等差异,只是在形式上sql文本看起来一样!相当于做了个聚合,是多个不同执行计划的sql的聚合和累计信息