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

Thread: Temporary Tablespace

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323

    Temporary Tablespace

    Hi Guru's

    I am using 9i(9.2.0.4). My sort_area_size is 4M. I have a TEMPORARY TABLESPACE LOCALLY MANAGED WITH UNIFORM 1M size. Do I need to re-create this tablespace with 4M( i.e according to sort_area_size) uniform size. Please suggest.


    Thanks In Advance
    Nagesh

  2. #2
    Join Date
    Oct 2000
    Posts
    467
    Not required.
    Vinit

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You certainly don't need to do so. With a locally managed TS the work involved for Oracle to allocate 4 extents instead of 1 is really very small. I don't think the users would notice any benefit, and you'd probably have trouble scientifically measuring it.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    And if you are using workarea_size_policy = AUTO and have a value set for pga_aggregate_target then you don't have to bother about the sort_area_size any more as it will be ignored.

    All you need to do is set the proper value of pga_aggregate_target and Oracle will take care of the sorting issue.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Thank You Very much for your suggestions. The reason behind this question is that, I ended up with
    ORA-1652: unable to extend temp segment by 128 in tablespace TEMP, so, I extracted the culprit SQL by joining v$sqlarea and v$sort_usage. The statement and explain is as follows:

    SELECT MIN(a.acct_seq_id) AS ACCT_SEQ_ID,
    COUNT(DISTINCT a.ACCT_ID) NUM_DIST_ACCT,
    a.acct_grp_id,
    b.FOCAL_ENTITY_RISK,
    b.FOCAL_GEO_RISK,'DR' CR_DR_FLG
    FROM BBI.ACCT a,(SELECT a.acct_grp_id,
    MAX(a.RISK_LEVEL_NB) AS FOCAL_ENTITY_RISK,
    MAX(CASE WHEN c.GEO_TRUST_LEVEL_NB > c.GEO_RISK_LEVEL_NB THEN 0
    ELSE c.GEO_RISK_LEVEL_NB END) AS FOCAL_GEO_RISK
    FROM BBI.ACCT a,BBI.CASH_TRXN c
    WHERE c.data_dump_dt > (SELECT cntl_dt
    FROM BBI.kdd_date_control
    WHERE dt_type_cd = 'CD') - 7
    AND c.DBT_CDT_CD = 'DR'
    AND NOT(a.TRUST_LEVEL_NB > a.RISK_LEVEL_NB AND a.TRUST_LEVEL_NB > 10)
    AND a.acct_id = c.acct_id
    AND a.CUST_ACCT_FL = 'Y'
    GROUP BY a.acct_grp_id) b
    WHERE a.acct_grp_id = b.acct_grp_id
    AND a.CUST_ACCT_FL = 'Y'
    GROUP BY a.acct_grp_id, b.FOCAL_ENTITY_RISK,
    b.FOCAL_GEO_RISK


    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | SELECT STATEMENT | | 23K| 1M| 2494 | | |
    | SORT GROUP BY | | 23K| 1M| 2494 | | |
    | VIEW | | 23K| 1M| 2156 | | |
    | SORT GROUP BY | | 23K| 2M| 2156 | | |
    | HASH JOIN | | 23K| 2M| 1436 | | |
    | HASH JOIN | | 6K| 378K| 811 | | |
    | PARTITION RANGE ITER| | | | | KEY | 13 |
    | TABLE ACCESS FULL |CASH_TRXN | 6K| 176K| 289 | KEY | 13 |
    | TABLE ACCESS BY IN|KDD_DATE_ | 1 | 11 | 1 | | |

    Plan Table
    --------------------------------------------------------------------------------
    | INDEX UNIQUE SCAN|PK_K_DATE | 16 | | | | |
    | TABLE ACCESS FULL |ACCT | 141K| 4M| 437 | | |
    | TABLE ACCESS FULL |ACCT | 141K| 5M| 437 | | |
    --------------------------------------------------------------------------------

    Any help appreciated.

    Thanks In Advance.
    Nagesh

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