Home » RDBMS Server » Performance Tuning » sql tuning (11g,Solaris 9)
sql tuning [message #448285] Mon, 22 March 2010 08:32 Go to next message
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 #448336 is a reply to message #448285] Mon, 22 March 2010 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we don't have your tables.
We don't have your data.
We don't even have your SQL.

In the future please provide fewer details to make answering more of a challenge.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Post EXPLAIN PLAN for both "fast" & "slow" SQL
Re: sql tuning [message #448419 is a reply to message #448336] Tue, 23 March 2010 05:20 Go to previous messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 #448590 is a reply to message #448285] Wed, 24 March 2010 03:15 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Posting the query source may help.
Re: sql tuning [message #448602 is a reply to message #448419] Wed, 24 March 2010 04:22 Go to previous messageGo to next message
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

Re: sql tuning [message #449224 is a reply to message #448285] Sun, 28 March 2010 06:16 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, no query, no tuning help, sorry.

Kevin
Previous Topic: Index Hit Retio
Next Topic: materialize view
Goto Forum:
  


Current Time: Sat Dec 10 20:36:41 CST 2016

Total time taken to generate the page: 0.10458 seconds