-
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
-
-
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.
-
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.
-
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.