Explore Oracle Stuff: April 2025

Thursday, April 17, 2025

How to create standby database using OEM

Thank you very much for your interest about this topic.

1. Are you a lazy DBA?

2. Do you understand architectural concept of physical standby ?

3. Passionate about OEM?

If all the answers are YES then you have landed in right place. I will share my experience and I believe after going through this post you will definitely try this feature in non-prod first.

Pre-requisite:

Create a named credential from OEM console for oracle user .If that is not already present create and validate login like below.

Under setup>security>named credential

Fill up necessary details and save it.

For existing named credential for oracle

Search for Host credential for oracle and validate login for standby host

Create a named credential from OEM console for asmsnmp user if that is not already present and validate login like below. This login will be used to find diskgroup and whether it has enough space to clone.

Similarly, check named credential for sys login in the database as rman clone will happen using that.

Make sure you have password file and your remote sys login as sysdba is working.If you dont have password .

Now you are ready for actual fun.

Standby creation steps:-

From the database home page click on

Availibility>Add Standby Database

Select 1st option as shown below Click next button

In this page just select named credential for oracle user and click next button.You can see it will create standby redo log file in your primary database

This page is very important.So pay your attention and provide correct hostname and oracle home location in standby database server.Select your named credential for oracle user.Click next button

In this page select named credential created for ASM in pre-requisite step and click next button

I have not changed anything and always select default GI listner.Click Nect button

This page is also important as we provide 2 input database unique name and target name.I recommend using same as instance_name.During clone all datafiles will be copied as +DATA/dbuniquename. I recommend using exiting net service name ,though that is not selected here.Click on next button

Finally review page appears and check every detail before you click on finish button

You will see a job has been created like below

Now,next question comes here how will you monitor progress from the server.We dont like this GUI we are black screen guy.

Okay,In primary database server,yes you here this right you do ps -ef | grep rman

you will find similar processes

oracle 45667 45665 0 12:14 ? 00:00:00 /u01/app/odaorahome/oracle/em/agentb/agent_13.4.0.0.0/perl/bin/perl - STANDBY_NO_RECOVERY N

oracle 45694 45667 0 12:14 ? 00:00:00 sh -c /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_18/bin/rman >/tmp/20eFwqFYQE/3OSural1km

tail output of that file and you can see rman script running to create standby database like below

RMAN>

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.06.00.00 in TARGET database is not current

PL/SQL package SYS.DBMS_RCVMAN version 19.06.00.00 in TARGET database is not current

connected to target database: CISPREP9 (DBID=3279006222)

RMAN>

connected to auxiliary database: CISPREP9 (not mounted)

RMAN>

echo set on

RMAN> run {

2> SQL CLONE "ALTER SYSTEM SET standby_file_management = ''MANUAL''";

3> allocate channel tgt1 type disk;

4> allocate channel tgt2 type disk;

5> allocate channel tgt3 type disk;

6> allocate channel tgt4 type disk;

7> allocate channel tgt5 type disk;

8> allocate channel tgt6 type disk;

9> allocate channel tgt7 type disk;

10> allocate channel tgt8 type disk;

11> allocate auxiliary channel dup1 type disk;

12> DUPLICATE TARGET DATABASE FOR STANDBY

13> FROM ACTIVE DATABASE

14> ;

15> SQL CLONE "ALTER SYSTEM SET standby_file_management = ''AUTO''";

16> }

AT the end you can see similar job successful from job console also

Feel free to ask questions. I will be happy to answer.

I hope you have learned something new

Saturday, April 5, 2025

How to run SQL tuning advisor when SQL is aged out of cursor cache

Problem statement:Application team reported one of the application query was taking time and you were reported.You got the sql_id either from awr/ash report which exactly matches with the query provided by application team.Now you analyze execution plan and tends to run SQL tuning advisor to see what improvement options it can offer.You tried to run tuning advisor manually and you get the error message saying The SQL has aged out of the cursor cache

Lets see how we can resolve this issue

Reproduce the case:

lets take a random sql_id and try to find whether it is in cursor cache.Simple query which can be used below

SQL> select sql_id from v$sql where sql_id='ajq0rjdv5p34z';

SQL_ID
-------------
ajq0rjdv5p34z

There are various way to run SQL tuning advisor calling set of PL/SQL code,from OEM and script.

Out of these I like oracle supplied script in case you dont have OEM license and remembering PL/SQL code is not an option for you.

execute script from $ORACLE_HOME/rdbms/admin/sqltrpt.sql which asks sql_id as input parameter but before that it will show 15 most expensive SQL in cursor cahe.It also lists out our intended SQL highlighted

Don't you think this is amazing feature....

15 Most expensive SQL in the cursor cache

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID ELAPSED

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

SQL_TEXT_FRAGMENT

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

4xm1ruvkx3awx 5,916.14

DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM

1fb9p2aq7dvbg 3,547.03

INSERT INTO CI_BILL

ajq0rjdv5p34z 2,810.85

select billcycles0_.BILL_CYC_CD as col_0_0_, billcycles

b6usrg82hwsa3 2,482.74

It immediately list outs recommendation like below

1- SQL Profile Finding (see explain plans section below)

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

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 79.46%)

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

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_37088',

task_owner => 'SYS', replace => TRUE);

Validation results

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

The SQL profile was tested by executing both its plan and the original plan

and measuring their respective execution statistics. A plan may have been

only partially executed if the other could be run to completion in less time

Original Plan With SQL Profile % Improved

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

Completion Status: COMPLETE COMPLETE

Elapsed Time (s): .000912 .000124 86.4 %

CPU Time (s): .000879 .000096 89.07 %

User I/O Time (s): 0 0

Buffer Gets: 224 46 79.46 %

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Rows Processed: 1 1

Fetches: 1 1

Executions: 1 1

Notes

-----

1. Statistics for the original plan were averaged over 10 executions.

2. Statistics for the SQL profile plan were averaged over 10 executions.

Now I will flush shared_pool so that sql gets aged out of cursor cache.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ajq0rjdv5p34z';

ADDRESS HASH_VALUE

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

0000000181BF7E68 1985645727

1 row selected.

SQL> exec DBMS_SHARED_POOL.PURGE('0000000181BF7E68,1985645727','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ajq0rjdv5p34z';

no rows selected

Now I am executing again

Specify the Sql id

~~~~~~~~~~~~~~~~~~

Enter value for sqlid: ajq0rjdv5p34z

Sql Id specified: ajq0rjdv5p34z

Tune the sql

~~~~~~~~~~~~

ERROR: statement is not in the cursor cache or the workload repository.

Execute the statement and try again

Get the awr snap_id like below

SQL> select snap_id from dba_hist_sqlstat where sql_id='ajq0rjdv5p34z';

SNAP_ID

----------

671

672

Then, you can create a tuning task using the DBMS_SQLTUNE.CREATE_TUNING_TASK procedure, specifying the begin_snap and end_snap values, as well as the SQL ID of the statement to be tuned.

set serveroutput on

declare

l_sql_tune_task_id varchar2(100);

begin

l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

begin_snap => 671,

end_snap => 672,

sql_id => 'ajq0rjdv5p34z',

scope => dbms_sqltune.scope_comprehensive,

time_limit => 10800,

task_name => 'SQLTUNE',

description => 'task description');

dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

BEGIN

DBMS_SQLTUNE.execute_tuning_task(task_name => 'SQLTUNE');

END;

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('SQLTUNE') AS recommendations FROM dual;

Hurray! now you got the same result

Recommendation (estimated benefit: 79.47%)

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

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTUNE',

task_owner => 'SYS', replace => TRUE);

Validation results

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

The SQL profile was tested by executing both its plan and the original plan

and measuring their respective execution statistics. A plan may have been

only partially executed if the other could be run to completion in less time.

Original Plan With SQL Profile % Improved

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

Completion Status: COMPLETE COMPLETE

Elapsed Time (s): .001606 .000196 87.79 %

CPU Time (s): .001658 .000152 90.83 %

User I/O Time (s): 0 0

Buffer Gets: 224 46 79.46 %

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Rows Processed: 1 1

Fetches: 1 1

Executions: 1 1

So save this set of statement as a .sql file like bwlow and try if your sql is aged out of cursor cache..

set serveroutput on

select snap_id from dba_hist_sqlstat where sql_id='&&sql_id';

declare

l_sql_tune_task_id varchar2(100);

begin

l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

begin_snap => &begin_snap,

end_snap => &end_snap,

sql_id => '&&sql_id',

scope => dbms_sqltune.scope_comprehensive,

time_limit => 10800,

task_name => '&&Tuning_task_name',

description => 'task description');

dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

BEGIN

DBMS_SQLTUNE.execute_tuning_task(task_name => '&&Tuning_task_name');

END;

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('&&Tuning_task_name') AS recommendations FROM dual;

I hope you have learnt something new...

Happy learning...

Please ask question/comment if you still have any question

Thursday, April 3, 2025

Importance of bind variable in SQL code

Thank you very much for landing up here.In this post I will explain negative impact of using literals instead of bind variables and how that creates SQL tuning more challenging.

Problem statement: Recently I have faced a SQL query performance issue which I would like to share with you.Query runs well when we execute manually but takes lot of time from application user interface.Also worst part is application is not using bind variable which is highlighted at the end of the SQL statement below.This is more problematic as every time it will create a new SQL_ID and we can not stabilize the plan using SQL profile or plan baseline.In addition They use global temporary table whose data is visible only for that particular session.

Gather and Analyze:

Below is the SQL text

SELECT X.ENDTIME,
X.EXPORTHISTORYKEY,
X.EXTERNALSYSTEMALTERNATEKEY,
X.FILEINVENTORYKEY,
X.INTERVALTOLERANCE,

       X.READINGGROUPALTERNATEKEY,
       X.READINGGROUPKEY,
       X.READINGGROUPTYPE,
       X.READINGSOURCEID,
       X.RECEIVEDTIME,
       X.SPCNODEID,
       X.STARTTIME,
       X.STATUSID,
       X.TIMETOLERANCE

FROM V_READINGGROUP_RGSPC X

INNER JOIN T$_NUMBER_LIST READINGGROUPTYPE#

ON READINGGROUPTYPE#.NLISTVALUE = X.READINGGROUPTYPE

AND READINGGROUPTYPE#.NLISTNAME = 'READINGGROUPTYPE#'

WHERE (1 = 1)

AND x.SPCNODEID = SYS_CONTEXT ('CTX_ITRONADM', 'SPCNODEID#')

AND (ReceivedTime >

TO_TIMESTAMP (' 2024/09/21 16:05:26.000', 'YYYY/MM/DD HH24:MI:SS.FF3'))

Lets deep dive into the problem.Actually V_READINGGROUP_RGSPC is a view and T$_NUMBER_LIST READINGGROUPTYPE# is a global temporary table.Lets get the script of the view .I assume you know how to extract DDL using DBM_METADATA.GET_DDL function.

CREATE OR REPLACE FORCE VIEW ITRONADM.V_READINGGROUP_RGSPC

(

READINGGROUPALTERNATEKEY,

SPCNODEID,

STARTTIME,

ENDTIME,

TIMETOLERANCE,

INTERVALTOLERANCE,

STATUSID,

READINGGROUPKEY,

RECEIVEDTIME,

EXTERNALSYSTEMALTERNATEKEY,

READINGGROUPTYPE,

READINGSOURCEID,

FILEINVENTORYKEY,

EXPORTHISTORYKEY

)

BEQUEATH DEFINER

SELECT rgSpc.ReadingGroupAlternateKey,

rgSpc.SpcNodeID,

rgSpc.StartTime,

rgSpc.EndTime,

rgSpc.TimeTolerance,

rgSpc.IntervalTolerance,

rgSpc.StatusID,

rg.ReadingGroupKey,

rg.ReceivedTime,

rg.ExternalSystemAlternateKey,

rg.ReadingGroupType,

rg.ReadingSourceID,

rg.FileInventoryKey,

rgSpc.ExportHistoryKey

FROM ReadingGroup rg

INNER JOIN ReadingGroupSpc rgSpc

ON rg.ReadingGroupAlternateKey = rgSpc.ReadingGroupAlternateKey;

Now ReadingGroup is relatively small table of size 3.25 GB and ReadingGroupSpc is a partitioned table of size 240 GB.

Lets focus on the execution plan

PLAN_TABLE_OUTPUT

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

SQL_ID 3c8c07fgmr1gu, child number 0

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

Plan hash value: 2768073067

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

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

| 0 | SELECT STATEMENT | | | | 1070 (100)| | | |

| 1 | NESTED LOOPS | | 2 | 588 | 1070 (0)| 00:00:01 | | |

| 2 | NESTED LOOPS | | 2 | 588 | 1070 (0)| 00:00:01 | | |

| 3 | NESTED LOOPS | | 2 | 516 | 5 (0)| 00:00:01 | | |

| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| READINGGROUP | 2 | 86 | 4 (0)| 00:00:01 | | |

|* 5 | INDEX RANGE SCAN | IX3_READINGGROUP | 2 | | 2 (0)| 00:00:01 | | |

|* 6 | INDEX RANGE SCAN | PK_T$_NUMBER_LIST | 1 | 215 | 1 (0)| 00:00:01 | | |

| 7 | PARTITION RANGE ALL | | 1 | | 532 (0)| 00:00:01 | 1 | 443 |

| 8 | PARTITION HASH SINGLE | | 1 | | 532 (0)| 00:00:01 | KEY | KEY |

|* 9 | INDEX RANGE SCAN | IX_RDGRPSPC_SPCNDEID_RGAK_SE | 1 | | 532 (0)| 00:00:01 | | |

| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | READINGGROUPSPC | 1 | 36 | 532 (0)| 00:00:01 | 1 | 1 |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

OPT_PARAM('_px_adaptive_dist_method' 'off')

OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

OPT_PARAM('optimizer_index_cost_adj' 60)

ALL_ROWS

OUTLINE_LEAF(@"SEL$9BA1C343")

MERGE(@"SEL$73112EEC" >"SEL$4")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$73112EEC")

MERGE(@"SEL$64EAE176" >"SEL$1")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$64EAE176")

MERGE(@"SEL$2" >"SEL$3")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$2")

INDEX_RS_ASC(@"SEL$9BA1C343" "RG"@"SEL$2" ("READINGGROUP"."RECEIVEDTIME"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9BA1C343" "RG"@"SEL$2")

INDEX(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1" ("T$_NUMBER_LIST"."NLISTNAME" "T$_NUMBER_LIST"."NLISTVALUE"

"T$_NUMBER_LIST"."ROWNUMBER"))

INDEX(@"SEL$9BA1C343" "RGSPC"@"SEL$2" ("READINGGROUPSPC"."SPCNODEID" "READINGGROUPSPC"."READINGGROUPALTERNATEKEY"

"READINGGROUPSPC"."STARTTIME" "READINGGROUPSPC"."ENDTIME"))

LEADING(@"SEL$9BA1C343" "RG"@"SEL$2" "READINGGROUPTYPE#"@"SEL$1" "RGSPC"@"SEL$2")

USE_NL(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1")

USE_NL(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

NLJ_BATCHING(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

5 - access("RG"."RECEIVEDTIME">

TIMESTAMP' 2024-09-21 16:05:26.000000000')

6 - access("READINGGROUPTYPE#"."NLISTNAME"='READINGGROUPTYPE#' AND "READINGGROUPTYPE#"."NLISTVALUE"="RG"."READINGGROUPTYPE")

9 - access("RGSPC"."SPCNODEID"=TO_NUMBER(SYS_CONTEXT('CTX_ITRONADM','SPCNODEID#')) AND "RG"."READINGGROUPALTERNATEKEY"="RGSPC"."READINGGROUPALTERNATEKEY")

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

80 rows selected.

Please see the line 5,6 and 9 where access predicate has been used and index was used to fetch rows from corresponding tables.

For line no 5 it has used index IX3_READINGGROUP based on column receivedtime under readinggroup table
For line no 6 it has used index PK_T$_NUMBER_LIST based on column nlisttime,nlistvalue,rownumber
For line no 9 it has used index IX_RDGRPSPC_SPCNDEID_RGAK_SE based on column SPCNODEID, READINGGROUPALTERNATEKEY, STARTTIME, ENDTIME under READINGGROUPSPC

This query was taking almost 20 minutes in production but less than a second in QA environment.

Lets find the execution plan in QA to compare

PLAN_TABLE_OUTPUT --------------------------------------------

SQL_ID 7uyxr4f6xdvh4, child number 0

Plan hash value: 3101949422

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

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

| 0 | SELECT STATEMENT | | | | 1782 (100)| | | |

|* 1 | HASH JOIN | | 2 | 588 | 1782 (0)| 00:00:01 | | |

|* 2 | HASH JOIN | | 2 | 516 | 9 (0)| 00:00:01 | | |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | READINGGROUP | 2 | 86 | 7 (0)| 00:00:01 | | |

|* 4 | INDEX RANGE SCAN | IX3_READINGGROUP | 2 | | 4 (0)| 00:00:01 | | |

|* 5 | TABLE ACCESS FULL | T$_NUMBER_LIST | 1 | 215 | 1 (0)| 00:00:01 | | |

| 6 | PARTITION RANGE ALL | | 1 | 36 | 887 (0)| 00:00:01 | 1 | 443 |

| 7 | PARTITION HASH SINGLE | | 1 | 36 | 887 (0)| 00:00:01 | KEY | KEY |

| 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| READINGGROUPSPC | 1 | 36 | 887 (0)| 00:00:01 | | |

|* 9 | INDEX RANGE SCAN | IX_RDGRPSPC_SPCNDEID_RGAK_SE | 1 | | 886 (0)| 00:00:01 | | |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

INDEX_RS_ASC(@"SEL$9BA1C343" "RGSPC"@"SEL$2" ("READINGGROUPSPC"."SPCNODEID" "READINGGROUPSPC"."READINGGROUPALTERNATEKEY"

"READINGGROUPSPC"."STARTTIME" "READINGGROUPSPC"."ENDTIME"))

USE_HASH(@"SEL$9BA1C343" "RGSPC"@"SEL$2")

FULL(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1")

USE_HASH(@"SEL$9BA1C343" "READINGGROUPTYPE#"@"SEL$1")

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

ALL_ROWS

OUTLINE_LEAF(@"SEL$9BA1C343")

MERGE(@"SEL$73112EEC" >"SEL$4")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$73112EEC")

MERGE(@"SEL$64EAE176" >"SEL$1")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$64EAE176")

MERGE(@"SEL$2" >"SEL$3")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$2")

INDEX_RS_ASC(@"SEL$9BA1C343" "RG"@"SEL$2" ("READINGGROUP"."RECEIVEDTIME"))

BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9BA1C343" "RG"@"SEL$2")

LEADING(@"SEL$9BA1C343" "RG"@"SEL$2" "READINGGROUPTYPE#"@"SEL$1" "RGSPC"@"SEL$2")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 – access(“RG”.”READINGGROUPALTERNATEKEY”=”RGSPC”.”READINGGROUPALTERNATEKEY”)

2 – access(“READINGGROUPTYPE#”.”NLISTVALUE”=”RG”.”READINGGROUPTYPE”)

4 – access(“RG”.”RECEIVEDTIME”>TIMESTAMP’ 2025-02-09 20:54:30.000000000’)

5 – filter(“READINGGROUPTYPE#”.”NLISTNAME”=’READINGGROUPTYPE#’)

9 – access(“RGSPC”.”SPCNODEID”=TO_NUMBER(SYS_CONTEXT(‘CTX_ITRONADM’,’SPCNODEID#’)))

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- this is an adaptive plan

Please see that in this plan temporary table has been accessed using full table scan and then filter

has been applied.Also, this plan is using hash join instead of nested loop join.

Veryt first thought came in mind that may be stats are not updated in production which leads to an unoptimized plan.I have checked stats in production and qa and both are updated as we have scheduled stats job runs over weekend.

Then thought process starts.What else is different as I know prod and QA has same kind of infrastructure.My guess was this could be a difference in optimizer.

If you look carefully outline section of both the plan

from 1st execution plan

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

OPT_PARAM('_px_adaptive_dist_method' 'off')

OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

OPT_PARAM('optimizer_index_cost_adj' 60)

From 2nd execution plan

OPTIMIZER_FEATURES_ENABLE('19.1.0')

DB_VERSION('19.1.0')

OPT_PARAM('_optimizer_cost_based_transformation' 'off')

Can you see that all hidden paraemeters with the keyword adaptive?

This is big indication that adaptive features are not being used in production and it is being used in QA

as end of outline we can see

- this is an adaptive plan

When I check parameter optimizer_adaptive_plans that is set to true in QA which leads to better execution plan but in prod it is set to false.

I changed it in QA to reproduce the case and it started perfoming slowly and giving same execution plan.Finally it was changed in prod and issue was resolved.

Conclusion:I hope now you have understood how critical optimizer_adaptive_plans and learnt something new.

Happy learning!!

Explore Oracle Stuff

Thursday, April 17, 2025

How to create standby database using OEM

Saturday, April 5, 2025

How to run SQL tuning advisor when SQL is aged out of cursor cache

Thursday, April 3, 2025

Importance of bind variable in SQL code

When SQL plan baseline is not used

Report Abuse

Labels