Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Getting ‘error while loading shared libraries’ while calling oratop on version 19c …

Posted by FatDBA on April 4, 2022

Recently someone asked me, How to run oratop utility on Oracle database version that is above 12.1 ? He was using Oracle version 19.3.0.0.0 and was getting error ‘error while loading shared libraries’ when tried to call the oratop, and it was quite an obvious error as the script trying to locate a shared object file called libclntshcore.so under $ORACLE_HOME/lib directory for version 12.1, whereas it was version 19.1 and hence the shared object version was libclntshcore.so.19.1

[oracle@oracleontario ~]$ ./oratop.RDBMS_12.1_LINUX_X64 -d -f -i 3 / as sysdba
./oratop.RDBMS_12.1_LINUX_X64: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory
[oracle@oracleontario ~]$

Solution to the problem is, starting from Oracle 19c, oratop is part of Oracle supplied tool and is present under ORACLE_HOME/suptools directory, and is not version specific. Other tools that you will see along with oratop in suptools directory are orachk and tfa. Let’s call it and see if it runs!

[oracle@oracleontario ~]$ cd $ORACLE_HOME/suptools/
[oracle@oracleontario suptools]$ ls
orachk  oratop  tfa
[oracle@oracleontario suptools]$ cd oratop/
[oracle@oracleontario oratop]$ ls
oratop
[oracle@oracleontario oratop]$
[oracle@oracleontario oratop]$

[oracle@oracleontario oratop]$ ./oratop  -f -i 3 / as sysdba

oratop: Release 15.0.0 Production on Thu Mar 10 07:33:49 2022
Copyright (c) 2011, Oracle.  All rights reserved.

Connecting ..
Processing ...

Oracle 19c - Primary dixitd 07:33:45 up: 5.2h,  1 ins,    0 sn,   0 us, 1.5G sga,    0% fra,   1 er,                        0.5%db
ID CPU  %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  ASP  LAT  MBPS IOPS  R/S  W/S  LIO  GCPS %FR  PGA TEMP UTPS  UCPS  RT/X DCTR DWTR
 1   1   9.1  0.2  0.2  0.0    0    0    0    0    0    0   0.1    3    3    0    3     0  45 153M    0    0     0  110m  118    8

EVENT (C)                                                         TOTAL WAITS   TIME(s)  AVG_MS  PCT                    WAIT_CLASS
db file sequential read                                                  5175        65    12.6   32                      User I/O
DB CPU                                                                               42           21
oracle thread bootstrap                                                    81        40   503.8   20                         Other
db file scattered read                                                    593        29    50.4   15                      User I/O
external table read                                                         1        23 23470.8   12                      User I/O

ID   SID     SPID USERNAME  PROGRAM    SRV  SERVICE  PGA  SQLID/BLOCKER OPN  E/T  STA  STE  WAIT_CLASS  EVENT/*LATCH           W/T
 1    58    14884 SYS       sqlplus@o  DED  SYS$USE 1.4M           1:77 UPD 8.0s  ACT  WAI  Applicatio  enq: TX - row lock co 7.8s


[oracle@oracleontario oratop]$
[oracle@oracleontario oratop]$

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | 2 Comments »

A low resource SQL, still want it in the AWR … ‘Color Your SQL’

Posted by FatDBA on March 29, 2022

Recently one of the customer asked me about their critical SQL on a production system running on Oracle 12cR2, if there is a way to pin it in the AWR. I mean the query was already super fast, gets completed very fast and doesn’t consume any system resources, or isn’t a ‘Top SQL’. He still want to track this low resource SQL historically to check few stats, the guys was coming from an Oracle DBA background, and got the impression that the AWR only captures statements considered to be Top N (DBA_HIST_WR_CONTROL.TOPNSQL) which is by default 30. So, If you require any SQL to be always available in the top SQL, then you need to modify the default value to store those many SQLs. But this will create a AWR snapshot stress and that he don’t want.

The answer was ‘Yes’, there is a way how this can be achieved, and that’s too quite easily, and is even present since 11gR1 without making any modifications to your snapshot settings of topnsql. This is by using DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL API to pin any SQL into the AWR report as long as the SQL is in memory. So, if you mark your SQL or color it, it will be captured in every snapshot and it doesn’t have to be a TOP-N SQL to present in an AWR.

Let’s do a demo to understand how we can do that. I am going to run a test SQL to count number of rows into the table.

SQL> select count(*) from TABLE_GROWTH_MB;

  COUNT(*)
----------
      2868

SQL> select sql_id, plan_hash_value, sql_text from  v$sqlarea where sql_text like '%TABLE_GROWTH_MB%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- --------------------------------------------------
4n3x6w1vhn1k3      1134407048  select count(*) from TABLE_GROWTH_MB

Okay, so we have executed the SQL and we have its SQL_ID with us now. Lets try to use awr_sql_report_text function that displays an Automatic Workload Repository (AWR) SQL report as plain text to see if the SQL is present under the latest snapshot range.

SQL>
SQL> select output from table (dbms_workload_repository.awr_sql_report_text(l_dbid => 2592296819 , l_inst_num => 2, 
l_bid => 36708, l_eid => 36718, l_sqlid => '4n3x6w1vhn1k3'));

OUTPUT
--------------------------------------------------------------------------------
WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
FATDBACA      2592296819 FATDBACA    PRIMARY          EE      12.2.0.1.0 YES NO

Instance     Inst Num Startup Time
------------ -------- ---------------
FATDBACA2           2 26-Oct-21 23:10

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     36708 10-Mar-22 16:00:37       190       2.5
  End Snap:     36718 10-Mar-22 18:30:06       181       2.6
   Elapsed:              149.48 (mins)
   DB Time:               91.13 (mins)

SQL Summary                    DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36718

                  No data exists for this section of the report.
          -------------------------------------------------------------

SQL ID: 4n3x6w1vhn1k3          DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36718

                  No data exists for this section of the report.
          -------------------------------------------------------------

Full SQL Text

                  No data exists for this section of the report.
SQL>
SQL>
SQL>
SQL>

And the report has no data about the SQL as the SQL was pretty fast, and hardly took few milliseconds to finish and is expected as it didn’t qualified as a TOP-SQL hence found no place in the AWR report. I am now going to color the SQLID and will re-run the SQL and check if its there in AWR now or not …

-- to PIN the SQLID in question
SQL> exec dbms_workload_repository.add_colored_sql('4n3x6w1vhn1k3');

PL/SQL procedure successfully completed.

SQL>

-- To UNPIN or remove the COLOR SQLID from repo
SQL> exec dbms_workload_repository.remove_colored_sql('<SQL_ID>');

-- Query below view to see list of all colored SQLs
SQL>  select * from DBA_HIST_COLORED_SQL;

   DB Id    SQL_ID        CREATE_TI     CON_ID
----------- ------------- --------- ----------
 2592296819 4n3x6w1vhn1k3 10-MAR-22          0


-- Re ran the SQL
SQL> select count(*) from TABLE_GROWTH_MB;

  COUNT(*)
----------
      2868



-- Let me generate a manual snap to capture fresh data
SQL>
SQL>
SQL> var n_snapid number
set feed off term on head off
set serveroutput on size unlimited
begin
        :n_snapid := dbms_workload_repository.create_snapshot();
        dbms_output.put_line('snap_id: ' || to_char(:n_snapid));
end;
/
snap_id: 36719
SQL> 


-- Let me retry again to see if the SQLID details are there in the AWR after I have colored it
SQL> SQL> select output from table (dbms_workload_repository.awr_sql_report_text(l_dbid => 2592296819 , l_inst_num => 2, 
l_bid => 36718, l_eid => 36719, l_sqlid => '4n3x6w1vhn1k3'));

OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
FATDBACA      2592296819 FATDBACA    PRIMARY          EE      12.2.0.1.0 YES NO

Instance     Inst Num Startup Time
------------ -------- ---------------
FATDBACA2           2 26-Oct-21 23:10

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     36708 10-Mar-22 16:00:37       190       2.5
  End Snap:     36719 10-Mar-22 18:39:40       177       2.6
   Elapsed:              159.06 (mins)
   DB Time:               93.43 (mins)

SQL Summary                    DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719

                Elapsed
   SQL Id      Time (ms)
------------- ----------

 Container DB
 ------------
4n3x6w1vhn1k3          0
Module: SQL*Plus
select count(*) from TABLE_GROWTH_MB
   2592296819

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

SQL ID: 4n3x6w1vhn1k3          DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select count(*) from TABLE_GROWTH_MB

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1134407048                      0             1         36719          36719
          -------------------------------------------------------------


Plan 1(PHV: 1134407048)
-----------------------

Plan Statistics                DB/Inst: FATDBACA/FATDBACA2  Snaps: 36708-36719
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                                 0            0.2     0.0
CPU Time (ms)                                     0            0.2     0.0
Executions                                        1            N/A     N/A
Buffer Gets                                      22           22.0     0.0
Disk Reads                                        0            0.0     0.0
Parse Calls                                       1            1.0     0.0
Rows                                              1            1.0     N/A
User I/O Wait Time (ms)                           0            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                        0            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 19            N/A     N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |    10 (100)|          |
|   1 |  SORT AGGREGATE    |                 |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TABLE_GROWTH_MB |  2624 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------



Full SQL Text

SQL ID        SQL Text
------------- -----------------------------------------------------------------
4n3x6w1vhn1k3 select count(*) from TABLE_GROWTH_MB

Perfect, the SQLID is there in the AWR now, and we can see all runtime stats, execution plan, SQL Text etc.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

HINT REPORT, a lesser known 19c enhancement to DBMS_XPLAN …

Posted by FatDBA on March 21, 2022

Oracle 19c has a great enhancement added to the DBMS_XPLAN package called the ‘Hint Usage Report’, which is pretty helpful in reporting if an incorrect hint was used, or there was any syntax error with the hint or if there was an unresolved hint used in the SQL.

This you can use with the DBMS_XPLAN package and its functions like DISPLAY, DISPLAY_CURSOR, DISPLAY_WORKLOAD_REPOSITORY or DISPLAY_SQL_PLAN_BASELINE. By default, DBMS_XPLAN in the default TYPICAL format will report only invalid hints. The report shows the text of the hints. The hint may also have one of the following annotations:

E : indicates a syntax error.
N : indicates an unresolved hint.
U : indicates that the corresponding hint was not used in the final plan.

Lets do some tests to understand it better. For our tests I have a test table created called BIGTAB and loaded it with sample data.
First case, I will try the PARALLEL_INDEX hint with one of the Index (non-partitioned) to see what the Hint Report says.

SQL> explain plan for select /*+ PARALLEL_INDEX(BIGTAB,WEIGHT_IDX,2) */ * from bigtab where weight between -168444405 and 639413572;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 441133017

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 13613 |   252K|    70   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIGTAB | 13613 |   252K|    70   (2)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

   1 - filter("WEIGHT">=(-168444405) AND "WEIGHT"<=639413572)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$1 / BIGTAB@SEL$1
         U -  PARALLEL_INDEX(BIGTAB,WEIGHT_IDX,4)

20 rows selected.

SQL> 

Here we are getting UNUSED (U) for PARALLEL_INDEX hint that we have used against index WEIGHT_INDEX, but it wasn’t honored because the PARALLEL_INDEX hint parallelizes an index range scan of a partitioned index. This is a common mistake with parallel_index hint is using it against a non-partitioned index, where it will be ignored.

Let me try the next case where I will make a syntax error with the hint INDEX, will pass it as INDEXX and see what the report says.

06:04:55 SQL>
06:04:55 SQL>
06:04:55 SQL> explain plan for select /*+ INDEXX(BIGTAB,WEIGHT_IDX) */ * from bigtab where weight between -168444405 and 639413572;

Explained.

Elapsed: 00:00:00.05
06:08:35 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 441133017

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|   1 |  TABLE ACCESS FULL| BIGTAB |
------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  INDEXX

15 rows selected.

Elapsed: 00:00:00.17
06:08:40 SQL>

And it says that there is was a syntax error (E) with the HINT, hence ignored by the optimizer.

Now, I will be trying a correct/valid hint with the SQL Statement and see what the hint report says about it.

SQL> explain plan for select /*+ INDEX(BIGTAB,WEIGHT_IDX) */ * from bigtab where weight between -168444405 and 639413572;

Explained.

SQL>

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2994241301

----------------------------------------------------------
| Id  | Operation                           | Name       |
----------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BIGTAB     |
|   2 |   INDEX RANGE SCAN                  | WEIGHT_IDX |
----------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   1 -  SEL$1 / BIGTAB@SEL$1
           -  INDEX(BIGTAB,WEIGHT_IDX)

16 rows selected.

And the report says that no error was reported and hint was correctly used, same can be seen in the change of access path of the execution plan.

Next, let me try any optimizer related hint, I will try the optimizer_index_cost_adj HINT to make the SQL more or less index friendly

03:32:43 SQL> explain plan for select /*+ opt_param('optimizer_index_cost_adj',20) */ * from bigtab where weight between -168444405 and 639413572;

Explained.

Elapsed: 00:00:00.00
03:32:53 SQL> 

03:32:58 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 441133017

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|   1 |  TABLE ACCESS FULL| BIGTAB |
------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  opt_param('optimizer_index_cost_adj',20)

15 rows selected.

Elapsed: 00:00:00.02

And that worked too as no error or any status flag was reported in the hint report.

Next, I will try a non existing Index and will see what the report says about it.

SQL> explain plan for select /*+ INDEX(FATDBA_INDEX) */ * from bigtab where weight between -168444405 and 639413572;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 441133017

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|   1 |  TABLE ACCESS FULL| BIGTAB |
------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))

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

   1 -  SEL$1
         N -  INDEX(FATDBA_INDEX)

15 rows selected.

SQL>

And the report says it was UNRESOLVED (N) as the Index doesn’t exists.

Let me try one more case where I will pass two bad or contradictory hints, will use both first_rows and all_rows together and will see what happens.

SQL> explain plan for select /*+ first_rows(1) all_rows */ * from BIGTAB;

Explained.

SQL> select * from dbms_xplan.display(format=>'basic +alias +hint_report');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 441133017

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|   1 |  TABLE ACCESS FULL| BIGTAB |
------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

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

   1 - SEL$1 / BIGTAB@SEL$1

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   0 -  STATEMENT
         U -  all_rows / hint conflicts with another in sibling query block
         U -  first_rows(1) / hint conflicts with another in sibling query block

21 rows selected.

SQL>

The preceding report shows that the all_rows and first_rows(1) CBO mode hints conflict with one other. As the First_rows attempts to optimize the query to get the very first row back to the client as fast as possible, whereas All_rows attempts to optimize the query to get the very last row as fast as possible.

Last example I will try to pass multiple hints where few of them will be valid and rest invalid. I will pass FULL & PARALLEL valid hints, and will use two INDEX unresolved hints and one UNRESOLVED hint of MERGE with query block.

SQL> explain plan for select /*+ FULL(bigtab) INDEX(WEIGHT_IDX) INDEX(ID_IDX) MERGE(@SEL$1) PARALLEL(2) */ * from bigtab where weight between -168444405 and 639413572;

Explained.

SQL> select * from dbms_xplan.display(format=>'basic +alias +hint_report');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 305217024

-----------------------------------------
| Id  | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  PX COORDINATOR      |          |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |
|   3 |    PX BLOCK ITERATOR |          |
|   4 |     TABLE ACCESS FULL| BIGTAB   |
-----------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / BIGTAB@SEL$1

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 5 (U - Unused (1), N - Unresolved (2))
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  PARALLEL(2)

   1 -  SEL$1
         N -  INDEX(ID_IDX)
         N -  INDEX(WEIGHT_IDX)
         U -  MERGE(@SEL$1)

   4 -  SEL$1 / BIGTAB@SEL$1
           -  FULL(bigtab)

32 rows selected.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

Autopsy of a non responsive database using SYSTEMSTATE dumps

Posted by FatDBA on March 14, 2022

One of the very common scenarios that we DBAs has encountered are when the database is hanging, freeze or totally non responsive, or when your database crashes with a serious performance problem, or when the SQLPlus cannot connect to the server or when you noticed that we can not save the instance/node then most of the time DBA take the system state dump for further analysis or you can say for RCA purpose.
In this case, if you want to obtain the current status of the database for later diagnosis, then we need to use SYSTEMSTATE dump to know what the process is doing, what it is waiting for, who is the resource owner, and who is blocking others. In case of the above problems, it is very helpful to analyze the cause of the problem by collecting SYSTEMSTATE dump in time.

In order to understand the purpose, let me create a situation, a problem, I will simulate a row locking (pessimistic type) case in to the database. From the first session I had created a table and insert few rows into it and didn’t commit and opened few others sessions and tried to update same row. And I can see a good amount of blocking is created in the database!

LOCK_TREE
----------
1.97
  1.58
  1.60
  1.75

USERNAME           SID    SERIAL# TY LOCK_TYPE     LOCK_REQUESTE       ID1        ID2 SQL_TEXT                           
----------- ---------- ---------- -- ------------- ------------- ---------- ---------- ----------------------------------------
SYS                 58      17036 TX None               Exclusive    589839       1777 update test set id=09 where ID=10
SYS                 60      21943 TX None               Exclusive    589839       1777 update test set id=09 where ID=10
SYS                 75         91 TX None               Exclusive    589839       1777 update test set id=09 where ID=10

Right time to generate the SYSTEMSTATE dump, below is how you can generate that. Do system state dump few times in a row, so we can determine whether the processes are hung or active.

-- connect with the database if its not hang or freeze
SQL>  connect / as sysdba

-- Connect using prelim option if its stalled or freeze
SQL>  sqlplus -prelim / as sysdba

-- Use SETMYPID command to select the current process as the ORADEBUG process
SQL>  oradebug setmypid
Statement processed. 

-- This is to remove the limitation on the size of the trace file use. 
-- In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED
SQL> oradebug unlimit
Statement processed. 

-- This is to take the systemstate dumps with 266 level (short stack + dump)
SQL>  oradebug dump systemstate 266
Statement processed. 

-- Or use below in case running on a RAC setup
SQL>  oradebug -g all dump systemstate 266

SQL>  oradebug dump systemstate 266
Statement processed. 

SQL>  oradebug dump systemstate 266
Statement processed. 

-- This command prints the name of the current trace file
SQL>  Oradebug tracefile_name
/u01/oracle/diag/rdbms/dixitdb/dixitdb/trace/dixitdb_ora_191381.trc

There are few other levels which you can use and are discussed below.
Note: I highly recommend to check with Oracle support to use there levels as few of them with higher level are little risky and known to crash instances.

Level 2 : Takes a dump (not including the lock element)
Level 10 : Takes a dump including lock details
Level 11 : Takes a dump + global cache of RAC
Level 256 : Takes short stacks (function stack)
Level 258 (256 + 2) : Takes a short stack + dump (not including the lock element)
Level 266 (256 + 10) : Takes a short stack + dump
Level 267 (256 + 11) : Takes a short stack + dump + global cache of RAC

So we have artificially created a locking scenario on the database and we are aware about the sessions involved in this pessimistic type locking. Now we will use the same information and that will help us to navigate through the massive SYSTEMSTATE dumps. Lets dig into the trace file that we have generated for the session.

The first part or the header contains details like hostname, machine info, service, module and client details, followed by command processing’s that we did through ORADEBUG.

Trace file /u01/app/oracle/diag/rdbms/dixitdb/dixitdb/trace/dixitdb_ora_5516.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1
System name:	Linux
Node name:	oracleontario.ontadomain
Release:	3.10.0-1062.el7.x86_64
Version:	#1 SMP Fri Aug 9 18:36:11 GMT 2019
Machine:	x86_64
Instance name: dixitdb
Redo thread mounted by this instance: 1
Oracle process number: 47
Unix process pid: 5516, image: oracle@oracleontario.ontadomain (TNS V1-V3)


*** 2022-03-07T23:29:00.706787-05:00
*** SESSION ID:(79.52211) 2022-03-07T23:29:00.706811-05:00
*** CLIENT ID:() 2022-03-07T23:29:00.706818-05:00
*** SERVICE NAME:(SYS$USERS) 2022-03-07T23:29:00.706824-05:00
*** MODULE NAME:(sqlplus@oracleontario.ontadomain (TNS V1-V3)) 2022-03-07T23:29:00.706830-05:00
*** ACTION NAME:() 2022-03-07T23:29:00.706836-05:00
*** CLIENT DRIVER:(SQL*PLUS) 2022-03-07T23:29:00.706840-05:00

Processing Oradebug command 'setmypid'

*** 2022-03-07T23:29:00.706867-05:00
Oradebug command 'setmypid' console output: <none>

*** 2022-03-07T23:29:05.127755-05:00
Processing Oradebug command 'unlimit'

*** 2022-03-07T23:29:05.127963-05:00
Oradebug command 'unlimit' console output: <none>

*** 2022-03-07T23:29:10.346290-05:00
Processing Oradebug command 'dump systemstate 266'

Next part is where you will see process summary and its dumps, including all user OSPIDs, their commands and blocking details with wait information and is the most interesting one and important section of the dump to understand the wait/blocking chain.

Dumping the Process Summary
       1: PSEUDO process
       2: PMON ospid 3904 sid 2 ser 9826, waiting for 'pmon timer' 
       3: CLMN ospid 3906 sid 3 ser 8008, waiting for 'pmon timer' 
       4: PSP0 ospid 3908 sid 4 ser 46393, waiting for 'rdbms ipc message' 
       5: VKTM ospid 3910 sid 5 ser 26025, waiting for 'VKTM Logical Idle Wait' 
       6: GEN0 ospid 3915 sid 6 ser 36023, waiting for 'rdbms ipc message' 
       7: MMAN ospid 3917 sid 7 ser 17720, waiting for 'rdbms ipc message' 
.....
......
.......
      47: USER ospid 5516 sid 79 ser 52211, 
      49: USER ospid 4429 sid 58 ser 17036, waiting for 'enq: TX - row lock contention' 
          Cmd: UPDATE
          Blocked by inst: 1, sid: 97, ser: 2507
          Final Blocker inst: 1, sid: 97, ser: 2507
      50: USER ospid 4538 sid 75 ser 91, waiting for 'enq: TX - row lock contention' 
          Cmd: UPDATE
          Blocked by inst: 1, sid: 97, ser: 2507
          Final Blocker inst: 1, sid: 97, ser: 2507
      51: W006 ospid 4290 sid 76 ser 17641, waiting for 'Space Manager: slave idle wait' 
      52: USER ospid 4636 sid 60 ser 21943, waiting for 'enq: TX - row lock contention' 
          Cmd: UPDATE
          Blocked by inst: 1, sid: 97, ser: 2507
          Final Blocker inst: 1, sid: 97, ser: 2507
      55: M004 ospid 4722 sid 74 ser 6422, waiting for 'class slave wait' 

And from the above trace section we understood that the final blocker is with SID 97, serial 2507 where SIDs 58,75 and 60 all waiting on event ‘enq: TX – row lock contention’.

Next section is where it dumps buffer cache working set information which contains size of the BC, number of pools, state information and other information. Followed by process level statistics – state, session, latching, post information along with short stack dumps which is debugging/triggering events that generates a trace file in case of any error and full chain. The trace also contains details about wait stacks, blocking chains (waiters), session event history,

Here in below section you will also see the SQL text (update test set id=09 where ID=10) and its details which has caused the blocking, its statistics, execution counts, active locking info, SQL ID, Table Info, HashValues and namespace info.

.....
........
............
 (WS) size: 48950 (0) wsid: 3 state: 0 pool: 3
 (WS) bsi: 0 bsz: 8192 pg: 0 dbwr: 0
 (WS) bgotten: 334899 fbwanted: 0
 (WS) sumwrt: 21641 sumscn: 0
 (WS) Waits fb: 0 wc: 0 bb: 33 sl: 0
 (WS) inspected fb: 296603 db: 9136 pn: 154
.....
...
.....

*** 2022-03-07T23:29:12.285778-05:00
PROCESS 46: 
  ----------------------------------------
  SO: 0x7deed748, type: process (2), map: 0x7d3d9878
      state: LIVE (0x4532), flags: 0x1
      owner: (nil), proc: 0x7deed748
      link: 0x7deed768[0x7deed768, 0x7deed768]
      child list count: 6, link: 0x7deed7b8[0x7def8f68, 0x74f28318]
      pg: 0
  SOC: 0x7d3d9878, type: process (2), map: 0x7deed748
       state: LIVE (0x99fc), flags: INIT (0x1)
  (process) Oracle pid:46, ser:2, calls cur/top: (nil)/0x7024aec8
            flags : (0x0) -  icon_uid:0 logon_pdbid=0
            flags2: (0x0),  flags3: (0x510) 
            call error: 0, sess error: 0, txn error 0
            intr queue: empty
    (post info) last post received: 166 0 3
                last post received-location: ksl2.h LINE:4395 ID:kslpsr
                last process to post me: 0x7d3b5758 1 6
...
....
    Short stack dump: 
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-read()+14<-sntpread()+28<-ntpfprd()+126<-nsbasic_brc()+399<-nioqrc()+438<-opikndf2()+999<-opitsk()+905<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
    KGL-UOL (Process state object)
    KGX Atomic Operation Log 0x7d3da9f8
     Mutex (nil)(0, 0) idn 0 oper NONE(0)
     FSO mutex uid 262142 efd 0 whr 0 slp 0
    KGX Atomic Operation Log 0x7d3daa60
...
....
.....
      LibraryHandle:  Address=0x6b734070 Hash=f528c430 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD 
        ObjectName:  Name=update test set id=09 where ID=10 
          FullHashValue=d45588e2ae2d8ef0798380f7f528c430 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=4113089584 OwnerIdn=0 
        Statistics:  InvalidationCount=0 ExecutionCount=4 LoadCount=2 ActiveLocks=4 TotalLockCount=8 TotalPinCount=1 
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0 
        Concurrency:  DependencyMutex=0x6b734120(0, 2, 0, 0) Mutex=0x6b7341c0(0, 95, 0, 0) 
        Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000] 
        WaitersLists:  
          Lock=0x6b734100[0x6b734100,0x6b734100] 
          Pin=0x6b7340e0[0x6b7340e0,0x6b7340e0] 
          LoadLock=0x6b734158[0x6b734158,0x6b734158] 
        Timestamp:  Current=03-07-2022 23:12:11 
        HandleReference:  Address=0x6b734258 Handle=(nil) Flags=[00] 
        LibraryObject:  Address=0x6b732f28 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] Flags3=[0000] PublicFlags=[0000] 
          ChildTable:  size='16' 
            Child:  id='0' Table=0x6b733da0 Reference=0x6b7338a8 Handle=0x6b732918 
        NamespaceDump:  
          Parent Cursor:  sql_id=7m0w0yzukjj1h parent=0x6b732ff8 maxchild=1 plk=y ppn=n prsfcnt=0 obscnt=0

Next we will check more details about the blocker or the session that has blocked others, here SID 97 is the case.

    (session) sid: 97 ser: 2507 trans: 0x7815a9a8, creator: 0x7d3d9878
              flags: (0x41) USR/- flags2: (0x40009) -/-/INC
              flags_idl: (0x0) status: -/-/-/- kill: -/-/-/-
              DID: 0001-002E-000000010000-0000-00000000, short-term DID: 
              txn branch: (nil)
              edition#: 134              user#/name: 0/SYS
              oct: 0, prv: 0, sql: (nil), psql: 0x71cc7dc8
              stats: 0x705a2930, PX stats: 0x128f8d84
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/1, ospid: 4321
      machine: oracleontario.ontadomain program: sqlplus@oracleontario.ontadomain (TNS V1-V3)
      application name: sqlplus@oracleontario.ontadomain (TNS V1-V3), hash value=2432467899
.....
........
.........
    Current Wait Stack:
     0: waiting for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=650 seq_num=652 snap_id=1
        wait times: snap=11 min 38 sec, exc=11 min 38 sec, total=11 min 38 sec
        wait times: max=infinite, heur=11 min 38 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x1a0
    There are 3 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 58, ser: 17036
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x9000f
        p3: 'sequence'=0x6f1
      row_wait_obj#: 77878, block#: 122337, row#: 0, file# 1
.........
............
.............
    Session Wait History:
        elapsed time of 0.000001 sec since current wait
     0: waited for 'SQL*Net message to client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=649 seq_num=651 snap_id=1
        wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000040 sec of elapsed time
     1: waited for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=648 seq_num=650 snap_id=1
        wait times: snap=0.000557 sec, exc=0.000557 sec, total=0.000557 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000017 sec of elapsed time
     2: waited for 'SQL*Net message to client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=647 seq_num=649 snap_id=1
        wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000015 sec of elapsed time
     3: waited for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=646 seq_num=648 snap_id=1
        wait times: snap=0.000280 sec, exc=0.000280 sec, total=0.000280 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000014 sec of elapsed time
     4: waited for 'PGA memory operation'
        =0x0, =0x0, =0x0
        wait_id=645 seq_num=647 snap_id=1
        wait times: snap=0.000349 sec, exc=0.000349 sec, total=0.000349 sec
        wait times: max=infinite
        wait counts: calls=0 os=0

....
......
........

You will also see details on SQLs that we’ve executed using same SID (97), here in my case I was running few SQLs to identify locking in the database and were captured in the dump along with its parent and child cursor details, followed by some object level stats.

      ----------------------------------------
      SO: 0x74f5c378, type: LIBRARY OBJECT LOCK (118), map: 0x6bd886b0
          state: LIVE (0x4532), flags: 0x1
          owner: 0x7def8f48, proc: 0x7deed748
          link: 0x74f5c398[0x74eec918, 0x74f44c98]
          child list count: 0, link: 0x74f5c3e8[0x74f5c3e8, 0x74f5c3e8]
          pg: 0
      SOC: 0x6bd886b0, type: LIBRARY OBJECT LOCK (118), map: 0x74f5c378
           state: LIVE (0x99fc), flags: INIT (0x1)

      LibraryObjectLock:  Address=0x6bd886b0 Handle=0x71cc7dc8 Mode=N 
        CanBeBrokenCount=1 Incarnation=1 ExecutionCount=1 
        
        User=0x7d658150 Session=0x7d658150 ReferenceCount=1 
        Flags=CNB/[0001] SavepointNum=558 Time=03/07/2022 23:17:34 
      LibraryHandle:  Address=0x71cc7dc8 Hash=3b6daacc LockMode=N PinMode=0 LoadLockMode=0 Status=VALD 
        ObjectName:  Name=select	sn.USERNAME,
	m.SID,
	sn.SERIAL#,
	m.TYPE,
	decode(LMODE,
		0, 'None',
		1, 'Null',
		2, 'Row-S (SS)',
		3, 'Row-X (SX)',
		4, 'Share',
		5, 'S/Row-X (SSX)',
		6, 'Exclusive') lock_type,
	decode(REQUEST,
		0, 'None',
		1, 'Null',
		2, 'Row-S (SS)',
		3, 'Row-X (SX)',
		4, 'Share',
		5, 'S/Row-X (SSX)',
		6, 'Exclusive') lock_requested,
	m.ID1,
	m.ID2,
	t.SQL_TEXT
from 	v$session sn,
	v$lock m ,
	v$sqltext t
where 	t.ADDRESS = sn.SQL_ADDRESS
and 	t.HASH_VALUE = sn.SQL_HASH_VALUE
and 	((sn.SID = m.SID  
          FullHashValue=245c9e2e0077915a5f18e8bc3b6daacc Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=997042892 OwnerIdn=0 
        Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1 
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 
        Concurrency:  DependencyMutex=0x71cc7e78(0, 2, 0, 0) Mutex=0x71cc7f18(0, 28, 0, 0) 
        Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000] 
        WaitersLists:  
          Lock=0x71cc7e58[0x71cc7e58,0x71cc7e58] 
          Pin=0x71cc7e38[0x71cc7e38,0x71cc7e38] 
          LoadLock=0x71cc7eb0[0x71cc7eb0,0x71cc7eb0] 
        Timestamp:  Current=03-07-2022 23:17:33 
        HandleReference:  Address=0x71cc8298 Handle=(nil) Flags=[00] 
        LibraryObject:  Address=0x70f48488 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] Flags3=[0000] PublicFlags=[0000] 
          ChildTable:  size='16' 
            Child:  id='0' Table=0x70f49300 Reference=0x70f48e08 Handle=0x70e14070 
        NamespaceDump:  
          Parent Cursor:  sql_id=5y678rhxqvaqc parent=0x70f48558 maxchild=1 plk=y ppn=n prsfcnt=0 obscnt=0 
      ----------------------------------------
      SO: 0x74eec8f8, type: LIBRARY OBJECT LOCK (118), map: 0x717c4470
          state: LIVE (0x4532), flags: 0x1
          owner: 0x7def8f48, proc: 0x7deed748
          link: 0x74eec918[0x74eec998, 0x74f5c398]
          child list count: 0, link: 0x74eec968[0x74eec968, 0x74eec968]
          pg: 0
      SOC: 0x717c4470, type: LIBRARY OBJECT LOCK (118), map: 0x74eec8f8
           state: LIVE (0x99fc), flags: INIT (0x1)

      LibraryObjectLock:  Address=0x717c4470 Handle=0x73516078 Mode=N 
        CanBeBrokenCount=3 Incarnation=3 ExecutionCount=0 
        Context=0x7f6fe229eb68 
        User=0x7d658150 Session=0x7d5ecd58 ReferenceCount=1 
        Flags=[0000] SavepointNum=0 Time=03/07/2022 23:17:33 
      LibraryHandle:  Address=0x73516078 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD 
        Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 
        Statistics:  InvalidationCount=1 ExecutionCount=1146 LoadCount=2 ActiveLocks=6 TotalLockCount=354 TotalPinCount=1506 
        Counters:  BrokenCount=3 RevocablePointer=3 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 
        Concurrency:  DependencyMutex=0x73516128(0, 0, 0, 0) Mutex=0x73517920(0, 1552, 0, 0) 
        Flags=RON/PIN/PN0/EXP/CHD/[10012111] Flags2=XRIV/[0010] 
        WaitersLists:  
          Lock=0x73516108[0x73516108,0x73516108] 
          Pin=0x735160e8[0x735160e8,0x735160e8] 
          LoadLock=0x73516160[0x73516160,0x73516160] 
        LibraryObject:  Address=0x73514f30 HeapMask=0000-0001-0001-0000 Flags=EXS/RIV[0200] Flags2=/FRI[400000] Flags3=[0000] PublicFlags=[0000] 
        NamespaceDump:  
          Child Cursor:  Heap0=0x73515000 Heap6=0x72b8cc00 Heap0 Load Time=03-07-2022 23:10:39 Heap6 Load Time=03-07-2022 23:10:39

Other important section is the ‘Enqueue Dump‘. Here in our case this section discloses that enqueue name was “TX-0009000F-000006F1-00000000-00000000” and was acquired in ‘X’ (Exclusive) mode by the holder session ID 97.

2022-03-07 23:29:12.319*:ksq.c@10787:ksqdmc(): Enqueue Dump      (enqueue) TX-0009000F-000006F1-00000000-00000000  DID: ksqlkdid: 0001-002E-00000001

      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x16
      mode: X, lock_flag: 0x20, lock: 0x7815a9e0, res: 0x7b39e9e0
      own: 0x7d658150, sess: 0x7d658150, proc: 0x7d3d9878, prv: 0x7b39e9f0
       xga: (nil), heap: UGA

Now let’s check the same info for waiter processes (49, 52).

-- SID 49
2022-03-07 23:29:12.445*:ksq.c@10787:ksqdmc(): Enqueue Dump      (enqueue) TX-0009000F-000006F1-00000000-00000000  DID: ksqlkdid: 0001-0031-00000009

      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x16
      req: X, lock_flag: 0x30, lock: 0x7b272110, res: 0x7b39e9e0
      own: 0x7d5f6cf8, sess: 0x7d5f6cf8, proc: 0x7d3dd898, prv: 0x7b39ea00


-- SID 52
2022-03-07 23:30:33.181*:ksq.c@10787:ksqdmc(): Enqueue Dump      (enqueue) TX-0009000F-000006F1-00000000-00000000  DID: ksqlkdid: 0001-0032-00000005

      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x16
      req: X, lock_flag: 0x30, lock: 0x7b271a78, res: 0x7b39e9e0
      own: 0x7d621360, sess: 0x7d621360, proc: 0x7d3dedf8, prv: 0x7b272120

At the end of the process dumps it prints details about the Hex/heap dump of the block, block row dumps involved.

-- For Process 52 (PID 52).
Dump of memory from 0x000000009012E000 to 0x0000000090130000
09012E000 0000A206 0041DDE1 0042614F 00000000  [......A.OaB.....]
09012E010 00000000 00110001 00013036 0042614E  [........60..NaB.]
09012E020 00008000 00031E02 00000000 00130005  [................]
09012E030 000006D9 0100068B 001C0190 80008000  [................]
09012E040 00423B71 00000000 00000000 00000000  [q;B.............]
09012E050 00000000 00000000 00000000 000D0100  [................]
09012E060 002CFFFF 1EFF1F52 00001EFF 1F9A000D  [..,.R...........]
09012E070 1F8E1F94 1F821F88 1F761F7C 1F6A1F70  [........|.v.p.j.]
09012E080 1F5E1F64 1F521F58 0284024C 02F302BC  [d.^.X.R.L.......]
09012E090 0363032C 03D1039A 04470408 04BD047E  [,.c.......G.~...]
09012E0A0 053304FC 05B00571 061C05E6 06900652  [..3.q.......R...]
09012E0B0 070006C8 076C0736 07DA07A3 08480810  [....6.l.......H.]
09012E0C0 08B5087F 092108EB 098E0958 0A0A09CC  [......!.X.......]
09012E0D0 0A7E0A40 0AF30ABC 0B610B2A 0BDF0BA0  [@.~.....*.a.....]
09012E0E0 0C4F0C18 0CBE0C86 0D2D0CF6 0D9D0D66  [..O.......-.f...]
09012E0F0 0E0B0DD4 0E810E42 0EF70EB8 0F6D0F36  [....B.......6.m.]
09012E100 0FEB0FAC 10591022 10C71090 113F1106  [....".Y.......?.]
09012E110 11AD1176 121D11E5 128D1254 12FB12C4  [v.......T.......]
09012E120 13691332 13DE13A7 145C141D 14D21493  [2.i.......\.....]
09012E130 15471511 15B3157D 162715E9 1695165F  [..G.}.....'._...]
09012E140 170216CB 176F1739 17DD17A7 18491813  [....9.o.......I.]
09012E150 18BD187F 193118F3 199F1969 1A1B19DD  [......1.i.......]
09012E160 1A871A51 1AF31ABD 1B641B2C 1BD01B9A  [Q.......,.d.....]
09012E170 1C3E1C07 1CAC1C74 1D191CE3 1D851D4F  [..>.t.......O...]
09012E180 1DF91DC3 74007000 6D006900 7A006900  [.....p.t.i.m.i.z]
09012E190 72006500 69005F00 6D006E00 6D006500  [.e.r._.i.n.m.e.m]
09012E1A0 72006F00 5F007900 77006100 72006100  [.o.r.y._.a.w.a.r]
09012E1B0 27006500 27002000 61006600 73006C00  [.e.'. .'.f.a.l.s]
09012E1C0 27006500 20002900 6F006E00 73005F00  [.e.'.). .n.o._.s]
09012E1D0 62007500 74007300 62007200 70005F00  [.u.b.s.t.r.b._.p]
....
......
...........
09012FFD0 002C12C1 11C10201 0201002C 002C10C1  [..,.....,.....,.]
09012FFE0 0FC10201 0201002C 002C0EC1 0DC10201  [....,.....,.....]
09012FFF0 0201002C 002C0CC1 0BC10201 614F0600  [,.....,.......Oa]
Block header dump:  0x0041dde1
 Object id on Block? Y
 seg/obj: 0x13036  csc:  0x000000000042614e  itc: 2  flg: O  typ: 1 - DATA


block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 0b
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 0c
tab 0, row 2, @0x1f8e
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 0d
tab 0, row 3, @0x1f88
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 0e
.....
.......
col  0: [ 2]  c1 16
end_of_block_dump

At the end it prints details about the redo transport, ASM File Info, SGA Information, PDB/CDB details …

KGSKSGA info:
is the system running: yes
  session that stopped the system: (nil)
number of plans 1 classes 4
low threshold delta 0
high threshold delta 2
number of cpus[0]: 1
thresholds - low[0]: 1, high[0]: 3
dynamic threshold adjustments - misbehave_adjust[0]: 0, cpu_util_adj[0]: 2
dynamic thresholds - low[0]: 3, high[0]: 5
running cnt[0]: 1, runnable cnt[0]: 3
flags: 0x0
debug: 0x0
all pointers:
num_latches 300 current 222 chg_class 0x74e364a0 chg_state 0x74e42020
class_list 0x74dfaa88,top_plan 0x7084e9d8, plan_list 0x74e03d38
heaps - kgsk subheap 0x74e142f0, plan heap 0x7b5d7438
backgrounds in_sched (pids): 

.....
..........
...............
Redo Log Management (from krsk_dump_rlm)
  SRL min count:  0
  SRL max count:  0
  SRL min avail:  0
  SRL file size:  0
  SRL block size: 0
  SRL use FRA :   FALSE

So, if you want to dig in deep and want more proofs and details about the incident, SYSTEMSTATE dumps is a great source of all internals that were involved at the time of the problem. This isn’t limited to row locking problems, you can generate it for all sort of events, problems or issues i.e. Row cache, latch free, LC Locks, CBC are few of the common ones …

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | 3 Comments »

Demystifying OUTLINE DATA in an Execution Plan …

Posted by FatDBA on March 7, 2022

‘Outline Data’ section displays the list of hints that would be needed to replicate the execution plan, even if the statistics change., but in case of a complex execution plans it comes with lot of strange looking and obscure terms used. As there isn’t any published explanation of stored outline hints, so today’s post is to decipher few of the terms that you see in an outline data of an execution plan.

If you want to read more about outlines, what are they, their purpose, please read my previous post on the same subject at –> https://fatdba.com/2017/11/30/how-to-fix-sql-plan-issues-using-outline-data/

Okay, coming back to the post, let me quickly generate the outline data for one of the test SQL and will try to explain about each of the hints, query blocks, aliases and other representations used.

SQL>
SQL> explain plan for 
SELECT d.department_name,e.employee_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;  

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'+alias +outline'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 3871261979
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     4 |   168 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |     4 |   168 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |     4 |   168 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPARTMENTS |     2 |    44 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |     6 |   120 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   3 - SEL$2BFA4EE4 / D@SEL$1
   4 - SEL$2BFA4EE4 / E@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$2BFA4EE4" "E"@"SEL$1")
      LEADING(@"SEL$2BFA4EE4" "D"@"SEL$1" "E"@"SEL$1")
      FULL(@"SEL$2BFA4EE4" "E"@"SEL$1")
      FULL(@"SEL$2BFA4EE4" "D"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$8812AA4E")
      ANSI_REARCH(@"SEL$2")
      OUTLINE(@"SEL$948754D7")
      MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
      OUTLINE_LEAF(@"SEL$2BFA4EE4")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
   3 - filter("D"."DEPARTMENT_ID">=30)
   4 - filter("E"."DEPARTMENT_ID"(+)>=30)

As per the above outline data captured for the execution plan, the very first entry is USE_HASH(@”SEL$2BFA4EE4″ “E”@”SEL$1”) This represents the use of HASH join methods used in the query. Other join method outline options you might see are USE_NL and USE_MERGE. Here in our example we’ve used the LEFT OUTER JOIN which will return all valid rows from the table on the left side of the JOIN keyword, that’s table EMPLOYEE (alias ‘E’) along with the values from the table on the right side.

i.e LEFT OUTER JOIN employees e ON d.department_id = e.department_id

LEADING(@”SEL$2BFA4EE4″ “D”@”SEL$1” “E”@”SEL$1”) The LEADING hints specifies the exact join order for the SQL that is followed by the optimizer in the plan. It displays the join order as an ordered list of table aliases and query block names. The aliases appears in the ordering in which we access those tables in the query.

The first part of the hint @”SEL$2BFA4EE4″ represents the main SELECT block and if you closely see the full hint definition you will see “D”@”SEL$1”, where “SEL$1” is the query block name for table alias ‘D’ for Department table, which is followed by “E”@”SEL$1”, that represents query block name “SEL$1” on second table joined ‘E’ for Employee table.

FULL(@”SEL$2BFA4EE4″ “E”@”SEL$1”) & FULL(@”SEL$2BFA4EE4″ “D”@”SEL$1”) are what you see next and these two hints for FULL TABLE SCANS on table alias “E”, that is EMPLOYEE table, followed by FTS on table alias “D”, that is DEPARTMENT table. Same you can see in the execution plan too.

OUTLINE(@”SEL$1″) & OUTLINE(@”SEL$2″) These two OUTLINE hints correspond to initial and the intermediate query blocks.

ANSI_REARCH(@”SEL$1″) This is the hint that instructs the optimizer to re-architecture of ANSI left, right, and full outer joins. In our case this was to re-arch the left outer join.

MERGE(@”SEL$8812AA4E” >”SEL$948754D7″) is the MERGE query block hint. SEL$8812AA4E and SEL$948754D7 are the transformed query blocks.

OUTLINE_LEAF(@”SEL$2BFA4EE4″) This hint builds an outline leaf for the specified query block. In our example it represents that the query block SEL$2BFA4EE4 is a “final” query block that has actually been subject to independent optimization. Outline leaf cannot be transformed. You can see multiple outline_leaf hints for the query blocks.

IGNORE_OPTIM_EMBEDDED_HINTS is a special hint instructs the CBO to ignore most of all the other supplied hints.

ALL_ROWS This hint instructs to optimize the query/statement block for best throughput with lowest resource utilization.

DB_VERSION(‘19.1.0’) & OPTIMIZER_FEATURES_ENABLE(‘19.1.0’) these two hints allows for the CBO to process the SQL on the said version of the database, 19.1.0 in our case.

Few others that you might see in OUTLINE DATA sections and are easy to decipher are INDEX_RS_ASC (Index Range Scan in ascending order) and it happens when the INDEX RANGE SCAN is used as an access path/method in SQL execution plan. Few others that you can see in case of Nested Loop Joins are NLJ_BATCHING which happens when Oracle batches multiple physical I/O requests and process them using a vector I/O (array) instead of processing them one at a time, and batching improves performance because it uses asynchronous reads..
Few others that you can see in case of NL’s are USE_NL, NLJ_PREFETCH etc.

INDEX_FFS in case of Index Fast Full Scans and the list is long …

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

Script to display latency wait times using DBA_HIST_EVENT_HISTOGRAM using Heat Maps

Posted by FatDBA on March 2, 2022

Today’s post if about a brilliant tool/script that I frequently use to get the outputs/graphs for customer meetings, presentations and for quick analysis of any performance problem …

This is one of the awesome Oracle provided tool/script that generates a heat map of latency times for a given wait event. The script takes the output from DBA_HIST_EVENT_HISTOGRAM and produces a heat map and a JPG version of the heat map is also produced representing the wait time latency for the given wait event over a specific time frame identified by range of snapshots.

Source : Script to Display Latency Wait Time From DBA_HIST_EVENT_HISTOGRAM Using Heat Map (Doc ID 1931492.1)

Note: This script queries ASH views, specially DBA_HIST_EVENT_HISTOGRAM which requires license as its part of the Diagnostics Pack on Oracle EE

The scripts takes the following input parameters:

  • Snapshot id range (first snapshot id and last snapshot id)
  • Database id
  • Instance number
  • Exact name of wait event
perl ./lhm.pl --begin [first snapid] --end [last snapid] --dbid [database id] --instance [instance num] --wait_event "wait event"

perl ./lhm.pl  --dbid 1266075800 --begin 19907 --end 19991 --event "log file parallel write" --instance 4


-- Example Output
database id:     1266075800
wait event:      log file parallel write
Matrix:          [26x85]
snap range:      [19907:19991]
instance id:     4
jpg file   :     latency_log_file_parallel_write.jpg

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , , , | Leave a Comment »

Is Oracle Database version 12.2.0.1 end of life ?

Posted by FatDBA on February 25, 2022

Lately a question was asked – For how long Oracle Database version 12.2.0.1 will be supported ? There are so many documents and blogs are available but they at the same time brings lot of confusion. So I thought to write a quick post about ES or regular support of Oracle DB 12cR2.

The bug fixing & full error corrections has already been ended for Oracle 12.2.0.1 on Nov 20, 2020, and on the top there has no plans from oracle 12.2.0.1 is not eligible for Extended Support (ES). At the moment 12.2.0.1 is running on the limited Error Correction from Dec 1, 2020 through March 31, 2022. Limited Error Correction means only Sev 1 and Security Updates only.

At the moment only 19c is the version that gives you a long term support, as the full span of bug fixing support until 31-APR-2024 with the option to have Extended Support until 31-Apr-2027. Premier Support (PS) ends April 30, 2024, Extended Support (ES) fees will be required beginning May 01, 2024 through April 30, 2027. Error Correction / Patching is available through April 30, 2027 with paid ES. Without paid ES, patching is only available until April 30, 2024

Hope It Helped!
Prashant Dixit

Posted in Basics | Tagged: , | 1 Comment »

A stubborn after upgrade issue, and how I handled it!

Posted by FatDBA on February 16, 2022

Writing this one from the misty mountains .. 🙂

Recently I had to part-time support a system where customer reported slowness issues with few of the critical SQLs which uses few IN-Memory tables and were impacted exactly since they moved from Oracle 12.1 to 12.2. We tried few things but none of them worked, and with that I started suspecting if the change in CBO version from 12.1 to 12.2 the reason behind those problems with SQLs.

So, today’s post will give you some idea about such scenarios when you’re not sure about the things that broke the performance after the upgrade, and you have to do the try and error approach to find out the cause. So, I decided to give it a try with one of those SQLs to set the OFE (Optimizer Feature Enable) to earlier DB version 12.1 and verify query performance. So I started my test with the optimizer_features_enable parameter to set it to the older version.

SQL> alter session set optimizer_features_enable='12.1.0.2';

And the query ran fine, At this stage we had some sort of understanding of the problem as it was clear that some changes in 12.2 that has trigger this problem. But now I have to identify among all those hidden/underscore parameters and fix control’s that one final parameter which is influencing the optimizer for that odd behavior. So, I started something like this …
We extract all the fixes and underscore parameters introduced in oracle 12.2 and I started with the hidden Parameters followed by Fix Control settings in 12.2.0.1 Optimizer environment to come to 12.1.0.2 OFE level

-- Hidden parameters added into Oracle version 12.2 
-- Setting them back to Oracle version 12.1 
alter session set "_optimizer_undo_cost_change"="12.1.0.2";
alter session set "_optimizer_cbqt_or_expansion"=off;
alter session set "_optimizer_ads_use_partial_results"=false;
alter session set "_query_rewrite_use_on_query_computation"=false;
alter session set "_px_scalable_invdist_mcol"=false;
alter session set "_optimizer_eliminate_subquery"=false;
alter session set "_sqlexec_hash_based_distagg_ssf_enabled"=false;
alter session set "_optimizer_union_all_gsets"=false;
alter session set "_optimizer_enhanced_join_elimination"=false;
alter session set "_optimizer_multicol_join_elimination"=false;
alter session set "_key_vector_create_pushdown_threshold"=0;
alter session set "_optimizer_enable_plsql_stats"=false;
alter session set "_recursive_with_parallel"=false;
alter session set "_recursive_with_branch_iterations"=1;
alter session set "_px_dist_agg_partial_rollup_pushdown"=off;
alter session set "_optimizer_key_vector_pruning_enabled"=false;
alter session set "_pwise_distinct_enabled"=false;
alter session set "_vector_encoding_mode"=off;
alter session set "_ds_xt_split_count"=0;
alter session set "_ds_sampling_method"=NO_QUALITY_METRIC;
alter session set "_optimizer_ads_use_spd_cache"=false;
alter session set "_optimizer_use_table_scanrate"=OFF;
alter session set "_optimizer_use_xt_rowid"=false;
alter session set "_xt_sampling_scan_granules"=off;
alter session set "_optimizer_band_join_aware"=false;
alter session set "_optimizer_vector_base_dim_fact_factor"=0;
alter session set "_ds_enable_view_sampling"=false;
alter session set "_optimizer_inmemory_use_stored_stats"=NEVER;
alter session set "_mv_access_compute_fresh_data"=off;
alter session set "_bloom_filter_ratio"=30;
alter session set "_optimizer_control_shard_qry_processing"=65535;
alter session set "_optimizer_interleave_or_expansion"=false;

Placed all above underscore parameters with the query and ran it again and observed the benefits (make sure to flush the previous plans from the shared pool). In my case this actually worked, but question was which underscore parameter helped. Do same with all fixes (controls).

-- Fix Controls added in Oracle version 12.2 
-- Setting them back to Oracle version 12.1 
alter session set "_fix_control"="16515789:0";
alter session set "_fix_control"="17491018:0";
alter session set "_fix_control"="17986549:0";
alter session set "_fix_control"="18115594:0";
alter session set "_fix_control"="18182018:0";
alter session set "_fix_control"="18302923:0";
alter session set "_fix_control"="18377553:0";
alter session set "_fix_control"="5677419:0";
alter session set "_fix_control"="18134680:0";
alter session set "_fix_control"="18636079:0";
alter session set "_fix_control"="18415557:0";
alter session set "_fix_control"="18385778:0";
alter session set "_fix_control"="18308329:0";
alter session set "_fix_control"="17973658:0";
alter session set "_fix_control"="18558952:0";
alter session set "_fix_control"="18874242:0";
alter session set "_fix_control"="18765574:0";
alter session set "_fix_control"="18952882:0";
alter session set "_fix_control"="18924221:0";
alter session set "_fix_control"="18422714:0";
alter session set "_fix_control"="18798414:0";
alter session set "_fix_control"="18969167:0";
alter session set "_fix_control"="19055664:0";
alter session set "_fix_control"="18898582:0";
alter session set "_fix_control"="18960760:0";
alter session set "_fix_control"="19070454:0";
alter session set "_fix_control"="19230097:0";
alter session set "_fix_control"="19063497:0";
alter session set "_fix_control"="19046459:0";
alter session set "_fix_control"="19269482:0";
alter session set "_fix_control"="18876528:0";
alter session set "_fix_control"="19227996:0";
alter session set "_fix_control"="18864613:0";
alter session set "_fix_control"="19239478:0";
alter session set "_fix_control"="19451895:0";
alter session set "_fix_control"="18907390:0";
alter session set "_fix_control"="19025959:0";
alter session set "_fix_control"="16774698:0";
alter session set "_fix_control"="19475484:0";
alter session set "_fix_control"="19287919:0";
alter session set "_fix_control"="19386746:0";
alter session set "_fix_control"="19774486:0";
alter session set "_fix_control"="18671960:0";
alter session set "_fix_control"="19484911:0";
alter session set "_fix_control"="19731940:0";
alter session set "_fix_control"="19604408:0";
alter session set "_fix_control"="14402409:0";
alter session set "_fix_control"="16486095:0";
alter session set "_fix_control"="19563657:0";
alter session set "_fix_control"="19632232:0";
alter session set "_fix_control"="19889960:0";
alter session set "_fix_control"="17208933:0";
alter session set "_fix_control"="19710102:0";
alter session set "_fix_control"="18697515:0";
alter session set "_fix_control"="18318631:0";
alter session set "_fix_control"="20078639:0";
alter session set "_fix_control"="19503668:0";
alter session set "_fix_control"="20124288:0";
alter session set "_fix_control"="19847091:0";
alter session set "_fix_control"="12618642:0";
alter session set "_fix_control"="19779920:0";
alter session set "_fix_control"="20186282:0";
alter session set "_fix_control"="20186295:0";
alter session set "_fix_control"="20265690:0";
alter session set "_fix_control"="16047938:0";
alter session set "_fix_control"="19507904:0";
alter session set "_fix_control"="18915345:0";
alter session set "_fix_control"="20329321:0";
alter session set "_fix_control"="20225191:0";
alter session set "_fix_control"="18776755:0";
alter session set "_fix_control"="19882842:0";
alter session set "_fix_control"="20010996:0";
alter session set "_fix_control"="20379571:0";
alter session set "_fix_control"="20129763:0";
alter session set "_fix_control"="19899588:0";
alter session set "_fix_control"="10098852:0";
alter session set "_fix_control"="19663421:0";
alter session set "_fix_control"="20465582:0";
alter session set "_fix_control"="16732417:0";
alter session set "_fix_control"="20732410:0";
alter session set "_fix_control"="20289688:0";
alter session set "_fix_control"="20543684:0";
alter session set "_fix_control"="20506136:0";
alter session set "_fix_control"="20830312:0";
alter session set "_fix_control"="19768896:0";
alter session set "_fix_control"="19814541:0";
alter session set "_fix_control"="17443547:0";
alter session set "_fix_control"="19123152:0";
alter session set "_fix_control"="19899833:0";
alter session set "_fix_control"="20754928:0";
alter session set "_fix_control"="20808265:0";
alter session set "_fix_control"="20808192:0";
alter session set "_fix_control"="20340595:0";
alter session set "_fix_control"="18949550:0";
alter session set "_fix_control"="14775297:0";
alter session set "_fix_control"="17497847:0";
alter session set "_fix_control"="20232513:0";
alter session set "_fix_control"="20587527:0";
alter session set "_fix_control"="19186783:0";
alter session set "_fix_control"="19653920:0";
alter session set "_fix_control"="21211786:0";
alter session set "_fix_control"="21057343:0";
alter session set "_fix_control"="21503478:0";
alter session set "_fix_control"="21476032:0";
alter session set "_fix_control"="20859246:0";
alter session set "_fix_control"="21639419:0";
alter session set "_fix_control"="20951803:0";
alter session set "_fix_control"="21683982:0";
alter session set "_fix_control"="20216500:0";
alter session set "_fix_control"="20906162:0";
alter session set "_fix_control"="20854798:0";
alter session set "_fix_control"="21509656:0";
alter session set "_fix_control"="21833220:0";
alter session set "_fix_control"="21802552:0";
alter session set "_fix_control"="21452843:0";
alter session set "_fix_control"="21800590:0";
alter session set "_fix_control"="21273039:0";
alter session set "_fix_control"="16750133:0";
alter session set "_fix_control"="22013607:0";
alter session set "_fix_control"="22152372:0";
alter session set "_fix_control"="22077191:0";
alter session set "_fix_control"="22123025:0";
alter session set "_fix_control"="16913734:0";
alter session set "_fix_control"="8357294:0";
alter session set "_fix_control"="21979983:0";
alter session set "_fix_control"="22158526:0";
alter session set "_fix_control"="21971099:0";
alter session set "_fix_control"="22090662:0";
alter session set "_fix_control"="21300129:0";
alter session set "_fix_control"="21339278:0";
alter session set "_fix_control"="20270511:0";
alter session set "_fix_control"="21424812:0";
alter session set "_fix_control"="22114090:0";
alter session set "_fix_control"="22159570:0";
alter session set "_fix_control"="22272439:0";
alter session set "_fix_control"="22372694:0";
alter session set "_fix_control"="22514195:0";
alter session set "_fix_control"="22520315:0";
alter session set "_fix_control"="22649054:0";
alter session set "_fix_control"="8617254:0";
alter session set "_fix_control"="22020067:0";
alter session set "_fix_control"="22864730:0";
alter session set "_fix_control"="21099502:0";
alter session set "_fix_control"="22904304:0";
alter session set "_fix_control"="22967807:0";
alter session set "_fix_control"="22879002:0";
alter session set "_fix_control"="23019286:0";
alter session set "_fix_control"="22760704:0";
alter session set "_fix_control"="20853506:0";
alter session set "_fix_control"="22513493:0";
alter session set "_fix_control"="22518491:0";
alter session set "_fix_control"="23103096:0";
alter session set "_fix_control"="22143411:0";
alter session set "_fix_control"="23180670:0";
alter session set "_fix_control"="23002609:0";
alter session set "_fix_control"="23210039:0";
alter session set "_fix_control"="23102649:0";
alter session set "_fix_control"="23071621:0";
alter session set "_fix_control"="23136865:0";
alter session set "_fix_control"="23176721:0";
alter session set "_fix_control"="23223113:0";
alter session set "_fix_control"="22258300:0";
alter session set "_fix_control"="22205301:0";
alter session set "_fix_control"="23556483:0";
alter session set "_fix_control"="21305617:0";
alter session set "_fix_control"="22533539:0";
alter session set "_fix_control"="23596611:0";
alter session set "_fix_control"="22937293:0";
alter session set "_fix_control"="23565188:0";
alter session set "_fix_control"="24654471:0";
alter session set "_fix_control"="24845754:0";
ALTER session set "_fix_control"='5483301:OFF;

In my case none of fix control gives any benefits. This was now clear there was some feature in 12.2 which when disabled in the form of underscore parameter helped to get us back to previous state. But the problem is, they are total 32 different parameters, so I thought to divide the parameter list into half and execute the query after setting first half parameters and see if I get the correct result, if not then tried again setting next half parameter list and executed the query.
And with that trial and error approach, I was able to reach that one parameter which caused issues with those set of SQLs. It was ‘_optimizer_inmemory_use_stored_stats‘ which was causing issues with those IN-Memory tables references by those SQLs, as the optimizer NEVER uses the stored statistics for in-memory tables in 12.1, but the same parameter in 12.2 is with default value of AUTO and that causes the issue.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , , | Leave a Comment »

Part 4 : How to generate an AWR report for a Data Guard physical standby database ?

Posted by FatDBA on February 7, 2022

Recently I was asked to test performance of an Active Dataguard or Physical standby database where the DWH/reporting type SELECT workload was running, and slowness was reported by the user. In order to understand the system better its always good to generate the AWR reports, but this being the standby database which is opened in READ ONLY mode, means you cannot directly call the AWR report related scripts on the standby as they will fail with the error ‘database opened in read only’ mode.

This post is all about enabling AWR reporting’s in the standby database. I am doing this test on 12.2.0.1.0 ADG.

About test databases, we have the primary (TESLPH) is opened in Read Write mode and the standby (TESLPRBH) in Read Only Mode With real time Apply.

SQL> select db_unique_name,open_mode, database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
TESLPH                         READ WRITE           PRIMARY


SQL> select db_unique_name,open_mode, database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
TESLPRBH                       READ ONLY WITH APPLY PHYSICAL STANDBY

Next, you have to enable the SYS$UMF account. The RMF is used for collecting performance statistics for an Oracle Database. The SYS$UMF user is the default database user that has all the privileges to access the system-level RMF views and tables. All the AWR related operations in RMF can be performed only by the SYS$UMF user. The SYS$UMF user is locked by default and it must be unlocked before deploying the RMF topology. You need to provide password for the SYS$UMF user when creating database links in the RMF topology. If the password for the SYS$UMF user is changed, all the database links in the RMF topology must be recreated.

Next you have to make sure if the “_umf_remote_enabled” underscore parameter is set to TRUE else you will receieve “ORA-20501: Remote UMF is disabled” when doing any UMF related operation on the database.

-- On both PRIMARY and STANDBY Databases.
SQL> select username,common,account_status from dba_users where username ='SYS$UMF';

USERNAME        COM ACCOUNT_STATUS
--------------- --- --------------------------------
SYS$UMF         YES OPEN

-- On both PRIMARY and STANDBY Databases.
SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;

System altered.

Next you need to create two database links for ‘primary to the standby‘ and ‘standby to the primary’ communication using SYS$UMF and DB UNIQUE NAME of the databases. Will test the connectivity of both of them to see if they are working okay.

-- On PRIMARY database.
SQL> CREATE DATABASE LINK "PRIMARY_TO_STANDBY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPRBH';

Database link created.

SQL> CREATE DATABASE LINK "STANDBY_TO_PRIMARY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPH';

Database link created.


SQL> select db_link, username, host from dba_db_links

DB_LINK                        USERNAME        HOST
------------------------------ --------------- ----------------------------------------
STANDBY_TO_PRIMARY_DBLINK      SYS$UMF         TESLPH
PRIMARY_TO_STANDBY_DBLINK      SYS$UMF         TESLPRBH


SQL> select db_unique_name from v$database@STANDBY_TO_PRIMARY_DBLINK;

DB_UNIQUE_NAME
------------------------------
TESLPH

1 row selected.


SQL> select db_unique_name from v$database@PRIMARY_TO_STANDBY_DBLINK;

DB_UNIQUE_NAME
------------------------------
TESLPRBH

1 row selected.


-- On PRIMARY database.
SQL> CREATE DATABASE LINK "PRIMARY_TO_STANDBY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPRBH';

Database link created.

SQL> CREATE DATABASE LINK "STANDBY_TO_PRIMARY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPH';

Database link created.


SQL> select db_link, username, host from dba_db_links

DB_LINK                        USERNAME        HOST
------------------------------ --------------- ----------------------------------------
STANDBY_TO_PRIMARY_DBLINK      SYS$UMF         TESLPH
PRIMARY_TO_STANDBY_DBLINK      SYS$UMF         TESLPRBH

-- Lets try the connectivity using DB Links that we have created
SQL> select db_unique_name from v$database@STANDBY_TO_PRIMARY_DBLINK;

DB_UNIQUE_NAME
------------------------------
TESLPH

1 row selected.


SQL> select db_unique_name from v$database@PRIMARY_TO_STANDBY_DBLINK;

DB_UNIQUE_NAME
------------------------------
TESLPRBH

1 row selected.

Next we have to add the primary database node to the UMF repository, for that you have to run below command on both the primary & standby databases.

I am assigning primary site name as –> ‘primary_site’
and standby database site name as –> ‘standby_site’

-- On PRIMARY database
SQL> exec dbms_umf.configure_node ('primary_site');

PL/SQL procedure successfully completed.

SQL>


-- On STANDBY database.
SQL> exec dbms_umf.configure_node('standby_site','STANDBY_TO_PRIMARY_DBLINK');

PL/SQL procedure successfully completed.

SQL>

-- Use in case want to UNCONFIGURE the node.
SQL> exec DBMS_UMF.UNCONFIGURE_NODE;

Next we need to create the UMF topology, I am creating it as ‘Topology_1‘ on primary database. You can only have a single UMF topology in the database, if you try to create more you will get “ORA-20506: Maximum number of topologies exceeded”

-- On PRIMARY Database
SQL> exec DBMS_UMF.create_topology ('Topology_1');

PL/SQL procedure successfully completed.

-- Lets query if the toplogy is created with no errors and is ACTIVE.
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME                   TARGET_ID TOPOLOGY_VERSION TOPOLOGY
------------------------------ ---------- ---------------- --------
Topology_1                     1530523744                1 ACTIVE

1 row selected.

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME                  NODE_NAME                         NODE_ID  NODE_TYPE AS_SO AS_CA STATE
------------------------------ ------------------------------ ---------- ---------- ----- ----- --------------------
Topology_1                     primary_site                      1530523744          0 FALSE FALSE OK

1 row selected.

-- Run in case want to DROP the topology
SQL>  exec DBMS_UMF.drop_topology('NAME-OF-TOPOLOGY');

Next you have to add the standby node to the topology, for that you need to use register_node procedure and need to mention both of the DBLinks that we have created earlier. This you have to run on PRIMARY node.

-- On PRIMARY database
SQL> exec DBMS_UMF.register_node ('Topology_1', 'standby_site', 'PRIMARY_TO_STANDBY_DBLINK', 'STANDBY_TO_PRIMARY_DBLINK', 'FALSE', 'FALSE');

PL/SQL procedure successfully completed.


SQL> select * from dba_umf_registration;

TOPOLOGY_NAME                  NODE_NAME                         NODE_ID  NODE_TYPE AS_SO AS_CA STATE
------------------------------ ------------------------------ ---------- ---------- ----- ----- --------------------
Topology_1                     primary_site                      1530523744          0 FALSE FALSE OK
Topology_1                     standby_site                      3265600723          0 FALSE FALSE OK

2 rows selected.

SQL>

Now when both of the nodes ‘primary_site‘ and ‘standby_site‘ are added to the topology, we have to register the standby node for the AWR service.

-- On PRIMARY database.
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'standby_site');

PL/SQL procedure successfully completed.

SQL> 

SQL> select * from dba_umf_service;

TOPOLOGY_NAME                     NODE_ID SERVICE
------------------------------ ---------- -------
Topology_1                     3265600723 AWR

1 row selected.


SQL> select * from dba_umf_link;

TOPOLOGY_NAME                  FROM_NODE_ID TO_NODE_ID LINK_NAME
------------------------------ ------------ ---------- ------------------------------
Topology_1                       1530523744 3265600723 PRIMARY_TO_STANDBY_DBLINK
Topology_1                       3265600723 1530523744 STANDBY_TO_PRIMARY_DBLINK

2 rows selected.

Now everything is set, no errors and we are all good to create some manual snaps. Let’s generate some remote snapshots, for that you have to run below command from PRIMARY database.

-- On PRIMARY database.
SQL>
SQL> alter system archive log current;

System altered.

SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');

PL/SQL procedure successfully completed.

Now time to call the AWR report base scripts to generate the AWR reports for the standby database, you can call them on either Primary of the Standby server. For example, below I called the AWR report from the primary database and passed DBID and instance number of the standby database and have got the report for the standby database.

SQL> SQL> @?/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter value for report_type: text
Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  4265600723     1      TESLPRBH     TESLPRBH     monkey02lx031
* 5576289360     1      TESLPRBH     TESLPH       monkey1903nm12

Enter value for dbid: 4265600723
Using 4265600723 for database Id
Enter value for inst_num: 1
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

TESLPRBH     TESLPRBH             1  13 Jan 2022 04:10    1
                                  2  13 Jan 2022 04:11    1
                                  3  13 Jan 2022 04:14    1
                                  4  13 Jan 2022 04:14    1
                                  5  13 Jan 2022 04:14    1
                                  6  13 Jan 2022 04:15    1
                                  7  13 Jan 2022 04:15    1
                                  8  13 Jan 2022 04:17    1
                                  9  13 Jan 2022 04:18    1
                                 10  13 Jan 2022 04:18    1
                                 11  13 Jan 2022 04:18    1
                                 12  13 Jan 2022 04:18    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:



WORKLOAD REPOSITORY report for

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
TESLPRBH      4265600723 TESLPRBH    PHYSICAL STANDBY EE      12.2.0.1.0 NO  NO

Instance     Inst Num Startup Time
------------ -------- ---------------
TESLPRBH            1 13-Feb-21 05:16

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
monkey02lx0315b   Linux x86 64-bit                    4     4       4      15.49

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:         1 13-Jan-22 04:10:57        61        .5
  End Snap:        12 13-Jan-22 04:18:27        60        .6
   Elapsed:                7.50 (mins)
   DB Time:                0.73 (mins)

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):               0.1              44.1      0.00      0.01
              DB CPU(s):               0.1              24.1      0.00      0.01
      Background CPU(s):               0.0              14.2      0.00      0.00
      Redo size (bytes):               0.0               0.0
  Logical read (blocks):           1,227.7         552,449.0
          Block changes:               7.7           3,465.0
 Physical read (blocks):             594.5         267,510.0
Physical write (blocks):              44.1          19,821.0
       Read IO requests:              16.6           7,466.0
.......
................
.......................

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

Part 3: Using SQLT (SQLTXPLAIN) tool with Data Guard physical standby databases

Posted by FatDBA on January 28, 2022

SQLT (Oracle’s Tool written by Carlos Sierra, read MOS note 215187.1 for more details) is an important tool when it comes to doing in-depth and advance troubleshooting & analysis of a SQL statement, it’s pretty easy to install, configure and use, but things gets little tricky when it comes to running it for the SQL that is slow or in question on the read-only standby database. The problem is obvious as ADG or standby databases doesn’t allow WRITE operations and you will get error “ORA-16000: database open for read-only access” when try on standby database

The only problem being that SQLTXTRACT and SQLTXECUTE need read/write access to the database (to store data in the SQLT repository and to install packages and procedures). How is it possible for SQLT to help us if we can’t even store data about the performance on the database with the performance problem? This is where SQLTXTRSBY comes into play. XTRSBY solves this problem by using local users (on a read/write database) and creating procedures that use database links to the read-only database.

First step : Install SQLTXPLAIN on the primary and allow the DDL to be propagated to the standby database.

-- On primary database
-- DDL Will get propogated to the standby in few secons/mins.	
[oracle@monkeyos1931jd install]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 13 16:13:41 2022

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL>
SQL> @1

   DB_UNIQUE_NAME     OPEN_MODE    DATABASE_ROLE
_________________ _____________ ________________
DIXITP            READ WRITE    PRIMARY

SQL>


SQL> START sqcreate.sql
        zip warning: name not matched: *_sq*.log
zip error: Nothing to do! (SQLT_installation_logs_archive.zip)
        zip warning: name not matched: *_ta*.log
........
...........
..............

SQUTLTEST completed.
  adding: 220113161601_10_squtltest.log (deflated 59%)

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.
SQL>
SQL>

Step 2 : Create a database link accessible to the SQLTXPLAIN schema linking to the standby database as changes will be fetched from the standby database SQLTXPLA in schema.

-- On Primary database to connect to the standby database.
SQL> create public database link dblink_tostandby connect to sqltxplain identified by oracle90 using 'DIXITSTAN';

Database link DBLINK_TOSTANDBY created.

SQL>
SQL>

SQL> select * from dba_db_links;

    OWNER                      DB_LINK      USERNAME        HOST      CREATED    HIDDEN
_________ ____________________________ _____________ ___________ ____________ _________
PUBLIC    DBLINK_TOSTANDBY             SQLTXPLAIN    DIXITSTAN    16-JAN-22    NO



SQL> select sysdate from dual@DBLINK_TOSTANDBY;

     SYSDATE
____________
16-JAN-22

SQL>

Step 3: Let’s run some SQLs on standby database which we will using against the SQLT tool as an input to generate the SQLT report.

-- On Standby Database
SQL> @1

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
DIXITSTAN                       READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>
SQL> select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);

  COUNT(*)
----------
   2431464

SQL> select sql_id, sql_fulltext from v$sqlarea where sql_text like '%distinct( weight) from bigtab%';

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------------------------------------------------------------------
6mg40znnrhzm8 select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)

In the steps above we ran some arbitrary SQL and got the SQL ID for that SQL. Remember we ran the SQL on the standby database (where our reports might have run). We can’t store any data on the Data Guard Physical Standby database so now we have to capture information about the SQL from across the database link from the primary database.

Step 4: Will run the SQLT for SQLID that we have captured from STANDBY database, from PRIMARY database. The script “sqltxtrsby.sql” is present under RUN directory of the tool.

[oracle@monkeyos1931jd run]$ ls *sqltxtrsby*
sqltxtrsby.sql


[oracle@monkeyos1931jd run]$ !sql
sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 16 06:01:12 2022

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> @1

   DB_UNIQUE_NAME     OPEN_MODE    DATABASE_ROLE
_________________ _____________ ________________
DIXITP            READ WRITE    PRIMARY


SQL> 
SQL> @sqltxtrsby 6mg40znnrhzm8 DBLINK_TOSTANDBY

PL/SQL procedure successfully completed.

Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

Parameter 3:
DBLINK to stand-by database (required)

Enter value for 3: DBLINK_TOSTANDBY

Values passed to sqltxtrsby:
SQL_ID_OR_HASH_VALUE: "6mg40znnrhzm8"
DB_LINK             : "@DBLINK_TOSTANDBY"


PL/SQL procedure successfully completed.


SQLT_VERSION
----------------------------------------
SQLT version number: 19.1.200226
SQLT version date  : 2020-02-26
Installation date  : 2022-01-13/16:15:27

... please wait ...
  adding: alert_DIXITP.log (deflated 86%)

NOTE:
You used the XTRSBY method connected as SYS.

.......
..............
...............
....
..................
File sqlt_s19812_xtrsby_6mg40znnrhzm8.zip for 6mg40znnrhzm8 has been created.

SQLTXTRSBY completed.

Now look out for sqlt_xxxx_main.html file, We’ll see the main sqlt_xxxx_main.html file, but fewer files than for a “normal” sqltxtract run: no 10053 trace file, no SQL profile script and no SQL Tuning Advisor reports. This is because the read-only status of the standby restricts what can be done.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: