V$sqlarea 分析以及max(),min() 调优
V$sqlarea 分析
（一）v$sqlarea作用和参数解析
V$sqlarea
用于分析SHARED_POOL中共享sql语句占用系统资源的情况
几个重要参数
buffer_gets,disk_reads，executions 三个最重要的参数
l HASH_VALUE：SQL语句的Hash值。
ADDRESS：SQL语句在SGA中的地址。这两列被用于鉴别SQL语句，有时，两条不同的语句可能hash值相同。这时候，必须连同ADDRESS一同使用来确认SQL语句。
l SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。
l SHARABLE_MEMORY：cursor使用的共享内存总数
l SORTS表示排序的次数；
l LOADS: 语句载入(载出)数量
l First_load_time:第一次载入时间
l Buffer_get:数据缓存读次数
l DISK_READS表示物理读的数量；
l PARSING_USER_ID：为语句解析第一条CURSOR的用户
（二）查找前10条性能差的sql语句 ,以disk_reads 降序排列
SELECT * FROM
(select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC ) a
where ROWNUM<=10;
PARSING_USER_ID EXECUTIONS SORTS COMMAND_TYPE DISK_READS SQL_TEXT
--------------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
43 7908 0 3 155872348 select max(SHEET_NO) as MAX_SHEET_NO from TB_SALE
43 7559 7559 3 5850862 select distinct A.PRO_CODE, B.BRAND_CODE, B.PRO_NAME, B.WARRANCY from TB_STORAGE
29 48043 -17493 3 2251703 SELECT * FROM STORAGE_D WHERE CORPID = :b1 AND PRO_CODE = :b2 AND WAREHOUSE
43 183 366 3 698754 select distinct X.*, Y.ADD_USER BALE_USER, Y.ADD_TIME BALE_TIME, get_cust_name(X
43 1173 1173 3 540707 select A.CUST_CODE, A.FEEDBACK_ID, A.FEEDBACK_TITLE, A.FEEDBACK_STATUS, A.FEEDBA
43 76 0 3 365181 SELECT * FROM TB_MOVEIN_PRO WHERE CORPID=:B2 AND SHEET_NO=:B1
29 7 91 3 313894 SELECT CORPID,PRO_CODE,WAREHOUSE,PLACE,SUM(QUANTITY) QUANTITY,SUM(SUM1) SUM1 F
5 93806 0 2 269639 INSERT INTO WILIAM.TB_STORAGE_D (COMPANY_ID, CUST_CODE, DEPOT_ID, UNIT_ID, PRO_C
29 3 3 3 262832 SELECT PRO_CODE,PRO_NAME,BIGTYPE_CODE,SMALLTYPE_CODE,BRAND_CODE,CUST_CODE,UNIT_I
29 1 5 3 122530 SELECT TO_CHAR(A.ORDDATE,'yyyymm') YYMM,B.PRO_CODE,B.WAREHOUSE,B.PLACE,B.BRAND_C
10 rows selected
（3）从这里很明显第一条语句DISK_READS有问题
.查看某条SQL语句的资源消耗：
SELECT hash_value, address,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;
(4)针对此条SQL语句查询hash_value,address,parsing_user_id,以此确定是哪些用户使用此SQL 语句
SELECT hash_value,address, PARSING_USER_ID,buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = '3645297957' AND address = '000000007E682E50';
HASH_VALUE ADDRESS PARSING_USER_ID BUFFER_GETS DISK_READS EXECUTIONS PARSE_CALLS
---------- ---------------- --------------- ----------- ---------- ---------- -----------
3645297957 000000007E682E50 43 174092875 156304357 7933 7933
（五）与v$session sql_address 关联
代码：
SELECT L.SQL_TEXT, S.* FROM V$SQLAREA L, V$SESSION S
WHERE UPPER(L.SQL_TEXT) LIKE 'select%'
AND S.SQL_ADDRESS = L.ADDRESS;
因为已经知道ADDRESS，所以直接查询：
select sid,serial#,user#,username
2 from v$session
3 where sql_address='000000007E682E50';
SID SERIAL# USER # USERNAME
---------- ---------- ---------- ------------------------------
91 39061 43 WILIAM
从上面可以看出用户是WILIAM
（6）确定用户后，就可以确定表为wiliam.tb_sale
先看此表总数量，以下面确定使用table access full 和index scan 哪个合理。表rows上百万条，而查询max只返回一条数据，显然不能全表扫描。
SQL> select count(*) from wiliam.tb_sale;
COUNT(*)
----------
1649979
（7）查看explain plan
由于事前我已经知道公司数据库是使用RBO，所以做explain plan 不会有详细的统计信息。所以我先执行一下SQL，看耗时说少。
select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE
MAX_SHEET_NO
--------------------
SL0001730973
18.282 seconds
从这里可以看出单个SQL查询耗时18.282s,显然不能忍受！！！
这里附带说一下max(),min()函数
B_TREE索引结构为：
Root----------branch----------leaf结构
所以leaf是有SEQ顺序的，比如从左到右边，其值是从小到大，要么是从大到小。
Max() 或者min()肯定不需要table access full.而且肯定不会indes full scan,
因为数据库只需要取LEAF第一个值，或者最后一个值，而不会扫描中间值。
Sql>explain plan for
2 select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE;
explained
解析完后，需要显示解析结果，这里我采用自动显示结果。
SQL> Select * from table(dbms_xplan.display);
SQL> Select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | TB_SALE | | | |
--------------------------------------------------------------------
Note: rule based optimization
10 rows selected
Mistest
分析：optimization 为RBO,此表为全表扫描，有10rows被选择。
实际上应该index scan 为 INDEX FULL SCAN (MIN/MAX)，但是结果没有，所以要分析后，tuning.
（8）查看wiliam.tb_sale索引情况
此查询SQL语句很有用，table_name 后面要大写表明，或者使用 upper()不需要添加用户’wiliam’
select index_name,index_type,table_name
from dba_indexes where table_name='TB_SALE';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
PK_TB_SALE NORMAL TB_SALE
IDX_TB_SALE NORMAL TB_SALE
IDX2_TB_SALE NORMAL TB_SALE
(9)查看索引所属字段
使用视图DBA_IND_COLUMNS,用user_ind_columns有时候不一定查到
select index_name,column_name,column_position from dba_ind_columns
where table_name='TB_SALE'
order by 2;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
IDX2_TB_SALE ADD_TIME 2
IDX_TB_SALE ADD_TIME 5
IDX2_TB_SALE CORPID 1
PK_TB_SALE CORPID 1
IDX_TB_SALE CORPID 1
IDX_TB_SALE CUST_CODE 3
IDX_TB_SALE SALE_USER 6
IDX_TB_SALE SALE_USER_2 7
IDX_TB_SALE SHEET_NO 2
PK_TB_SALE SHEET_NO 2
IDX_TB_SALE SHEET_STATUS 4
11 rows selected
分析：从上面我们可以看到字段sheet_NO 索引IDX_TB_SALE以及主键PK_TB_SALE用到。
（10）分析结果
做了上面的分析后，我得出结论：表wiliam.tb_sale是全表扫描，而且表解析上百万次数，很影响数据库性能。而且字段sheet_no建有索引，但是没有采用索引扫描。
（12）分析表,获得统计信息
说明10g以后，分析表不需要加上“compute statistics”，而且还有更加好的统计表信息方法，rdbms_stat，这里不多述。
analyze table wiliam.tb_sale compute statistics
SQL> analyze table wiliam.tb_sale compute statistics;
Table analyzed
165万条左右数据，ANALYZE花了196.89seconds
同时我把相关的一个表也分析
select count(*) from wiliam.tb_sale_pro
SQL> select count(*) from wiliam.tb_sale_pro;
COUNT(*)
----------
2239356
SQL> analyze table wiliam.tb_sale_pro compute statistics;
Table analyzed
Table analyzed in 328.14seconds
（13）再次查看explain plan
sql语句：
explain plan for
select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE;
select * from table(dbms_xplan.display)
SQL> explain plan for
2 select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE;
Explained
SQL> Select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 517 |
| 1 | SORT AGGREGATE | | 1 | 12 | |
| 2 | INDEX FAST FULL SCAN| PK_TB_SALE | 1650K| 18M| 517 |
---------------------------------------------------------------------
Note: cpu costing is off
10 rows selected
分析：从plan 可以看到，走INDEX FAST FULL SCAN，走的是fast index，很好！！！扫描1650krows
（14）验证结果
为了验证结果，同时避免相同的共享SQL，我把”as” 前面增加了一个空格，SQL语句，那怕大小写，空格不一样，
数据库就认为不是相同的SQL语句。
select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE;
SQL> select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE;
MAX_SHEET_NO
--------------------
SL0001731248
1row selected in 2.92seconds
如果采用sqlplus,那么可以采用下面方式看EXPLAIN PLAN。
set autotrace traceonly explain
select max(SHEET_NO) as MAX_SHEET_NO from WILIAM.TB_SALE
set autotrace off
很高兴，今天对此语句做了优化调整，非常好！
[@more@]来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22934571/viewspace-1044269/ ，如需转载，请注明出处，否则将追究法律责任。
