Optimize OLTP system performance by binding variables and variable oltp system performance

Optimize OLTP system performance by binding variables and variable oltp system performance
Previously, An OLTP database was optimized for a customer in Nanjing. A large number of query statements with very similar structures were executed in the database for a certain period of time, resulting in a large use of shared_pool, resulting in a decline in database performance. In this case, The Best optimization solution is to let the application vendor modify the corresponding code and effectively reduce the number of hard resolutions when executing similar SQL statements by adding binding variables, reduce the consumption of shared_pool. The following is a test on variable binding:
1. Create a test user and grant the permission to [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,201 1, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
-- Create user SQL> create user zlm identified by zlm;
User created.
-- Grant permissions to SQL> grant dba to zlm;
Grant succeeded.
-- Create tablespace SQL> create tablespace zlm datafile '/u01/app/oracle/oradata/zlm11g/zlm01.dbf 'size 100 m reuse autoextend on next 10 m maxsize 1G extent management local segment space management auto;

Tablespace created.
-- Set the default tablespace SQL> alter user zlm default tablespace zlm;

User altered.
-- Connect to the user SQL> conn zlm/zlmConnected.
-- Create test table SQL> create table t1 as select object_id, object_name from dba_objects;
Table created.
-- Create index SQL> create index inx_t1_id on t1 (object_id );

Index created.
-- Collect table statistics SQL> exec dbms_stats.gather_table_stats ('zlm ', 't1', estimate_percent => 100, cascade => true );
PL/SQL procedure successfully completed.
2. Do not bind variables -- set the tracle File Identifier SQL> alter session set tracefile_identifier = 'zlm01 ';

Session altered.
-- Enable SQL _traceSQL> alter session set SQL _trace = true;
Session altered.
-- Execute PL/SQL program section 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.
-- Disable SQL _traceSQL> 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.3 M Sep 14 zlm11g_ora_14341_ZLM01.trc [oracle @ zlm trace] $ tkprof release/home/oracle /zlm01.log
TKPROF: Release 11.2.0.3.0-Development on Sun Sep 14 15:05:46 2014
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.

[Oracle @ zlm trace] $
-- View the last log segment zlm01.log formatted with tkprof
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
Call count cpu elapsed disk query current rows ------- ------ -------- ---------- Parse 10000 6.26 6.53 0 0 0 Execute 10000 0.23 0.26 0 0 0 Fetch 0 0.00 0 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.trcTrace file compatibility: 11.1.0.7Sort 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.
Analysis: The preceding PL/SQL statements were hard parsed for 10000 times and executed for 10000 times. The total CPU consumption was 6.26 + 0.23 = 6.50, it takes 6.53 + 0.26 = 6.79. We can see that there are 90068 rows in the trace file. Because SQL statements of the same structure are not bound to variables, Oracle considers each statement to be different, therefore, a lot of SQL statements are generated. The size of the zlm01.log file is about 12 Mb.

2. bind variables -- clear shared_pool
SQL> alter system flush shared_pool;

System altered.
-- Set the tracle File Identifier SQL> alter session set tracefile_identifier = 'zlm02 ';
Session altered.
-- Enable SQL _traceSQL> alter session set SQL _trace = true;
Session altered.
-- Run PL/SQL program section 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.
-- Disable SQL _traceSQL> alter session set SQL _trace = false;
Session altered.
SQL>!
-- View the content formatted with tkprof again [oracle @ zlm trace] $ ll-lrth | grep ZLM02.trc-rw-r ----- 1 oracle oinstall 18 K Sep 14 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,201 1, 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 0 Fetch 19 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.trcTrace file compatibility: 11.1.0.7Sort 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.
Analysis: After variables are bound, the resource consumption is greatly reduced. The SQL statement that runs 10000 times is parsed only once. Although the execution times are 15 times more, the CPU time is 0.13, and the consumption time is 0.11, which is negligible, there are only 20156 rows in the trace file, and the content is much lower than before. The zlm02.log file is only 19 k in size.
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 ------------- explain ---------- 28gj7tsy13xq8 select * from t1 where object_id =: I 10000 -- the select statement bound to variables is also executed for 10000 times.

Conclusion: In OLTP and other reporting systems, if we execute statements with similar structures in our applications: for example, select * from t1 where object_id = '10 ', select * from t1 where object_id = '201312 ',...... If no variable is added, the number of hard parsing times will be greatly increased. for 10000 executions, there will be 10000 hard parsing (at the first execution). If you execute the command again, some soft resolutions may exist in the shared_pool cache, reducing the number of hard resolutions. Once a variable is bound, the shared_pool is cleared, you only need to perform a few hard resolutions to execute 10000 query statements, which greatly reduces the usage of shared_pool in SGA and improves query performance. In OLAP, you need to be cautious when using Bound variables, which may not necessarily improve performance. Specific analysis is required. This situation is only applicable to OLTP systems.




Why bind a variable in Oracle's OLTP system?

Improve libary cache performance and avoid hard Parsing
 
What is the role of ORACLE variable binding?

The performance improvement of variable binding in the OLTP system is obvious. We all know that, however, variable binding sometimes produces bad execution plans, especially columns requiring histograms.
Starting from ORACLE9i, the function of binding variables is provided, that is, when ORACLE parses the SQL statement for the first time, the real value of the variable is substituted into the execution plan, in the future, this execution plan will be used for all SQL statements that bind the same variables.
If the first actual value happens to be a special value, this will seriously affect the execution plan and future execution efficiency.
For the Adaptive Cursor Sharing feature provided by oracle11g, there can be multiple execution plans for an SQL statement that is bound to the same variable, so as to dynamically optimize the execution plan, this has not been tried yet.
However, according to the description, it seems that oracle11g needs to achieve this effect to increase the resolution and memory of each SQL statement. In addition, in the future, it will increase the histogram judgment for the same SQL statement and execute the judgment of the Plan CUBE, if the results are different, the optimization will be done. However, if the results are the same, it is equal to repeating these tasks. In actual application, 99.9% of the results are the same, therefore, the memory is increased by 11 GB to increase the value of the time for determining each SQL statement. Especially for High-concurrency business systems, most of the SQL statements are executed at a very small time.
This is just my assumption, but I feel that the role of binding variables is really not obvious. In the OLTP system, it is better not to bind variables to special values, in OLAP, it is recommended that you do not bind variables with many special values. 11g improvement is not clear about the effect, but it seems that the cost is relatively high.