通过绑定变量优化OLTP系统性能
通过绑定变量优化OLTP系统性能
之前给南京某客户优化一套OLTP数据库，其数据库中在某个时间段，会执行大量结构非常相似的查询语句，造成shared_pool被大量占用，导致数据库性能下降。碰到这种情况，其实最佳优化方案，就是让应用厂商修改相应代码，通过增加绑定变量，来有效减少相似SQL语句执行时的硬解析数，降低对shared_pool的消耗。下面来做一个关于绑定变量的测试： 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. --设置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 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 10015 0.13 0.11 0 0 0 0 Fetch 19 0.00 0.00 0 47 0 12 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10035 0.13 0.12 0 47 0 12 Misses in library cache during parse: 1 4 user SQL statements in session. 12 internal SQL statements in session. 16 SQL statements in session. ******************************************************************************** Trace file: zlm11g_ora_14546_ZLM02.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 4 user SQL statements in trace file. 12 internal SQL statements in trace file. 16 SQL statements in trace file. 16 unique SQL statements in trace file. 20156 lines in trace file. 118 elapsed seconds in trace file. 分析：使用绑定变量以后，前后对比一下，资源消耗降低了非常多。运行了10000次的SQL语句，只解析了1次，执行次数虽然多了15次，但CPU时间为0.13，消耗时间为0.11，基本可以忽略不计，trace文件中只有20156行，内容非常较之前要低了非常多，zlm02.log文件仅19k大小。 SQL> select sql_id,sql_text,executions from v$sqlarea where sql_text like '%select * from t1 where object_id=%'; SQL_ID SQL_TEXT EXECUTIONS ------------- -------------------------------------------------- ---------- 28gj7tsy13xq8 select * from t1 where object_id=:i 10000 --采用绑定变量的select语句也被执行了10000次
总结：在OLTP等报表系统中，当我们的应用中如果执行结构非常类似的语句：如，select * from t1 where object_id='10',select * from t1 where object_id='100',……如果不加绑定变量，会大大增加硬解析的次数，10000次执行，就有10000次硬解析(第一次执行时)，如果再次执行，可能会因为在shared_pool缓存中已经存在，会有一部分软解析，而使硬解析数减少，而一旦使用了绑定变量，就算把shared_pool清空掉，也只需很少的几次硬解析，就可以执行10000次查询语句，大大减少了SGA中对shared_pool的占用，提高查询性能。如果在OLAP中，使用绑定变量需要谨慎，未必一定会提高性能，具体情况还需具体分析，这种情况仅仅适合OLTP系统。
评论暂时关闭