Query execute 1st time good but 2nd time query execution take long time in Oracle

Query performs good on 1st run but from subsequent runs the query keeps executing cause performance issue

Solution:
We need to try disable different feature which is executing with query at run time like cardility functionality , statistics gather, compare plan and choose better at run time during query execution in Oracle.
Note: First try at session level then at system level you can make it.

1. Disable the optimizer_adaptive_plans parameter then try.


Show Parameter optimizer_adaptive_plans
NAME                     TYPE    VALUE
------------------------ ------- ------
optimizer_adaptive_plans boolean TRUE

For disable 
ALTER SESSION SET optimizer_adaptive_plans = FALSE;
ALTER SYSTEM SET optimizer_adaptive_plans = FALSE SCOPE=both;

For enable
ALTER SESSION SET optimizer_adaptive_plans = TRUE;
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE SCOPE=both;

2. Disable the _optimizer_use_feedback parameter then check. Hopefully this parameter help it calculate cardinality for SQL to find better execution plan and compare plan at runtime.


Check the current value
col Name for a31
col Value for a10
col Description for a31
select ksppinm "Name",ksppstvl "Value"---,ksppdesc "Description" 
from x$ksppi a, x$ksppsv b 
where a.indx=b.indx and ksppinm = '_optimizer_use_feedback';
Name                            Value
------------------------------- ----------
_optimizer_use_feedback         TRUE

Enable the cardinality feedback
alter session set "_optimizer_use_feedback" = true;
alter system set "_optimizer_use_feedback" = true scope=both;
 
Disable the cardinality feedback
alter session set "_optimizer_use_feedback" = false;
alter system set "_optimizer_use_feedback" = false scope=both;

3. Disable the _optimizer_gather_feedback parameter then check


Check the current value
col Name for a31
col Value for a10
col Description for a31
select ksppinm "Name",ksppstvl "Value"---,ksppdesc "Description" 
from x$ksppi a, x$ksppsv b 
where a.indx=b.indx and ksppinm = '_optimizer_gather_feedback';
Name                            Value
------------------------------- ----------
_optimizer_gather_feedback      TRUE

Enable the gather feedback
ALTER SYSTEM SET "_optimizer_gather_feedback" = TRUE scope=both;
ALTER SESSION SET "_optimizer_gather_feedback" = TRUE; 

Disable the gather feedback
ALTER SYSTEM SET "_optimizer_gather_feedback"=FALSE SCOPE=BOTH;
ALTER SESSION SET "_optimizer_gather_feedback"=FALSE;

4. Disable the optimizer_dynamic_sampling parameter then check. If it generate stats during runtime during query execution then it impact query performance, so need to disable,

SHOW PARAMETER optimizer_dynamic_sampling
NAME                       TYPE    VALUE
-------------------------- ------- -----
optimizer_dynamic_sampling integer 2

For DISABLE
ALTER SESSION SET optimizer_dynamic_sampling = 0;
ALTER SYSTEM SET optimizer_dynamic_sampling = 0 scope=both;

FOR ENABLE(1 - 11), all havie different option(default is 2)
ALTER SESSION SET optimizer_dynamic_sampling = 2;
ALTER SYSTEM SET optimizer_dynamic_sampling = 2 scope=both;
This entry was posted in Oracle on by .

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.