【筆記】 使用物化檢視（一）

有一個年代久遠的報表實在太慢，加之最近報表庫效能下的很厲害，這個報表基本就是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/，如需轉載，請註明出處，否則將追究法律責任。