Tales From A Lazy Fat DBA

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

Posts Tagged ‘Database’

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: , , , , , | 1 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 »

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 1 : Running SQL Tuning Advisor for a slow SQL in a Read Only Standby Database

Posted by FatDBA on January 15, 2022

Here goes my maiden post blog post from series about “Troubleshooting SQLs & other Issues on a Read Only Standby/Dataguard Environments” …

Recently I helped one of my customer stabilize performance of one of their critical DWH/BI decision support system workload running on an ADG (Physical Standby). At one time I have to generate SQL tuning advisory reports for few slow SQLs on the database, but that being a RO dataguard, it always ends with an error "ORA-13792: This operation requires a database link." when I try to call the SQL tuning advisor directly on the standby database.

So, this post is about how to run SQL Tuning advisor on a dataguard environment. Let’s assume we want to get SQLTA recommendation for this below SQL that is going for a FULL TABLE SCAN and is a costly SQL.

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

Explained.

SQL> select * from  table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  0mauvkjsvmcmj, child number 1


Plan hash value: 2140185107

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    13 |    74   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| BIGTAB | 80223 |  1018K|    74   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter("WEIGHT" IS NOT NULL)

This is what I have received on the standby database when tried to run the SQL Tuning advisor directly on it where it says that it requires a database link in order to run the advisory.

-- On standby database:
SQL>
SQL> @?/rdbms/admin/sqltrpt.sql


Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 0y521mggg73pk

Sql Id specified: 0y521mggg73pk

Tune the sql
~~~~~~~~~~~~
DECLARE
*
ERROR at line 1:
ORA-13792: This operation requires a database link.
ORA-06512: at line 36
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1571
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1563
ORA-06512: at "SYS.DBMS_SQLTUNE", line 798
ORA-06512: at line 20


ERROR:
ORA-13608: The specified name NULL is invalid.
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1571
ORA-06512: at "SYS.PRVT_ADVISOR", line 7080
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 85
ORA-06512: at "SYS.PRVT_ADVISOR", line 5938
ORA-06512: at "SYS.PRVT_ADVISOR", line 7011
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1535
ORA-06512: at "SYS.DBMS_SQLTUNE", line 1324
ORA-06512: at line 1

In remote tuning, the database on which you initiate a tuning task differs from the database in which the tuning process executes or in which results are stored. For example, a standby database can have its own workload of queries, some of which may require tuning. You can issue SQL Tuning Advisor statements on a standby database. A standby-to-primary database link enables DBMS_SQLTUNE to write data to and read data from the primary database. The link is necessary because the standby database, which is read-only, cannot write the SQL tuning data.

Okay, for that first you need to create the DB Link between primary and the standby database, and same will be used by the SQL tuning advisor where it will do the write operations on the primary database. We will check its connectivity from the standby database if its working or not …

-- On Primary database
SQL> select username,common,account_status from dba_users where username ='SYS$UMF';

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

-- On Primary database:
SQL> create database link lnk_to_pri connect to "SYS$UMF" identified by "oracle90" using 'DXTPRI';

Database link created.


-- On Standby database:
SQL> select  db_unique_name from v$database@lnk_to_pri;

DB_UNIQUE_NAME
------------------------------
DXTPRI

1 row selected.

SQL>


To tune a standby workload on a primary database, specify the database_link_to parameter in DBMS_SQLTUNE procedures. By default, the database_link_to parameter is null, which means that tuning is local. The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. You issue all statements on the standby database. DBMS_SQLTUNE uses the database link both to fetch data from the primary database, and store data in the primary database.
Let’s create the TUNING TASK for the SQL Text.

-- run it on the STANDBY Database.
SQL>
SQL> 
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
task_name => 'TEST_sql_tuning_task1',
database_link_to => 'lnk_to_pri');
END;
/  
PL/SQL procedure successfully completed.

SQL>

-- Lets check if the tuning task is created 
SQL> SELECT task_name, STATUS, EXECUTION_start, EXECUTION_end FROM dba_advisor_log;

TASK_NAME
--------------------------------------------------------------------------------
STATUS      EXECUTION EXECUTION
----------- --------- ---------
TEST_sql_tuning_task1
EXECUTING   13-JAN-22

Next you need to execute the tuning task using the task_name you have assigned in the last step while creating the tuning task.

-- On Standby database
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&&your_sta_taks_name', database_link_to => 'lnk_to_pri');
Enter value for your_sta_taks_name: TEST_sql_tuning_task1

PL/SQL procedure successfully completed.

SQL>

Next, lets do the final step, generate the tuning task report with all recommendations for the SQL that is slow in the standby database.

-- Run it on the Standby database.
SQL>
SQL> SET LINES 150
SQL> SET pages 50000
SQL> SET long 5000000
SQL> SET longc 5000000
SQL> select dbms_sqltune.report_tuning_task('&&your_sta_taks_name', database_link_to => 'lnk_to_pri') from dual;
old   1: select dbms_sqltune.report_tuning_task('&&your_sta_taks_name', database_link_to => 'lnk_to_pri') from dual
new   1: select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task1', database_link_to => 'lnk_to_pri') from dual

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1',DATABASE_LINK_TO=>'LNK_TO_PRI')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------


RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sql_tuning_task_5mxdwvuf9j3vp
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 01/13/2021 22:05:52
Completed at       : 01/13/2021 22:06:25

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : pdxdwvuf9j3co
SQL Text   : select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.94%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'sql_tuning_task_pdxdwvuf9j3co', task_owner => 'SYS', replace =>
            TRUE);

  Validation results
  ------------------

So, that’t it, you finally have the tuning recommendations for the slow SQL in the STANDBY database.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , , | 3 Comments »

What are those strange columns in my execution plan OMem 1Mem O/1/M and Used-Mem ?

Posted by FatDBA on January 12, 2022

Few of the readers after my last post asked me – What are those strange columns named ‘OMem’, ‘1Mem’ and ‘O/1/M’ or ‘Used-Mem’ in execution plan ? This is something what you will see in the execution plan if called the DBMS_XPLAN using ‘+memstats’ or the ‘+allstats’ options.

----------------------------           -----------------------------
|  OMem |  1Mem |  O/1/M   |   OR      |  OMem |  1Mem | Used-Mem   |
----------------------------           -----------------------------
--
--
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0m329sngnhv1p', 0, 'ALLSTATS'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (xxxxxxxxxxxxxx

Plan hash value: xxxxxx

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | O/1/M|
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |xxxxxxxx  |      1 |        |     15 |00:00:00.62 |     353K|       |       |      |
|*  1 |  FILTER                                    |xxxxxxxx  |      1 |        |     15 |00:00:00.62 |     353K|       |       |      |
|*  2 |   VIEW                                     |xxxxxxxx  |      1 |   5530 |     15 |00:00:00.62 |     353K|       |       |      |
|   3 |    SORT ORDER BY                           |xxxxxxxx  |      1 |   5530 |   7873 |00:00:00.62 |     353K|   549K|   457K| 1/0/0|
|   4 |     COUNT                                  |xxxxxxxx  |      1 |        |   7873 |00:00:00.46 |     353K|       |       |      |
|   5 |      NESTED LOOPS                          |xxxxxxxx  |      1 |   5530 |   7873 |00:00:00.46 |     353K|       |       |      |
|   6 |       NESTED LOOPS                         |xxxxxxxx  |      1 |   5530 |    266K|00:00:00.44 |     240K|       |       |      |
|   7 |        NESTED LOOPS                        |xxxxxxxx  |      1 |   5530 |    266K|00:00:00.19 |   46039 |       |       |      |
|*  8 |         TABLE ACCESS FULL                  |xxxxxxxx  |      1 |   4392 |     90 |00:00:00.01 |     878 |       |       |      |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED|xxxxxxxx  |     90 |      1 |    266K|00:00:00.17 |   45161 |       |       |      |
|* 10 |          INDEX RANGE SCAN                  |xxxxxxxx  |     90 |      1 |    266K|00:00:00.04 |    3871 |       |       |      |
|* 11 |        INDEX UNIQUE SCAN                   |xxxxxxxx  |    266K|      1 |    266K|00:00:00.20 |     194K|       |       |      |
|* 12 |       TABLE ACCESS BY INDEX ROWID          |xxxxxxxx  |    266K|      1 |   7873 |00:00:00.20 |     113K|       |       |      |
|  13 |        TABLE ACCESS BY INDEX ROWID BATCHED |xxxxxxxx  |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |      |
|* 14 |         INDEX RANGE SCAN                   |xxxxxxxx  |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |      |
----------------------------------------------------------------------------------------------------------------------------------------

First talking about column ‘Starts’, that is the number of times that operation actually happened. The column with title ‘Buffers’ refers to the amount of buffer read/write (IO) performed. Now comes the column ‘OMem’, and it is the memory estimate needed to perform the operation in memory only. This is also called the optimal execution.
Next column with title ‘1Mem’ is the memory estimate needed to perform the operation in a single pass (Read/Write from disk (temp) only once), called one-pass execution. A multi-pass execution is when the same data is written to and read from disk more than once. Think about the sorting, where the database has to do a sort on large amount data in a small PGA or sort area.

Last column with title ‘0/1/M’ and sometimes ‘Used-Mem‘ is the ACTUAL amount of memory used for the operation. You also see some numbers in the brackets for this column. There is a significance for them – If the number is 0, then it was an optimal execution, used only memory and no temporary space. If the number is 1, then it was a one-pass execution. If the number is > 1, it was a multi-pass execution, and that number represents the number of passes.

Hope It Helped!
Prashant Dixit

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

Use gather_plan_statistics hint to understand optimizer’s estimations and much more ..

Posted by FatDBA on January 10, 2022

Lately someone told me that he ran the gather_plan_statistics hint with his SQL, but he is not getting the detailed execution plan, I mean all extra stats that you see i.e. starts, estimated time, starts, buffers, actual and estimated number of rows were not there and he was getting the regular/simple execution plan.

But I found he was trying it in a wrong way! The /*+ gather_plan_statistics */ hint does not save data into PLAN_TABLE, but it stores execution statistics in V$SQL_PLAN performance view. To display these data you can use (dbms_xplan.display_cursor (format=>’ALLSTATS LAST’)), but this not always work, because you must execute the second command immediately after the SQL query. The better method is to query V$SQL or V$SQLAREA or any useful view to obtain SQL_ID of the query, and then use DISPLAY_CURSOR function, for example in this way …

SQL>
SQL> SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE A.TYPE='MOBILE'
AND A.STATUS='Available' AND A.ASSIGNED_CSN_ID IS NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME='com.paratapata.killer.mfs.oare.mainframe.locking.ADAKingstonToKillerRangeStst' AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID
AND C.STATUS = NVL('Active', C.STATUS) AND C.CATEGORY = 'Range'
AND 'Internal' = NVL(C.NUMBER_USAGE,'External') AND (A.lock_id IS NULL OR A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value 
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name = 'DEFAULT_LOCK_PERIOD' )) / 60 / 24))
ORDER BY TO_NUMBER(A.NAME) ) RESULTS WHERE RESULTS.IDX BETWEEN 1 AND 15; 

MANDNA_KKAJ_DI NAME                                                      IDX
-------------- -------------------------------------------------- ----------
      91891302 0444915115                                                 12
      91891309 0444915122                                                 13
      91891310 0444915123                                                 14
      91891314 0444915127                                                 15
      91891723 0444915536                                                  2
      91891724 0444915537                                                  3
      91891726 0444915539                                                  4
      91891730 0444915543                                                  5
      91891739 0444915552                                                  6
      91891748 0444915561                                                  7
      91891766 0444915579                                                  8
      91891768 0444915581                                                  9
      91891807 0444915620                                                 10
      91891854 0444915667                                                 11
      17116808 04466962472                                                 1

15 rows selected.

SQL> select sql_id, plan_hash_value, executions, sql_text from gv$sqlarea where sql_fulltext like '%gather_plan_statistics%';

SQL_ID        PLAN_HASH_VALUE EXECUTIONS SQL_TEXT
------------- --------------- ---------- ------------------------------------------------------------
0m329sngnhv1p      2185860753          1 SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.OBJECT
                                         _INST_ID,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR A,INS
                                         TALL.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
                                          A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_
                                         CSN_ID IS NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND B
                                         .BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJE
                                         CT_ID AND C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGO
                                         RY = :"SYS_B_04" AND :"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_
                                         B_06") AND (A.lock_id IS NULL OR A.LOCK_DATE <= (sysdate - N
                                         VL(A.LOCK_PERIOD,(SELECT mark_raar_value FROM DIXDROI.EAI_HY
                                         BRIS_CONFIGURATION_DATA WHERE mark_raar_name = :"SYS_B_07" )
                                         ) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME) )
                                          RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_1
                                         1"

The above query returns SQL_ID=0m329sngnhv1p and CHILD_NUMBER=0(child number is just a cursor number). Use these values to query the collected plan. This will show you statistics such as the actual number of rows processed (A-Rows), rather than just the estimates (E-Rows). A-Rows is the total number of rows produced by all starts of that operation. But E-Rows is an estimate of the number of rows produced by a single start of an operation. If you want to read about E-Rows and A-Rows columns, please read a brilliant post by Jonathan Lewis.

Okay, the plan also includes a column called Starts, which tells you how many times each step was executed. A-Rows, E-Rows and Starts are all incredibly useful if you want to understand a plan.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0m329sngnhv1p', 0, 'ALLSTATS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"


Plan hash value: 2185860753


-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |      1 |        |     15 |00:00:00.62 |     353K|       |       |      |
|*  1 |  FILTER                                    |                               |      1 |        |     15 |00:00:00.62 |     353K|       |       |      |
|*  2 |   VIEW                                     |                               |      1 |   5530 |     15 |00:00:00.62 |     353K|       |       |      |
|   3 |    SORT ORDER BY                           |                               |      1 |   5530 |   7873 |00:00:00.62 |     353K|   549K|   457K| 1/0/0|
|   4 |     COUNT                                  |                               |      1 |        |   7873 |00:00:00.46 |     353K|       |       |      |
|   5 |      NESTED LOOPS                          |                               |      1 |   5530 |   7873 |00:00:00.46 |     353K|       |       |      |
|   6 |       NESTED LOOPS                         |                               |      1 |   5530 |    266K|00:00:00.44 |     240K|       |       |      |
|   7 |        NESTED LOOPS                        |                               |      1 |   5530 |    266K|00:00:00.19 |   46039 |       |       |      |
|*  8 |         TABLE ACCESS FULL                  | INF_KRA_PRIMAR_RANGE          |      1 |   4392 |     90 |00:00:00.01 |     878 |       |       |      |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B              |     90 |      1 |    266K|00:00:00.17 |   45161 |       |       |      |
|* 10 |          INDEX RANGE SCAN                  | EAI_NUMBER_REL_1              |     90 |      1 |    266K|00:00:00.04 |    3871 |       |       |      |
|* 11 |        INDEX UNIQUE SCAN                   | PK_INF_KRA_PRIMAR             |    266K|      1 |    266K|00:00:00.20 |     194K|       |       |      |
|* 12 |       TABLE ACCESS BY INDEX ROWID          | INF_KRA_PRIMAR                |    266K|      1 |   7873 |00:00:00.20 |     113K|       |       |      |
|  13 |        TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH  |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |      |
|* 14 |         INDEX RANGE SCAN                   | IDXGETNUMBERPD1               |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |      |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(:SYS_B_11>=:SYS_B_10)
   2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
   8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
  10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
  11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
  12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
              "A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
  14 - access("mark_raar_NAME"=:SYS_B_07)


49 rows selected.


There are multiple other ways how you can add or remove more details to your execution plan, lets take a look on few of those methods.

-- To get additional COST and BYTES column into plan

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0m329sngnhv1p',format=>'ALLSTATS LAST +cost +bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"

Plan hash value: 2185860753

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |      1 |        |       |       |  3753 (100)|     15 |00:00:00.62 |     353K|       |       |          |
|*  1 |  FILTER                                    |                               |      1 |        |       |       |            |     15 |00:00:00.62 |     353K|       |       |          |
|*  2 |   VIEW                                     |                               |      1 |   5530 |   286K|       |  3753   (1)|     15 |00:00:00.62 |     353K|       |       |          |
|   3 |    SORT ORDER BY                           |                               |      1 |   5530 |   918K|  1064K|  3753   (1)|   7873 |00:00:00.62 |     353K|   549K|   457K|  487K (0)|
|   4 |     COUNT                                  |                               |      1 |        |       |       |            |   7873 |00:00:00.46 |     353K|       |       |          |
|   5 |      NESTED LOOPS                          |                               |      1 |   5530 |   918K|       |  3544   (1)|   7873 |00:00:00.46 |     353K|       |       |          |
|   6 |       NESTED LOOPS                         |                               |      1 |   5530 |   918K|       |  3544   (1)|    266K|00:00:00.44 |     240K|       |       |          |
|   7 |        NESTED LOOPS                        |                               |      1 |   5530 |   615K|       |  2437   (1)|    266K|00:00:00.19 |   46039 |       |       |          |
|*  8 |         TABLE ACCESS FULL                  | INF_KRA_PRIMAR_RANGE          |      1 |   4392 | 92232 |       |   240   (1)|     90 |00:00:00.01 |     878 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B              |     90 |      1 |    93 |       |     1   (0)|    266K|00:00:00.17 |   45161 |       |       |          |
|* 10 |          INDEX RANGE SCAN                  | EAI_NUMBER_REL_1              |     90 |      1 |       |       |     1   (0)|    266K|00:00:00.04 |    3871 |       |       |          |
|* 11 |        INDEX UNIQUE SCAN                   | PK_INF_KRA_PRIMAR             |    266K|      1 |       |       |     1   (0)|    266K|00:00:00.20 |     194K|       |       |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID          | INF_KRA_PRIMAR                |    266K|      1 |    56 |       |     1   (0)|   7873 |00:00:00.20 |     113K|       |       |          |
|  13 |        TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH  |      1 |      1 |    28 |       |     1   (0)|      1 |00:00:00.01 |       2 |       |       |          |
|* 14 |         INDEX RANGE SCAN                   | IDXGETNUMBERPD1               |      1 |      1 |       |       |     1   (0)|      1 |00:00:00.01 |       1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(:SYS_B_11>=:SYS_B_10)
   2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
   8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
  10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
  11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
  12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
              "A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
  14 - access("mark_raar_NAME"=:SYS_B_07)


49 rows selected.







-- To get OUTLINE data into your execution plan

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0m329sngnhv1p', format=>'ALLSTATS LAST +outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"

Plan hash value: 2185860753

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |      1 |        |     15 |00:00:00.62 |     353K|       |       |          |
|*  1 |  FILTER                                    |                               |      1 |        |     15 |00:00:00.62 |     353K|       |       |          |
|*  2 |   VIEW                                     |                               |      1 |   5530 |     15 |00:00:00.62 |     353K|       |       |          |
|   3 |    SORT ORDER BY                           |                               |      1 |   5530 |   7873 |00:00:00.62 |     353K|   549K|   457K|  487K (0)|
|   4 |     COUNT                                  |                               |      1 |        |   7873 |00:00:00.46 |     353K|       |       |          |
|   5 |      NESTED LOOPS                          |                               |      1 |   5530 |   7873 |00:00:00.46 |     353K|       |       |          |
|   6 |       NESTED LOOPS                         |                               |      1 |   5530 |    266K|00:00:00.44 |     240K|       |       |          |
|   7 |        NESTED LOOPS                        |                               |      1 |   5530 |    266K|00:00:00.19 |   46039 |       |       |          |
|*  8 |         TABLE ACCESS FULL                  | INF_KRA_PRIMAR_RANGE          |      1 |   4392 |     90 |00:00:00.01 |     878 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B              |     90 |      1 |    266K|00:00:00.17 |   45161 |       |       |          |
|* 10 |          INDEX RANGE SCAN                  | EAI_NUMBER_REL_1              |     90 |      1 |    266K|00:00:00.04 |    3871 |       |       |          |
|* 11 |        INDEX UNIQUE SCAN                   | PK_INF_KRA_PRIMAR             |    266K|      1 |    266K|00:00:00.20 |     194K|       |       |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID          | INF_KRA_PRIMAR                |    266K|      1 |   7873 |00:00:00.20 |     113K|       |       |          |
|  13 |        TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH  |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 14 |         INDEX RANGE SCAN                   | IDXGETNUMBERPD1               |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "RESULTS"@"SEL$1")
      FULL(@"SEL$2" "C"@"SEL$2")
      INDEX_RS_ASC(@"SEL$2" "B"@"SEL$2" ("INF_KRA_PRIMAR_B"."TARGET_OBJECT_ID" "INF_KRA_PRIMAR_B"."BINDING_NAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "B"@"SEL$2")
      INDEX(@"SEL$2" "A"@"SEL$2" ("INF_KRA_PRIMAR"."MANDNA_KKAJ_DI"))
      LEADING(@"SEL$2" "C"@"SEL$2" "B"@"SEL$2" "A"@"SEL$2")
      USE_NL(@"SEL$2" "B"@"SEL$2")
      USE_NL(@"SEL$2" "A"@"SEL$2")
      NLJ_BATCHING(@"SEL$2" "A"@"SEL$2")
      PUSH_SUBQ(@"SEL$3")
      INDEX_RS_ASC(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3" ("PAM_KRIAIS_MAINFATRAARA_AHAH"."mark_raar_NAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3")
      END_OUTLINE_DATA
  */

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

   1 - filter(:SYS_B_11>=:SYS_B_10)
   2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
   8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
  10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
  11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
  12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
              "A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
  14 - access("mark_raar_NAME"=:SYS_B_07)


77 rows selected.

SQL>







-- Lets try a more sophisticated one to get all sorts of information available

SQL>
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '0m329sngnhv1p', FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE +PROJECTION +REMOTE +NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"

Plan hash value: 2185860753

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |      1 |        |       |       |  3753 (100)|          |     15 |00:00:00.62 |     353K|       |       |          |
|*  1 |  FILTER                                    |                               |      1 |        |       |       |            |          |     15 |00:00:00.62 |     353K|       |       |          |
|*  2 |   VIEW                                     |                               |      1 |   5530 |   286K|       |  3753   (1)| 00:00:01 |     15 |00:00:00.62 |     353K|       |       |          |
|   3 |    SORT ORDER BY                           |                               |      1 |   5530 |   918K|  1064K|  3753   (1)| 00:00:01 |   7873 |00:00:00.62 |     353K|   549K|   457K|     1/0/0|
|   4 |     COUNT                                  |                               |      1 |        |       |       |            |          |   7873 |00:00:00.46 |     353K|       |       |          |
|   5 |      NESTED LOOPS                          |                               |      1 |   5530 |   918K|       |  3544   (1)| 00:00:01 |   7873 |00:00:00.46 |     353K|       |       |          |
|   6 |       NESTED LOOPS                         |                               |      1 |   5530 |   918K|       |  3544   (1)| 00:00:01 |    266K|00:00:00.44 |     240K|       |       |          |
|   7 |        NESTED LOOPS                        |                               |      1 |   5530 |   615K|       |  2437   (1)| 00:00:01 |    266K|00:00:00.19 |    46039|       |       |          |
|*  8 |         TABLE ACCESS FULL                  | INF_KRA_PRIMAR_RANGE          |      1 |   4392 | 92232 |       |   240   (1)| 00:00:01 |     90 |00:00:00.01 |      878|       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B              |     90 |      1 |    93 |       |     1   (0)| 00:00:01 |    266K|00:00:00.17 |    45161|       |       |          |
|* 10 |          INDEX RANGE SCAN                  | EAI_NUMBER_REL_1              |     90 |      1 |       |       |     1   (0)| 00:00:01 |    266K|00:00:00.04 |     3871|       |       |          |
|* 11 |        INDEX UNIQUE SCAN                   | PK_INF_KRA_PRIMAR             |    266K|      1 |       |       |     1   (0)| 00:00:01 |    266K|00:00:00.20 |     194K|       |       |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID          | INF_KRA_PRIMAR                |    266K|      1 |    56 |       |     1   (0)| 00:00:01 |   7873 |00:00:00.20 |     113K|       |       |          |
|  13 |        TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH  |      1 |      1 |    28 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |        2|       |       |          |
|* 14 |         INDEX RANGE SCAN                   | IDXGETNUMBERPD1               |      1 |      1 |       |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |        1|       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$2 / RESULTS@SEL$1
   3 - SEL$2
   8 - SEL$2 / C@SEL$2
   9 - SEL$2 / B@SEL$2
  10 - SEL$2 / B@SEL$2
  11 - SEL$2 / A@SEL$2
  12 - SEL$2 / A@SEL$2
  13 - SEL$3 / PAM_KRIAIS_MAINFATRAARA_AHAH@SEL$3
  14 - SEL$3 / PAM_KRIAIS_MAINFATRAARA_AHAH@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "RESULTS"@"SEL$1")
      FULL(@"SEL$2" "C"@"SEL$2")
      INDEX_RS_ASC(@"SEL$2" "B"@"SEL$2" ("INF_KRA_PRIMAR_B"."TARGET_OBJECT_ID" "INF_KRA_PRIMAR_B"."BINDING_NAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "B"@"SEL$2")
      INDEX(@"SEL$2" "A"@"SEL$2" ("INF_KRA_PRIMAR"."MANDNA_KKAJ_DI"))
      LEADING(@"SEL$2" "C"@"SEL$2" "B"@"SEL$2" "A"@"SEL$2")
      USE_NL(@"SEL$2" "B"@"SEL$2")
      USE_NL(@"SEL$2" "A"@"SEL$2")
      NLJ_BATCHING(@"SEL$2" "A"@"SEL$2")
      PUSH_SUBQ(@"SEL$3")
      INDEX_RS_ASC(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3" ("PAM_KRIAIS_MAINFATRAARA_AHAH"."mark_raar_NAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'MOBILE'
   2 - :2 (VARCHAR2(30), CSID=873): 'Available'
   3 - (VARCHAR2(30), CSID=873): 'com.paratapata.killer.mfs.oare.mainframe.locking.ADAKingstonToKillerRangeStst'
   4 - (VARCHAR2(30), CSID=873): 'Active'
   5 - (VARCHAR2(30), CSID=873): 'Range'
   6 - (VARCHAR2(30), CSID=873): 'Internal'
   7 - (VARCHAR2(30), CSID=873): 'External'

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

   1 - filter(:SYS_B_11>=:SYS_B_10)
   2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
   8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
  10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
  11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
  12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
              "A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
  14 - access("mark_raar_NAME"=:SYS_B_07)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "RESULTS"."MANDNA_KKAJ_DI"[NUMBER,22], "RESULTS"."NAME"[VARCHAR2,50], "RESULTS"."IDX"[NUMBER,22]
   2 - "RESULTS"."MANDNA_KKAJ_DI"[NUMBER,22], "RESULTS"."NAME"[VARCHAR2,50], "RESULTS"."IDX"[NUMBER,22]
   3 - (#keys=1) TO_NUMBER("NAME")[22], "A"."MANDNA_KKAJ_DI"[NUMBER,22], "A"."NAME"[VARCHAR2,50], ROWNUM[22]
   4 - "A"."MANDNA_KKAJ_DI"[NUMBER,22], "NAME"[VARCHAR2,50], ROWNUM[8]
   5 - "A"."MANDNA_KKAJ_DI"[NUMBER,22], "NAME"[VARCHAR2,50]
   6 - "A".ROWID[ROWID,10], "A"."MANDNA_KKAJ_DI"[NUMBER,22]
   7 - "B"."OWNING_OBJECT_ID"[NUMBER,22]
   8 - "C"."MANDNA_KKAJ_DI"[NUMBER,22]
   9 - "B"."OWNING_OBJECT_ID"[NUMBER,22]
  10 - "B".ROWID[ROWID,10]
  11 - "A".ROWID[ROWID,10], "A"."MANDNA_KKAJ_DI"[NUMBER,22]
  12 - "NAME"[VARCHAR2,50]
  13 - "mark_raar_VALUE"[NUMBER,22]
  14 - "PAM_KRIAIS_MAINFATRAARA_AHAH".ROWID[ROWID,10]


120 rows selected.

SQL>

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , , , | 1 Comment »

Using Liquibase with Oracle for versioning objects and track database changes …

Posted by FatDBA on December 30, 2021

Hi All,

Last few weeks I was busy doing some CI/CD integrations using Liquibase, and this was the first time I was using Liquibase and I immediately fell in love with this brilliant tool, that you can use for tracking, managing, automation and applying database schema changes. This is gaining popularity as a DevOps tool to automate your database deployments.

Today’s post is about how to integrate Liquibase with Oracle databases. I did all of the demos on Oracle database version 19.3.0.0.0 on RHEL8 and using Liquibase community version 4.6.2 You can download latest version from https://www.liquibase.org/download and they also have certified courses available on their university website https://learn.liquibase.com/

Okay, let’s quickly build the playground to do demos. I will first first un-tar the file that I have downloaded from their website.

[root@fatdba liqui]# tar -xvf liquibase-4.6.2.tar.gz
ABOUT.txt
GETTING_STARTED.txt
LICENSE.txt
examples/sql/
examples/sql/samplechangelog.h2.sql
.....
........
...........
liquibase
liquibase.bat
liquibase.jar

[root@fatdba liqui]# ls
ABOUT.txt      examples             lib       LICENSE.txt  liquibase-4.6.2.tar.gz  liquibase.jar  UNINSTALL.txt
changelog.txt  GETTING_STARTED.txt  licenses  liquibase    liquibase.bat           README.txt
[root@fatdba liqui]#

Lets add the PATH variable to .bash_profile and set it to export PATH=$PATH:/root/liquibase (my Liquibase un-tar directory) this is to call the executable from anywhere. With that you’re all set to use the Liquibase, yes! you only need to unzip/un-tar the software and ready to go. It’s installation and configuration both is very easy and straight forward.

Here in this demo, I will be using all options or flags directly with the Liquibase cli to better understanding, but I recommend to create the property file and put all your configuration entries there, like the one I have shared below.

[root@localhost liquibase]# more liquibase.properties
changeLogFile: changelogfile.sql
driver: oracle.jdbc.OracleDriver
classpath: /root/liquibase/lib/ojdbc8-18.3.0.0.jar
url: jdbc:oracle:thin:@localhost.ontadomain:1521/dixitdb
username: dixdroid
password: dixdroid
outputFile=output_local.sql
loglevel=SEVERE
liquibase.hub.mode=off

Here changeLogFile is the changelog file to use, driver is the database driver class name, its ‘oracle.jdbc.OracleDriver’ as I am doing this demo on Oracle database. Classpath flag is to point the jar file for the classpath containing migration files and JDBC Driver, URL is the database JDBC URL (hostname:portnumber/SID), username/password is the database username and the password, the outputFile is the used to send output to a file. The loglevel parameter controls the amount of messages that are generated when running Liquibase commands, possible options are SEVERE/WARNING/INFO/FINE/OFF and the last option liquibase.hub.mode disables the HUB mode for Liquibase. There are whole lot of other parameters and are available on https://docs.liquibase.com/

Next I am going to create a test schema where I will create objects later on, and will track changes using Liquibase.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 30 02:42:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create user dixdroid identified  by dixdroid;

User created.

-- Granting SYSDBA to the test user for demo purpose only
SQL> grant connect, sysdba to dixdroid;

Grant succeeded.

SQL> conn dixdroid
Enter password:
Connected.
SQL>

-- Next, I will add some test data to this schema. 
-- Will create a Table, insert few rows, index, function and a sequence. 

SQL> @testdata.sql

Table created.


1 row created.


1 row created.


Index created.


Table created.


Sequence created.


1 row created.


Function created.


Commit complete.

SQL>
SQL>
SQL> col object_name for a30
SQL> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
TEST                           TABLE
IDX_TEST1                      INDEX
DIXIT1                         TABLE
DIXIT1_PK                      INDEX
DIXIT1_SEQ                     SEQUENCE
GET_DIXIT1_COUNT               FUNCTION

Now I will check if the Liquibase connection is successful, for that you should use the status command.

[root@localhost liquibase]# liquibase --username=liquibase --password=liquibase --changeLogFile=changelogfile.sql status
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 04:35:09 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
Output saved to /root/liquibase/output_local.sql
Liquibase command 'status' was executed successfully.

[root@localhost liquibase]# more /root/liquibase/output_local.sql
LIQUIBASE@jdbc:oracle:thin:@localhost.ontadomain:1521/dixitdb is up to date
[root@localhost liquibase]#

Next we need to generate the ‘changelog‘. Liquibase uses a changelog to consecutively list all changes made to your database. Think of it as a account book or a daybook. It is a file that contains a record of all your database changes (changesets). Liquibase uses this changelog record to inspect your database and execute any changes that are not yet applied to your database.

[root@localhost liquibase]# liquibase --driver=oracle.jdbc.OracleDriver --changeLogFile=changelogfile.sql  --classpath=/root/liquibase/lib/ojdbc8-18.3.0.0.jar --url="jdbc:oracle:thin:@localhost.ontadomain:1521/dixitdb"  --username=liquibase --password=liquibase --defaultSchemaName=dixit generateChangeLog
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 22:44:36 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed.

When generating formatted SQL changelogs, it is important to decide if batched statements
should be split or not.  For storedlogic objects, the default behavior is 'splitStatements:false'
.All other objects default to 'splitStatements:true'.  See https://docs.liquibase.org for additional information.

Generated changelog written to /root/liquibase/changelogfile.sql
Output saved to /root/liquibase/output_local.sql
Liquibase command 'generateChangelog' was executed successfully.
[root@localhost liquibase]#

Next we will run the ‘updateSQL‘ command which is a helper command that allows you to inspect the SQL Liquibase will run while using the update command. The updateSQL command is used when you want to inspect the raw SQL before running the update command, so you can correct any issues that may arise before running the command.

[root@localhost liquibase]# liquibase --username=dixdroid --password=dixdroid --changeLogFile=changelogfile.sql updateSQL
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 22:46:25 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
Output saved to /root/liquibase/output_local.sql
Liquibase command 'updateSql' was executed successfully.
[root@localhost liquibase]#
[root@localhost liquibase]#

Lets examine the output_local.sql file generate by the last command where we ran updateSQL command with Liquibase.

[root@localhost liquibase]#
[root@localhost liquibase]# more /root/liquibase/output_local.sql
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog.sql
-- Ran at: 12/29/21 10:46 PM
-- Against: DIXDROID@jdbc:oracle:thin:@localhost.ontadomain:1521/dixitdb
-- Liquibase version: 4.6.2
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Lock Database
UPDATE DIXDROID.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'localhost.ontadomain (192.168.154.142)', LOCKGRANTED = TO_TIMESTAMP('2021-12-29 22:46:27.69
5', 'YYYY-MM-DD HH24:MI:SS.FF') WHERE ID = 1 AND LOCKED = 0;

-- Create Database Change Log Table
CREATE TABLE DIXDROID.DATABASECHANGELOG (ID VARCHAR2(255) NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255) NOT NULL, DATEEXECUTED TIMESTAMP NO
T NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, MD5SUM VARCHAR2(35), DESCRIPTION VARCHAR2(255), COMMENTS VARCHAR2(255), TAG VARCHAR2(
255), LIQUIBASE VARCHAR2(20), CONTEXTS VARCHAR2(255), LABELS VARCHAR2(255), DEPLOYMENT_ID VARCHAR2(10));

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Create Database Lock Table
CREATE TABLE DIXDROID.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DAT
ABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DIXDROID.DATABASECHANGELOGLOCK;

INSERT INTO DIXDROID.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

-- Changeset changelog.sql::1640835878960-1::root
CREATE TABLE DIXIT1 (ID NUMBER NOT NULL, DESCRIPTION VARCHAR(50), CONSTRAINT DIXIT1_PK PRIMARY KEY (ID));

INSERT INTO DIXDROID.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBA
SE, DEPLOYMENT_ID) VALUES ('1640835878960-1', 'root', 'changelog.h2.sql', SYSTIMESTAMP, 1, '8:17e41ee520cc38d8600cb88325a89679', 'sql', '', 'EXECUTED', NULL,
 NULL, '4.6.2', '0835988346');

-- Changeset changelog.h2.sql::1640835878960-2::root
CREATE SEQUENCE DIXIT1_SEQ START WITH 21 MAXVALUE 9999999999999999999999999999;

INSERT INTO DIXDROID.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBA
SE, DEPLOYMENT_ID) VALUES ('1640835878960-2', 'root', 'changelog.h2.sql', SYSTIMESTAMP, 2, '8:8b670db06f2e0ad02cf1deeec1f9b79b', 'sql', '', 'EXECUTED', NULL,
 NULL, '4.6.2', '0835988346');

-- Changeset changelog.sql::1640835878960-3::root
CREATE TABLE TEST (ID NUMBER(10, 0), NAME VARCHAR(30));

INSERT INTO DIXDROID.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBA
SE, DEPLOYMENT_ID) VALUES ('1640835878960-3', 'root', 'changelog.h2.sql', SYSTIMESTAMP, 3, '8:e56593db5135656a87586790c3d5b671', 'sql', '', 'EXECUTED', NULL,
 NULL, '4.6.2', '0835988346');

-- Changeset changelog.sql::1640835878960-4::root
CREATE INDEX IDX_TEST1 ON TEST(ID, NAME);

INSERT INTO DIXDROID.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBA
SE, DEPLOYMENT_ID) VALUES ('1640835878960-4', 'root', 'changelog.h2.sql', SYSTIMESTAMP, 4, '8:178519da11977278e2192549595aed7b', 'sql', '', 'EXECUTED', NULL,
 NULL, '4.6.2', '0835988346');

-- Release Database Lock
UPDATE DIXDROID.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

[root@localhost liquibase]#

Now after careful examination of the last .sql file, time to call the ‘update‘ command which deploys any changes that are in the changelog file and that have not been deployed to your database yet. During this step (first time) only it creates the DATABASECHANGELOG table which is used to track which changesets have been run, and the DATABASECHANGELOGLOCK table to ensure only one instance of Liquibase is running at one time.

When you run the update command, Liquibase sequentially reads changesets in the changelog file, then it compares the unique identifiers of id, author, and path to filename to the values stored in the DATABASECHANGELOG table.

You can see both of the two new (liquibase specific) tables DATABASECHANGELOGLOCK & DATABASECHANGELOG created under the schema.

SQL> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
TEST                           TABLE
IDX_TEST1                      INDEX
DIXIT1                         TABLE
DIXIT1_PK                      INDEX
DIXIT1_SEQ                     SEQUENCE
GET_DIXIT1_COUNT               FUNCTION
DATABASECHANGELOGLOCK          TABLE
PK_DATABASECHANGELOGLOCK       INDEX
DATABASECHANGELOG              TABLE

9 rows selected.


SQL> desc DATABASECHANGELOG
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 AUTHOR                                    NOT NULL VARCHAR2(255)
 FILENAME                                  NOT NULL VARCHAR2(255)
 DATEEXECUTED                              NOT NULL TIMESTAMP(6)
 ORDEREXECUTED                             NOT NULL NUMBER(38)
 EXECTYPE                                  NOT NULL VARCHAR2(10)
 MD5SUM                                             VARCHAR2(35)
 DESCRIPTION                                        VARCHAR2(255)
 COMMENTS                                           VARCHAR2(255)
 TAG                                                VARCHAR2(255)
 LIQUIBASE                                          VARCHAR2(20)
 CONTEXTS                                           VARCHAR2(255)
 LABELS                                             VARCHAR2(255)
 DEPLOYMENT_ID                                      VARCHAR2(10)

Now the baseline is created, we can create the next version of the database. I have created four new SQL files to create new sequence, view, function and a table with few records and an Index.

[root@localhost liquibase]# ls -ltrh *.sql*
-rwxrwxrwx. 1 root root  88 Dec 29 23:03 seq2.sql
-rwxrwxrwx. 1 root root  65 Dec 29 23:05 view1.sql
-rwxrwxrwx. 1 root root 172 Dec 29 23:07 func2.sql
-rwxrwxrwx. 1 root root 159 Dec 30 00:34 tab2.sql

I will now create the master.xml file which acts as a master index and is an ordered list of all changelogs. I have added a master.xml file with following contents. includeAll path=”/root/liquibase” is the XML tag that allows you to specify a directory that contains multiple changelog files.
include file=./changelog1.xml is the XML file which I have created and will be referenced or called by the master.xml file.

If you check the ‘changelog1.xml‘ file, it has the order that you want Liquibase to follow, like in my example it will first create the sequence after reading from file seq2.sql, followed by table script tab2.sql and last file which is to create the function using func2.sql There are few parameters used with the XML and are explained below.
relativeToChangelogFile=”true” : is cause we are using relative paths.
ID : tag is used to assign a unique value to the action.
endDelimiter: is the attribute can be set in a sql or sqlFile Change Type to override the default value of ;. The endDelimiter can be set to ” or to a character other than ; to indicate the end of the SQL statement.
stripComments : Set to true to remove any comments in the SQL before executing, otherwise false. Defaults to true if not set

[root@localhost liquibase]# more master.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
    <includeAll path="/root/liquibase"/>
    <include file="./changelog1.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
[root@localhost liquibase]#


-- Changelog XML file that is called by the above master.xml file.
[root@localhost liquibase]# more changelog1.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">

    <changeSet author="dixdroid" id="seq2">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="./seq2.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
    <changeSet author="dixdroid" id="table2">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="./tab2.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
    <changeSet author="dixdroid" id="getcount" runOnChange="true">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="./func2.sql"
               relativeToChangelogFile="true"
               splitStatements="false"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

Now let’s run the Liquibase ‘update‘ command using master.xml as the new changeLogFile which will perform all the changes that are mentioned in the master xml and related changelog1.xml

[root@localhost liquibase]# liquibase --username=dixdroid --password=dixdroid --changeLogFile="master.xml" update
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 00:36:07 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
Output saved to /root/liquibase/output_local.sql
Liquibase command 'update' was executed successfully.
[root@localhost liquibase]#

Let’s check if all new objects are created in the database, and we have table TESLA and its Index IDX_TESLA and RETURNTABLECOUNT function created.

SQL> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
TEST                           TABLE
IDX_TEST1                      INDEX
DIXIT1                         TABLE
DIXIT1_PK                      INDEX
DIXIT1_SEQ                     SEQUENCE
GET_DIXIT1_COUNT               FUNCTION
DATABASECHANGELOGLOCK          TABLE
PK_DATABASECHANGELOGLOCK       INDEX
DATABASECHANGELOG              TABLE
TESLA                          TABLE
IDX_TESLA                      INDEX
RETURNTABLECOUNT               FUNCTION

12 rows selected.


-- Lets check DATABASECHANGELOG table to view all details about this schema level change.

SQL> select id, AUTHOR,FILENAME,DATEEXECUTED,ORDEREXECUTED,DESCRIPTION,LIQUIBASE,DEPLOYMENT_ID from DATABASECHANGELOG;

ID         AUTHOR     FILENAME        DATEEXECUTED                   ORDEREXECUTED DESCRIPTIO LIQUIBASE            DEPLOYMENT
---------- ---------- --------------- ------------------------------ ------------- ---------- -------------------- ----------
seq2       dixdroid   changelog1.xml  30-DEC-21 12.33.02.051131 AM               1 sqlFile    4.6.2                0842381718
table2     dixdroid   changelog1.xml  30-DEC-21 12.36.11.790990 AM               2 sqlFile    4.6.2                0842571528
getcount   dixdroid   changelog1.xml  30-DEC-21 12.36.11.937545 AM               3 sqlFile    4.6.2                0842571528

Perfect! Let me add one more object to the schema and see what happens next and how details added to the changelog table. This time I will create a new VIEW using SQL file with name view1.sql. Below are the master.xml and changelog file that I have created for this new addition to the schema and to track it.

[root@localhost liquibase]# more changelog2.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">

    <changeSet author="dixdroid" id="view1">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="./view1.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>




[root@localhost liquibase]# more master.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
    <includeAll path="/root/liquibase"/>
    <include file="./changelog2.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
[root@localhost liquibase]#
[root@localhost liquibase]#

Time to run the update command once again using modified master.xml file to create view.

[root@localhost liquibase]#
[root@localhost liquibase]# liquibase --username=dixdroid --password=dixdroid --changeLogFile="master.xml" update
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 00:57:25 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
Output saved to /root/liquibase/output_local.sql
Liquibase command 'update' was executed successfully.
[root@localhost liquibase]#
[root@localhost liquibase]#


-- Letscheck if the VIEW named PEEK is created or not!


SQL> select object_name, object_type from user_objects;
OBJECT_NAME                                                                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
TEST                                                                                                                             TABLE
IDX_TEST1                                                                                                                        INDEX
DIXIT1                                                                                                                           TABLE
DIXIT1_PK                                                                                                                        INDEX
DIXIT1_SEQ                                                                                                                       SEQUENCE
GET_DIXIT1_COUNT                                                                                                                 FUNCTION
DATABASECHANGELOGLOCK                                                                                                            TABLE
PK_DATABASECHANGELOGLOCK                                                                                                         INDEX
DATABASECHANGELOG                                                                                                                TABLE
TESLA                                                                                                                            TABLE
IDX_TESLA                                                                                                                        INDEX
RETURNTABLECOUNT                                                                                                                 FUNCTION
PEEK                                                                                                                             VIEW

13 rows selected.

SQL>  select id, AUTHOR,FILENAME,DATEEXECUTED,ORDEREXECUTED,DESCRIPTION,LIQUIBASE,DEPLOYMENT_ID from DATABASECHANGELOG;


ID         AUTHOR     FILENAME        DATEEXECUTED                   ORDEREXECUTED DESCRIPTIO LIQUIBASE            DEPLOYMENT
---------- ---------- --------------- ------------------------------ ------------- ---------- -------------------- ----------
seq2       dixdroid   changelog1.xml  30-DEC-21 12.33.02.051131 AM               1 sqlFile    4.6.2                0842381718
table2     dixdroid   changelog1.xml  30-DEC-21 12.36.11.790990 AM               2 sqlFile    4.6.2                0842571528
getcount   dixdroid   changelog1.xml  30-DEC-21 12.36.11.937545 AM               3 sqlFile    4.6.2                0842571528
view1      dixdroid   changelog2.xml  30-DEC-21 12.57.31.827752 AM               4 sqlFile    4.6.2                0843851310

Great, its there!
About Liquibase, you can use it as core DevOps tool to track schema changes and for deployments and automations, integrate it with your CI/CD pipelines or can be used as a migration tool. There are multiple use cases. For more details check their official website.

Hope It Helped!
Prashant Dixit

Posted in Basics | Tagged: , , , | 1 Comment »

Too many INACTIVE sessions ? Is this due to JDBC connection leakage ?

Posted by FatDBA on December 21, 2021

Hi All,

Last week I was looking into a database problem where the customer reported database hang issues when trying to use their Java based application, and that application requires to connect with the database every now and then. This was a brand new platform where they were doing some UAT testing’s and were using WebLogic which they recently configured to use with connection pooling enabled.

Application team reported that they are getting frequent ‘ORA-00018 maximum number of sessions exceeded’ errors in the alert log and that was a big clue. Operations team tried many times to identify all INACTIVE sessions (session limit was 800 in the DB) and manually kill them, but new JDBC Thin Client gets spawns every time … I was sure that this is most probably the JDBC connection leak issue, and those are always difficult to identify!

This is what it was, the peak at the time of issue

Now talking about – How to detect this problem ?. Though there were multiple other performance problem on this platform, but none of them seems related with the SQLs, as the execution plan were perfect and reflects a well designed SQL workload. So I suspected the application, started with the WebLogic server logs and there I saw multiple instances of “BEA-000627 Reached maximum capacity of pool “cgDataSource”, making “0” new resource instances instead of “1” “ warnings, and with that, it once again solidified my initial assumption that it as happening all as a result of applications’ code not closing connections properly, connection leakage.

A leaked connection is a connection that was not properly returned to the connection pool in the data source. To automatically recover leaked connections, you can specify a value for Inactive Connection Timeout on the JDBC Data Source. When you set a value for Inactive Connection Timeout, WebLogic Server forcibly returns a connection to the data source when there is no activity on a reserved connection for the number of seconds that you specify. When set to 0 (the default value), this feature is turned off. So we have to we need to set one specific parameter for the data source to force close those connections that are not closed by the application.

Click on Services --> Data Sources --> Click on the Data Source you want to configure --> Click on Connection Pool --> Click on Advanced --> “Inactive Connection Timeout”

And it was set to its default value of 0, means no inactive sessions will be snapped or removed and will stay in the database and consume sessions limit. After carefull observation and a discussion with the team, we set it to a reasonable value and bounced Weblogic Admin and manager servers (MS) to make changes persistent.

But if this was the solution to the problem ? No, it was not! and is always a remedy, to immediately handle the situation while the root cause of the problem is investigated.
I later on investigated the log files in order to isolate the culprit class. Also noticed that after we’d set the ‘Inactive Connection Timeout’ setting, multiple instances of BEA-001153 Warnings were captured within logs. This was because the code does not close the connection, and was waiting for the “Inactive connection timeout” to trigger closure of these objects and that had caused the warning message printed in the admin server logs.

<Dec 3, 2021 10:12:34 AM GMT> 
<Warning> <JDBC> <BEA-001153> <Forcibly releasing inactive connection "weblogic.jdbc.wrapper.PoolConnection_oracle_jdbc_driver_xxCConnection@xxx" back into the connection pool "xxxxxxxx-xxx", currently reserved by: java.lang.Exception
at weblogic.jdbc.common.internal.ConnectionEnv.setup(ConnectionEnv.java:325)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:363)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:329)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:417)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:324)
at weblogic.jdbc.common.internal.MultiPool.searchLoadBalance(MultiPool.java:312)
at weblogic.jdbc.common.internal.MultiPool.findPool(MultiPool.java:180)
at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:89)
at weblogic.jdbc.common.internal.RmiDataSource.getPoolConnection(RmiDataSource.java:350)
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:369)
at oracle.jbo.server.DBTransactionImpl.establishNewConnection(DBTransactionImpl.java:990)
.....
..........

Later I collected JDBC Diagnostic Dumps via admin console to detect and further troubleshoot this JDBC Connection Leak.

Dumping Resource Pool:cgDataSource
Resource Pool:cgDataSource:dumpPool Current Capacity = 4
Resource Pool:cgDataSource:dumpPool dumping available resources, #entries = 0
Resource Pool:cgDataSource:dumpPool dumping reserved resources, #entries = 4
Resource Pool:cgDataSource:dumpPool reserved[0] = autoCommit=true, enabled=true, isXA=true, isJTS=false, vendorID=11, connUsed=true, doInit=false, 'null', destroyed=false, poolname=cgDataSource, appname=null, moduleName=null, connectTime=4941, dirtyIsolationLevel=false, initialIsolationLevel=2, infected=false, lastSuccessfulConnectionUse=1344715611974, secondsToTrustAnIdlePoolConnection=10, currentUser=java.lang.Exception
  at weblogic.jdbc.common.internal.ConnectionEnv.setup(ConnectionEnv.java:308)
  at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:314)
  at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:292)
  at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:425)
  at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:316)
  at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:93)
  at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:61)
  at weblogic.jdbc.jta.DataSource.getXAConnectionFromPool(DataSource.java:1473)
  at weblogic.jdbc.jta.DataSource.refreshXAConnAndEnlist(DataSource.java:1302)
  at weblogic.jdbc.jta.DataSource.getConnection(DataSource.java:425)
  at weblogic.jdbc.jta.DataSource.connect(DataSource.java:382)
  at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:338)
  at troubleshooting.servlets.JdbcConnections.service(JdbcConnections.java:97)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
  at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
......
.........
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:338)
  at troubleshooting.servlets.JdbcConnections.service(JdbcConnections.java:97)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
  at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
.........
...............
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:338)
  at troubleshooting.servlets.JdbcConnections.service(JdbcConnections.java:97)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
  at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
.....
......
at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:338)
  at troubleshooting.servlets.JdbcConnections.service(JdbcConnections.java:97)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
  at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
......
.........

If you watch carefully, on all the dumps above, the method that initiates the connection to the database is troubleshooting.servlets.JdbcConnections.service(), specifically at line 97 on Class JdbcConnections.java, as the stack says.

We went back to the source code for the application (dummy code):

      } else if (i == -1) {
        try {
          InitialContext localInitialContext2 = new InitialContext();

          DataSource localDataSource = (DataSource)localInitialContext2.lookup("cgDataSource");    <========== HERE IS WHERE THE LEAK STARTS

          localObject = localDataSource.getConnection();                                          
          localObject = null;                                                                      <========== HERE IS WHERE THE LEAK OCCURS, WHEN OBJECT IS SET TO NULL BUT CONNECTION NOT CLOSED.
          System.out.println("xxxxxxxxx.");
          localInitialContext2.close();
        } catch (Exception localException2) {
localPrintWriter.println("An exception has been thrown while trying to increase the number of JDBC connection to " + i + ", the error is<br><br>");

And application team immediately recognized the cause, there was a need to explicitly closed the connection and was later on fixed by the application team to something below.

          InitialContext localInitialContext2 = new InitialContext();

          DataSource localDataSource = (DataSource)localInitialContext2.lookup("cgDataSource");

          localObject = localDataSource.getConnection();

          System.out.println("xxxxxxxxx.");
          localInitialContext2.close();
        } catch (Exception localException2) {
          localPrintWriter.println("An exception has been thrown while trying to increase the number of JDBC connection to " + i + ", the error is<br><br>");

        } finally{

          localObject.close()
          localObject = null;

         }

So, that’s how it got resolved after changing the problematic code. Connection leakage is a very tricky scenario which requires careful observation primarily of the application code.

Hope It Helped
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | 2 Comments »

Security vulnerability in Oracle EBS : CVE-2021-44228

Posted by FatDBA on December 14, 2021

Hi All,

Recently while doing a database migration/upgrade project, we encountered a strange case where the orachk utility caught a new security vulnerability (CVE-2021-44228) on this new upgraded platform, and were related with customer’s logging platform log4j and for their EBS (E-business suite version 12.2) middleware. The vulnerability was for its JNDI features that do not protect against attacker controlled LDAP and other JNDI related endpoints, and coming with a 10 out of 10 severity score.

We checked with Oracle customer support and they asked us to apply a workaround (link below). I later on found that its not only Oracle products, but has impacted many other applications & cloud services. This weakness poses a significant risk to many applications and cloud services and it needs to be patched right away!

Oracle document for the alert: https://www.oracle.com/security-alerts/alert-cve-2021-44228.html

Master note for this alert: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=178124739549299&id=2827611.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=zowp8g1a4_369

Oracle EBS related fix : https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=178249002646089&id=2827804.1&_afrWindowMode=0&_adf.ctrl-state=zowp8g1a4_418

Hope It Worked!
Prashant Dixit

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

Why the optimizer not picking the correct Index ? Bringing chaos to order ..

Posted by FatDBA on December 13, 2021

Hi Guys,

Would like to discuss one interesting problem that I’ve recently faced while supporting one of the customer migration, where one critical SQL statement turned out to be very slow on this new infrastructure. They moved from 12.1 standalone to 12.2 2-Node RAC cluster and moved to a brand new hardware, but with similar HW and resource configurations as earlier.

As per the team, one of the core application API was behaving very slow while processing requests, and also failing due to time limits applied within the application. Hence the entire system becoming slow after they moved the workload to this new system.

Query was using multiple subqueries or nesting results that it got from them using a UNION ALL operator which returned all rows as it does not eliminate duplicate selected rows. The query was frequently waiting on User IO wait class, specially on ‘direct path read‘ and ‘db file sequential read‘ events, and took ~ 2.7 minutes to complete, but was expected to complete in less than a second. The query text was something like below ..

(select * from (select * from dix_table_A where perfor_column_12=:1 and testcas_idnu=:2 ) where rownum <= :"SYS_B_0") 
union all (select * from (select * from dix_table_A where perfor_column_12=:3 and testcas_idnu=:4 ) where rownum <= :"SYS_B_1") 
union all (select * from (select * from dix_table_A where perfor_column_12=:5 and testcas_idnu=:6 ) where rownum <= :"SYS_B_2") 
union all (select * from (select * from dix_table_A where perfor_column_12=:7 and testcas_idnu=:8 ) where rownum <= :"SYS_B_3");


SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME AVG_CPU_TIME        AVG_LIO      AVG_PIO
------------- --------------- ------------ ------------ ------------ -------------- ------------
8sn7dhnash901      891893112           45      162.276      101.390   19,618,917.8  11,911,560.6


--- Execution Plan of the ill/slow SQL
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |       |    24 (100)|          |
|   1 |  UNION-ALL                            |               |       |       |            |          |
|   2 |   COUNT STOPKEY                       |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |   187 | 17952 |     6   (0)| 00:00:03 |
|   4 |     INDEX SKIP SCAN                   | I2DIX_TABLE_A |   168K|       |     2   (0)| 00:00:03 |
|   5 |   COUNT STOPKEY                       |               |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |   187 | 17952 |     6   (0)| 00:00:03 |
|   7 |     INDEX SKIP SCAN                   | I2DIX_TABLE_A |   168K|       |     2   (0)| 00:00:03 |
|   8 |   COUNT STOPKEY                       |               |       |       |            |          |
|   9 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |   187 | 17952 |     6   (0)| 00:00:03 |
|  10 |     INDEX SKIP SCAN                   | I2DIX_TABLE_A |   168K|       |     2   (0)| 00:00:03 |
|  11 |   COUNT STOPKEY                       |               |       |       |            |          |
|  12 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |   187 | 17952 |     6   (0)| 00:00:03 |
|  13 |     INDEX SKIP SCAN                   | I2DIX_TABLE_A |   168K|       |     2   (0)| 00:00:03 |
-------------------------------------------------------------------------------------------------------

As per the SQL text, its querying PERFOR_COLUMN_12 and TESTCASE_IDNU columns in the WHERE clause, and per the execution plan (above), I2DIX_TABLE_A index is picked to prepare the plan with INDEX SKIP SCAN as the access method/path for said Index. Index skip scan means, that the leading or other columns of the index is ignored. This costs performance since Oracle has read every item of the first column, second or subsequent columns and check if the second (or third etc.) column is what you searched for. This usually is faster than a full-table scan (depends on your query), but slower than a index range scan.

I2DIX_TABLE_A index is a multi column index which has both of the referenced columns PERFOR_COLUMN_12 and TESTCASE_IDNU, but in the reverse direction, not as the leading columns.

-- I2DIX_TABLE_A Index DDL (BAD Index)
CREATE UNIQUE INDEX "DIXIT"."I2DIX_TABLE_A" ON "DIXIT"."DIX_TABLE_A" ("PRODUCT_CLASSIC_CAT", "BUILD_CLASSIC_ID", "TESTCASE_IDNU", "PERFOR_COLUMN_12");

While doing further analysis, I saw there is already one dedicated index I1DIX_TABLE_A there to cover this combination of columns PERFOR_COLUMN_12 and TESTCASE_IDNU, and in the right order too, but was ignored by the optimizer while preparing the estimations or costing.

-- I1DIX_TABLE_A Index DDL (GOOD Index)
CREATE INDEX "DIXIT"."I1DIX_TABLE_A" ON "DIXIT"."DIX_TABLE_A" ("PERFOR_COLUMN_12", "TESTCASE_IDNU");

Now, this is strange, why the CBO didn’t considered that index even when the required column set is present in the other index and in the right order, and instead it opted to go for an expensive index ? These are the other stats about both the two indexes.

INDEX_NAME           UNIQUENES     BLEVEL DEGREE   TABLE_NAME      NUM_ROWS    LEAF_BLOCKS  INI_TRANS  MAX_TRANS STATUS  LAST_ANAL
-------------------- --------- ---------- -------- --------------- ----------- ------------ ---------- --------- ------  ------------
I1DIX_TABLE_A        NONUNIQUE          4 1        DIX_TABLE_A     33457571    942391        2         255       VALID    01-DEC-21
I2DIX_TABLE_A        UNIQUE             3 1        DIX_TABLE_A     35084294    494579        2         255       VALID    01-DEC-21

All statistics looks good, I mean the row count is almost same, ITL initial and max values, status and stats collection date, but the difference is there for the branch level (BLevel) and Leaf_Blocks as they are different. Taking about the Blevel, it’s the part of the B-tree index that relates to the number of times Oracle has to narrow its search on the index while searching for a particular record, or the blevel is the number of branch levels. On the other hand, leaf_blocks represents number of leaf blocks in an index.

As we know the Blevel and Clustering Factor are important elements of an index scan cost. The index that has lower values for these is likely to be chosen. I2DIX_TABLE_A has lower values than I1DIX_TABLE_A. About the CF, It is hard to decrease clustering factor because it requires rebuilding the table.

So, lets focus to decrease the BLEVEL, will try rebuilding I1DIX_TABLE_A as that might help to decrease the Blevel.

SQL> alter index dixit.I1DIX_TABLE_A rebuild;

Index altered.


-- Index stats after rebuilding 
INDEX_NAME           UNIQUENES     BLEVEL DEGREE   TABLE_NAME      NUM_ROWS    LEAF_BLOCKS  INI_TRANS  MAX_TRANS STATUS  LAST_ANAL
-------------------- --------- ---------- -------- --------------- ----------- ------------ ---------- --------- ------  ------------
I1DIX_TABLE_A        NONUNIQUE          3 1        DIX_TABLE_A     33457571    446703        2         255       VALID    01-DEC-21
I2DIX_TABLE_A        UNIQUE             3 1        DIX_TABLE_A     35084294    494579        2         255       VALID    01-DEC-21

And Yes, both the BLEVEL and the LEAF_BLOCKS of I1DIX_TABLE_A got reset, and I’ve immediately started hearing some good news from the application team about query’s runtime improvements.
The expensive index I2DIX_TABLE_A was finally replaced by the good I1DIX_TABLE_A and that’s how the INDEX SKIP SCAN was replaced with the fast INDEX RANGE SCAN.

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |     8 |   10M |     4   (0)| 00:00:01 |
|   1 |  UNION-ALL                            |               |       |       |            |          |
|*  2 |   COUNT STOPKEY                       |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |     2 |   192 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | I1DIX_TABLE_A | 28707 |       |     1   (0)| 00:00:01 |
|*  5 |   COUNT STOPKEY                       |               |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |     2 |   192 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN                  | I1DIX_TABLE_A | 28707 |       |     1   (0)| 00:00:01 |
|*  8 |   COUNT STOPKEY                       |               |       |       |            |          |
|   9 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |     2 |   192 |     1   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN                  | I1DIX_TABLE_A | 28707 |       |     1   (0)| 00:00:01 |
|* 11 |   COUNT STOPKEY                       |               |       |       |            |          |
|  12 |    TABLE ACCESS BY INDEX ROWID BATCHED| DIX_TABLE_A   |     2 |   192 |     1   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN                  | I1DIX_TABLE_A | 28707 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

You might be thinking that the other difference is the UNIQUENESS, though the Unique scan cost is lower than Range scan cost. But, in this case, unique scan cannot be used for any index. Unique index is NOT always used for “unique scan.” It depends on predicates. The bad index is chosen not because it is a unique index but its scan cost is lower.

Hope It Helped
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | 4 Comments »

 
%d bloggers like this: