PT - Improving Performance Through Literals, Bind Variables and Cursor Sharing

Improving Performance Through Literals, Bind Variables and Cursor Sharing 

Scenario :

In this example we execute query and find explain plan in terms by executing value from literals to bind variable we will see same explain plan sql_id  for the query.

SQL> conn soe/soe

Connected.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='01-2010';

TOTAL

------------

  53,308,707

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

------------

  49,166,476

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='02-2010';

TOTAL

------------

  49,166,476

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') ='03-2010';

TOTAL

------------

  53,907,203

• Explain plan for previous query

SQL> COL SQL_TEXT FORMAT a30

SQL>  SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%my query%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT /* my query */ TO_CHAR( 4qa4ftwp1nks3             1  706366211

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='02-2010'

SELECT /* my query */ TO_CHAR( 6xt52tc3u9h82             1  128237826

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='01-2010'

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT /* my query */ TO_CHAR( agv63ft219k4w             1 1142212764

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

='03-2010'

SQL>

• Flush shared pool

SQL>  ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

• Use variable instead using literals

SQL> VARIABLE month_year VARCHAR2(7)

SQL>  EXEC :month_year := '01-2010';

PL/SQL procedure successfully completed.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') =:month_year;

TOTAL

------------

  53,308,707

SQL> EXEC :month_year := '02-2010';

PL/SQL procedure successfully completed.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') =:month_year;

TOTAL

------------

  49,166,476

SQL> EXEC :month_year := '03-2010';

PL/SQL procedure successfully completed.

SQL> SELECT /* my query */ TO_CHAR(SUM(ORDER_TOTAL),'999,999,999') TOTAL FROM SOE.ORDERS WHERE TO_CHAR(ORDER_DATE,'MM-RRRR') =:month_year;

TOTAL

------------

  53,907,203

SQL>

• sql_id is same from previous query.

SQL> COL SQL_TEXT FORMAT a30

SQL>  SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%my query%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT /* my query */ TO_CHAR( g6muxwg8d93aj             1 3503590737

SUM(ORDER_TOTAL),'999,999,999'

) TOTAL FROM SOE.ORDERS WHERE

TO_CHAR(ORDER_DATE,'MM-RRRR')

=:month_year

SQL>