| straight sql vs procedural ASAP [message #219727] |
Thu, 15 February 2007 13:24  |
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 #219739 is a reply to message #219738] |
Thu, 15 February 2007 15:47  |
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 |
 |
BlackSwan
Messages: 26766
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 |
rleishman
Messages: 3728
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
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
