| sql tuning [message #448285] |
Mon, 22 March 2010 08:32  |
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  |
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 |
cookiemonster
Messages: 13922
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 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
