Re: Extract SQL statements from Database/File
From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 15 Sep 2009 17:06:35 +0200
Message-ID: <4AAFAD7B.3060309_at_roughsea.com>
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.
Date: Tue, 15 Sep 2009 17:06:35 +0200
Message-ID: <4AAFAD7B.3060309_at_roughsea.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 15 2009 - 10:06:35 CDT