sql tuning [message #448285] |
Mon, 22 March 2010 08:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orafacjublu
Messages: 95 Registered: May 2006 Location: KOLKATA
|
Member |
|
|
Hi, I am doing a multitable insert based on a select query of a table of nearly 40billion rows . The query takes about 30 minutes to fetch the data. Any way to reduce the time . This is an aggregate table. Interestingly same sql takes much less around 15 minutes in 9i
|
|
|
|
Re: sql tuning [message #448419 is a reply to message #448336] |
Tue, 23 March 2010 05:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orafacjublu
Messages: 95 Registered: May 2006 Location: KOLKATA
|
Member |
|
|
sorry I can't give the explain_plan for 9i but for 11g it is
PLAN_TABLE_OUTPUT
Plan hash value: 281876647
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47M| 7566M| 54134 (3)| 00:12:38 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 47M| 7566M| 54134 (3)| 00:12:38 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 47M| 7566M| 54134 (3)| 00:12:38 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| t1 | 47M| 7566M| 54134 (3)| 00:12:38 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
[Updated on: Tue, 23 March 2010 05:27] by Moderator Report message to a moderator
|
|
|
Re: sql tuning [message #448421 is a reply to message #448285] |
Tue, 23 March 2010 05:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Without something to compare to there's not really anything we can say.
You're apparently querying 47 million rows out of the table and using parallel processing to do so. With the information provided I can't tell if that's good or bad.
|
|
|
|
Re: sql tuning [message #448602 is a reply to message #448419] |
Wed, 24 March 2010 04:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Difficult to say with the limited information you provided.Would have been better if you provide the plan in both database.Does the plan goes for parallel processing in 9i? Try avoiding parallel processing for the sql and observe if it helps. You can use NO_PARALLEL hint for this.You can have more information about this hint by doing a search in Google.
Regards,
Ved
[Updated on: Wed, 24 March 2010 04:25] Report message to a moderator
|
|
|
|