Oracle hard parsing examples

In order to verify the scenario of SQL hard parsing, the following six test examples are set for Oracle hard parsing: 1. Common queries without bound variables 2. Test queries with Bound variables 3. Test queries with changed SQL statements under Bound variables 4. Test Analysis of unbound DML variables 5. Test SQL parsing of non-bound variables during insertion 6. After variables are bound, SQL parsing in the process

[SQL]/** test Example 1: Common query without variable binding **/drop table foo purge; CREATE TABLE foo AS SELECT LEVEL AS x, 100000-level as y FROM dual connect by level <= 100000; alter system flush SHARED_POOL; SELECT * FROM foo WHERE x = 100; SELECT * FROM foo WHERE x = 999; SELECT * FROM foo WHERE x= 10000; select t. SQL _TEXT, T. SQL _ID, T. EXECUTIONS, T. parse_callfrom V $ SQL T WHERE UPPER (T. SQL _TEXT) LIKE '% FOO %'; [SQL]/** test Example 2: Test the query in the bound variable **/drop table foo purge; create table foo as select level as x, 100000-level as y FROM dual connect by level <= 100000; alter system flush SHARED_POOL; VARIABLE temp NUMBER; exec: temp: = 99; SELECT * FROM foo where x =: temp; exec: temp: = 100; SELECT * FROM foo where x =: temp; exec: temp: = 101; SELECT * FROM foo where x =: temp; select t. SQL _TEXT, T. SQL _ID, T. EXECUTIONS, T. parse_callfrom V $ SQL T WHERE UPPER (T. SQL _TEXT) LIKE '% FOO %';/** [SQL] test example 3: test the SQL change query in the bound variable **/drop table foo purge; create table foo as select level as x, 100000-level as y FROM dual connect by level <= 100000; alter system flush SHARED_POOL; VARIABLE temp NUMBER; exec: temp: = 99; SELECT * FROM foo where x =: temp; exec: temp: = 100; SELECT * from foo where x =: temp; exec: temp: = 101; SELECT * FROM foo where x =: temp; select t. SQL _TEXT, T. SQL _ID, T. EXECUTIONS, T. parse_callfrom V $ SQL T WHERE UPPER (T. SQL _TEXT) LIKE '% FOO %'; [SQL]/** test example 4: Test DML non-bound variable parsing **/drop table foo purge; create table foo as select level as x, 100000-level as y FROM dual connect by level <= 100000; alter system flush SHARED_POOL; insert into foo values (100,200 ); insert into foo values (101,201); insert into foo values (103,203); select t. SQL _TEXT, T. SQL _ID, T. EXECUTIONS, T. parse_callfrom V $ SQL T WHERE UPPER (T. SQL _TEXT) LIKE '% FOO %'; [SQL]/** test example 5: Test SQL parsing when inserting data during execution **/drop table foo purge; create table foo as select level as x, 100000-level as y FROM dual connect by level <= 100000; alter system flush SHARED_POOL; begin for I IN 1 .. 3 loop if I = 1 then insert into foo values (1, 1); elsif I = 2 THEN INSERT INTO FOO VALUES (2, 2 ); elsif I = 3 THEN INSERT INTO FOO VALUES (3, 3); END IF; END LOOP; END;/SELECT T. SQL _TEXT, T. SQL _ID, T. EXECUTIONS, T. parse_callfrom V $ SQL T WHERE UPPER (T. SQL _TEXT) LIKE '% FOO %'; [SQL]/** test example 6: SQL parsing in the process after a variable is bound **/drop table foo purge; create table foo as select level as x, 100000-level as y FROM dual connect by level <= 100000; alter system flush SHARED_POOL; begin for I IN 1 .. 200 loop insert into foo values (I, 100000-I); END LOOP; END;/SELECT T. SQL _TEXT, T. SQL _ID, T. EXECUTIONS, T. parse_callfrom V $ SQL T WHERE UPPER (T. SQL _TEXT) LIKE '% FOO % ';

 

Through the above six cases, we can conclude that the SQL statements for soft parsing in Oracle must be identical, the so-called same SQL statement must be case-insensitive (test example 3), and cannot even contain one or more spaces. This conclusion can be obtained by modifying test example 3 to add a space, different SQL _ID values are obtained. Only SQL statements with identical values can obtain corresponding HASH values for soft parsing. In the SQL pool, we need to analyze the SQL statements that only have different parameters in the SQL pool. If the SQL statements appear many times, it is necessary to bind the corresponding variables to them, this reduces hard parsing costs and improves performance.