Home » RDBMS Server » Performance Tuning » Mysterious Insufficient TEMP Tablespace...
 Mysterious Insufficient TEMP Tablespace... [message #391712] |
Fri, 13 March 2009 02:51  |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
I have run the same script against the same amount of data in the PRODUCTION server without any hassles but not in DEVELOPMENT server.
I kept on getting this error messages:
ORA-01652: unable to extend temp segment by 128 in tablespace NIS_TEMP
The table structures of the accessed tables by the script are 100% the same. All the indexes and so on.
DEVELOPMENT (IP removed by MC)
TABLESPACE_NAME FILE_NAME Size (MB) AUT Max Size (MB) INCREMENT_BY
------------------------- ------------------------------------------------------------ ---------- --- ------------- ------------
NIS_TEMP /dmdwh02/NIS/oradata/NIS_TEMP-01.dbf 8000 YES 8000 12800
NIS_TEMP /dmdwh02/NIS/oradata/NIS_TEMP-02.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh02/NIS/oradata/NIS_TEMP-03.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-04.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-05.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-06.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-07.dbf 10000 YES 10000 12800
---------- -------------
68000 68000
PRODUCTION (IP removed by MC)
TABLESPACE_NAME FILE_NAME Size (MB) AUT Max Size (MB) INCREMENT_BY
------------------------- ------------------------------------------------------------ ---------- --- ------------- ------------
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-01.dbf 8000 YES 8000 12800
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-02.dbf 8000 YES 8000 12800
NIS_TEMP /tmw/dw/oradata37/NIS/oradata/NIS_TEMP-03.dbf 3000 YES 3000 12800
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-04.dbf 5000 YES 5000 12800
NIS_TEMP /tmw/oradata12/oradata/NIS_TEMP-05.dbf 10000 YES 10000 12800
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-06.dbf 10000 YES 10000 12800
NIS_TEMP /tmw/dw/oradata21/oradata/NIS_TEMP-07.dbf 2040 YES 10000 1280
---------- -------------
46040 54000
Where could it be wrong then? 
Thank you.
[Updated on: Fri, 13 March 2009 03:14] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #392683 is a reply to message #391835] |
Wed, 18 March 2009 21:22  |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| rleishman wrote on Sat, 14 March 2009 05:26 | Post the Explain Plan of both queries. If they are different, they may use different amounts of TEMP.
Ross Leishman
|
Thanks.
I am so sorry.
I am not very familiar with Explain Plan.
How do I retrieve that?
By the way, what do u mean BOTH QUERIES?
It is the same script exactly since both are using the same sql script file. Both here is referring to the DEVELOPMENT and PRODUCTION environment.
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #392687 is a reply to message #391840] |
Wed, 18 March 2009 22:18 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| coleing wrote on Sat, 14 March 2009 05:46 | Yes, I would suspect you have a bad explain plan on the development server.
Soem tables with lots of data, and missing statistics = potential for exponential queries. (merge join cartesian is a prime example of missing stats).
|
I've ran compute statistics for the development server.
Anyway. this is the script.
/* Thursday, 23 October 2008 08:44:55 AM */
------------------------------------------------------------------------------------------------------------
-- create index PORL_CIRT_NAME_IDX on PORT_LINKS (PORL_CIRT_NAME) tablespace NIS_CABLE_CORES_IDX;
-- create index PORL_ID_IDX on PORT_LINKS (PORL_ID) tablespace NIS_CABLE_CORES_IDX;
-- create index POLP_PORL_ID_IDX on PORT_LINK_PORTS(POLP_PORL_ID) tablespace NIS_CABLE_CORES_IDX;
-- create index POLP_PORT_ID_IDX on PORT_LINK_PORTS(POLP_PORT_ID) tablespace NIS_CABLE_CORES_IDX;
-- create index POLP_FRAA_ID_IDX on PORT_LINK_PORTS(POLP_FRAA_ID) tablespace NIS_CABLE_CORES_IDX;
-- Telephony circuit cross connect
truncate table T_NIS_TEL_PROFILE_TEL;
commit;
insert into T_NIS_TEL_PROFILE_TEL (
select substr(CIRT_DISPLAYNAME,1,11) service_num,
CIRT_INSERVICE, CIRT_CUSR_ABBREVIATION, CIRT_STATUS, CIRT_DISPLAYNAME,
max(PORT_NAME) PORT_NAME, CARD_SLOT, EQUP_EQUT_ABBREVIATION, EQUP_INDEX, EQUP_LOCN_TTNAME,
FRAA_POSITION, FRAU_NAME, FRAU_POSITION,
FRAC_FRAN_NAME, FRAC_LOCN_TTNAME, FRAC_INDEX, FRAC_ID, AREA_AREA_CODE
from circuits
left join PORT_LINKS on PORL_CIRT_NAME = CIRT_NAME
left join PORT_LINK_PORTS on POLP_PORL_ID = PORL_ID
left join PORTS on PORT_ID = POLP_PORT_ID
left join cards on PORT_CARD_SLOT = card_slot AND CARD_EQUP_ID = PORT_EQUP_ID
left join equipment on CARD_EQUP_ID = EQUP_ID and PORT_EQUP_ID = EQUP_ID
left join FRAME_APPEARANCES on POLP_FRAA_ID = FRAA_ID
left join FRAME_UNITS on FRAA_FRAU_ID = FRAU_ID
left join FRAME_CONTAINERS on FRAU_FRAC_ID = FRAC_ID
left join locations on EQUP_LOCN_TTNAME = LOCN_TTNAME
left join areas on AREA_CODE = LOCN_AREA_CODE
where CIRT_SERT_ABBREVIATION in ('PSTN', 'ISDN_PRI', 'ISDN_BRI', 'PABX', 'PAYPHONE') and CIRT_SERV_ID is not null
group by substr(CIRT_DISPLAYNAME,1,11),
CIRT_INSERVICE, CIRT_CUSR_ABBREVIATION, CIRT_STATUS, CIRT_DISPLAYNAME,
CARD_SLOT, EQUP_EQUT_ABBREVIATION, EQUP_INDEX, EQUP_LOCN_TTNAME, FRAA_POSITION, FRAU_NAME, FRAU_POSITION,
FRAC_FRAN_NAME, FRAC_LOCN_TTNAME, FRAC_INDEX, FRAC_ID, AREA_AREA_CODE);
commit;
-- ADSL bearer circuit cross connect
truncate table T_NIS_TEL_PROFILE_ADSL_BEARER;
commit;
insert into T_NIS_TEL_PROFILE_ADSL_BEARER (
select substr(CIRT_DISPLAYNAME,15) service_num,
CIRT_INSERVICE, CIRT_CUSR_ABBREVIATION, CIRT_STATUS, CIRT_DISPLAYNAME,
max(PORT_NAME) PORT_NAME, CARD_SLOT, EQUP_EQUT_ABBREVIATION, EQUP_INDEX, EQUP_LOCN_TTNAME,
FRAA_POSITION, FRAU_NAME, FRAU_POSITION,
FRAC_FRAN_NAME, FRAC_LOCN_TTNAME, FRAC_INDEX, FRAC_ID, AREA_AREA_CODE
from circuits
left join PORT_LINKS on PORL_CIRT_NAME = CIRT_NAME
left join PORT_LINK_PORTS on POLP_PORL_ID = PORL_ID
left join PORTS on PORT_ID = POLP_PORT_ID
left join cards on PORT_CARD_SLOT = card_slot AND CARD_EQUP_ID = PORT_EQUP_ID
left join equipment on CARD_EQUP_ID = EQUP_ID and PORT_EQUP_ID = EQUP_ID
left join FRAME_APPEARANCES on POLP_FRAA_ID = FRAA_ID
left join FRAME_UNITS on FRAA_FRAU_ID = FRAU_ID
left join FRAME_CONTAINERS on FRAU_FRAC_ID = FRAC_ID
left join locations on EQUP_LOCN_TTNAME = LOCN_TTNAME
left join areas on AREA_CODE = LOCN_AREA_CODE
where CIRT_DISPLAYNAME like '%ADSL_BEARER%'
group by substr(CIRT_DISPLAYNAME,15),
CIRT_INSERVICE, CIRT_CUSR_ABBREVIATION, CIRT_STATUS, CIRT_DISPLAYNAME,
CARD_SLOT, EQUP_EQUT_ABBREVIATION, EQUP_INDEX, EQUP_LOCN_TTNAME, FRAA_POSITION, FRAU_NAME, FRAU_POSITION,
FRAC_FRAN_NAME, FRAC_LOCN_TTNAME, FRAC_INDEX, FRAC_ID, AREA_AREA_CODE);
commit;
create index TELPRO1_servnum_idx on T_NIS_TEL_PROFILE_TEL(service_num);
create index TELPRO2_servnum_idx on T_NIS_TEL_PROFILE_ADSL_BEARER(service_num);
create index TELPRO1_fracid_idx on T_NIS_TEL_PROFILE_TEL(frac_id);
create index TELPRO2_fracid_idx on T_NIS_TEL_PROFILE_ADSL_BEARER(frac_id);
create index SADD_SERV_ID_IDX on SERVICES_ADDRESS(SADD_SERV_ID);
Table records:
areas - 3261
cards - 732222
circuits - 1710834
equipment - 411128
FRAME_APPEARANCES - 30116602
FRAME_CONTAINERS - 281643
FRAME_UNITS - 333286
locations - 278801
PORT_LINK_PORTS - 165970918
PORT_LINKS - 99822576
PORTS - 44269817
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #392688 is a reply to message #392594] |
Wed, 18 March 2009 22:30 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| gkrishn wrote on Wed, 18 March 2009 23:20 | Secondly, check any other sessions using TEMPORARY tablespace.
select username,user,TABLESPACE,CONTENTS,SEGTYPE,blocks
2 from v$sort_usage;
|
Thanks.
FYI, the tablespace NIS_TEMP in the development server is 100% dedicated to that particular user.
In addition, there is no other running processes during my execution.
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #402144 is a reply to message #391712] |
Thu, 07 May 2009 19:26 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
I've modified the script to force index..
select
/*+ index(circuits,CIRT_PRIME_KEY) index(circuits,CIRT_SERT_INDEX) index(circuits,CIRT_SERV_INDEX) index(port_links,PORL_CIRT_NAME_IDX) index(port_links,PORL_ID_IDX) index(port_link_ports,POLP_PORL_ID_IDX) index(port_link_ports,POLP_FRAA_ID_IDX) index(ports,PORT_PRIME_KEY) index(ports,PORT_CARDSLOT_IDX) index(ports,T_NIS_PORT_EQUP_ID_IDX) index(frame_appearances,FRAA_FRAU_INDEX) index(frame_appearances,FRAA_PRIME_KEY) index(equipment,EQUP_LOCN_INDEX) index(equipment,EQUP_PRIME_KEY) index(frame_containers,FRAC_PRIME_KEY) index(frame_units,FRAU_PRIME_KEY) index(frame_units,T_NIS_FRAU_FRAC_ID_IDX) index(cards,CARD_SLOT_IDX index(cards,MIG_CARD_EQUPID) index(locations,LOCN_LOCN_TTNAME_UK_UK) index(locations,LOCN_AREA_FK_I) index(areas,AREA_AREA_FK_I) */
substr(CIRT_DISPLAYNAME,1,11) service_num,
CIRT_INSERVICE, CIRT_CUSR_ABBREVIATION, CIRT_STATUS, CIRT_DISPLAYNAME,
max(PORT_NAME) PORT_NAME, CARD_SLOT, EQUP_EQUT_ABBREVIATION, EQUP_INDEX, EQUP_LOCN_TTNAME,
FRAA_POSITION, FRAU_NAME, FRAU_POSITION,
FRAC_FRAN_NAME, FRAC_LOCN_TTNAME, FRAC_INDEX, FRAC_ID, AREA_AREA_CODE
from circuits
left join PORT_LINKS on PORL_CIRT_NAME = CIRT_NAME
left join PORT_LINK_PORTS on POLP_PORL_ID = PORL_ID
left join PORTS on PORT_ID = POLP_PORT_ID
left join cards on PORT_CARD_SLOT = card_slot AND CARD_EQUP_ID = PORT_EQUP_ID
left join equipment on CARD_EQUP_ID = EQUP_ID and PORT_EQUP_ID = EQUP_ID
left join FRAME_APPEARANCES on POLP_FRAA_ID = FRAA_ID
left join FRAME_UNITS on FRAA_FRAU_ID = FRAU_ID
left join FRAME_CONTAINERS on FRAU_FRAC_ID = FRAC_ID
left join locations on EQUP_LOCN_TTNAME = LOCN_TTNAME
left join areas on AREA_CODE = LOCN_AREA_CODE
where CIRT_SERT_ABBREVIATION in ('PSTN', 'ISDN_PRI', 'ISDN_BRI', 'PABX', 'PAYPHONE') and CIRT_SERV_ID is not null
group by substr(CIRT_DISPLAYNAME,1,11),
CIRT_INSERVICE, CIRT_CUSR_ABBREVIATION, CIRT_STATUS, CIRT_DISPLAYNAME,
CARD_SLOT, EQUP_EQUT_ABBREVIATION, EQUP_INDEX, EQUP_LOCN_TTNAME, FRAA_POSITION, FRAU_NAME, FRAU_POSITION,
FRAC_FRAN_NAME, FRAC_LOCN_TTNAME, FRAC_INDEX, FRAC_ID, AREA_AREA_CODE
But still it failed to be processed with the insufficient TEMP tablespace error..
Here is the query plan generated by the Embarcadero DBArtisan..
Can please anyone explain and advice me regarding this.
Any help is very much appreciated. Thank you.. 
|
|
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #402146 is a reply to message #391712] |
Thu, 07 May 2009 19:46 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT Substr(cirt_displayname,1,11) service_num,
cirt_inservice,
cirt_cusr_abbreviation,
cirt_status,
cirt_displayname,
Max(port_name) port_name,
card_slot,
equp_equt_abbreviation,
equp_index,
equp_locn_ttname,
fraa_position,
frau_name,
frau_position,
frac_fran_name,
frac_locn_ttname,
frac_index,
frac_id,
area_area_code
FROM circuits
LEFT JOIN port_links
ON porl_cirt_name = cirt_name
LEFT JOIN port_link_ports
ON polp_porl_id = porl_id
LEFT JOIN ports
ON port_id = polp_port_id
LEFT JOIN cards
ON port_card_slot = card_slot
AND card_equp_id = port_equp_id
LEFT JOIN equipment
ON card_equp_id = equp_id
AND port_equp_id = equp_id
LEFT JOIN frame_appearances
ON polp_fraa_id = fraa_id
LEFT JOIN frame_units
ON fraa_frau_id = frau_id
LEFT JOIN frame_containers
ON frau_frac_id = frac_id
LEFT JOIN locations
ON equp_locn_ttname = locn_ttname
LEFT JOIN areas
ON area_code = locn_area_code
WHERE cirt_sert_abbreviation IN ('PSTN','ISDN_PRI','ISDN_BRI','PABX',
'PAYPHONE')
AND cirt_serv_id IS NOT NULL
GROUP BY Substr(cirt_displayname,1,11),
cirt_inservice,
cirt_cusr_abbreviation,
cirt_status,
cirt_displayname,
card_slot,
equp_equt_abbreviation,
equp_index,
equp_locn_ttname,
fraa_position,
frau_name,
frau_position,
frac_fran_name,
frac_locn_ttname,
frac_index,
frac_id,
area_area_code
For each element in the SELECT clause identify the source table
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #402154 is a reply to message #402146] |
Thu, 07 May 2009 21:22 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
Thanks..
So here it is..
SELECT Substr(cirt_displayname,1,11) service_num, -- CIRCUITS
cirt_inservice, -- CIRCUITS
cirt_cusr_abbreviation, -- CIRCUITS
cirt_status, -- CIRCUITS
cirt_displayname, -- CIRCUITS
Max(port_name) port_name, -- PORTS
card_slot, -- CARDS
equp_equt_abbreviation, -- EQUIPMENT
equp_index, -- EQUIPMENT
equp_locn_ttname, -- EQUIPMENT
fraa_position, -- FRAME_APPEARANCES
frau_name, -- FRAME_UNIT
frau_position, -- FRAME_UNIT
frac_fran_name, -- FRAME_CONTAINERS
frac_locn_ttname, -- FRAME_CONTAINERS
frac_index, -- FRAME_CONTAINERS
frac_id, -- FRAME_CONTAINERS
area_area_code -- AREAS
FROM circuits
LEFT JOIN port_links
ON porl_cirt_name = cirt_name
LEFT JOIN port_link_ports
ON polp_porl_id = porl_id
LEFT JOIN ports
ON port_id = polp_port_id
LEFT JOIN cards
ON port_card_slot = card_slot
AND card_equp_id = port_equp_id
LEFT JOIN equipment
ON card_equp_id = equp_id
AND port_equp_id = equp_id
LEFT JOIN frame_appearances
ON polp_fraa_id = fraa_id
LEFT JOIN frame_units
ON fraa_frau_id = frau_id
LEFT JOIN frame_containers
ON frau_frac_id = frac_id
LEFT JOIN locations
ON equp_locn_ttname = locn_ttname
LEFT JOIN areas
ON area_code = locn_area_code
WHERE cirt_sert_abbreviation IN ('PSTN','ISDN_PRI','ISDN_BRI','PABX','PAYPHONE') -- CIRCUITS
AND cirt_serv_id IS NOT NULL -- CIRCUITS
GROUP BY Substr(cirt_displayname,1,11),
cirt_inservice,
cirt_cusr_abbreviation,
cirt_status,
cirt_displayname,
card_slot,
equp_equt_abbreviation,
equp_index,
equp_locn_ttname,
fraa_position,
frau_name,
frau_position,
frac_fran_name,
frac_locn_ttname,
frac_index,
frac_id,
area_area_code |
|
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #402225 is a reply to message #402157] |
Fri, 08 May 2009 02:25 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| BlackSwan wrote on Fri, 08 May 2009 11:39 | I do "old school" SQL.
I have not mastered ANSI joins.
For old school SQL a couple of rules of thumb exist.
1) the number of phrases (P_ID = C_ID) in WHERE clause had to equal to (number of tables in FROM clause)-1 or greater.
In other words if 5 tables were in FROM clause at last 4 phrases must be in WHERE clause to avoid Cartesian Product.
2) The only tables in the FROM clause should be those tables which contribute elements to the SELECT clause.
In your case as a single example port_link_ports should NOT be included in FROM clause.
When such a table is required for desired filtering, it should be subordinated into the WHERE clause.
My conclusion, which could be 100% incorrect, is that I suspect this SQL results in a Cartesian Product which is why/what is consuming the TEMP space.
I suggest you eliminate out of the FROM clause those tables which do not provide data to the SELECT clause & move them into the WHERE clause as appropriate.
Ideally this will eliminate any Cartesian Product.
Good Luck.
|
Ok thanks BlackSwan.
Now, I have modified the script as per your advice..
SELECT Substr(cirt_displayname,1,11) service_num,
cirt_inservice,
cirt_cusr_abbreviation,
cirt_status,
cirt_displayname,
Max(port_name) port_name,
card_slot,
equp_equt_abbreviation,
equp_index,
equp_locn_ttname,
fraa_position,
frau_name,
frau_position,
frac_fran_name,
frac_locn_ttname,
frac_index,
frac_id,
area_area_code
FROM circuits
left join ports on port_cirt_name = cirt_name
-- LEFT JOIN port_links
-- ON porl_cirt_name = cirt_name
-- LEFT JOIN port_link_ports
-- ON polp_porl_id = porl_id
-- LEFT JOIN ports
-- ON port_id = polp_port_id
LEFT JOIN frame_appearances
ON FRAA_CIRT_NAME = cirt_name
LEFT JOIN cards
ON port_card_slot = card_slot
AND card_equp_id = port_equp_id
LEFT JOIN equipment
ON card_equp_id = equp_id
AND port_equp_id = equp_id
-- LEFT JOIN frame_appearances
-- ON polp_fraa_id = fraa_id
LEFT JOIN frame_units
ON FRAA_FRAU_ID = frau_id
LEFT JOIN frame_containers
ON frau_frac_id = frac_id
LEFT JOIN locations
ON equp_locn_ttname = locn_ttname
LEFT JOIN areas
ON area_code = locn_area_code
WHERE cirt_sert_abbreviation IN ('PSTN','ISDN_PRI','ISDN_BRI','PABX','PAYPHONE')
AND cirt_serv_id IS NOT NULL
and cirt_name in (select porl_cirt_name from port_links)
and port_id in (select polp_port_id from port_link_ports)
and equp_locn_ttname in (select locn_ttname from locations)
and fraa_id in (select polp_fraa_id from port_link_ports)
GROUP BY Substr(cirt_displayname,1,11),
cirt_inservice,
cirt_cusr_abbreviation,
cirt_status,
cirt_displayname,
card_slot,
equp_equt_abbreviation,
equp_index,
equp_locn_ttname,
fraa_position,
frau_name,
frau_position,
frac_fran_name,
frac_locn_ttname,
frac_index,
frac_id,
area_area_code
So, now the query plan become something like this..
|
|
|
|
|
|
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #403812 is a reply to message #403079] |
Mon, 18 May 2009 21:35 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| JRowbottom wrote on Thu, 14 May 2009 00:18 | Just a thought - you do definitely have the same sized tempfile on dev as you do on live, don't you?
|
Yes I do!
In fact, there's a little extra on the development..
DEVELOPMENT
TABLESPACE_NAME FILE_NAME Size (MB) AUT Max Size (MB) INCREMENT_BY
------------------------- ------------------------------------------------------------ ---------- --- ------------- ------------
NIS_TEMP /dmdwh02/NIS/oradata/NIS_TEMP-01.dbf 8000 YES 8000 12800
NIS_TEMP /dmdwh02/NIS/oradata/NIS_TEMP-02.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh02/NIS/oradata/NIS_TEMP-03.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-04.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-05.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-06.dbf 10000 YES 10000 12800
NIS_TEMP /dmdwh04/NIS/oradata/NIS_TEMP-07.dbf 10000 YES 10000 12800
---------- -------------
68000 68000
PRODUCTION (IP removed by MC)
TABLESPACE_NAME FILE_NAME Size (MB) AUT Max Size (MB) INCREMENT_BY
------------------------- ------------------------------------------------------------ ---------- --- ------------- ------------
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-01.dbf 8000 YES 8000 12800
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-02.dbf 8000 YES 8000 12800
NIS_TEMP /tmw/dw/oradata37/NIS/oradata/NIS_TEMP-03.dbf 3000 YES 3000 12800
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-04.dbf 5000 YES 5000 12800
NIS_TEMP /tmw/oradata12/oradata/NIS_TEMP-05.dbf 10000 YES 10000 12800
NIS_TEMP /tmw/oradata14/NIS/oradata/NIS_TEMP-06.dbf 10000 YES 10000 12800
NIS_TEMP /tmw/dw/oradata21/oradata/NIS_TEMP-07.dbf 2040 YES 10000 1280
---------- -------------
46040 54000 |
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #403813 is a reply to message #403072] |
Mon, 18 May 2009 21:59 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| Mahesh Rajendran wrote on Thu, 14 May 2009 00:02 | Please post Oracle version information (4 digits).
>>It still failed
Did you run the just the query
or
Whole script (create index followed by select)?
Post the value for parameters optimizer_features_enable and compatible. Compare them in Dev and Prod databases.
For what it is worth, please check alert side log for any possible ora-0600 errors that might occur during the same time frame.
|
The whole script.
But all the indexes have been already created before.
So there is no issue.
The value for optimizer_features_enable:
DEVELOPMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 10.2.0.3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.3.0
PRODUCTION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 9.2.0
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0
Thank you.
|
|
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #403965 is a reply to message #403842] |
Tue, 19 May 2009 21:05 |
aimy
Messages: 225
Registered: June 2006
|
Senior Member |
|
|
| Mahesh Rajendran wrote on Tue, 19 May 2009 15:52 | That makes a ton of differences in CBO behavior.
You must try your work in the same version of Oracle.
Else you are comparing apples with oranges.
|
But did you noticed that the failed one is actually on the latest version of Oracle i.e. Oracle 10g.
Does it make sense?
Thank you.
|
|
|
|
|
|
| Re: Mysterious Insufficient TEMP Tablespace... [message #404187 is a reply to message #391712] |
Wed, 20 May 2009 13:00 |
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
|
Junior Member |
|
|
LooK what do you want first,
If you want to run the query succesfully, try running the query and keep monitoring the temp tablespace... when you see it is almost full(88-90%) , increase it manually and keep increasing till the query finish. dont go for autoextend,
Secondly, if you dont want to do that,
make the query to use index for those , who are going for a full scan..
More imp is , try to resuffle the from clause, so that the joining can opt for maximum less rows to join(remember a big table hash joins to small is different from a small table joins to big
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 14:05:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
