You Asked
Tom,
Looking for your opinion on the following.....
I am an associate DBA in a moderate sized shop. Currently we are
working on a complex data model that involves a central application
schema with attendant code. My concern is, the developer has written a package where the procedures used in the application (record inserts, deletes, updates, etc.) are created dynamically during the processing.
I question whether this will scale or not, but being the low man on the totem pole I hesitate to press the issue.
What are your thoughts on this type of package?
and Tom said...
my rule on dynamic sql in plsql is simple:
you use it when there is no other method available to you.
here is an excerpt from my book "Effective Oracle by Design" on this very relevant topic:
Use Static SQL
If you can achieve your goals using static SQL-even if you need to write a little more code-do it that way. Generic code using dynamic SQL is wonderful, but it will be less scalable, harder to debug and maintain, and slower running than the same code using static SQL.
Advantages of Static SQL
The advantages of using static SQL over dynamic SQL in PL/SQL are many and profound. Consider some of these reasons to use static SQL:
o Static SQL is checked at compile time. This way, you know that the procedure is valid and can execute. You might still have a bug in your code, but at least you know the SQL isn't the problem.
o PL/SQL will validate datatypes, sizes, and so on. This means that you don't need to define records to fetch into or define tons of variables. You do less coding work, and the compiler does more work.
o The dependencies are set up and maintained in the data dictionary. So, no one can claim "I didn't know you were using that."
o If the base objects change over time, your code fixes itself automatically. If you add or remove columns or grants, you don't need to worry about your code. On the other hand, when you make these changes and are using dynamic SQL, you will need to inspect your code for correctness.
o Static SQL makes parse once, execute many a reality. Dynamic SQL makes it easier to lose out on this benefit. This is especially true with the newer native dynamic SQL, where each and every execution of a statement is preceded by a parse call (this changes slightly in 10g).
o Static SQL is faster. Doing something with dynamic SQL in PL/SQL is slower than doing the same thing with static SQL.
The most important features of static SQL that we lose with dynamic SQL are dependency-tracking and performance-related issues surrounding parsing and overall execution speed.
Look for Opportunities to Replace Dynamic SQL
People frequently use dynamic SQL where static SQL could be used instead. For example, suppose we want to write a small lookup routine that will return one of three columns. We might code that routine like this:
ops$tkyte@ORA920> create or replace function get_value_dyn
( p_empno in number, p_cname in varchar2 ) return varchar2
2 as
3 l_value varchar2(4000);
4 begin
5 execute immediate
6 'select ' || p_cname || ' from emp where empno = :x'
7 into l_value
8 using p_empno;
9
10 return l_value;
11 end;
12 /
Function created.
Instead, we could code this routine using static SQL, like this:
ops$tkyte@ORA920> create or replace function get_value_static
( p_empno in number, p_cname in varchar2 ) return varchar2
2 as
3 l_value varchar2(4000);
4 begin
5 select decode( upper(p_cname),
6 'ENAME', ename,
7 'EMPNO', empno,
8 'HIREDATE', to_char(hiredate,'yyyymmddhh24miss'))
9 into l_value
10 from emp
11 where empno = p_empno;
12
13 return l_value;
14 end;
15 /
Function created.
This version is not as generic as the dynamic SQL routine, so if we wanted to support additional columns over time, we would need to modify code. But a simple Runstats test might help convince you that it is well worth it. Here are the results when calling the dynamic function:
ops$tkyte@ORA920> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_dummy varchar2(30);
3 begin
4 for i in 1 .. 500
5 loop
6 for x in ( select empno from emp )
7 loop
8 l_dummy := get_value_dyn(x.empno, 'ENAME' );
9 l_dummy := get_value_dyn(x.empno, 'EMPNO' );
10 l_dummy := get_value_dyn(x.empno, 'HIREDATE' );
11 end loop;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
And here is what happens when we do it statically:
ops$tkyte@ORA920> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> declare
2 l_dummy varchar2(30);
3 begin
4 for i in 1 .. 500
5 loop
6 for x in ( select empno from emp )
7 loop
8 l_dummy := get_value_static(x.empno, 'ENAME' );
9 l_dummy := get_value_static(x.empno, 'EMPNO' );
10 l_dummy := get_value_static(x.empno, 'HIREDATE' );
11 end loop;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1102 hsecs
Run2 ran in 703 hsecs
run 1 ran in 156.76% of the time
The static version is obviously faster by the wall clock. However, the more compelling reasons to use that version follow:
Name Run1 Run2 Diff
STAT...session cursor cache hi 21,003 4 -20,999
STAT...opened cursors cumulati 21,005 5 -21,000
STAT...parse count (total) 21,005 5 -21,000
LATCH.shared pool 63,565 21,566 -41,999
LATCH.library cache pin 127,062 43,064 -83,998
LATCH.library cache 127,087 43,088 -83,999
Those are some really big differences. Look at the latching statistics here:
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
463,431 253,318 -210,113 182.94%
PL/SQL procedure successfully completed.
This shows almost double the latching activity using dynamic SQL. Also notice the statistic for session cursor cache hits, which shows that we made heavy use of that feature. If session-cached cursors had been disabled on this system, the latching for the dynamic SQL would have been three times that of static SQL.
Static SQL Wrap-up
Yes, there are times when nothing else but dynamic SQL will do the job. But you should look for opportunities to remove dynamic SQL, especially if the dynamic SQL is there simply to save a couple lines of code. Consider this example:
If (condition1) then
Update using predicate1
Elsif (condition2) then
Update using predicate2
?
Elsif (conditionN) then
update using predicateN
End if;
An alternative is to use this code:
L_stmt := 'update ?' || some_predicate;
Execute immediate l_stmt;
It would be worth having 40 to 60 lines of code (the first version) instead of 2 lines of code, if this statement is executed repeatedly in a session. The bottom line is to use common sense and be aware of the trade-offs.
Dynamic SQL is something you want to use when static SQL is no longer practical-when you would be writing hundreds of lines of code, not just 40 or so. Or, you will need to use dynamic SQL when no amount of static SQL will do.
When coding dynamic SQL, do not forget about the existing DBMS_SQL package in addition to native dynamic SQL. There are opportunities for scaling up with DBMS_SQL that are not available with native dynamic SQL. For example, with native dynamic SQL, every EXECUTE has a corresponding PARSE call to go along with it (as you can see from the Runstats example in the previous section). The additional parsing is the major cause of excessive latching. This can be removed by using the parse once, execute many rule.
DBMS_SQL, since it is a procedural API, allows Oracle to parse a statement once (such as an INSERT statement) and then execute it hundreds or thousands of times. So, if you know that your dynamic SQL statement will be executed many times but with different inputs, you would use DBMS_SQL. For example, with a dynamic-loader routine that accepts as inputs the table name and filename to load, the SQL INSERT will be constant; just the inputs will vary. If, on the other hand, you know your routine will rarely (if ever) execute the same dynamic SQL statement call after call, you would use native dynamic SQL.
Rating
(9 ratings)
Is this answer out of date? If it is, please let us know via a Comment