Home » RDBMS Server » Performance Tuning » Mysterious Insufficient TEMP Tablespace...
icon5.gif  Mysterious Insufficient TEMP Tablespace... [message #391712] Fri, 13 March 2009 02:51 Go to next message
aimy
Messages: 209
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? Sad

Thank you.

[Updated on: Fri, 13 March 2009 03:14] by Moderator

Report message to a moderator

Re: Mysterious Insufficient TEMP Tablespace... [message #391714 is a reply to message #391712] Fri, 13 March 2009 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I removed the IP addresses in your ouput.
Don't put them in a forum if you don't want to be hacked, above all for a production database.

Regards
Michel
Re: Mysterious Insufficient TEMP Tablespace... [message #391717 is a reply to message #391714] Fri, 13 March 2009 03:20 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Michel Cadot wrote on Fri, 13 March 2009 16:13
I removed the IP addresses in your ouput.
Don't put them in a forum if you don't want to be hacked, above all for a production database.

Regards
Michel


Oh I'm so sorry for that.. Sad

And thanks a lot.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif

So, do you have any idea about this michael? Surprised
Re: Mysterious Insufficient TEMP Tablespace... [message #391722 is a reply to message #391717] Fri, 13 March 2009 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, no idea, used temp space depends on query execution plans which may be different and concurrent load and instance parameters...

Regards
Michel
Re: Mysterious Insufficient TEMP Tablespace... [message #391835 is a reply to message #391722] Fri, 13 March 2009 16:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan of both queries. If they are different, they may use different amounts of TEMP.

Ross Leishman
Re: Mysterious Insufficient TEMP Tablespace... [message #391840 is a reply to message #391712] Fri, 13 March 2009 16:46 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
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).
Re: Mysterious Insufficient TEMP Tablespace... [message #392594 is a reply to message #391840] Wed, 18 March 2009 10:20 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Secondly, check any other sessions using TEMPORARY tablespace.
select username,user,TABLESPACE,CONTENTS,SEGTYPE,blocks
  2  from v$sort_usage;
Re: Mysterious Insufficient TEMP Tablespace... [message #392683 is a reply to message #391835] Wed, 18 March 2009 21:22 Go to previous messageGo to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
aimy
Messages: 209
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.. Sad

Here is the query plan generated by the Embarcadero DBArtisan..
http://img26.picoodle.com/img/img26/2/5/7/aimy/f_20090508082m_1e7a0b9.gif

Can please anyone explain and advice me regarding this.

Any help is very much appreciated. Thank you.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif
Re: Mysterious Insufficient TEMP Tablespace... [message #402145 is a reply to message #391712] Thu, 07 May 2009 19:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I've ran compute statistics for the development server.
I could not find evidence this was done based upon your posts.
Statistics need to be collected AFTER the data is loaded.
Re: Mysterious Insufficient TEMP Tablespace... [message #402146 is a reply to message #391712] Thu, 07 May 2009 19:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous messageGo to next message
aimy
Messages: 209
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 #402157 is a reply to message #391712] Thu, 07 May 2009 22:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
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.
Re: Mysterious Insufficient TEMP Tablespace... [message #402225 is a reply to message #402157] Fri, 08 May 2009 02:25 Go to previous messageGo to next message
aimy
Messages: 209
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..

http://img26.picoodle.com/img/img26/2/5/8/aimy/f_20090508152m_234cdfc.gif
Re: Mysterious Insufficient TEMP Tablespace... [message #402460 is a reply to message #402225] Sun, 10 May 2009 18:20 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
It still failed.. Shocked Confused Sad Sad Sad Sad Sad
Re: Mysterious Insufficient TEMP Tablespace... [message #403072 is a reply to message #402460] Wed, 13 May 2009 11:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
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.
Re: Mysterious Insufficient TEMP Tablespace... [message #403079 is a reply to message #402460] Wed, 13 May 2009 11:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just a thought - you do definitely have the same sized tempfile on dev as you do on live, don't you?

Re: Mysterious Insufficient TEMP Tablespace... [message #403812 is a reply to message #403079] Mon, 18 May 2009 21:35 Go to previous messageGo to next message
aimy
Messages: 209
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 Go to previous messageGo to next message
aimy
Messages: 209
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 #403842 is a reply to message #403813] Tue, 19 May 2009 02:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
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.
Re: Mysterious Insufficient TEMP Tablespace... [message #403965 is a reply to message #403842] Tue, 19 May 2009 21:05 Go to previous messageGo to next message
aimy
Messages: 209
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 #403967 is a reply to message #403965] Tue, 19 May 2009 22:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
That is what I am exactly talking about.
10gR2 introduced many many changes with CBO and these sought of issues were pretty common.
If you can afford,
try this.
set optimizer_features_enable = 9.2.0 in the 10g Database (init.ora or spfile.ora).
Essentially, you are asking 10g CBO to behave as 9.2.
Bounce the database and try your work.
Post the results.

Re: Mysterious Insufficient TEMP Tablespace... [message #404187 is a reply to message #391712] Wed, 20 May 2009 13:00 Go to previous message
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
Previous Topic: Query Slow
Next Topic: Performance in joins
Goto Forum:
  


Current Time: Fri Dec 09 06:06:00 CST 2016

Total time taken to generate the page: 0.09704 seconds