Home » RDBMS Server » Performance Tuning » VERY URGENT -- SQL- TUNING
VERY URGENT -- SQL- TUNING [message #124027] Wed, 15 June 2005 19:49 Go to next message
Fl_reader
Messages: 15
Registered: March 2005
Junior Member
How to tune the query to select 80 Million data.
Design of the table is as follows:

Cust_no ,Inv_no,Inv_dt,pay_dt, Amt,Py_no,cr_no,adj_no,pay_mtd
1 01 2/9/05 2/09/05 10 CK
1 01 2/9/05 2/25/05 5 CC
1 01 2/9/05 3/03/05 -15 1233 CC

1 02 3/5/05 4/25/05 25 CKC
1 02 3/5/05 5/25/05 -25 455 CK

2 11 3/5/05 4/9/05 30 CC
2 12 3/5/05 5/23/05 -30 4478 CHK
---
My output should be

Cust_no Bill_amt Bill_dt Pay_amt Pay_dt Pay_mthd
1 15 2/9/05 -15 3/3/05 CC
1 25 3/5/05 -25 5/25/05 CK
2 30 3/5/05 -30 5/23/05 CHK
Re: VERY URGENT -- SQL- TUNING [message #124047 is a reply to message #124027] Thu, 16 June 2005 00:58 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
What query? Please also post your explain plan and the SQL execution stats.

Best regards.

Frank
Re: VERY URGENT -- SQL- TUNING [message #124114 is a reply to message #124027] Thu, 16 June 2005 08:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And there isn't much to tune if it is simply a matter of doing a full table scan on a table and printing everything out.
Re: VERY URGENT -- SQL- TUNING [message #124174 is a reply to message #124047] Thu, 16 June 2005 12:50 Go to previous messageGo to next message
Fl_reader
Messages: 15
Registered: March 2005
Junior Member
 OPERATION	OPTIONS		OPERATION_1		OPTIMIZER	COST
SELECT STATEMENT		SELECT STATEMENT	HINT: RULE	
SORT		GROUP BY	SORT		
NESTED LOOPS			NESTED LOOPS		
NESTED LOOPS			NESTED LOOPS		
NESTED LOOPS			NESTED LOOPS		
TABLE ACCESS	BY INDEX ROWID	TABLE ACCESS		ANALYZED	
INDEX		RANGE SCAN	INDEX		
TABLE ACCESS	BY INDEX ROWID	TABLE ACCESS		ANALYZED	
AND-EQUAL			AND-EQUAL		
INDEX		RANGE SCAN	INDEX		
INDEX		RANGE SCAN	INDEX		
TABLE ACCESS	BY INDEX ROWID	TABLE ACCESS		ANALYZED	
INDEX		RANGE SCAN	INDEX		
TABLE ACCESS	BY INDEX ROWID	TABLE ACCESS		ANALYZED	
INDEX		RANGE SCAN	INDEX		
Re: VERY URGENT -- SQL- TUNING [message #124179 is a reply to message #124174] Thu, 16 June 2005 14:03 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
We still don't know anything about your query or your Oracle version.

However my #1 top SQL tuning tip, which routinely works miracles (I'm serious) is Remove any Optimiser Hints. Is there by any chance a /*+ RULE */ hint? You will also want to analyze the table(s) involved in the query, unless for some reason you cannot use the CBO in which case it's a whole different ball game.
Re: VERY URGENT -- SQL- TUNING [message #124185 is a reply to message #124027] Thu, 16 June 2005 14:40 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'd like to second William's comments. Gather stats, then get out of the way and let the CBO do its thing.
Previous Topic: Decide the size of every component of SGA in given case?
Next Topic: how to avoid the full table scan?
Goto Forum:
  


Current Time: Tue Sep 29 12:27:44 CDT 2020