OraFAQ Forum: Performance Tuning » VERY URGENT -- SQL- TUNING

Home » RDBMS Server » Performance Tuning » VERY URGENT -- SQL- TUNING

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

VERY URGENT -- SQL- TUNING [message #124027]

Wed, 15 June 2005 19:49

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

Report message to a moderator

Re: VERY URGENT -- SQL- TUNING [message #124047 is a reply to message #124027]

Thu, 16 June 2005 00:58

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

What query? Please also post your explain plan and the SQL execution stats.

Best regards.

Frank

Report message to a moderator

Re: VERY URGENT -- SQL- TUNING [message #124114 is a reply to message #124027]

Thu, 16 June 2005 08:31

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.

Report message to a moderator

Re: VERY URGENT -- SQL- TUNING [message #124174 is a reply to message #124047]

Thu, 16 June 2005 12:50

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

Report message to a moderator

Re: VERY URGENT -- SQL- TUNING [message #124179 is a reply to message #124174]

Thu, 16 June 2005 14:03

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.

Report message to a moderator

Re: VERY URGENT -- SQL- TUNING [message #124185 is a reply to message #124027]

Thu, 16 June 2005 14:40

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.

Report message to a moderator

Previous Topic:	Decide the size of every component of SGA in given case?
Next Topic:	how to avoid the full table scan?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 11:26:59 CDT 2025