Home » RDBMS Server » Performance Tuning » TEMP tablespace getting filled up with multi table join
TEMP tablespace getting filled up with multi table join [message #421684] Wed, 09 September 2009 16:11 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
I am running query and its joining four tables and most of the tables having lots of records and its pulling more than 600k records but running longer and its throwing an temp tablespace error.
I am increasing temp tablespace upto 4 gb but still temp TS getting filled up.
Could you please guide me?

Query:
select a.email,  a.fname, a.lname, a.bdate, a.date_created, a.date_updated, 
e.OptIn, 
b.add1, b.add2, b.city, b.state, b.zip, b.country, b.phone, b.fax
from user a, ADD B, U_ADD c, GROUP d, U_GROUP e 
WHERE a.email = c.email AND c.add_id = b.add_id 
and d.grp_id = c.grp_id and d.grp_id = e.grp_id
and e.email = a.email 
and e.GRP_ID in (select GRP_ID from GROUP where DIV = 'HOM');


Table has following counts:
COUNT(*)
--------
     199 ==> Group
  862696 ==> User
 1231965 ==> U_ADD
 1237186 ==> Add
 5539815 ==> U_GROUP


You help really appreciated!
Thanks in advance!

Re: TEMP tablespace getting filled up with multi table join [message #421685 is a reply to message #421684] Wed, 09 September 2009 16:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Oracle version?
Are you using PGA_AGGREGATE_TARGET or SORT_AREA_SIZE?
Re: TEMP tablespace getting filled up with multi table join [message #421686 is a reply to message #421685] Wed, 09 September 2009 16:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, the first suspect would be that the join is wrong and you get a cartesian product between some of the tables.

Re: TEMP tablespace getting filled up with multi table join [message #421687 is a reply to message #421684] Wed, 09 September 2009 16:50 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Sorry as i forgot to mention, Oracle 9i R2 version.
PGA_AGGREGATE_TARGET ==> 1048576000
SORT_AREA_SIZE ==> 65536


Re: TEMP tablespace getting filled up with multi table join [message #421690 is a reply to message #421687] Wed, 09 September 2009 17:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First look into your code as ThomasG has suggested.
Quote:
PGA_AGGREGATE_TARGET ==> 1048576000
SORT_AREA_SIZE ==> 65536

If you are using PGA_AGGREGATE_TARGET then SORT_AREA_SIZE values are ignored.
So please check the docs. Use appropriate values.
Re: TEMP tablespace getting filled up with multi table join [message #421691 is a reply to message #421686] Wed, 09 September 2009 17:35 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, although its right that table has some duplicate records except date_created as we allow add multiple same user records for the same day.
I am trying to connect tables as its pulling data from multiple tables and that's why i am joining the each tables.

Thanks,



Re: TEMP tablespace getting filled up with multi table join [message #421699 is a reply to message #421684] Wed, 09 September 2009 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am running query and its joining four tables
>from user a, ADD B, U_ADD c, GROUP d, U_GROUP e
A - 1
B - 2
C - 3
D - 4
E - 5

Since when does 4 equal 5?

Table C & D should not be in FROM clause, because they contribute no data to SELECT clause.
Re: TEMP tablespace getting filled up with multi table join [message #421700 is a reply to message #421684] Wed, 09 September 2009 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It is BAD practice to use KEYWORD as table name.
Re: TEMP tablespace getting filled up with multi table join [message #421796 is a reply to message #421699] Thu, 10 September 2009 11:04 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks for your resposne.
I am using tables because i have to join the tables from onetable to another tables to retrieve data although i don't need those columns in my select tables but i have to match the relationship.
I have changed the table name but i am not using Keyword.

Please see the following table relation ship:
for user a, ADD B, U_ADD c, GROUP d, U_GROUP e
USER ==> Email ==>PK
U_Group ==> Email, Grp_id, U_Date ==> Composite PK
GROUP ==> Grp_id  ==>PK
U_ADD ==> Email, Add_type, Grp_id ==> Composite PK
Add ==> Add_id ==> PK


So User_Group and user_Add have ref to USer table.
User_group and User_Add tables ref to Group table
and User_Add table Ref to Add table.

We have increased TEMP TS upto 8 GB.
Your help will greatly appreciated!

Thanks,

Re: TEMP tablespace getting filled up with multi table join [message #421800 is a reply to message #421796] Thu, 10 September 2009 12:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
We still don't know enough to decide whether some join conditions are missing.

You should break the query down into smaller parts. Only join two tables at a time, and see with which join the query "explodes". Then that is the join that is missing a condition.

It would perhaps also become clearer if you re-write the joins the ansi way.
Re: TEMP tablespace getting filled up with multi table join [message #421814 is a reply to message #421684] Thu, 10 September 2009 14:10 Go to previous messageGo to next message
rkl1
Messages: 97
Registered: June 2005
Member
I have seen that if lots of parallel query running, temp tablespace filled up very fast.

select * from v$pq_slave;

select * from v$pq_sysstat;

select * from v$px_process;

select a.server_name, a.status, b.osuser from v$px_process a, v$session b
where a.sid=b.sid and a.serial#=b.serial# ;

if possible just experiment by disabling the parallel query option in the session basis and run your query:

alter session disable parallel query;

---run your SELECT statements.

Thanks.
Re: TEMP tablespace getting filled up with multi table join [message #421817 is a reply to message #421814] Thu, 10 September 2009 16:35 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks everyone.
I was able to geneate the text file after increasing TEMP TS size.
I really appreciate your help and guidence!

thanks once again!

Poratips
Re: TEMP tablespace getting filled up with multi table join [message #421907 is a reply to message #421796] Fri, 11 September 2009 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am using tables because i have to join the tables from onetable to another tables to retrieve data although i don't need those
>columns in my select tables but i have to match the relat
ionship.
Then they should be in WHERE clause & not FROM clause
>I have changed the table name but i am not using Keyword.

>Please see the following table relation ship:
>for user a, ADD B, U_ADD c, GROUP d, U_GROUP e

USER, ADD, & GROUP are all Oracle KEYWORDs
Re: TEMP tablespace getting filled up with multi table join [message #421968 is a reply to message #421684] Sat, 12 September 2009 10:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I question the validity of the query. The spelling errors in particular tell me maybe this is not real code. Additionally I hesitate to accept the joins as correct given the tables you use as passthrough tables. Then again I have seen more that one table design where this was needed and you should know your own stuff better than I.

If you really want speed and no sort areas used, try different index sets created specifically to optimize this query. Here is one such set.

SELECT a.email, 
       a.fname, 
       a.lname, 
       a.bdate, 
       a.date_created, 
       a.date_updated, 
       e.optin, 
       b.add1, 
       b.add2, 
       b.city, 
       b.state, 
       b.zip, 
       b.country, 
       b.phone, 
       b.fax 
FROM   USER a, 
       add b, 
       u_add c, 
       group d, 
       u_group e 
WHERE  a.email = c.email 
       AND c.add_id = b.add_id 
       AND d.grp_id = c.grp_id 
       AND d.grp_id = e.grp_id 
       AND e.email = a.email 
       AND e.grp_id IN (SELECT grp_id 
                        FROM   group 
                        WHERE  div = 'HOM'); 

group (div,grp_id)
u_group (grp_id, email, optin)
user (email,fname,lname,bdate,adate_created,adate_updated)
group (grp_id)
u_add (grp_id, add_id)
add (add_id,add1,add2,city,state,zip,country,phone,fax)


+---------------------------+
|                           |
a      b ---- c ---- d ---- e
|             |             |
+-------------+             (group)
                               |
                              (c)


I assume the "div = 'HOM' will reduce your rowset significantly? If so you should check your query plans to make sure this predicate is being used correctly. Depending upon your version of Oracle this may require setting of various tuning parameters at the database level (predicate pushing, query rewrites, and all that).

Good luck, Kevin
Re: TEMP tablespace getting filled up with multi table join [message #422024 is a reply to message #421684] Sun, 13 September 2009 14:54 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post explain plan or (even better) TKPROF.

Previous Topic: Use of DBMS_LOGSTDBY
Next Topic: Useing USE_HASH in a GROUP BY Clause (merged 7)
Goto Forum:
  


Current Time: Sat Dec 10 17:00:46 CST 2016

Total time taken to generate the page: 0.10459 seconds