Home » RDBMS Server » Performance Tuning » straight sql vs procedural ASAP  () 1 Vote
straight sql vs procedural ASAP [message #219727] Thu, 15 February 2007 13:24 Go to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Hello!
We are having a major issue with how some of our statements are being processed in Oracle. We have Oracle 9i, and front-end PeopleSoft.
What we have noticed is the following:
1) a SELECT statment with "where clause" parameter being hard-coded is executed from sqlplus...Cost is 2, return time 1 second
2) Same SELECT, but this time parameters are being passed in the procedure, cost goes up to 800, return time over 5 minutes.

What should be done in terms of gather stats, analyze, to resolve this issue.

[Updated on: Thu, 15 February 2007 13:31]

Report message to a moderator

Re: straight sql vs procedural ASAP [message #219733 is a reply to message #219727] Thu, 15 February 2007 14:53 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Look at both EXPLAINs
Re: straight sql vs procedural ASAP [message #219734 is a reply to message #219733] Thu, 15 February 2007 14:57 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Explain plans are different. I realize that. Can someone help me understand why Oracle would take 2 different path's when processing theoretically identical SELECT statements.
With the only difference being values hardcoded, or being passed-in
Re: straight sql vs procedural ASAP [message #219738 is a reply to message #219734] Thu, 15 February 2007 15:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Mind posting those plans?
I have no idea about peoplesoft. Is there anything in the environment? Like forcing RBO or something like that?
Best method is to trace using 10046 and see the 'real time' plan.
>>why Oracle would take 2 different path'
Only 10053 trace can answer that Smile
Re: straight sql vs procedural ASAP [message #219739 is a reply to message #219738] Thu, 15 February 2007 15:47 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

I am posting two explain plans:
1) This is when running SELECT statment from sqlplus:
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	2  	 	      	             	 
  NESTED LOOPS		1  	32  	2  	 	      	             	 
    INDEX RANGE SCAN	PSBDISTRIB_LINE	1  	17  	3  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	PS_VOUCHER	1  	15  	1  	 	      	             	 
      INDEX UNIQUE SCAN	PS_VOUCHER	1  	 	1  	 	      	             	 



2) This is when running SELECT from procedure:
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		407 K	 	822  	 	      	             	 
TABLE ACCESS BY INDEX ROWID	PS_DISTRIB_LINE	2  	34  	1  	 	      	             	 
NESTED LOOPS		407 K	12 M	822  	 	      	             	 
INDEX FAST FULL SCAN	PSCVOUCHER	263 K	3 M	184  	 	      	             	 
INDEX RANGE SCAN	PS_DISTRIB_LINE	1  	 	2  	 	      	    

[Updated on: Thu, 15 February 2007 19:21] by Moderator

Report message to a moderator

Re: straight sql vs procedural ASAP [message #219760 is a reply to message #219727] Thu, 15 February 2007 19:28 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Oracle would take 2 different path's when processing theoretically identical SELECT statements.
If the two statements were really, really identical they would execute the same, IMO.
While you may think they are identical, obviously Oracle does not.
Since you've chosen to NOT post the two statements & DESC <TABLE_NAMES>, we (TINW) have nothing upon which to offer better suggestion(s).

[Updated on: Thu, 15 February 2007 21:46] by Moderator

Report message to a moderator

Re: straight sql vs procedural ASAP [message #219774 is a reply to message #219760] Thu, 15 February 2007 22:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It uses a different plan in SQL*Plus because you are over-typing a bind variable with a constant. With the knowledge of the constant value, CBO figures a range scan will be fast. Without knowledge of the column value, it is choosing a plan that is sub-optimal for that one value you tested, but perhaps optimal for many others.

Having said that, the slow plan is not actually a very good one in any circumstances, so it may be that your statistics are out of date.

Something else that is very odd: the fast plan finds all of the columns it needs in the index, yet the slow plan needs to access the table for columns not found in the index. Did you edit the SQL in any other way? Perhaps by deleting some WHERE conditions or SELECT columns?

Gather statistics. If that doesn't help, post the SQLs.

Ross Leishman
Previous Topic: Updating a BIG table
Next Topic: Power Builder Data window linkage performance
Goto Forum:
  


Current Time: Fri Dec 02 14:34:21 CST 2016

Total time taken to generate the page: 0.11292 seconds