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  |
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 #421796 is a reply to message #421699] |
Thu, 10 September 2009 11:04  |
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 |
ThomasG
Messages: 3212
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 |
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 #421968 is a reply to message #421684] |
Sat, 12 September 2009 10:28 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 22:57:29 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
