Hard Parses
If session execute the SQL statement that not present in the shared pool then Oracle need following steps to placed in memory:
1. Allocate memory for the statement from the shared pool.
2. Check the statement syntactically
3. Check if the user trying to execute the statement has the necessary rights to execute it
Note: Hard parse is expensive in both terms of CPU used and number of shared pool latch and library cache latch it needs to acquire and release.
On checking the statspack report of one of the DB, we are getting the high parse rate which consuming CPU resources.
Hard parsing causing performance issue in Oracle Database.
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 66.2 57.8
sql execute elapsed time 56.4 49.2
parse time elapsed 29.1 25.4
hard parse elapsed time 25.5 22.4
connection management call elapsed 1.5 1.1
Cause:
Application is not using bind variables to overcome the hard parsing issue.
Hard parsing is expensive, It creates shared cursor in SGA,causes library cache latch contention, caused shared pool contention.
Solution
1. Application need to relook the code and tried to use the bind variables in SQL Queries. Bind variables also reduce risk of SQL injection security issue.
2. If application is not to change it in code, then you can do it at database level by changing parameter cursor_sharing.
-- Set the cursor sharing to Force, optimizer convert forcefully as bind variables to sql queries.
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=BOTH;
--Set to exact value then optimizer use exact value and generate execution plan every time on value change.
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=BOTH;
Example of using bind variable in application
Bind Variables in Java
•Instead of:
String query = "SELECT EMPLOYEE_ID, SALARY FROM " +"EMPLOYEES WHERE EMPLOYEE_ID =" + generateNumber(MIN_EMPLOYEE_ID, MAX_EMPLOYEE_ID);
pstmt= connection.prepareStatement(query);
rs= pstmt.executeQuery();
•Change to:
String query = "SELECT EMPLOYEE_ID, SALARY FROM" +"EMPLOYEES WHERE EMPLOYEE_ID =?";
pstmt= connection.prepareStatement(query);
pstmt.setInt(1, n); rs= pstmt.executeQuery();
Bind Variable in OCI
static char *MY_SELECT = "select employee_id, salary from employees where employee_id =:EMPNO";
OCIBind*bndp1;
OCIStmt *stmthp;
ub4 emp_id;
OCIStmtPrepare2 (svchp, &stmthp, /* returned stmt handle */ errhp,/* error handle */ (const OraText *) MY_SELECT, strlen((char *) MY_SELECT), NULL, 0, /* tagging parameters:optional */ OCI_NTV_SYNTAX, OCI_DEFAULT);
Bind input parameters:
OCIBindByName(stmthp, &bndp1, errhp, (text *) ":EMPNO", -1, &(emp_id), sizeof(emp_id), SQLT_INT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
