-
Materilized View % Temproary Tablespace
Dear All,
I face problem with materilzed view via temproary tablespace segments
It expand my temproary tablespace to become very huge 30+Gb and
once i tried to make join with another materilzed view the query run very slowly
Can any one help in what i need to do
regards
This is example of my main materilized view
CREATE OR REPLACE VIEW brands_trans_all_v (
fldmainid,
fldstore,
fldstoreheading1,
alu,
supitemno,
description,
flddocdate,
fldvoucomm1,
qtysold,
total_sold,
qtyrcvd,
total_rcvd,
qtytrnsfrd,
intransfred,
outtransfered,
total_trnsfrd,
intrnsf_value,
outtrnsf_value )
AS
select /*+ ALL_ROWS */ fldmainid, fldStore, fldStoreHeading1, NFLDALU ALU,NFLDDESC1 SupItemNo,nflddesc2 Description,flddocdate, FLDVOUCOMM1,0 QtySold,0 TOTAL_SOLD, sum(decode(fldvoutype, 'Receive', nflditmrcvd, -nflditmrcvd)) QtyRcvd, sum(decode(fldvoutype, 'Receive', nfldExtPrc, -nfldExtPrc)) Total_Rcvd, 0 QtyTrnsfrd,0 INTransfred ,0 OutTransfered ,0 Total_Trnsfrd,0 INTRNSF_VALUE,0 OUTTRNSF_VALUE from tblfrmrvouchers where fldmark='1' and fldvoustatus in('Regular', 'Changed') and fldvoutype in ('Receive','Return') group by fldmainid, fldStore, fldStoreHeading1, NFLDDESC1,nflddesc2,flddocdate,NFLDDESC3,NFLDDCSNAME,NFLDALU ,NFLDATTR ,NFLDSIZE , nfldDCS,FLDVOUCOMM1
union all
select /*+ ALL_ROWS */ fldmainid, fldStore, fldStoreHeading1,NFLDALU , NFLDDESC1 ,nflddesc2 ,flddocdate,FLDSLIPCOMM1,0,0,0,0,sum(decode(fldSlipType, 'In Slip', nfldqty, -nfldQty)),sum(decode(fldSlipType, 'In Slip', nfldqty, 0)) ,sum(decode(fldSlipType, 'Out Slip', nfldqty, 0)) , sum(decode(fldSlipType, 'In Slip', nfldExtPrc,-nfldExtPrc)),sum(decode(fldSlipType, 'In Slip', nfldExtPrc, 0)) ,sum(decode(fldSlipType, 'Out Slip', nfldExtPrc)) from tblslips where fldmark=1 and fldslipstatus in ('Regular','Changed') group by fldmainid, fldStore, fldStoreHeading1, NFLDDESC1,nflddesc2,flddocdate,NFLDALU,FLDSLIPCOMM1
union all
select /*+ ALL_ROWS */ Fldmainid,fldStore, fldStoreHeading1,NFLDALU ALU,NFLDDESC1 SupItemNo,nflddesc2 Description, flddocdate, FLDINVCCOMMENT1,sum(decode(fldinvchdrrcpttype, 'Sale', nfldqty, -nfldQty)) , sum(decode(fldinvchdrrcpttype, 'Sale', NFLDEXTORIGPRC ,-NFLDEXTORIGPRC)) , 0 ,0 , 0 ,0 ,0,0,0,0 from tblinvoices where fldmark='1' and fldinvchdrrcptstatus in ('Regular', 'Changed') and fldinvchdrrcpttype in ('Sale','Return') group by fldmainid, fldStore, fldStoreHeading1,NFLDDESC1,nflddesc2,flddocdate,NFLDALU , nfldDCS,FLDINVCCOMMENT1
-
How large are your base tables and how large are your MVs?
Oracle version?
SGA/PGA sizes?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks PAVB for your update
tblinvoices 1230940 ROWS
tblitems 1107630 ROWS
tblslips 242240 ROWS
tblfrmrvouchers 127330 ROWS
SGA SIZE 1745955136 Bytes
Oracle Enterprise Edition 9.2.0.1.0
-
Can you post the execution plans for each select statement?
-
That's very little data, you shouldn't be experiencing such an ordeal.
Just to see if we can lure Oracle to do something different please try altering a little your session environment by adding commands below before your CREATE MATERIALIZED VIEW command...
alter session set db_file_multiblock_read_count = 64;
alter session set sort_area_retained_size = 250331648;
alter session set sort_area_size = 250331648;
alter session set sort_direct_writes = true;
if it doesn't work, you can try to pre-build your MVIEW table.
1- Create table brands_trans_all_v with all columns you need.
2- Refurbish you mview creation script to insert into table brands_trans_all_v all the data you want to have in your MView
3- Issue a create materialized view brands_trans_all_v on prebuilt table... command
At this point you should be the happy owner of mview brands_trans_all_v
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|