Installing and Configuring cx_Oracle on CentOS 6

cx_Oracle module of python is used to connect to Oracle from Python. This works only with python 2.7. Here are the steps that can be used to install python2.7 on CentOS 6 and cx_Oracle module.

Installing python 2.7 as alternate python installation on CentOS 6

wget "https://www.python.org/ftp/python/2.7.13/Python-2.7.13.tgz"
tar -zxvf Python-2.7.13.tgz 
cd Python-2.7.13
./configure 
make
make altinstall 
which python2.7 # To verify installation path

Installing Oracle Instant Client

Download Oracle Instant Client from this link. We need instant client and instant client SDK. To install Oracle Instant client just unzip both the zip files to /opt or any other direcory

Create symbolic link libclntsh.so so it points to correct version.

cd /opt/instantclient_11_2/
ln -s libclntsh.so.11.1 libclntsh.so

Set environment variables

export ORACLE_HOME=/opt/instantclient_11_2
export LD_LIBRARY_PATH=/opt/instantclient_11_2

Installing pip for python 2.7

pip is a package management system used to install python libraries. Here are the steps to install.

wget https://bootstrap.pypa.io/get-pip.py
python2.7 get-pip.py

Installing cx_Oracle module using pip

pip install cx_Oracle

Sample program to test cx_Oracle

import cx_Oracle

connection = cx_Oracle.connect ("username/password@hostname/service")

cursor = connection.cursor ()
cursor.execute ("SELECT 1 a, 'AA' b, sysdate c FROM dual union select 2 a, 'BB' b, sysdate + 1 c from dual")
result = cursor.fetchall ()

for row in result:
    print row[0], row[1], row[2]

cursor.close ()
connection.close ()

Analyze AWS billing detailed line items file, and getting bill details using script

We can configure itemized bill of AWS to be uploaded S3. It can be done from "Billing Management Console" of AWS -> "Preferences", and turning on option "Receive Billing Reports". Now we can do analysis of "Detailed billing report with resources and tags", and get bill amount in INR, and know which resources are getting billed more etc.

Here is the script that provides analysis of itemized bill that's already uploaded to S3.

# This script uses command line utility q - Text as Data
# Download Page: https://harelba.github.io/q/

# This script expects aws - CLI already configured on the system

# Hard coded values first
account_id=xxxxxxxxx #AWS account ID
tax=15  # Service tax + Swacch Bharath + Krishi Kalyan
bucket_name="xxxxxx" #Name of S3 bucket where billing reports are uploaded automatically
month=`date +"%Y-%m"` #Current month

#month=2017-02 #Needed if bill of another month is needed

# Get currency conversion rate from Google
usd=`wget -q -O - "http://www.google.com/finance/converter?a=1&from=USD&to=INR" \
      | grep "currency_converter_result" \
      | sed 's/<[^>]*>//g' \
      | cut -f4 -d ' '`

echo "Conversion Rate: $usd, Tax=$tax% "
echo " "

aws_url="s3://$bucket_name/$account_id-aws-billing-detailed-line-items-with-resources-and-tags-AISPL-$month.csv.zip"

rm -f bill_line_items*

aws s3 cp $aws_url bill_line_items.csv.zip --quiet
unzip -p bill_line_items.csv.zip > bill_line_items.csv

cat bill_line_items.csv | q -T -b -O -H -d "," -w all \
"select \
    \"user:Name\" as Resource_Name, \
    UsageType,\
    strftime('%Y-%m-%d %H:%M', datetime(UsageStartDate,'+5.5 hours')) as Usage_Start_Time, \
    round(Cost*$usd*(1+$tax/100.0),2) as \"Cost+Tax(INR)\"  \
from -  \
where cost<>0 and Rate <> '' \
order by UsageEndDate, Resource_Name"

echo " "

cat bill_line_items.csv | q -T -b -H -d "," -w all \
"select 'Total cost in INR including tax: ' || \
 sum( round(Cost*$usd*(1+$tax/100.0),2)) \
 from - where cost<>0 and Rate <> '' "

Sample output

Conversion Rate: 66.2050, Tax=15% 
 
Resource_Name   UsageType                   Usage_Start_Time    Cost+Tax(INR)
node1       APS3-BoxUsage:t2.large      2017-03-03 16:30    9.06
Hadoop      APS3-BoxUsage:t2.large      2017-03-06 17:30    9.06
Hadoop      APS3-BoxUsage:t2.large      2017-03-06 18:30    9.06
.....
Cloudera1   APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
gw          APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.09
hadoop      APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.23
node1       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node2       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node3       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
node4       APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.17
photos_os   APS3-EBS:VolumeUsage.gp2    2017-03-14 03:30    0.09
 
Total cost in INR including tax: 297.81

Working with AWS EC2 instances from aws command line tool

Command to list instances using JMESPath query

aws ec2 describe-instances --query "Reservations[*].Instances[*].[InstanceId, Tags[?Key=='Name'].Value|[0], State.Name, PrivateIpAddress, InstanceType]" --output=table

-------------------------------------------------------------------------------
|                              DescribeInstances                              |
+----------------------+------------+----------+-----------------+------------+
|  i-xxxxxxxxxxxxxxxxx |  node0     |  running |  172.31.11.20   |  t2.micro  |
|  i-yyyyyyyyyyyyyyyyy |  node1     |  stopped |  172.31.11.21   |  t2.large  |
+----------------------+------------+----------+-----------------+------------+

Command to start instances

aws ec2 start-instances --instance-ids i-xxxxxxxxxxxxxxxxx i-yyyyyyyyyyyyyyyyy

Command to stop instances

aws ec2 stop-instances --instance-ids i-xxxxxxxxxxxxxxxxx i-yyyyyyyyyyyyyyyyy

Conversion between UTF-16, UTF-8 encoded files on Linux

1. Introduction

The encoding used by Windows for Unicode is UTF-16, to be specific, UTF-16LE (Little Endian). Linux uses UTF-8 to encode Unicode. A file encoded with Unicode can optionally contain a Byte Order Mark(BOM) which is a special magic number at the start of file. Byte Order Mark(BOM) is optional for UTF-8, but mandatory for UTF-16 as per Unicode standard. So, Linux does not use BOM for Unicode files as it uses UTF-8. But Windows applications look for BOM in Unicode encoded file as they use UTF-16.

So in summary, Windows uses UTF-16LE with BOM, and Linux uses UTF-8 without BOM.

To verify type of encoding used for a file, we can use file command on Linux.

$ file Unicode_Windows.txt 
Unicode_Windows.txt: Little-endian UTF-16 Unicode text, with CR line terminators

We can see more details using hexdump also,

$ hexdump -C Unicode_Windows.txt 
00000000  ff fe 24 0c 46 0c 32 0c  41 0c 17 0c 41 0c 0d 00  |..$.F.2.A...A...|
00000010  0a 00                                             |..|
00000012

ff fe is BOM for UTF-16LE, and we can see end of line character as 0d 00 (Carriage Return CR) and 0a 00 (Line Feed LF)

2. Converting from UTF-16 to UTF-8

Above file is created in Windows. To convert it to Linux encoding, there are multiple ways.

2a. Using iconv

$ iconv -f UTF-16LE -t UTF-8 Unicode_Windows.txt > Unicode_Linux1.txt

Let us check this file.

$ file Unicode_Linux1.txt 
Unicode_Linux1.txt: UTF-8 Unicode (with BOM) text, with CRLF line terminators

$ hexdump -C Unicode_Linux1.txt 
00000000  ef bb bf e0 b0 a4 e0 b1  86 e0 b0 b2 e0 b1 81 e0  |................|
00000010  b0 97 e0 b1 81 0d 0a                              |.......|
00000017

This converts to UTF-8, but keeps BOM at the begining of file (ef bb bf). Also we have CR (0d) and LF (0a) characters for end of line. So, convert it to UTF-8 without BOM and CR, here is the command.

$ iconv -f UTF-16LE -t UTF-8 Unicode_Windows.txt | sed 1s/^.//g | sed s/"\r$"//g > Unicode_Linux1.txt

We can verify it using below commands.

$ file Unicode_Linux1.txt 
Unicode_Linux1.txt: UTF-8 Unicode text

$ hexdump -C Unicode_Linux1.txt 
00000000  e0 b0 a4 e0 b1 86 e0 b0  b2 e0 b1 81 e0 b0 97 e0  |................|
00000010  b1 81 0a                                          |...|
00000013

2b. Using dos2unix command

We can also use dos2unix command, which converts file from UTF-16LE to UTF-8, and also removes BOM and CR characters. Here is the example,

$ dos2unix -n Unicode_Windows.txt Unicode_Linux2.txt 
dos2unix: converting file Unicode_Windows.txt to file Unicode_Linux2.txt in Unix format ...

$ file Unicode_Linux2.txt 
Unicode_Linux2.txt: UTF-8 Unicode text

$ hexdump -C Unicode_Linux2.txt 
00000000  e0 b0 a4 e0 b1 86 e0 b0  b2 e0 b1 81 e0 b0 97 e0  |................|
00000010  b1 81 0a                                          |...|
00000013

3. Converting from UTF-8 to UTF-16

Now to convert files from UTF-8 to UTF-16LE on Linux, there is no direct way. The command unix2dos coverts from UTF-8 to UTF-8 only just by adding CR character. Also, unix2dos does not add BOM by default. So, we have to force it with -m option.

$ unix2dos -m -n Unicode_Linux1.txt Unicode_Windows1.txt 
unix2dos: converting file Unicode_Linux1.txt to file Unicode_Windows1.txt in DOS format ...

$ file Unicode_Windows1.txt 
Unicode_Windows1.txt: UTF-8 Unicode (with BOM) text, with CRLF line terminators

$ hexdump -C Unicode_Windows1.txt 
00000000  ef bb bf e0 b0 a4 e0 b1  86 e0 b0 b2 e0 b1 81 e0  |................|
00000010  b0 97 e0 b1 81 0d 0a                              |.......|
00000017

To covert it to UTF-16LE, we have to use iconv command after using unix2dos.

$ iconv -f UTF-8 -t UTF-16LE Unicode_Windows1.txt > Unicode_Windows2.txt

$ file Unicode_Windows2.txt 
Unicode_Windows2.txt: Little-endian UTF-16 Unicode text, with CR line terminators

$ hexdump -C Unicode_Windows2.txt
00000000  ff fe 24 0c 46 0c 32 0c  41 0c 17 0c 41 0c 0d 00  |..$.F.2.A...A...|
00000010  0a 00                                             |..|
00000012

Instead of using unix2dos, we can directly use sed command to add BOM and CR like below and covert to UTF-16.

$ sed 1s/^/"\xef\xbb\xbf"/g Unicode_Linux1.txt | sed s/$/"\r"/g | iconv -f UTF-8 -t UTF-16LE > Unicode_Windows3.txt 
 
$ file Unicode_Windows3.txt 
Unicode_Windows3.txt: Little-endian UTF-16 Unicode text, with CR line terminators

$ hexdump -C Unicode_Windows3.txt 
00000000  ff fe 24 0c 46 0c 32 0c  41 0c 17 0c 41 0c 0d 00  |..$.F.2.A...A...|
00000010  0a 00                                             |..|
00000012

Fixing execution plans using SQL plan baselines

This post shows how to use SQL plan baselines to fix execution plan of a query. This can be used as a replacement for stored outlines.

For this demonstration we create 2 tables each with a row and collect statistics on them.

create  table tab1 (a number, b varchar2(10));
insert into tab1 values(10, 'AA');
create index tab1_i1 on tab1(a);

create  table tab2 (a number, c varchar2(10));
create index tab2_i1 on tab2(a);
insert into tab2 values(10, 'BBB');

exec dbms_stats.gather_table_stats (ownname=>'MURTY', tabname=>'TAB1', estimate_percent=>10, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true);
exec dbms_stats.gather_table_stats (ownname=>'MURTY', tabname=>'TAB2', estimate_percent=>10, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true);

Here is the query for which we will change execution plan using SQL plan baselines.

select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

The execution plan shows nested loop join

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684952675

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | TAB1    |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TAB2_I1 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("TAB1"."A"="TAB2"."A")

17 rows selected.

If the parameter optimizer_capture_sql_plan_baselines is set to true, after multiple executions of the query, it will create a baseline automatically.

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3684952675

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | TAB1    |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TAB2_I1 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("TAB1"."A"="TAB2"."A")

Note
-----
   - SQL plan baseline "SQL_PLAN_2ppbwajdruq05fc48919a" used for this statement

21 rows selected.

If the parameter optimizer_capture_sql_plan_baselines is set to false, we can create a SQL plan baseline manually using SQL_ID of statement.

SQL> select sql_id, sql_text from v$sql where sql_text like 'select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a';

SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
bqn8dqudd4ajf
select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a

set serveroutput on
declare
  i pls_integer;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'bqn8dqudd4ajf');
  dbms_output.put_line('Plans Loaded: ' || i);
END;
/
Plans Loaded: 1

PL/SQL procedure successfully completed.

Details of SQL Plan baselines can be viewed from dictionary view dba_sql_plan_baselines

SQL> select SQL_HANDLE, SQL_TEXT, PLAN_NAME from dba_sql_plan_baselines where PLAN_NAME='SQL_PLAN_2ppbwajdruq05fc48919a';

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SQL_2ad57c545b7d5805           select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a                SQL_PLAN_2ppbwajdruq05fc48919a

And more details can be get like below using sql_handle

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic'));

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

--------------------------------------------------------------------------------
SQL handle: SQL_2ad57c545b7d5805
SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05fc48919a         Plan id: 4232614298
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3684952675

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | TAB1    |
|   4 |    INDEX RANGE SCAN          | TAB2_I1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |
------------------------------------------------

23 rows selected.

Now we will try to force sort merge join for the same SQL using hint USE_MERGE

SQL> explain plan for select /*+ USE_MERGE(tab1 tab2) */ tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1351227119

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     1 |    13 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |     1 |     6 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     1 |     7 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | TAB2    |     1 |     7 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("TAB1"."A"="TAB2"."A")
       filter("TAB1"."A"="TAB2"."A")

18 rows selected.

Execute once to get details in v$sql_plan, and get SQL_ID

SQL> select /*+ USE_MERGE(tab1 tab2) */ tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

         A B          C
---------- ---------- ----------
        10 AA         BBB
        
SQL> select distinct SQL_ID from v$sql_plan where PLAN_HASH_VALUE=1351227119;

SQL_ID
-------------
fqjpqc2t0byr0

Now we will add the plan that uses sort merge join to SQL plan baseline. So, without using any hint, the execution of query uses sort merge join. Here we are using sql_handle of baseline created for original query, but sql_id and plan_hash_value of hinted query.

set serveroutput on
declare
  i pls_integer;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'fqjpqc2t0byr0', plan_hash_value => 1351227119, sql_handle => 'SQL_2ad57c545b7d5805');
  dbms_output.put_line('Plans Loaded: ' || i);
END;
/
Plans Loaded: 1

PL/SQL procedure successfully completed.

Baseline of original query now shows 2 plans.

SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic'));

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

--------------------------------------------------------------------------------
SQL handle: SQL_2ad57c545b7d5805
SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05e3fe5496         Plan id: 3825095830
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1351227119

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  MERGE JOIN                  |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |
|   4 |   SORT JOIN                  |         |
|   5 |    TABLE ACCESS FULL         | TAB2    |
------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05fc48919a         Plan id: 4232614298
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE 
--------------------------------------------------------------------------------

Plan hash value: 3684952675

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | TAB1    |
|   4 |    INDEX RANGE SCAN          | TAB2_I1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |
------------------------------------------------

41 rows selected.

But still we see nested loop join plan only for the query.

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684952675

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |         |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | TAB1    |     1 |     6 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | TAB2_I1 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("TAB1"."A"="TAB2"."A")

Note
-----
   - SQL plan baseline "SQL_PLAN_2ppbwajdruq05fc48919a" used for this statement

21 rows selected.

We will make sort-merge join plan as fixed, so it always uses that fixed execution plan.

declare
  i  pls_integer;
begin
  i := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_2ad57c545b7d5805', plan_name=>'SQL_PLAN_2ppbwajdruq05e3fe5496', attribute_name=>'FIXED', attribute_value=>'YES');
  dbms_output.put_line('Plans altered: ' || i);
end;
/
Plans altered: 1

PL/SQL procedure successfully completed.

Details of baseline now show that sort-merge plan as fixed.

SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic'));

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

--------------------------------------------------------------------------------
SQL handle: SQL_2ad57c545b7d5805
SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05e3fe5496         Plan id: 3825095830
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1351227119

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  MERGE JOIN                  |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |
|   4 |   SORT JOIN                  |         |
|   5 |    TABLE ACCESS FULL         | TAB2    |
------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2ppbwajdruq05fc48919a         Plan id: 4232614298
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3684952675

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | TAB1    |
|   4 |    INDEX RANGE SCAN          | TAB2_I1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TAB2    |
------------------------------------------------

41 rows selected.

So, we can get execution plan with sort-merge without any hints.

SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1351227119

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |     1 |    13 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1    |     1 |     6 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | TAB1_I1 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |     1 |     7 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | TAB2    |     1 |     7 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("TAB1"."A"="TAB2"."A")
       filter("TAB1"."A"="TAB2"."A")

Note
-----
   - SQL plan baseline "SQL_PLAN_2ppbwajdruq05e3fe5496" used for this statement

22 rows selected.

If we do not like to use plans from SQL Plan baseline, we can set optimizer_use_sql_plan_baselines parameter as false. Also, if we want to drop baseline, we can do it like below.

declare
  i  pls_integer;
begin
  i :=  dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_2ad57c545b7d5805',plan_name=>NULL);
  dbms_output.put_line('Plans removed: ' || i);
end;
/
Plans removed: 2

PL/SQL procedure successfully completed.