parameter CURSOR_SHARING. In this post, I will demonstrate that hard parsing can be reduced by

•  – Replacing literals with bind variables
•  – setting CURSOR_SHARING=SIMILAR

Overview

1. create a procedure to query sh.sales without using bind variables
2. set cursor_sharing to exact
3. Take begin snapshot for awr/statspack
4. run the procedure
5. Take end snapshot for awr/statspack
6. Generate awr/statspack report
7. Both reports indicate low soft parsing
8. Resolve the problem using

– set cursor_sharing to similar

OR

– set cursor_sharing to exact and use bind variables in the procedure

9. Run the procedure and generate awr/statspack report
10. Verify that soft parsing has increased considerably

————————————————————————-

Implementation

SQL>conn sh/sh

— create package test in which we are querying table sh.sales repeatedly (50000 times)  without using bind variables

sql>
CREATE OR REPLACE PACKAGE test AS
     PROCEDURE Workload;
     PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY test AS
PROCEDURE Workload IS
BEGIN
FOR i in 1 .. 50000
LOOP
Foo(i);
END LOOP;
END Workload;
PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
BEGIN
DECLARE
l_stmt VARCHAR2(2000);
BEGIN
l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = ' ||
TO_CHAR(CUSTID);
EXECUTE IMMEDIATE l_stmt;
END;
END Foo;
END;
/

— Create begin snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create begin snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();
    alter system set cursor_sharing = exact scope=memory;

— execute the workload —

sql>conn sh/sh
       exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat
      exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
       exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
    @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % is very low as cursor sharing is exact   and bind variables have not been used.

— To solve this issue, we can take two actions :

1.  Change cursor_sharing to similar
2.  Use bind variables in the procedure

-—————– Solution 1 —————————

       Change cursor_sharing to similar (if application code cannot be changed)

sql>conn / as sysdba
    alter system set cursor_sharing=similar scope=memory;

— create begin snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();

— execute the workload —

sql>conn sh/sh
    exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat

       exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
    @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % has increased substantially as cursor  sharing has been set to similar

—————— Solution 2 —————————

      . Use bind variables in the procedure

— Reset cursor_sharing to exact

sql>conn / as sysdba
    alter system set cursor_sharing=exact scope=memory;

— alter the Foo procedure to use bind variables :

CREATE OR REPLACE PACKAGE BODY test AS
      PROCEDURE Workload IS
       BEGIN
             FOR i in 1 .. 50000     LOOP
                 Foo(i);
             END LOOP;
         END Workload;

       PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
       BEGIN
           DECLARE
              l_stmt VARCHAR2(2000);
            BEGIN
                   l_stmt := ‘SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id';
                  EXECUTE IMMEDIATE l_stmt USING CUSTID;
            END;
         END Foo;
END;
/

— Create begin snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create begin snapshot for awr report —

sql>conn / as sysdba
 exec dbms_workload_repository.create_snapshot();
        alter system set cursor_sharing = exact scope=memory;

— execute the workload —

sql>conn sh/sh
      exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
       exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
      @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % has increased substantially as bind variables  have been used.
————————————————————————————————————————-

Tuning Index
Cursor Sharing Demystified 
Library Cache Lock And Pin Demonstrated
Parent And Child Cursors In Oracle
Quick Overview Of Shared Pool Tuning
Shared Pool Architecture 

                                                                                                                                   ————–