Blog Feed - alfredokriegdba.com

Collaborate 2019 Presentations

Collaborate 2019 will take place April 7-11 in San Antonio this year.

This time I have 3 presentations accepted and ready to roll starting April 7^th.

Simplifying EBS 12.2 ADOP

In this session I’ll talk about basic ADOP concepts, how to us them and real-life problems and best practice recommendations.

Listen to your Database. How to Understand an Oracle AWR Report.

This session is one of my favorites. In this quick tip I’ll show you the method I use to quickly identify a bottleneck in the DB system using an AWR report.

SMART Performance Monitoring. Exadata and OEM 13c.

I’ll talk about a product that has been set aside by Oracle for a while. Yes, that is OEM 13c.

This session will cover how to monitor Exadata systems using OEM 13c, how to use the plug-in features to find the system’s performance and to alert in case of issues.

Don’t forget to also visit us at our Viscosity booth. I’ll see you there in case you have questions.

Click on the Viscosity logo for all the company events.

Thanks,

Alfredo

Configure Oracle Cloud Infrastructure’s VCN Network

Oracle Cloud Infrastructure requires a VCN (Virtual Cloud Network) to be created in order to connect to DB Systems (Bare Metal and VM). There are basically tow options to configure this VCN.

· Public Subnet with Internet Gateway

· Private Subnet

The Public Subnet with Internet Gateway will expose the network to the Internet. The Internet Gateway is a virtual router that provides a path for traffic between he VCN and the Internet. This option is mainly recommended for non-production workloads.

The Private Subnet cannot be reached from the Internet. You will need to configure a Dynamic routing Gateway (DRG) with either FastConnect or IPSec VPN on your on-premises site.

Once the VCN is created, you can associate the Bare Metal or the VM system to it. The navigation menu to create the VCN is Menu -> Networking -> Virtual Cloud Networks

Then click on Create Virtual Cloud Network and follow the wizard.

Once you create your VCN it will show as a (green) available icon.

More configuration information below:

https://docs.cloud.oracle.com/iaas/Content/Database/Tasks/network.htm

Thanks,

Alfredo

Avoid Transaction Errors Due To Tablespace Shortage

How many times we have had a call from application teams regarding a session error due to a tablespace (either DATA, TEMP or UNDO) getting 100% full?

How many times we had ask for an estimate of space used by a process, session or transaction?

There are several options that can help us overcome this type of issues.

a) If you are using Oracle Enterprise Manager (if you don’t, you should), make use of Corrective Actions to manage your data tablespaces.

http://www.alfredokriegdba.com/2017/01/oem-13c-new-features-corrective-actions.html

b) If your issue is more with tablespace quotas, TEMP or UNDO you may want to enable RESUMABLE session

RESUMABLE is an option which allows the session to go into a suspended state in such a way that it won’t fail/abort. The session cannot wait forever though, this is controlled by RESUMABLE_TIMEOUT initialization parameter.

In order to enable this option at the session level you have to execute below SQL statement:

SQL> ALTER SESSION ENABLE RESUMABLE;

Alertlog file will record if the session entered into suspended mode.

Some examples are:

###########
statement in resumable session ‘User TEST1(32), Session 3, Instance 2’ was
suspended due to
ORA-01536: space quota exceeded for tablespace ‘USERS’

###########
statement in resumable session ‘User TEST1(32), Session 15, Instance 1’ was
suspended due to
ORA-01562: failed to extend rollback segment number 4

###########
statement in resumable session ‘User TEST1(32), Session 23, Instance 1’ was
suspended due to
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP

###########
statement in resumable session ‘User TEST1(54), Session 2, Instance 1’ was
suspended due to
ORA-01653: unable to extend table TEST1.TEST_RESUMABLE by 256 in tablespace USERS

While the session is into this suspended state it will account time to the “statement suspended, wait error to be cleared” wait event.

Once the error that sent the session to the suspended state is fixed, the session will resume its work.

More details on below Oracle’s documentation:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema002.htm#ADMIN11581

Thanks,

Alfredo

Create a Linux VM on Azure

In this post I’ll show you how easy is to create a Linux VM in Microsoft’s Azure cloud.

Once you login to Azure using your account you will be presented with the main dashboard.

Click on Virtual Machines and then click Add.

The first section contains the basic information of your VM.

Select a subscription. In my case is the Free Trial subscription.

For Resource group we are going to click Create new. A resource group is a logical way to organize your resources.

I just created an oracle-vm-resource-group.

Type a VM name and select the desired Region for your VM. The region is where the datacenter is located. You may want to choose the closest one to you. For availability I’m going to choose no infrastructure redundancy required as this is a test VM. There are different availability types for your VM. Below URL links to a document that explains the Azure regions and sets in detail.

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/regions-and-availability

The size of the VM will have a direct impact on your bill. Be careful when you chose the sizing as you don’t want to waste resources and money and on the other side you don’t want to have a system that is underperforming. Click on Change size and below table will show you all your options.

I selected 2vcpus, 8 GB memory VM.

For the ADMINISTRATOR ACCOUNT I’m going to chose SSH public key to connect to my VM.

In the SSH public key, I’m going to provide the public key of my local computer so I can gain access to the VM.

I don’t have a corporate Active Directory in my lab, so Login with Azure Active Directory is turned off.

Public inbound ports will allow SSH to connect.

Nor for Disks. There are 3 options. Premium SSD is the fastest option available and is used for production like workloads. Standard SSD still delivers good performance while the Standard HDD is the cheapest and slowest option.

I’m going to create a 300 GB Standard SSD disk for this VM.

For the NETWORK INTERFACE section my Virtual Network is going to be under my oracle-vm-resource-group. The Subnet and the Public IP will be created (new) and as previously selected the SSH inbound port is going to be open.

For Management and Guest config I’ll leave the defaults and I’m going to jump to the Review + Create section. In this Review + create section you can see an estimate of your VM pricing.

Now, if you need to create multiple VMs like this, you may want to automate the process using the Azure CLI. Click on the Download a template for automation link located in the bottom right part of the page. I’ll cover Azure CLI in a later post.

Now click on Create and wait for your VM to be provisioned. You’ll receive a notification once the VM is created.

Now is time to connect to it. From the VM dashboard find the Public IP address. Or you can click on connect button for more details.

Now connect to it using SSH. As we already shared our public key this should work without using a password.

$ ssh alfredokrieg@23.99.209.18

The authenticity of host ‘23.99.209.18 (23.99.209.18)’ can’t be established.

ECDSA key fingerprint is SHA256:yfdef8ToOBoI5a/FCogDbVMGCsdsZXmSylnH4yZ4sZs.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘23.99.209.18’ (ECDSA) to the list of known hosts.

$ hostname

oraclevm

Thanks,

Alfredo

Oracle Cloud Infrastructure – Database Service – Bare Metal

The Database service part of the Oracle Cloud Infrastructure offers the autonomous and user-managed database options. The user-managed can be customized and you can choose between using Bare Metal, Virtual Machine or Exadata systems. These are called DB Systems.

The autonomous database cannot be customized as they come pre-configured. You can choose between Autonomous Transaction Processing or Autonomous Data Warehouse.

Licensing wise there are two models. License included covers the costs of the license in the cloud service. Bring Your Own License (BYOL) allows to use costumer’s Unlimited License Agreement or Non-Unlimited License Agreement with Oracle Cloud Infrastructure. There are some restrictions that apply to BYOL. More details about pricing can be found in below link:

https://cloud.oracle.com/database/pricing

There are two ways to access Oracle Cloud Infrastructure. You can use the Console (browser) and the REST API. Below is the Oracle Cloud Infrastructure menu.

Let’s start looking at Bare Metal DB Systems. They are available as a single metal server running Oracle Linux 6.8 and NVMe storage locally attached. NVMe provides additional boost in performance for these systems.

When a bare metal DB system is created you select the Oracle Database Edition for the whole system. This means that you can have multiple DB homes with different versions but all with the same edition.

Bare metal comes in two shapes available. BM.DenselO2.52 that provides 1-node with up to 52 CPU cores, 768 GB of memory and eight 6.4 TB NVMe storage drives. BM.DenselO1.36 that is 1-node with up to 36 CPU cores, 512 GB of memory and nine 3.2 TB drives.

Database versions available are 11.2.0.4, 12.1.0.2, 12.2.0.1 and 18.0.0.0.

As you can see from the previous screenshot. You can opt for automatic backups going to an Oracle Cloud Object Storage.

I’ll cover Virtual Machine and Exadata DB systems including backups in my next post. Stay tuned!

Thanks,

Alfredo

Oracle 19c Available for Exadata On-Premises

Oracle just released Oracle 19c for Exadata On-Premises. This is just close to a year when they released the 18c version for Exadata On-Premises on February 16^th 2018.

If they follow the same schedule, we may expect 19c for the Database Cloud Service early next month.

Release dates can be found in the MOS Doc Id 742060.1

I did check the versions available in the Oracle Cloud for VM, Bare Metal and Exadata.

No 19c version as expected.

Now is time to start playing with the new features of 19c. Stay tuned!

Thanks,

Alfredo

OEM 12c/13c Metric Extension – Collection Result Maximum Flood Control

I recently had an issue in OEM 12c while creating a Metric Extension that was returning more than 5000 records. In OEM 12c versions 12.1.0.4 and 12.1.0.5 there a flood control mechanism in order to limit the maximum number of rows for a metric.

You can also verify this in the agent_inst/sysman/log/gcagent.log file:

2018-10-08 13:38:50,330 [66:F119EDFE:GC.Executor.1 (host:hostname:ll_host_config) (host:hostname:ll_host_config:ECM_OS_COMPONENT)] WARN – Result set exceeding min flood control level

The solution is provided in the MOS Doc Id 1499381.1

We didn’t want to proceed to apply the patch but rather we limited the number of rows from the Metric Extension.

This OMS patch is available also for 13.1 version and the bug states that is fixed in 13.2 versions.

Thanks,

Alfredo

Install VirtualBox on MacOS using HomeBrew

I’m fairly new on using MacOS. I’ve been struggling finding shortcuts and keys that I was very comfortable with in Windows. I friend of mine helped me with tips and also mentioned to install HomeBrew in order to install VirtualBox. Here’s a short post on how to do it.

First you need to install Xcode. Xcode is a MacOS development framework application.

$:~ user$ xcode-select –install

xcode-select: note: install requested for command line developer tools

Then, you need to install HomeBrew. This version of HomeBrew doesn’t require to install Cask separately.

$:~ user$ ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

==> This script will install:

/usr/local/bin/brew

/usr/local/share/doc/homebrew

/usr/local/share/man/man1/brew.1

/usr/local/share/zsh/site-functions/_brew

/usr/local/etc/bash_completion.d/brew

/usr/local/Homebrew

…

==> Homebrew is run entirely by unpaid volunteers. Please consider donating:

https://github.com/Homebrew/brew#donations

==> Tapping homebrew/core

Cloning into ‘/usr/local/Homebrew/Library/Taps/homebrew/homebrew-core’…

remote: Enumerating objects: 4894, done.

remote: Counting objects: 100% (4894/4894), done.

remote: Compressing objects: 100% (4697/4697), done.

remote: Total 4894 (delta 52), reused 324 (delta 6), pack-reused 0

Receiving objects: 100% (4894/4894), 4.00 MiB | 372.00 KiB/s, done.

Resolving deltas: 100% (52/52), done.

Tapped 2 commands and 4680 formulae (4,936 files, 12.4MB).

Already up-to-date.

==> Installation successful!

==> Homebrew has enabled anonymous aggregate formulae and cask analytics.

Read the analytics documentation (and how to opt-out) here:

https://docs.brew.sh/Analytics

==> Homebrew is run entirely by unpaid volunteers. Please consider donating:

https://github.com/Homebrew/brew#donations

==> Next steps:

– Run `brew help` to get started

– Further documentation:

https://docs.brew.sh

As I previously mentioned, Cask is already included in the HomeBrew installation.

Now, lest try to use HomeBrew to search for the VirtualBox package.

$:~ user$ brew search virtualbox

==> Casks

homebrew/cask-versions/virtualbox-beta

homebrew/cask-versions/virtualbox-extension-pack-beta

homebrew/cask/virtualbox

homebrew/cask/virtualbox-extension-pack

$:~ user$ brew cask info virtualbox

==> Tapping homebrew/cask

Cloning into ‘/usr/local/Homebrew/Library/Taps/homebrew/homebrew-cask’…

remote: Enumerating objects: 4195, done.

remote: Counting objects: 100% (4195/4195), done.

remote: Compressing objects: 100% (4185/4185), done.

remote: Total 4195 (delta 26), reused 915 (delta 7), pack-reused 0

Receiving objects: 100% (4195/4195), 1.33 MiB | 241.00 KiB/s, done.

Resolving deltas: 100% (26/26), done.

Tapped 1 command and 4096 casks (4,205 files, 4.2MB).

virtualbox: 6.0.0,127566

https://www.virtualbox.org/

Not installed

From: https://github.com/Homebrew/homebrew-cask/blob/master/Casks/virtualbox.rb

==> Name

Oracle VirtualBox

==> Artifacts

VirtualBox.pkg (Pkg)

==> Caveats

To install and/or use virtualbox you may need to enable their kernel extension in

System Preferences → Security & Privacy → General

For more information refer to vendor documentation or the Apple Technical Note:

https://developer.apple.com/library/content/technotes/tn2459/_index.html

Now is time to install VirtualBox.

$:~ user$ brew cask install virtualbox

==> Caveats

To install and/or use virtualbox you may need to enable their kernel extension in

System Preferences → Security & Privacy → General

For more information refer to vendor documentation or the Apple Technical Note:

https://developer.apple.com/library/content/technotes/tn2459/_index.html

==> Satisfying dependencies

==> Downloading https://download.virtualbox.org/virtualbox/6.0.0/VirtualBox-6.0.

######################################################################## 100.0%

==> Verifying SHA-256 checksum for Cask ‘virtualbox’.

==> Installing Cask virtualbox

==> Running installer for virtualbox; your password may be necessary.

==> Package installers may write to any location; options such as –appdir are i

Password:

installer: Package name is Oracle VM VirtualBox

installer: Installing at base path /

installer: The install failed (The Installer encountered an error that caused the installation to fail. Contact the software manufacturer for assistance.)

==> Purging files for version 6.0.0,127566 of Cask virtualbox

Error: Failure while executing; `/usr/bin/sudo -E — env LOGNAME=user USER=user USERNAME=user /usr/sbin/installer -pkg /usr/local/Caskroom/virtualbox/6.0.0,127566/VirtualBox.pkg -target /` exited with 1. Here’s the output:

installer: Package name is Oracle VM VirtualBox

installer: Installing at base path /

installer: The install failed (The Installer encountered an error that caused the installation to fail. Contact the software manufacturer for assistance.)

Follow the instructions here:

https://github.com/Homebrew/homebrew-cask#reporting-bugs

/usr/local/Homebrew/Library/Homebrew/system_command.rb:107:in `assert_success’

/usr/local/Homebrew/Library/Homebrew/system_command.rb:50:in `run!’

/usr/local/Homebrew/Library/Homebrew/system_command.rb:27:in `run’

/usr/local/Homebrew/Library/Homebrew/system_command.rb:31:in `run!’

/usr/local/Homebrew/Library/Homebrew/cask/artifact/pkg.rb:59:in `block in run_installer’

/usr/local/Homebrew/Library/Homebrew/cask/artifact/pkg.rb:65:in `with_choices_file’

/usr/local/Homebrew/Library/Homebrew/cask/artifact/pkg.rb:52:in `run_installer’

/usr/local/Homebrew/Library/Homebrew/cask/artifact/pkg.rb:32:in `install_phase’

/usr/local/Homebrew/Library/Homebrew/cask/installer.rb:209:in `block in install_artifacts’

/System/Library/Frameworks/Ruby.framework/Versions/2.3/usr/lib/ruby/2.3.0/set.rb:674:in `each’

/usr/local/Homebrew/Library/Homebrew/cask/installer.rb:200:in `install_artifacts’

/usr/local/Homebrew/Library/Homebrew/cask/installer.rb:95:in `install’

/usr/local/Homebrew/Library/Homebrew/cask/cmd/install.rb:21:in `block in run’

/usr/local/Homebrew/Library/Homebrew/cask/cmd/install.rb:14:in `each’

/usr/local/Homebrew/Library/Homebrew/cask/cmd/install.rb:14:in `run’

/usr/local/Homebrew/Library/Homebrew/cask/cmd/abstract_command.rb:34:in `run’

/usr/local/Homebrew/Library/Homebrew/cask/cmd.rb:89:in `run_command’

/usr/local/Homebrew/Library/Homebrew/cask/cmd.rb:155:in `run’

/usr/local/Homebrew/Library/Homebrew/cask/cmd.rb:120:in `run’

/usr/local/Homebrew/Library/Homebrew/cmd/cask.rb:7:in `cask’

/usr/local/Homebrew/Library/Homebrew/brew.rb:88:in `’

$:~ user$

As you can see, my installation failed. This is due to a new MacOS Sierra feature that prevents to load external libraries. I had to follow the instructions in below note:

https://developer.apple.com/library/archive/technotes/tn2459/_index.html

Now that I have approved the extension let’s execute the install again.

$:~ user$ brew cask install –force virtualbox

==> Caveats

To install and/or use virtualbox you may need to enable their kernel extension in

System Preferences → Security & Privacy → General

For more information refer to vendor documentation or the Apple Technical Note:

https://developer.apple.com/library/content/technotes/tn2459/_index.html

==> Satisfying dependencies

==> Downloading https://download.virtualbox.org/virtualbox/6.0.0/VirtualBox-6.0.

Already downloaded: /Users/user/Library/Caches/Homebrew/downloads/c68cfddc9133da5c221557cd77b23f34e0b513dc380e6b010dc36b3d0eb2df45–VirtualBox-6.0.0-127566-OSX.dmg

==> Verifying SHA-256 checksum for Cask ‘virtualbox’.

==> Installing Cask virtualbox

==> Running installer for virtualbox; your password may be necessary.

==> Package installers may write to any location; options such as –appdir are i

installer: Package name is Oracle VM VirtualBox

installer: Installing at base path /

installer: The install was successful.

virtualbox was successfully installed!

Now, just use spotlight search to find the VirtualBox application.

Hope this helps.

Alfredo

OEM 13c – AMP EBS Discovery Error Due To Lack Of Privileges

We recently had to enable monitoring for an EBS 11i system using the Application Management Pack. Even though the EM_MONITOR user was already provisioned by the EBS patch, the discovery was failing due to lack of privileges on several FND tables.

My Oracle Support shows several bugs related to missing privileges like the one below, but I wasn’t able to find one for 11i.

Patch 21951154: 1OFF:12.2.0: READ ACCESS NOT PRESENT FROM EM_MONITOR USER FOR FOLLOWING TABLES

I decided to manually track the missing privileges on these tables and here’s the list I found in order to make the discovery work.

GRANT SELECT ON “APPLSYS”.”AD_APPL_TOPS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”AD_FIXED_ISSUES” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”AD_PATCH_DRIVER_LANGS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”AD_PATCH_RUNS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”AD_PATCH_RUN_BUGS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”FND_LOG_EXCEPTIONS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”FND_LOG_UNIQUE_EXCEPTIONS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”FND_NODES” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”FND_PROFILE_OPTIONS_TL” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPLSYS”.”FND_USER” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “APPS”.”HR_OPERATING_UNITS” TO “EM_OAM_MONITOR_ROLE”;

GRANT EXECUTE ON “APPS”.”JTF_DIAG_DEPENDENCIES” TO “EM_OAM_MONITOR_ROLE”;

GRANT EXECUTE ON “APPS”.”JTF_DIAG_INPUTS” TO “EM_OAM_MONITOR_ROLE”;

GRANT EXECUTE ON “APPS”.”JTF_DIAG_INPUTTBL” TO “EM_OAM_MONITOR_ROLE”;

GRANT EXECUTE ON “APPS”.”JTF_DIAG_VERSION” TO “EM_OAM_MONITOR_ROLE”;

GRANT EXECUTE ON “APPS”.”JTF_DIAG_VERSION_NT” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”ALL_IND_COLUMNS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”ALL_OBJECTS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”ALL_QUEUES” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”ALL_TRIGGERS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_DATA_FILES” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_OBJECTS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_PROCEDURES” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_SOURCE” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_TAB_PRIVS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_TEMP_FILES” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_USERS” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”DBA_USERS_WITH_DEFPWD” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”GV_$INSTANCE” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”GV_$LOGFILE” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”GV_$SESSION” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”V_$INSTANCE” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”V_$THREAD” TO “EM_OAM_MONITOR_ROLE”;

GRANT SELECT ON “SYS”.”V_$VERSION” TO “EM_OAM_MONITOR_ROLE”;

Hope this helps to your discovery.

Thanks,

Alfredo

Using SQL Patch To Inject Hints

In my previous post (Execution Plans, Hints and Outlines in Oracle 18c) we saw how to use a SQL Hint to modify the order in which the CBO joins the tables. Today I want to show you a cool feature to inject this change without re-writing your SQL statement.

Let’s use the same SQL statement:

explain plan SET statement_id = ‘ex_plan’ for SELECT

COUNT(*)

FROM

hr.employees a,

hr.departments b,

hr.locations c

WHERE

a.department_id = b.department_id

AND b.location_id = c.location_id

AND a.salary > 3000

AND c.postal_code IS NOT NULL;

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ‘ex_plan’,’ADVANCED’));

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

Plan hash value: 3098668569

—————————————————————————————————

—————————————————————————————————

| 0 | SELECT STATEMENT | | 1 | 24 | 8 (13)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 24 | | |

|* 2 | HASH JOIN | | 80 | 1920 | 8 (13)| 00:00:01 |

| 3 | MERGE JOIN | | 27 | 459 | 5 (20)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 22 | 220 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | LOC_ID_PK | 23 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 27 | 189 | 3 (34)| 00:00:01 |

| 7 | VIEW | index$_join$_002 | 27 | 189 | 2 (0)| 00:00:01 |

|* 8 | HASH JOIN | | | | | |

| 9 | INDEX FAST FULL SCAN | DEPT_ID_PK | 27 | 189 | 1 (0)| 00:00:01 |

| 10 | INDEX FAST FULL SCAN | DEPT_LOCATION_IX | 27 | 189 | 1 (0)| 00:00:01 |

|* 11 | TABLE ACCESS FULL | EMPLOYEES | 81 | 567 | 3 (0)| 00:00:01 |

—————————————————————————————————

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

————————————————————-

1 – SEL$1

4 – SEL$1 / C@SEL$1

5 – SEL$1 / C@SEL$1

7 – SEL$FD64DD72 / B@SEL$1

8 – SEL$FD64DD72

9 – SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72

10 – SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72

11 – SEL$1 / A@SEL$1

Outline Data

————-

/*+

BEGIN_OUTLINE_DATA

USE_HASH(@”SEL$1″ “A”@”SEL$1”)

USE_MERGE(@”SEL$1″ “B”@”SEL$1”)

LEADING(@”SEL$1″ “C”@”SEL$1” “B”@”SEL$1” “A”@”SEL$1”)

FULL(@”SEL$1″ “A”@”SEL$1”)

INDEX_JOIN(@”SEL$1″ “B”@”SEL$1” (“DEPARTMENTS”.”DEPARTMENT_ID”)

(“DEPARTMENTS”.”LOCATION_ID”))

INDEX(@”SEL$1″ “C”@”SEL$1” (“LOCATIONS”.”LOCATION_ID”))

OUTLINE_LEAF(@”SEL$1″)

OUTLINE_LEAF(@”SEL$FD64DD72″)

ALL_ROWS

DB_VERSION(‘18.1.0’)

OPTIMIZER_FEATURES_ENABLE(‘18.1.0’)

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

The idea is to use the Hint /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”) */ without the need to actually write it into the SQL text.

In order to do this, Oracle provides a procedure called DBMS_SQLDIAG.CREATE_SQL_PATCH.

We can invoke it using the SQL text or the SQL_ID of our statement.

In my example, I’m going to use the SQL_ID 8b2gpq57hxdws.

Here will be the syntax to create the SQL patch:

DECLARE

patch_name VARCHAR2(32767);

BEGIN

patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(

sql_id => ‘8b2gpq57hxdws’,

hint_text => ‘LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)’,

name => ‘TEST_PATCH’);

END;

PL/SQL procedure successfully completed.

Now, let’s get the execution plan again:

explain plan SET statement_id = ‘ex_plan’ for SELECT

COUNT(*)

FROM

hr.employees a,

hr.departments b,

hr.locations c

WHERE

a.department_id = b.department_id

AND b.location_id = c.location_id

AND a.salary > 3000

AND c.postal_code IS NOT NULL;

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ‘ex_plan’,’ADVANCED’));

PLAN_TABLE_OUTPUT

Plan hash value: 2122977163

———————————————————————————————-

———————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | 24 | 8 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 24 | | |

|* 2 | HASH JOIN | | 80 | 1920 | 8 (0)| 00:00:01 |

|* 3 | HASH JOIN | | 80 | 1120 | 5 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | EMPLOYEES | 81 | 567 | 3 (0)| 00:00:01 |

| 5 | VIEW | index$_join$_002 | 27 | 189 | 2 (0)| 00:00:01 |

|* 6 | HASH JOIN | | | | | |

| 7 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 189 | 1 (0)| 00:00:01 |

| 8 | INDEX FAST FULL SCAN| DEPT_LOCATION_IX | 27 | 189 | 1 (0)| 00:00:01 |

|* 9 | TABLE ACCESS FULL | LOCATIONS | 22 | 220 | 3 (0)| 00:00:01 |

———————————————————————————————-

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

————————————————————-

1 – SEL$1

4 – SEL$1 / A@SEL$1

5 – SEL$FD64DD72 / B@SEL$1

6 – SEL$FD64DD72

7 – SEL$FD64DD72 / indexjoin$_alias$_001@SEL$FD64DD72

8 – SEL$FD64DD72 / indexjoin$_alias$_002@SEL$FD64DD72

9 – SEL$1 / C@SEL$1

Outline Data

————-

/*+

BEGIN_OUTLINE_DATA

USE_HASH(@”SEL$1″ “C”@”SEL$1”)

USE_HASH(@”SEL$1″ “B”@”SEL$1”)

LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

FULL(@”SEL$1″ “C”@”SEL$1”)

INDEX_JOIN(@”SEL$1″ “B”@”SEL$1” (“DEPARTMENTS”.”DEPARTMENT_ID”)

(“DEPARTMENTS”.”LOCATION_ID”))

FULL(@”SEL$1″ “A”@”SEL$1”)

OUTLINE_LEAF(@”SEL$1″)

OUTLINE_LEAF(@”SEL$FD64DD72″)

ALL_ROWS

DB_VERSION(‘18.1.0’)

OPTIMIZER_FEATURES_ENABLE(‘18.1.0’)

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

Pretty cool, right?

As always, this is not the ultimate solution but it may buys us time to have the vendor or developers fix the code (hahaha, is not a joke!!!).

Anyway, another tool to keep in our radar after migrations, upgrades and major patches.

Thanks,

Alfredo