V$sqlarea 分析以及max(),min() 调优

V$sqlarea 分析

（一）v$sqlarea作用和参数解析

V$sqlarea

用于分析SHARED_POOL中共享sql语句占用系统资源的情况

几个重要参数

buffer_gets,disk_reads，executions三个最重要的参数

lHASH_VALUE：SQL语句的Hash值。

ADDRESS：SQL语句在SGA中的地址。这两列被用于鉴别SQL语句，有时，两条不同的语句可能hash值相同。这时候，必须连同ADDRESS一同使用来确认SQL语句。

lSQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。

lSHARABLE_MEMORY：cursor使用的共享内存总数

lSORTS表示排序的次数；

lLOADS:语句载入(载出)数量

lFirst_load_time:第一次载入时间

lBuffer_gets:数据缓存读次数

lDISK_READS表示物理读的数量；

lPARSING_USER_ID：为语句解析第一条CURSOR的用户

（二）查找前10条性能差的sql语句,以disk_reads降序排列

SELECT *FROM

(select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlareaorderBY disk_readsDESC ) a

where ROWNUM<=10;

PARSING_USER_ID EXECUTIONSSORTS COMMAND_TYPE DISK_READS SQL_TEXT

--------------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------

43 79080 3 155872348 select max(SHEET_NO) as MAX_SHEET_NO from TB_SALE

43 75597559 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_DWHERE CORPID = :b1 AND PRO_CODE = :b2AND WAREHOUSE

.

.

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

ORDERBY buffer_gets + 100 * disk_readsDESC;

(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 ADDRESSPARSING_USER_ID BUFFER_GETS DISK_READS EXECUTIONS PARSE_CALLS

---------- ---------------- --------------- ----------- ---------- ---------- -----------

3645297957 000000007E682E5043174092875 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%'

ANDS.SQL_ADDRESS = L.ADDRESS;

因为已经知道ADDRESS，所以直接查询：

select sid,serial#,user#,username

2from v$session

3wheresql_address='000000007E682E50';

SIDSERIAL#USER # USERNAME

---------- ---------- ---------- ------------------------------

913906143 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被选择。

实际上应该indexscan为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_TYPETABLE_NAME

------------------------------ --------------------------- ------------------------------

PK_TB_SALE NORMALTB_SALE

IDX_TB_SALE NORMALTB_SALE

IDX2_TB_SALE NORMALTB_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_NAMECOLUMN_POSITION

------------------------------ -------------------------------------------------------------------------------- ---------------

IDX2_TB_SALE ADD_TIME2

IDX_TB_SALE ADD_TIME5

IDX2_TB_SALE CORPID1

PK_TB_SALE CORPID1

IDX_TB_SALE CORPID1

IDX_TB_SALE CUST_CODE3

IDX_TB_SALE SALE_USER6

IDX_TB_SALE SALE_USER_27

IDX_TB_SALE SHEET_NO2

PK_TB_SALE SHEET_NO2

IDX_TB_SALE SHEET_STATUS4

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

很高兴，今天对此语句做了优化调整，非常好！

来自半路出家