Re: Extract SQL statements from Database/File
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Rajesh.Rao@xxxxxxxxxxxx
- Date: Tue, 15 Sep 2009 17:06:35 +0200
Rajesh Rao wrote:
> The objective is to generate explain plans against all of the statements in a
> performance environment before deployment in Production. This is, in case,
> the current performance test cases never execute certain SQL statements. I
> understand that this would not be foolproof and explain plans might still
> differ from actual execution plans.
>
> The problem with PL/SQL profiler is the same as I stated in the earlier
> email. There would be a need to execute the packages and it is possible that
> the execution might not execute some SQL statements due to the conditional
> flows, unless the data is conditioned appropriately for each and every
> possible test case. This is for a critical application that is being
> re-architected and it's a complete rewrite. Hence the need to cover all bases.
>
> Regards
> Raj
>
>
I see. Well, the point raised by Joe about dynamic SQL was very valid,
and perhaps you should be careful with the EXECUTE IMMEDIATE statements
and calls to DBMS_SQL. And I'm not sure that checking execution plans
will give you must assurance about plan changes. If there is a place
where bind variables are systematically used, it's PL/SQL. Don't give
more worth to the exercise than it really deserves, even with a nearly
perfect code coverage.
There is a tool I haven't personally used but which I have seen demo'd
and that looks interesting, which might be an answer to the problem of
extracting statements -Smart-TS. There may be others, but at least I
have heard of this one.
HTH
SF
--
//www.freelists.org/webpage/oracle-l
Other related posts:
