Home » SQL & PL/SQL » SQL & PL/SQL » SQL query with parallel hint running very slow (Oracle 10g, Solaris 10)
SQL query with parallel hint running very slow [message #418896] Wed, 19 August 2009 01:58 Go to next message
Messages: 1
Registered: July 2009
Location: Chennai
Junior Member

I have a SQL query which joins three huge tables. (given below)

insert /*+ append */ into final_table (oid, rmeth, id, expdt, crddt, coupon, bitfields, processed_count)
select /*+ full(t2) parallel(t2,31) full(t3) parallel(t3,31)*/
seq_final_table.nextval, '200', t2.id, t3.end_date, '1/jul/2009',123,t2.bitfield, 0
from table1 t1, table2 t2, table3 t3 where
t1.id=t2.id and
t2.pid=t3.pid and
t2.vid=t3.vid and
t3.end_date is not null and
(trunc(t1.expiry_date) != trunc(t3.end_date) or trim(t1.expiry_date) is null);

Below are some statistics of the three tables.
Table_Name RowCount Size(MB)
table1 36469938 532
table2 242172205 39184
table3 231756758 29814

The above query ran for 30+ hours, and returned with no rows inserted into final_table. I didn't get any error message also.
But when I ran the query with table1 containing just 10000 records, the query completed succesfully within 20 minutes.

Can any one please optimize the above query?
Re: SQL query with parallel hint running very slow [message #418906 is a reply to message #418896] Wed, 19 August 2009 02:24 Go to previous message
Michel Cadot
Messages: 63907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove ALL hints and analyze how you can optimize it.
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Previous Topic: Failing to access the objects in other schema thru procedures
Next Topic: Update BLOB in procedure (BLOB coming as INPUT to procedure)
Goto Forum:

Current Time: Thu Oct 20 19:56:53 CDT 2016

Total time taken to generate the page: 0.06027 seconds