DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: Temporary tablespace filled up

  1. #11
    Join Date
    Nov 2003
    Posts
    89
    User is issuing below query.

    create table wilson2a
    as select cpolicypfx, cpolicyno, criskstate, cpecode, nclaimoffc, nclaimno, nclaimsuff,
    nrtline, NOCCURDATE, NCMADEDATE, CMINORCO, cmajorline,CBORISCODE, CFILLER1, creinsco, caccountid,
    sum(nvl(npayment,0)) npayment,sum(nvl(nreserve,0)) nreserve,sum(nvl(npaidtodat,0)) npaidtodat,
    sum(nvl(nopencount,0)) nopencount, sum(nvl(ncwpcount,0)) ncwpcount,
    sum(nvl(ncnpcount,0)) ncnpcount
    from homesupp a, homemain b where cpolicyno in
    ('1951726','5969327','9086977','9130806','9131869','9170162','9170171','9170223','9195863','9196064' ,
    '9196103','9259702','9259743','9259750','9259783','9259786','9259795','9259859','9259865','9259926',
    '9259965','9260213','9260516','9260517','9260521','9260529','9260563','9260570','9260577','9260700',
    '9260719','9260762','9260770','9260771','9260810','9260816','9260841','9260853','9260857','9260886',
    'C148574','C148579','C148581','C148582','C148588','C148596','C148597','C148601','C148611','C148642')
    and a.nrecnum=b.nrecnum
    group by cpolicypfx, cpolicyno, criskstate, cpecode, nclaimoffc, nclaimno, nclaimsuff,
    nrtline, NOCCURDATE, NCMADEDATE, CMINORCO, cmajorline, CBORISCODE, CFILLER1, creinsco, caccountid;

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do you have an estimate of how large the wilson2a table is going to be?

    Also, post an explain plan for the query, and don't forget to use code tags ...

    [ code ]
    place code here
    [ /code ]

    (Take out the spaces to get the tags to work)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Also, "sum(nvl(npayment,0))" is probably better as "Nvl(sum(npayment),0)"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is oracle release?

    How big the tables are?

    Since, 16 columns in group by clause, it may need a huge temp space.

    Increase sort_area_size at the session level to match the extent size of temp tablespace. This may solve your problem.

    You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.

    Tamil

  5. #15
    Join Date
    Nov 2003
    Posts
    89
    The table is going to be with 6000 rows. HOMEMAIN and HOMESUPP tables has 60 millions rows each.

    [CODE ]

    OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
    -------------------------------------------------------
    CREATE TABLE STATEMENT ~ ~ 0 ~ 460400
    LOAD AS SELECT ~ ~ 1 0 1
    SORT GROUP BY ~ 2 1 1
    MERGE JOIN ~ ~ 3 2 1
    SORT JOIN ~ 4 3 1
    TABLE ACCESS FULL HOMESUPP 5 4 1
    SORT JOIN ~ 6 3 2
    TABLE ACCESS FULL HOMEMAIN 7 6 1

    [ END CODE]

  6. #16
    Join Date
    Nov 2003
    Posts
    89
    ORACLE 8.1.7.4
    upgradeed from 7.3 using export/ import.

  7. #17
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.

    Did you do this?

    Tamil

  8. #18
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    create a new one and drop the old.

    That cannot be solution every time and I guess thats not correct practice.

    create a new one and drop the old.

    This is the only solution and best practice.
    Temperary tablespace is not allowed to get shrinked because oracle has to do extra processing to allocate extents everytime. It is made this way so that oracle don need to deaalocate and reallocate extent everytime.
    An elephant is a mouse with an operating system.

  9. #19
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ==
    OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
    -------------------------------------------------------
    CREATE TABLE STATEMENT ~ ~ 0 ~ 460400
    LOAD AS SELECT ~ ~ 1 0 1
    SORT GROUP BY ~ 2 1 1
    MERGE JOIN ~ ~ 3 2 1
    SORT JOIN ~ 4 3 1
    TABLE ACCESS FULL HOMESUPP 5 4 1
    SORT JOIN ~ 6 3 2
    TABLE ACCESS FULL HOMEMAIN 7 6 1
    ==

    The problem is with MERGE JOIN.
    You need to create indexes on the column cpolicyno.
    Also another index on nrecnum.

    Post the explain plan here.
    The plan should not go for MERGE JOIN.

    Tamil

  10. #20
    Join Date
    Nov 2003
    Posts
    89
    I ran same explain plan again and I did not see Merge operation.
    Both tables have indexes on nrecnum.


    OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
    ------------------------ ---------- ---------- ----------
    CREATE TABLE STATEMENT ~ ~ 0 ~ 18086
    LOAD AS SELECT ~ ~ 1 0 1
    SORT GROUP BY ~ 2 1 1
    NESTED LOOPS ~ ~ 3 2 1
    TABLE ACCESS FULL HOMESUPP 4 3 1
    TABLE ACCESS BY INDEX ROWID HOMEMAIN 5 3 2
    INDEX NIQUE SCAN I_HOMEMAIN_NRECNUM 6 5 1

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