比较愚钝，从事调优工作这么些年，处理过的性能问题也不少，也就总结出这么个公式来
S指sql所需访问的资源总量，V指sql单位时间所能访问的资源量，T自然就是SQL执行所需时间了
这个公式很简单，调优的目标是减少T，T＝S/V，那么我们要调的对象自然就要放到这里的S和V上，调优的方法就是围绕减少S和增大V
增大V（单位时间所能访问的资源量），在硬件设备不变的情况下所能做的只有充分利用现有资源，如：通过调整SGA充分利用物理MEMORY，通过并行处理充分利用CPU，通过调整IO分布充分利用硬盘处理能力，还有。。。。升级设备？
减少S（sql所需访问的资源总量），这通常是调优工作的重中之重，SQL调优的主要目的就是围绕着如何减少S在进行，在ORACLE中，所需访问的资源以block记，一条SQL执行所读写的block数直接影响到SQL的执行时间，如何知道SQL执行所读写的block数呢，简单的方法如下：
set autotrace traceonly
SQL> select count(*) from test;
已用时间: 00: 00: 10.01
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19342 (1)| 00:03:53 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 5285K| 19342 (1)| 00:03:53 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
204 recursive calls
0 db block gets
70655 consistent gets
70616 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
通过上面的信息我们可以看到此SQL访问的block数为70655，并且基本上是物理读，其执行时间为00: 00: 10.01
大家可能一眼就看出来了，这条SQL执行了全表扫描，加索引优化就可以了，没错，索引访问正是减少SQL所需访问资源的一个主要途径
其效果也很明显
已用时间: 00: 00: 01.89
执行计划
----------------------------------------------------------
Plan hash value: 826211483
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
---------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11218 consistent gets
11197 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
其访问的block数变为11218，其执行时间随之变为00: 00: 01.89
通过减少S，我们可以看到T得到明显的减小
上面情况是在V不变的情况下（都是物理读）的差别
再看看V最大化的结果
SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 826211483
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
---------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
11218 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
已用时间: 00: 00: 00.79
SQL> select /*+parallel(test 2)*/ count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 826211483
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3089 (2)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I_TEST1 | 5285K| 3089 (2)| 00:00:38 |
---------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11218 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
已用时间: 00: 00: 00.68
T被控制到了秒以下。
可是对于这样的一个业务11218个block的资源需求量是否也不是很合理呢，如果这个业务发生的很频繁，每次执行都需要重复扫描到相同资源并且这部分资源占总资源的绝大部分，这肯定也是很不合理的。
既然是减少S，那么减少这些重复访问的绝大部分资源理应得到更好的效果。
以上面的业务为例：
本业务的需求是实时统计表内的行数，数据都是具有生命周期的，通常情况下一个业务表内的数据大多是处于不活动状态，如此以来，预先统计好这部分数据并形成结果，每次需要统计这部分数据时直接调用结果必将大幅减少业务所需访问的资源
如本例，已知object_id小于等于13000的数据处于不活动状态
构建分析结果表
create table test_analyzed as select count(*) o_count，13000 as o_data from test where object_id<=13000;
SQL> select o_count from test_analyzed;
O_COUNT
----------
5242624
已用时间: 00: 00: 00.00
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from test where object_id>13000;
COUNT(*)
----------
42624
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3544821501
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 159 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| I_TEST1 | 73774 | 360K| 159 (1)| 00:00:02 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">13000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
S变成了3＋98＝101，T自然也就变成不到10毫秒了
当然现实工作中，业务需求不可能就可以如此简单的加以分解，这里只是提供一个思路
性能调优是产品全生命周期的工作，一条sql的调优很可能会追溯到产品需求的定义及产品设计等环节
而在调优工作中V的提升通常是不可持续的，一定程度的提升后再想有所提高是需要付出财力的，S的提升却是很有潜力可挖的。
昨天,客户来了需求要对2007年-2008年的数据进行统计,先
1.将本番数据库相关数据导入本地环境
2.先按需求写出SQL文如下
/* Formatted on 2008/01/22 12:30 (Formatter Plus v4.8.5) */
SELECT SUM (d.mileage) / MAX (ROWNUM)
FROM (SELECT c.vinno, c.orderno, c.mileage, c.srvdate,
ROW_NUMBER () OVER (PARTITION BY c.vinno ORDER BY c.vinno) rn
FROM (SELECT DISTINCT a.vinno, a.orderno, a.mileage, a.srvdate
FROM a,
b,
c
WHERE a.dealercode = b.dealercode
AND a.orderno = b.orderno
AND a.datafrom = b.datafrom
AND a.vinno = c.vinno
AND TO_CHAR (srvdate, 'yyyy-mm-dd')
BETWEEN '2007-01-01'
AND '2007-12-31'
AND NVL (a.deleteflag, '0') != '1'
AND NVL (b.deleteflag, '0') != '1'
AND b.partscode = 'aaa'
AND a.dealercode = 'bbb'
AND c.model = 'ccc'
ORDER BY srvdate) c) d
WHERE d.rn = '2';
3.根据执行计划对model 字段建索引
4.对每张表进行表分析
5.因为要多次执行此SQL文以获得不同的统计数据(上万次),且每张表的数据量也很大,本人用JAVA写了个程序按客户要求循环执行以上SQL文生成数据报表,执行速度不能忍受,
6.建立物化视图
DBMS_MVIEW.EXPLAIN_REWRITE()
CREATE MATERIALIZED VIEW SPK_PARTS_mv
ENABLE QUERY REWRITE
AS
select distinct a.vinno, a.orderno, a.MILEAGE, a.srvdate
from A, B, C
where a.dealercode = b.dealercode
and a.orderno = b.orderno
and a.DATAFROM = b.DATAFROM
and a.vinno = c.vinno
and to_char(srvdate, 'yyyy-mm-dd') between '2007-01-01' and '2007-12-31'
and nvl(a.deleteflag, '0') != '1'
and nvl(b.deleteflag, '0') != '1'
7.建立后发现并不能应用到SQL文中,速度仍然很慢,删除此视图,用如下重建
DBMS_MVIEW.EXPLAIN_REWRITE()
CREATE MATERIALIZED VIEW SPK_PARTS_mv
ENABLE QUERY REWRITE
AS
select distinct a.vinno, a.orderno, a.MILEAGE, a.srvdate,a.dealercode, b.partscode, c.model
from A, B, C
where a.dealercode = b.dealercode
and a.orderno = b.orderno
and a.DATAFROM = b.DATAFROM
and a.vinno = c.vinno
and to_char(srvdate, 'yyyy-mm-dd') between '2007-01-01' and '2007-12-31'
and nvl(a.deleteflag, '0') != '1'
and nvl(b.deleteflag, '0') != '1'
8.改写SQL如下
select sum(d.mileage)/max(rownum)
from (select c.vinno, c.orderno, c.MILEAGE, c.srvdate,
row_number() over(Partition By c.vinno order by c.vinno) rn
from
(select * from (select distinct a.vinno, a.orderno, a.MILEAGE, a.srvdate,a.dealercode, b.partscode, c.model
from A, B, C
where a.dealercode = b.dealercode
and a.orderno = b.orderno
and a.DATAFROM = b.DATAFROM
and a.vinno = c.vinno
and to_char(srvdate, 'yyyy-mm-dd') between '2007-01-01' and '2007-12-31'
and nvl(a.deleteflag, '0') != '1'
and nvl(b.deleteflag, '0') != '1') h
where h.dealercode = 'aaa'
and h.MODEL = 'bbb'
and h.partscode = 'ccc'
order by h.srvdate) c) d
where d.rn = '1'
9.此时再执行SQL可以看到数据已从建立的视图中获得,速度可以接受,
10.在执行程序的时候遇到新的问题,用如下SQL文根踪查看,发现开始很快,可是到后面每一个SQL文都执行的很慢
select /*+ordered*/
sql_text
from v$sqltext a
where (a.hash_value, a.address) in (
select decode (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
decode (sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session b
where b.sid = '12')
order by piece asc;
查看v$session_wait,查看等待,db file sequential read DB,
因为这是本机的随便建的一个数据库,参数都设的比较小,
11.执行
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
查看发现比较正常
接下来执行
SELECT name,value
FROM V$SYSSTAT
WHERE name IN ('db block gets','consistent gets','physical reads');
用如下公式:
命中率=1-physical reads/(dbblock gets+consistent gets)
计算发现很低,分析为db_cache_size过小导致
将其增大,重启数据库,再执行正常
来源：http://space.itpub.net/27378/viewspace-157789
扫一扫
855
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
