ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【笔记】 使用物化视图(一)

【笔记】 使用物化视图(一)

原创 Linux操作系统 作者:yellowlee 时间:2009-08-31 18:19:55 0 删除 编辑

有一个年代久远的报表实在太慢,加之最近报表库性能下的很厉害,这个报表基本就是ora-01555了。
逻辑上修改感觉代价更大,由于这个报表是定期的月报表,试一下使用物化视图的效果。
在hp dl585上虚拟了一个红帽,系统都ok了,接下来就是试验了:

--sys用户下:
create tablespace test datafile '/tpsys/oracle/product/10.2.0/db_1/oradata/test.dbf' size 300m;

create user test
identified by test
  default tablespace test
  temporary tablespace temp
profile DEFAULT;

grant connect,dba,resource to test;


--进入test用户,建立相关的dblink,同义词等
-- Create database link
.........................................................

select 'create synonym ' || a.object_name || ' for ' || a.object_name ||
       '@xxxx;'
  from all_objects@xxxx a
 where a.object_type = 'SYNONYM'
   and a.object_name like 'T!_%' escape '!';


select 'drop synonym '||a.object_name||';'
 from all_objects a where a.object_type = 'SYNONYM' and a.owner = 'SYS' and a.object_name like 'T%';

--中间遇到了一点问题,忘记切换到test用户下了,
--并且由于另外一个进程正在查询其中的一个表,锁住了
--查询或者锁的相关信息:
--sid
select * from v$lock a where a.ADDR = '000000007E504198';
--serialno
select SERIAL# col1 from v$session
  where sid=&1;
--kill session
alter system kill session '&1, &serialno';
--kill session后出现 session marked killed
--并未及时释放资源
--于是找到对应linux系统的进程号,对于windows来说是线程号,然后kill掉,以释放资源
select spid, osuser, s.program from v$session s,v$process p where
s.paddr=p.addr and s.sid=152;
--windows 和linux分别是,本次操作是在linux下进行的,使用的kill -9
--orakill 152 11846
--kill -9 11846   linux

select * from v$session  a where a.STATUS = 'ACTIVE' and a.USERNAME = 'TEST';
select * from v$sqlarea;
select a.sid,a.USERNAME,a.SQL_ID,b.SQL_TEXT from  v$session  a,v$sqlarea b where a.SQL_ID = b.SQL_ID
and a.STATUS = 'ACTIVE' and a.USERNAME = 'TEST';


--扩展表空间
--数据量有点大,先扩展一下。
alter database test datafile '/tpsys/oracle/product/10.2.0/db_1/oradata/test.dbf' resize 3072m;

select * from v$datafile;


下面就要吧sql中的with temp as  () 拿出来,以后可以固定在月底25日刷新
代码大致如下:
create materialized view mv_report_customer
build immediate
refresh force
on demand
as
..

.....

...

 

--done in 1696s


虽然花了20多分钟,不过还是很值得的。

--物化视图建立好以后,本地会有一个相应的table,根据业务逻辑建立必要的几个索引,并分析
analyze table table_name compute statistics for all indexes;

select 'analyze table '||a.object_name||' compute statistics ;' from all_objects a where a.object_type = 'SYNONYM' and a.owner = 'TEST';

然后是
在此物化视图基础上的查询操作,

具体代码省略。

 

这个中间报了几个错:
1,temp表空间不够
2,文件无法扩展
3,系统io报错

1更换默认的临时表空间后解决,后面两个是因为系统文件大小限制:
ORA-09817 和文件系统不能写。
估计是空间不够,也懒得去管,删了前面的一个3g的表空间,不用的,就ok了。

运行结果:
--20090831 27907 rows selected in 938.125 seconds

10多分钟ok!

也就是说刷新和拉数据总共的时间不到40分钟,这在以前的情况下,还是比较困难的,涉及到的几张主要的表数据量都在千万以上,
而且有很多统计操作。反正不用一半夜了ora-01555了。性能方面具体还有待再搞搞清楚。


--10g的临时表空间组是新东西,本次也需要扩展temp表空间,
--使用的方法是,新建一个新的temp空间,然后设置为当前临时表空间
--临时表空间组也是需要再仔细研究下的。
select * from v$tablespace;
SELECT * FROM DBA_TABLESPACE_GROUPS;
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
       FROM DBA_USERS;
--临时表空间大小
select file#,name,bytes/1024/1024 from v$tempfile;
--当前使用大小
select (sum (blocks))*8/1000 "MB" from v$sort_usage;
select * from (
select sess.SID,  segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10;

Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
       round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2)  "Free MB" ,
       d.file_name "Datafile name",
       round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
       round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB",
       round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2)  "Free KB",
       round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",
       0 "Fragmentation Index"
from   SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where  f.tablespace_name(+) = d.tablespace_name
and    f.file_id(+) = d.file_id
and    p.file_id(+) = d.file_id
;


create temporary tablespace temp1 tempfile '/tpsys/oracle/product/10.2.0/db_1/oradata/test/temp1.dbf' size 2048m;

alter database default temporary tablespace temp1;
--drop掉之前的temp表空间
drop tablespace temp;

select * from v$tempstat;
select * from v$sort_usage;
select * from v$tempseg_usage;

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-613648/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle Library Cache
请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    656024