How to force a SQl query to do a hard parse in Oracle

As a DBA we all know how to force all sql queries for a hard parse.

Just Flush the Shared pool and all sql's already present in shared pool will be purged and hence forth if a new sql comes it will go for a hard parse.

But what if I want to force hard parse for a single sql statement in a Production Box. I cannot flush the shared pool a sit lead to performance issues in production. All new sql's will go for a hard parse and performance will be degraded.

I just did a POC for this that how we can force hard parse for a particular sql.

I agree we cannot flush Shared pool as it leads for performance problems. But we can purge a single SQL statement from Shared pool so that when that sql comes again it goes for a hard parse. Reason for this is , as we know wheneven a sql comes to Oracle , it first checks in shared pool that whether this sql was already executed. If Oracle finds this sql it uses the same execution plan again, this is called Soft Parse else it goes for Hard parse. I will writing more on this topic in another article.

Force a SQL for Hard Parse :-

SQL> SELECT * FROM AMIT WHERE ROWNUM < 10
                /             
       199 dummy1               dummy2
       200 dummy1               dummy2
       201 dummy1               dummy2
       202 dummy1               dummy2
       203 dummy1               dummy2
       204 dummy1               dummy2
       205 dummy1               dummy2
       206 dummy1               dummy2
       207 dummy1               dummy2

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                       13
parse time elapsed                                                 14
parse count (total)                                                 153
parse count (hard)                                                 74
parse count (failures)                                              2
parse count (describe)                                             0

SQL> SELECT * FROM AMIT WHERE ROWNUM < 10
                 /
       199 dummy1               dummy2
       200 dummy1               dummy2
       201 dummy1               dummy2
       202 dummy1               dummy2
       203 dummy1               dummy2
       204 dummy1               dummy2
       205 dummy1               dummy2
       206 dummy1               dummy2
       207 dummy1               dummy2

9 rows selected.

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                          13
parse time elapsed                                                    14
parse count (total)                                                    155
parse count (hard)                                                    74
parse count (failures)                                                2
parse count (describe)                                               0

As we can see we ran above query twice and its not going for a hard parse. Its taking the existing execution plan from shared pool. PFB :-

From above , its clear that sql is already present in shared pool and whenever you execute this sql, it will go for a soft parse.

Now we remove this sql details from shared pool

SQL>  select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9ag79yf9mmh3p';

ADDRESS          HASH_VALUE

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

000000015EF29F38 2470035573

SQL> exec DBMS_SHARED_POOL.PURGE ('000000015EF29F38,2470035573','C');

PL/SQL procedure successfully completed.

As we can see from above, sql details deleted from Shared pool.

Now we will again check for the same query

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                     14
parse time elapsed                                               19
parse count (total)                                               163
parse count (hard)                                               80
parse count (failures)                                          2
parse count (describe)                                         0

SQL> SELECT * FROM AMIT WHERE ROWNUM < 10
                /
       199 dummy1               dummy2
       200 dummy1               dummy2
       201 dummy1               dummy2
       202 dummy1               dummy2
       203 dummy1               dummy2
       204 dummy1               dummy2
       205 dummy1               dummy2
       206 dummy1               dummy2
       207 dummy1               dummy2

9 rows selected.

SQL> SELECT a.name
        ,b.value
  FROM   v$statname a
        ,v$mystat b
  WHERE a.statistic# = b.statistic#
  AND   a.name like 'parse%';  2    3    4    5    6

parse time cpu                                                           14
parse time elapsed                                                     19
parse count (total)                                                     164
parse count (hard)                                                     81
parse count (failures)                                                 2
parse count (describe)                                                0

6 rows selected.-

So we can see now it goes for a hard parse . 

I hope this article helped.

Regards,

Amit Rath