The DBMS_SQL package is not as easy to use as native dynamic SQL. There are many procedures and functions that must be used in a strict sequence. Typically, performing simple operations requires a large amount of code when you use the DBMS_SQL package. You can avoid this complexity by using native dynamic SQL instead.
Performance Improvements
The performance of native dynamic SQL in PL/SQL is comparable to the performance of static SQL because the PL/SQL interpreter has built-in support for native dynamic SQL. Therefore, the performance of programs that use native dynamic SQL is much better than that of programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent statements that use the DBMS_SQL package. Of course, your performance gains may vary depending on your application.
The DBMS_SQL package is based on a procedural API and, as a result, incurs high procedure call and data copy overhead. For example, every time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for later use during execution. Similarly, every time you execute a fetch, first the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead resulting from data copying. In contrast, native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.
此外EXECUTE IMMEDIATE 的使用，也有优化，如下：
Performance Tip
When using either native dynamic SQL or the DBMS_SQL package, you can improve performance by using bind variables, because using bind variables allows Oracle to share a single cursor for multiple SQL statements.
For example, the following native dynamic SQL code does not use bind variables:
CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE DELETE FROM dept WHERE deptno = || to_char (my_deptno);
END;
/
For each distinct my_deptno variable, a new cursor is created, which can cause resource contention and poor performance. Instead, bind my_deptno as a bind variable, as in the following example:
CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE DELETE FROM dept WHERE deptno = :1 USING my_deptno;
END;
/
5，动态SQL中，尽量多用execute immediate,而少用DBMS_SQL,前者综合效率优于后者
6，对于很复杂的查询语句，可以建立临时表进行缓冲（关于临时表的解释与使用，还希望同行告诉我在哪里有。。。）
7，COUNT（*）与COUNT（某列）一样进行全表扫描Fast Full Index Scan，速度差不多
8，经常同时存取多列，或经常使用GROUP BY的SQL语句，最好对表的GROUP字段建立组合索引。组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。
9，对于字段取值单一（如性别字段只有男与女），而经常在性别上做查询，则建立位图索引。
注：BITMAP INDEX通常用于DSS,如果你的系统是OLTP,DML操作将LOCK整个BITMAP SEGMENT,因此只在DSS下 考虑BITMAP INDEX
。。。。。。。。。。。。。。。
还有很多，希望各位补充与修正。
---ORACLE爱好者最常用的东西就是SQL了，而它的优化又是一门比较综合复杂的东东，希望ITPUT下一期的杂志能出版一个关于SQL优化的专题，我想一定是很多人都希望的事情。
SQL语句：
是对数据库(数据)进行操作的惟一途径；
消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；
可以有不同的写法；易学，难精通。
SQL优化：
固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。
应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致
ORACLE优化器：
在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是
要么结果表达式能够比源表达式具有更快的速度
要么源表达式只是结果表达式的一个等价语义结构
不同的SQL结构有时具有同样的操作（例如：= ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。