DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Materilized View % Temproary Tablespace

  1. #1
    Join Date
    Aug 2001
    Posts
    87

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Aug 2001
    Posts
    87
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Can you post the execution plans for each select statement?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width