1.创建测试用户并赋予权限 [oracle@zlm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 14 14:47:32 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
--创建用户 SQL> create user zlm identified by zlm;
User created.
--赋权限 SQL> grant dba to zlm;
Grant succeeded.
--创建表空间 SQL> create tablespace zlm datafile '/u01/app/oracle/oradata/zlm11g/zlm01.dbf' size 100m reuse autoextend on next 10m maxsize 1G extent management local segment space management auto;
Tablespace created.
--设置缺省表空间 SQL> alter user zlm default tablespace zlm;
User altered.
--连接用户 SQL> conn zlm/zlm Connected.
--创建测试表 SQL> create table t1 as select object_id,object_name from dba_objects;
Table created.
--创建索引 SQL> create index inx_t1_id on t1(object_id);
Index created.
--收集表的统计信息 SQL> exec dbms_stats.gather_table_stats('ZLM','T1',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
2.不使用绑定变量的情况 --设置tracle文件标识符 SQL> alter session set tracefile_identifier='ZLM01';
Session altered.
--开启sql_trace SQL> alter session set sql_trace=true;
Session altered.
--执行PL/SQL程序段 SQL> begin
2 for s in 1..10000 3 loop 4 execute immediate 'select * from t1 where object_id='||s; 5 end loop; 6 end; 7 /
PL/SQL procedure successfully completed.
--关闭sql_trace SQL> alter session set sql_trace=false;
Session altered.
SQL> !
[oracle@zlm ~]$ cd /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace/
[oracle@zlm trace]$ ll -lrth | grep ZLM01.trc -rw-r----- 1 oracle oinstall 7.3M Sep 14 15:00 zlm11g_ora_14341_ZLM01.trc [oracle@zlm trace]$ tkprof zlm11g_ora_14341_ZLM01.trc /home/oracle/zlm01.log
TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 14 15:05:46 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@zlm trace]$
--查看用tkprof格式化后的日志zlm01.log最后一段
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10000 6.26 6.53 0 0 0 0 Execute 10000 0.23 0.26 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 20000 6.50 6.79 0 0 0 0
Misses in library cache during parse: 10000
10003 user SQL statements in session. 0 internal SQL statements in session. 10003 SQL statements in session. ******************************************************************************** Trace file: zlm11g_ora_14341_ZLM01.trc Trace file compatibility: 11.1.0.7 Sort options: default
1 session in tracefile. 10003 user SQL statements in trace file. 0 internal SQL statements in trace file. 10003 SQL statements in trace file. 10003 unique SQL statements in trace file. 90068 lines in trace file. 138 elapsed seconds in trace file.
分析：刚才的那段PL/SQL的语句被硬解析了10000次，并且执行了10000次，CPU总共消耗了6.26+0.23=6.50，花费时间6.53+0.26=6.79，可以看到，在trace文件中共有90068行，由于同样结构的SQL语句，未使用绑定变量，使Oracle认为每个语句都不同，因此产生了非常多的SQL语句，zlm01.log日志文件大小约为12M。
2.使用绑定变量的情况 --清空shared_pool
SQL> alter system flush shared_pool;
System altered.
--设