Shared pool tuning due to performance issues
In Shared pool area of SGA memory, Oracle keeps the DDL of SQL queries, PL/SQL packages, object information and etc.
For understanding the performance issue, first under stand the role of shared pool. Shared pool keep the SQL Queries in memory area which is executed by the Customer, So that on next execution of Same query we find in shared pool area to avoid extra CPU cost in parsing, generating execution plan and latches management.
Type of SQL queries:
Literal SQL
Literal SQL statement is uses literals value in SQL Statement rather than bind variables which caused to generate different executions of the statement.
-- Both statement will use different execution plan
Select id from test where id = 3;
select id from test where id = 5;
-- If you use bind variable instead of literals value you save execution plan generation cost every time(hard parse cost in SPREPORT)
Select id from test where id := :bind_variable;
Note:
Hard Parse:
When new SQL statement comes, Oracle check in shared pool area first if not find then Oracle do parsing(syntax or semantically steps) and allocate area in shared pool. Its make busy the CPU and latch gets.
Soft Parse:
When SQL statement comes, It’s available in shared pool area then use existing version of that statement is ‘soft parse’. It save CPU cost for generating new execution plan or etc.
Identical Statements
Two statement executing are identical but having different in character upper or lower case then treated as different in Oracle.
SELECT ENAME from EMP;
select ename from emp;
Sharable SQL
Two sessions issue identical SQL statements it does NOT mean that the statement is sharable. Because user is different, both user has same object.
--Scott user
SELECT ENAME from EMP;
--HR User
SELECT ENAME from EMP;
Versions of SQL Query
Two SQL Statements are identical but not shareable is treated as versions.
Note:
Cost Based Optimizer (CBO) works best when it has full statistics and when statements use literals in their predicate.
Tune the SHARED POOL
1. Execute the statement many time but parse its one.
Parameter OPEN_CURSORS act at session level for holding cursors open for total number of concurrently open cursors.
2. Avoid use of Literal SQL, use bind variable SQL to reduce cost.
You cannot avoid all literal statement but from V$SQLAREA you can identified the queries which can be converted to use bind variables.
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions 30
ORDER BY 2;
Note: Output show more than 30 occurrence of same SQL queries in shared pool which are good candidate for convert them into bind variables.
3. Cursor sharing parameter for convert at database level. For avoid literal value if you cannot modified your application then you modified at DB level or session level,
you can use cursor_sharing parameter.
-- Forcefully convert literal SQL's to bind variable.
ALTER SESSION SET cursor_sharing = FORCE;
--Default value
ALTER SESSION SET cursor_sharing = EXACT;
4. SESSION_CACHED_CURSORS parameter
When query execute Oracle search at private session cached area if exits then used. it reduce less CPU and latches benefit.
Check in AWR/Statspack report this parameter ‘session cursor cache hits’ which shows if the cursor cache is giving any benefit.
-- Increase the value of session_cached_cursor default is 50.
ALTER SESSION SET session_cached_cursors = 300;
5. SHARED_POOL_SIZE Parameter
You can check the advisory reports and control the size of shared pool according to your need.
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB", shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;
6. Pining most used SQL Queries, procedures , triggers , function in Shared pool.
Pinning in Shared pool Link
7. Sometime any ETL process gone which cause lot of insert update delete statements. So after that you can flush your shared pool
ALTER SYSTEM FLUSH SHARED_POOL;