Follow-up
Larry Ma, February 17, 2016 - 5:56 pm UTC
Dear Connor,
Apologies for the delay in responding. We were able to resolve the issue adding a index to one of the tables. Now the performances between PL/SQL and direct query are comparable.
However, this does not explain the difference we experienced in the first place (PL/SQL with parameters being passed vs direct SQL). We saw a similar posting here at
https://community.oracle.com/thread/2485857?tstart=0 Thanks,
Larry
February 18, 2016 - 3:28 am UTC
It could be
- a bind peeking related issue
- an adaptive cursor sharing related issue
- a cardinality feedback related issue
- something else :-)
With the the specifics, we cant really say.
I'm glad you got a resolution.
When not to use Package
Boris, October 22, 2017 - 5:29 pm UTC
I always use package for real code over stand alone functions/procedures. Never ever think of stand alone functions/production.
But while reading the Steven article from Oracle magazine, I am totally confused
Section :
When Not to Use a Package http://www.oracle.com/technetwork/issue-archive/2005/05-may/o35plsql-085074.html Is this out-dated one? Is this really applicable in Oracle 11g?
I can't think of any reason not to use package? Do you have any valid case from your experience?
October 24, 2017 - 2:50 am UTC
Well...that article is 12 years old ! And Steven said:
"...there are some aspects of packages that can give pause—and sometimes, though rarely, argue against the use of packages."
And the example was:
"The chip_util package is constantly updated with new utilities, thus requiring the recompilation of the chip_util package specification"
In v11 onwards that restriction no longer applies - you can add routines to the tail of a package specification without causing invalidation.
When not to use Package
Boris, October 27, 2017 - 3:16 am UTC
Thanks a lot for your time in clarifying my doubt.s
Follow-up on your answer, for the same issue in different environment
narmada, April 05, 2019 - 3:04 pm UTC
Hi Tom,
Sorry If I can ask my question here. But my issue is exactly same as above stated issue. Sql alonerunning good in 3secs, but where as same sql with stored procedure taking time.. I took your inputs and executed the commands. Outputs are below:
1st query
=========
Elapsed: 00:00:08.37
Statistics
----------------------------------------------------------
66 recursive calls
0 db block gets
29649 consistent gets
17928 physical reads
0 redo size
4131 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
90 rows processed
2nd query:
==========
+000000000 00:00:03.823192000
Could you please advise on this how to further investigate this issue..
Thanks
Narmada
April 09, 2019 - 1:35 am UTC
That might be caching of data. Try the following:
- run with SQL 3 or 4 times
- run with PLSQL 3 or 4 times
compare the last couple of results for each
Diagnostic Process?
Aaron, April 20, 2020 - 4:07 pm UTC
Since this question didn't really run to completion, could you give some explanation on the diagnostics steps that were intended?
What was hoped to be learned by running the loop over the select? What would have been the next diagnostics steps? How would you determine if bind peaking was the issue?
begin
for i in ( <same select as above> )
loop
null;
end loop;
April 22, 2020 - 3:57 am UTC
OK, the motivation for the first answer is:
Often people will run something in (say) SQL Developer then in PLSQL and say "Its different". But when you run a query in SQL Dev, by default, we only fetch the first 50 rows, versus PLSQL which gets them all.
So the answer is about firstly establishing a "like for like" playing field.
Then if *still* PLSQL is behaving differently, we then look for things like session settings, bind peeking etc...
In particular, PLSQL will keep a cursor open in the session, and hence you don't repeatedly parse the same SQL statement. Run the same statement in SQL Plus or SQL Dev and you parse it each time. Bind peeking and things like adaptive cursor sharing need those additional parses to adjust the plan.
Hope that makes sense.