|
原帖由 jhw4048 于 2008-1-22 12:46 发表
昨天,客户来了需求要对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文生成数据报表,执行速度不能忍受,
5.建立物化视图
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'
6.建立后发现并不能应用到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'
7.改写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'
8.此时再执行SQL可以看到数据已从建立的视图中获得,速度可以接受,
9.在执行程序的时候遇到新的问题,用如下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,
因为这是本机的随便建的一个数据库,参数都设的比较小,
10.执行
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过小导致
将其增大,重启数据库,再执行正常
总结:以上为本人的亲身经历,发上来与大家共享,并请大侠们多多指正,谢谢
以另外一位puber的案例为例
通过MV,一定程度的减少了S
通过调整db_cache_size,一定程度的增大V
但是这个案例,S所减少的程度显然还不够,执行上万次,其中多数都是无用功。。。。 |
|