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.
--设置tracle文件标识符
SQL> alter session set tracefile_identifier='ZLM02';
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' using s;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
--关闭sql_trace
SQL> alter session set sql_trace=false;
Session altered.
SQL> !
--再次查看用tkprof格式化以后的内容
[oracle@zlm trace]$ ll -lrth | grep ZLM02.trc
-rw-r----- 1 oracle oinstall 18K Sep 14 15:16 zlm11g_ora_14546_ZLM02.trc
[oracle@zlm trace]$ tkprof zlm11g_ora_14546_ZLM02.trc /home/oracle/zlm02.log
TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 14 15:17:09 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@zlm trace]$
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------