High CPU Usage in Oracle Active Data Guard 19c

 Recently i noticed in an Oracle 19.16 database, the standby database server CPU usage went extremely high, we noticed it was the BEQ LOCAL CONNECTION PROCESS is using high cpu, and it was not any application which was running any workload, not even a single application connection was connected to this standby database. Please note this bug was hit even when PDB was mounted while CDB was read only mode. Hence make sure you have the patch 33359773 available for you for the bug id 33359773. You must prove to the Oracle Development that you have hit thig bug. 

Kindly provide the following to the Oracle support for your issue to get the above bug fix and prove that the symptoms match the problem : 

1) alertlog and trace files after you applied the second diag patch.

2)a. Turn on the diagnostics fix

alter system set events '10315 trace name context forever, level 3';

b. Run the test which caused the issue

c. Collect the trace generated

d. Collect AWR repository dump covering the this test period.

e. Collect OSWatcher log covering the this test period.

f. Collect TFA:

$tfactl diagcollect -srdc dbrman

g. Collect the pstack log

watch -n 2 'pstack <target process ospid> >> pstack.txt; echo -e "\n" >> pstack.txt'

h. Turn off the diagnostics fix

alter system set events '10315 trace name context off';

When you run the top command in linux you would see below processes using the high cpu :

So when you ps -ef   grep pid you would see :

 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Types of Deadlocks in Oracle Database

 

In a curious case recently, we faced a deadlock in Oracle RAC, so in this post i thought to share the different types of deadlock that can occur in Oracle database.

Deadlock types in Oracle :

Here are some common deadlock types:

1. TX deadlock in Exclusive(X) mode

trace shows:

Global Wait-For-Graph(WFG) at ddTS[0.170] :

BLOCKED 0x8aafb0ec 5 wq 2 cvtops x1 TX 0x320001.0x121c97 [99000-0001-00000002]0

BLOCKER 0x8aafafec 5 wq 1 cvtops x8 TX 0x320001.0x121c97 [9A000-0001-00000002]0

BLOCKED 0x8acb55e4 5 wq 2 cvtops x1 TX 0x430003.0x3843f [9A000-0001-00000002] 0

BLOCKER 0x8acb54e4 5 wq 1 cvtops x8 TX 0x430003.0x3843f [99000-0001-00000002] 0

These values are:

<BLOCKED|BLOCKER> <lockp> <cvt|held mode> <which q> <convert options> <res name - type id1.id2> [deadlock-level-trace] <node> 

Above graph shows the deadlock is related with 2 TX lock in mode 5 (exclusive) which happens on instance 1 (instance# starts from 0)

Above deadlock means two sessions involved in TX-0x320001-0x121c97 and TX-0x430003-0x3843f forms a deadlock, both sessions are from instance 1.

This is a typical application transaction TX enqueue lock, usually caused by SQL commit sequence and high concurrency. To avoid such deadlock, application code and logic need to be modified.

The application and SQL involved in the deadlock can be found in lmd0 or foreground trace (check all instances). Search for: "user session for deadlock lock" section to find out the SQL involved in the deadlock. For example:

user session for deadlock lock 0x8aafb0ec:

...

  current SQL:

  update test set OWNER='APPS' where rownum < 2

 

2. TX deadlock in Share(S) mode

trace shows:

Global Wait-For-Graph(WFG) at ddTS[0.b7] :

BLOCKED 0x2310e8918 3 [0x731000e][0x56268],[TX] [F1000-0001-0000000F] 0

BLOCKER 0x2310e87c8 3 [0x731000e][0x56268],[TX] [19F000-0001-00000011] 0

BLOCKED 0x2310e3c50 3 [0x72a0023][0x530d7],[TX] [19F000-0001-00000011] 0

BLOCKER 0x2310e7e80 3 [0x72a0023][0x530d7],[TX] [F1000-0001-0000000F] 0 

Corresponding to:

<BLOCKED|BLOCKER> <lockp> <cvt|held mode> <res name> <pid|did|txn_id> <node> 

mode 3 is shared lock

The causes for TX deadlock in S mode wait can be:

a. ITL contention, eg: INITRANS setting for the object is too small, it can not handle the number of concurrent transactions.

The solution is to increase INITRANS setting for the object involved in the deadlock using "alter table" or "alter index" command

The SQL involved in the deadlock can be found in lmd0 or client trace. The object involved in the SQL should be checked including table and its associated index.

b. If the object involved is an unique key index, the wait could be caused by uniqueness validation. Application needs to be checked to avoid unique key violation.

c. If the object involved has a bitmap index, then the bitmap index should be dropped to accommodate concurrent DML. Please refer to Document 1496403.1 ORA-60 DEADLOCK DUE TO BITMAP INDEX IN RAC.

3. TM deadlock

trace shows:

Global Wait-For-Graph(WFG) at ddTS[0.1] :

BLOCKED 0x7000003ccbf4798 3 wq 2 cvtops x1 TM 0x1cbde.0x0 [1004-004D-00000003] 0

BLOCKER 0x7000003d0bf9ad8 3 wq 1 cvtops x1 TM 0x1cbde.0x0 [200A-00AC-00000019] 1

BLOCKED 0x7000003d0bfcf88 2 wq 2 cvtops x1 TM 0x1cc77.0x0 [200A-00AC-00000019] 1

BLOCKER 0x7000003cc338e88 2 wq 2 cvtops x1 TM 0x1cc77.0x0 [2006-0063-00000055] 1

BLOCKED 0x7000003cc338e88 3 wq 2 cvtops x1 TM 0x1cc77.0x0 [2006-0063-00000055] 1

BLOCKER 0x7000003c879f9c0 3 wq 1 cvtops x1 TM 0x1cc77.0x0 [2006-0063-00000020] 1

BLOCKED 0x7000003c87978a8 2 wq 2 cvtops x1 TM 0x1cbde.0x0 [2006-0063-00000020] 1

BLOCKER 0x7000003ccbf4798 2 wq 2 cvtops x1 TM 0x1cbde.0x0 [1004-004D-00000003] 0

The object involved here are 0x1cbde and 0x1cc77, convert the hex number to decimal, they are the object_id for the tables involved in above deadlock

The deadlock is usually caused by missing index for foreign key constraint, refer to Document 473124.1 - "Frequent GES: Potential Blocker (Pid=nnnn) On Resource TM-<id1>-<id2>" for more information. Check dba_constraints and dba_index to verify if foreign key index is missing. Also refer to Document 1019527.6 Script to Check for Foreign Key Locking Issues for a Specific User which will generate a report for all problem objects.

The solution is to create index for every foreign key constraint.

4. Single resource deadlock for TX , TM, IV or LB

trace shows:

Single resource deadlock: blocking enqueue which blocks itself, f 1

Granted global enqueue 0xd078cec0

...

resname :[0x2001f][0x1a96c3],[TX]

or

resname :[0x00001432][0x0],[TM]

or

resname : [0xbb7cc5db][0x82d0d4b5],[IV]

or

resname : [0x5e582fb9][0xa216c7af],[LB]

a. For single resource deadlock on TX enqueue, often it is caused by using autonomous transaction in stored procedure or PL/SQL. It is a known issue that the use of autonomous transactions is vulnerable to deadlocks. Please check out Oracle® Database Concepts  Overview of Autonomous Transactions for detail explanation. Since AUTONOMOUS transaction has been used in the stored procedure, the system would consider any DML statement under this transaction as a separate one (commit/rollback won't affect the parent), and this would cause conflict if the same row is involved in the parent transaction (INSERT, UPDATE or DELETE), and hence deadlock is reported rightly. Usually the SQL involved in the deadlock is called from a stored procedure or PL/SQL with the following line:

PRAGMA AUTONOMOUS_TRANSACTION;

To avoid such deadlock, please remove the autonomous transaction in the application code.

b. If there is no autonomous_transaction involved, please check out Document 6145177.8, it can also be caused by Bug 6145177 - Single resource deadlock with a zero DID

c. For single resource deadlock on TM enqueue, missing foreign key index is often the cause, please check case 3 for the solution.

d. For single resource deadlock type IV (Instance Validation), refer to Document 973178.1, as mentioned in Bug 8843816, this message can be ignored. Bug 8843816 has been fixed in 11.1.

e. For single resource deadlock on LB (Library Cache Lock) with DID 0, if it is on external table disable stats on external table as below:  alter session set "_px_external_table_default_stats" = false; Reference Bug 21204478

5. LB deadlock

Global Wait-For-Graph(WFG) at ddTS[0.390] :

BLOCKED 0x3bc3e1b48 5 wq 2 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [7B000-0002-00006C9D] 1

BLOCKER 0x3bc2efad0 5 wq 1 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [48000-0001-000069FE] 0

BLOCKED 0x3bc2ed1c0 3 wq 2 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [48000-0001-000069FE] 0

BLOCKER 0x3bc2dc648 3 wq 2 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [34000-0001-0000095E] 0

BLOCKED 0x3bc2dc648 5 wq 2 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [34000-0001-0000095E] 0

BLOCKER 0x3bc2dbbb0 5 wq 1 cvtops x0 [0x415d0160][0xca28e8cf],[LB] [76000-0002-000074D4] 1

BLOCKED 0x3bc2ef830 3 wq 2 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [76000-0002-000074D4] 1

BLOCKER 0x3bc3e1b48 3 wq 2 cvtops x0 [0xd2703c03][0x545a14a5],[LB] [7B000-0002-00006C9D] 1

LB lock type refers to library cache lock. This type of deadlock is usually caused by a bug. 

For example: Bug 6475688  Concurrent rewrite and on-commit refresh can deadlock (library cache pin <--> lock) Document 6475688.8

The bug has been fixed in 11.1.0.7 and 11.2. Please apply patch accordingly.

6. Known Issues

For other deadlock type or known issues related to dead lock, refer to Document 554567.1 Summary Of Bugs Which Could Cause Deadlock In RAC Environment

7. Further Diagnosis

Please collect the following information for further diagnosis:

a. alert log lmd0, and trace mentioned in the alert log from all instances.

b. set the following event to collect systemstate dump ONLY if the information in trace files are insufficient:

alter system set events '60 trace name systemstate level 258';

It will cause a systemstate dump to be generated whenever a deadlock is reported. If there are constant deadlocks, it could cause a lot of trace files being generated, monitor the system carefully.

To turn off the trace:

alter system set events '60 trace name context off';

8. Deadlock Parser Tool (Enterprise Manager)

EM 12c, EM 13c: Using The Deadlock Parser Tool For Gathering EM Repository Deadlock Information Document:2222769.1

How to open a database from read write mode to mount status

 SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      READ WRITE

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01100: database already mounted

# So here we get the error database is already mounted, in this case we need to ensure the database is shutdown first and then only we can mount the database.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

# So lets see how we can do it

SQL> startup mount

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             306184192 bytes

Database Buffers          754974720 bytes

Redo Buffers                2887680 bytes

Database mounted.

SQL> SQL>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      MOUNTED

# With this we know how we can mount the database from open state.

Recovering control file from Oracle database

 RMAN> backup database;

Starting backup at 28-APR-23

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf

channel ORA_DISK_1: starting piece 1 at 28-APR-23

channel ORA_DISK_1: finished piece 1 at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_04_28/o1_mf_nnndf_TAG20230428T221502_l4qy0gcj_.bkp tag=TAG20230428T221502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 28-APR-23

channel ORA_DISK_1: finished piece 1 at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_04_28/o1_mf_nnndf_TAG20230428T221502_l4qy2sjf_.bkp tag=TAG20230428T221502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 28-APR-23

channel ORA_DISK_1: finished piece 1 at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_04_28/o1_mf_nnndf_TAG20230428T221502_l4qy3ll3_.bkp tag=TAG20230428T221502 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 28-APR-23

Starting Control File and SPFILE Autobackup at 28-APR-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_04_28/o1_mf_s_1135376217_l4qy41rz_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 28-APR-23

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/ORCL/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 fast_recovery_area/ORCL/contro

                                                 l02.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

diagnostics_control                  string      IGNORE

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rm /u01/app/oracle/oradata/ORCL/control01.ctl

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:19:23 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> shu abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             306184192 bytes

Database Buffers          754974720 bytes

Redo Buffers                2887680 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Apr 28 22:22:28 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_04_28/o1_mf_s_1135376217_l4qy41rz_.bkp';

Starting restore at 28-APR-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=422 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ORCL/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

Finished restore at 28-APR-23

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 04/28/2023 22:23:00

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 04/28/2023 22:23:08

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:23:12 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using BACKUP CONTROLFILE ;

ORA-00279: change 4671729 generated at 04/28/2023 22:15:26 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_7_l4qy8174

_.arc

ORA-00280: change 4671729 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 4671801 generated at 04/28/2023 22:19:05 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_8_l4qy8477

_.arc

ORA-00280: change 4671801 for thread 1 is in sequence #8

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_7_l4qy817

4_.arc' no longer needed for this recovery

ORA-00279: change 4671815 generated at 04/28/2023 22:19:08 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_9_l4qy877k

_.arc

ORA-00280: change 4671815 for thread 1 is in sequence #9

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_8_l4qy847

7_.arc' no longer needed for this recovery

ORA-00279: change 4671823 generated at 04/28/2023 22:19:11 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_10_l4qy8b5

x_.arc

ORA-00280: change 4671823 for thread 1 is in sequence #10

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_9_l4qy877

k_.arc' no longer needed for this recovery

ORA-00279: change 4671831 generated at 04/28/2023 22:19:14 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_11_%u_.arc

ORA-00280: change 4671831 for thread 1 is in sequence #11

ORA-00278: log file

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_10_l4qy8b

5x_.arc' no longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_04_28/o1_mf_1_11_%u_.ar

c'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

SQL>

SQL>

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Apr 28 22:23:52 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1644501746, not open)

RMAN> recover database;

Starting recover at 28-APR-23

Starting implicit crosscheck backup at 28-APR-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=430 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 28-APR-23

Starting implicit crosscheck copy at 28-APR-23

using channel ORA_DISK_1

Finished implicit crosscheck copy at 28-APR-23

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_04_28/o1_mf_s_1135376217_l4qy41rz_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/oradata/ORCL/redo02.log

archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=11

media recovery complete, elapsed time: 00:00:00

Finished recover at 28-APR-23

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:24:12 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

sel

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQ

SQL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      READ WRITE

Optimizer settings in 21C in Oracle

 SQL> show parameter optimizer

NAME                                 TYPE        VALUE

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

optimizer_adaptive_plans             boolean     TRUE

optimizer_adaptive_reporting_only    boolean     FALSE

optimizer_adaptive_statistics        boolean     FALSE

optimizer_capture_sql_plan_baselines boolean     FALSE

optimizer_capture_sql_quarantine     boolean     FALSE

optimizer_cross_shard_resiliency     boolean     FALSE

optimizer_dynamic_sampling           integer     2

optimizer_features_enable            string      21.1.0

optimizer_ignore_hints               boolean     FALSE

optimizer_ignore_parallel_hints      boolean     FALSE

optimizer_index_caching              integer     0

optimizer_index_cost_adj             integer     100

optimizer_inmemory_aware             boolean     TRUE

optimizer_mode                       string      ALL_ROWS

optimizer_real_time_statistics       boolean     FALSE

optimizer_secure_view_merging        boolean     TRUE

optimizer_session_type               string      NORMAL

optimizer_use_invisible_indexes      boolean     FALSE

optimizer_use_pending_statistics     boolean     FALSE

optimizer_use_sql_plan_baselines     boolean     TRUE

optimizer_use_sql_quarantine         boolean     TRUE

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Apr 28 22:17:53 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> select * from v$version;

BANNER

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

BANNER_FULL

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

BANNER_LEGACY

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

    CON_ID

----------

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

         0

BANNER

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

BANNER_FULL

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

BANNER_LEGACY

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

    CON_ID

----------