OBIEE 12.2.1.3 - BI administration tool performance problem -- on 12CR1 database

 Here is a quick tip for a quick win. Especially for OBIEE users!

You may encounter performance problems while using BI administration tool , especially while importing metadata, on that wizard while selecting Metadata Types and all that..

We have seen that problem on an OBIEE 12.2.1.3 environment. BI admin tool version was also 12.2.1.3 and the database version was 12CR1.  This tool-specific performance problem started to be seen after the database upgrade. (in our case, after the DWH upgrade - 11gR2 to12CR1)

Client side was analyzed and there wasn't any problems there.

Traced the db session and saw that, it was active all the time, different queries one after another. Those queries were reading data from dictionary views like all_tab_columns and all_nested_tables..

Considered collecting fixed object stats and dictionary stats, but didn't do any of those, as the system was a very mission critical one, and performing that type of a statistics collection was not allowed. (especially at that point where we cannot predict whether collecting those stats will be our solution or will bring some new problems to the environment) ..

It was obvious that, import medata wizard wasn't producing very optimized SQL, or let's say the wizard wasn't producing the SQLs by considering the optimizer fixes and features in newer Oracle releases. Actually, this may also be a database related problem because we have the following note already in place in Oracle Support;

Query to Dictionary ALL_CONSTRAINTS Slow after Upgrade to 12.1.0.2 (Doc ID 2266016.1)

Using the wizard for this task is actually an optional way.. I mean, we can always do that metadata import manually, but in this case it was hard to do it in manual way, because there were several tables to be processed..

We didn't have the motivation to open a support ticket for this. That wizard was already optional and the problem was something in the middle between the tool and the database.. Besides, we were after a quick win..

Recently we dealt with a similar problem in a Oracle Discoverer environment.. There, the database was upgraded to 19C and the customer was facing dramatic performance problems almost in all discoverer reports. 

If you want to read that story, here is the link - > 

https://ermanarslan.blogspot.com/2020/12/ebs-122-rdbms-optimizerfeaturesenable.html 

In this case, too we did something similar.. We created an after logon trigger and by the help of that trigger, we made some optimizer related parameters automatically set for the BI admin tool sessions during the database login.  (note that, there wasn't any performance problems in ETL or OBIEE reports.)

This fixed the issue!

Here is the setting we've done inside our custom after logon trigger -> 

IF LOWER (v_program) LIKE ('%admintool.exe%')
THEN
EXECUTE IMMEDIATE 'alter session set optimizer_features_enable="11.2.0.4"';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_squ_bottomup" = FALSE';
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_cost_based_transformation" = OFF';
END IF;

Ofcourse, this is not a supported solution, but currently (according to my research), it is the best thing we have :)

That is it. I hope you find this useful.

OVM -- license / CPU-cores alignment , CPU Pinning , CPU Affinity , OVM Manager + ovm_vmcontrol

As mentioned in my earlier posts (years ago), you need to have your computational resources aligned with your Oracle Database and Application licenses.. 

In virtualized Oracle environments (OVM and KVM), you have the ability to dedicate your cpu cores to your virtual machines and keep them aligned with the licenses you have for your applications and databases.. This can also be thought as a capacity on demand solution.. That is, you grow as you pay and you license only the cpu cores you use.. 

Doing such a configuration starts with analyzing the licenses you have. If you have CPU-core license, you take your license counts and divide it with the core factor (for intel it is 0.5) to get the maximum count of cpus that you can have with  those license you have.. For user named plus licenses, you also need to be sure that your user count is aligned with the user count defined in your named plus license.. But! again, you need to be aligned with the cpu count.. In other words, you can't have a 24 core machine to host a database or application which is licensed with 25 named users. So, there is a user count & cpu count alignment as well..

Okay enough with the intro : ) Check the articles below for more on this topic.

https://ermanarslan.blogspot.com/2017/03/rdbms-licensing-cpu-limits-for-named.html

https://ermanarslan.blogspot.com/2017/01/oda-quick-tip-for-cpu-vcpu-counts-named.html

In this blog post, I will give you the technical side for implementing license & cpu alignment in OVM environments..

So, as you can guess we configure Guest VM cpu resources according to the licenses we have.. But! not only that, we also neet do implement cpu pinning for the Guest VM to dedicate them to the specific CPU cores according to our license count.. Without this pinning action, it is not accepted to license only the cores that we use.. In other words, if you don't do this pinning action, you will need license all the cores on your Oracle VM server. (it is the same in KVM environments as well)

In order to configure the cpu cores of a guest machine and set CPU pinning, we use OVM Manager and Oracle VM Virtual Machine Control (ovm_vmcontrol utility.) -- supposing we are dealing with Oracle VM 3.4 and on-wards.. Note that, it is better to be on 3.4.3 and on-wards, because; on 3.4.1 and 3.4.2, CPU pinning with ovm_vmcontrol utility on running guest does not work.

We first start by setting the maximum number of virtual CPU cores (maxvcpu) and actual number of virtual cpu cores (vcpu) for our Guest VM.. We use OVM Manager for this task.

Note that, these numbers(maxvcpu and vcpu) are actually based on threads, not based actual physical cores.

In this context, if we have a 4 core intel - Linux machine and if we have hyper threading enabled, our host sees those cores as 8 threads. So, there in OVM Manager, we set maxvcpu and vcpu counts for a guest machine based on the thread count.

Note that, if we change maximum number of cpus for a Guest VM we need to reboot that vm after the value is changed.

Well.. Suppose we set the maxcpu to 4 and vcpu to 4.. With this config, we actually make or Guest VM  use 4 threads. However; in order to get benefit from capacity on demand (aligned Cpu cores and licenses), we need to set CPU pinning for those 4 threads as well and we do that by using ovm_vmcontrol.

A quick command toolbox for getting cpu related info from our OVM host -> 

xm info
xenpm get-cpu-topology
xm vcpu-list

In order to use the vm_vmcontrol, we need to download and install it first..

Download url : http://www.oracle.com/technetwork/server-storage/vm/downloads/index.html

ovm_vmcontrol is delivered by -> Patch 13602094: ORACLE VM 3.0 UTILS RELEASES: 1.0.2, 2.0.1, 2.1.0

We install the tool on the OVM Manager host. (in my opinion, this is easiest installation method)

We unzip it in /u01/app/oracle/ovm-manager-3 directory and if the OS user of the OVM Manager has java installed, no further actions required.

Before using the tool, we check our cpu topology and get the info about our threads and physical cores.

Below, we see a a single socket server with 4 cores and 2 threads per core.

# xenpm get-cpu-topology

CPU core socket node

CPU0 0 0 0

CPU1 0 0 0

CPU2 1 0 0

CPU3 1 0 0

CPU4 2 0 0

CPU5 2 0 0

CPU6 3 0 0

CPU7 3 0 0

In the above outputs, the CPU lines represents the threads.. So CPU0 is thread 0 of core 0. CPU1 is thread 1 of core 0.. CPU 2 is the thread 0 of core 1 etc.. 

So we have 2 threads in each core.. So if we have 2 core license for this Guest VM, we need to pin those 4 vcpus (threads) to 2 physical cores..  for instance, to -> Core 0 and Core 1

In the above output, cpu 0,1,2,3 are the threads that correspond to core 0 and core 1 and that's why our ovm_vmcontrol command in this case will be similar to following;

 ./ovm_vmcontrol -u admin -p <admins_password> -h <ovm_managers_hostname> -v <guest_machine_name> -c setvcpu -s 0-3

Got the point right?

You can also get the cpu pinning related info for a vm using a ovm_vmcontrol command similar to following;

 ./ovm_vmcontrol -u admin -p <admins_password> -h <ovm_managers_hostname> -v <guest_machine_name> -c getvcpu

The command above lets you check the current pinning configuration of a vm..

" xm vcpu-list" also gives you that info in its output -- exactly the values under the column named Cpu affitinity.

# xm vcpu-list

Name ID VCPU CPU State Time(s) CPU Affinity

0003fb00000600007c351fa24276c63f 1 0 5 -b- 4676.8 0-3

Domain-0 0 0 0 -b- 932.1 any cpu

Domain-0 0 1 6 -b- 1168.0 any cpu

Some references in this context:

https://www.oracle.com/technetwork/server-storage/vm/ovm-hardpart-168217.pdf

https://docs.oracle.com/cd/E50245_01/E50251/html/vmadm-utils-vmcontrol.html

Set CPU Pinning for VMs on Oracle VM 3.4.1 and 3.4.2 (Doc ID 2213691.1)

https://docs.oracle.com/cd/E50245_01/E50251/html/vmadm-utils-vmcontrol.html

We (Blog and Forum) ranked high in the lists of the most known Oracle and Database (including all databases) blogs, websites & Influencers 2021

Continuing to produce content from two different lines and support oracle users all around the world. My blog was already on the top 100 list, "this week my forum as well entered that list at the top".  In addition to that, both my forum and blog entered Top 80 Database Blogs, websites and Influencers list..

Thanks to Feedspot for Top 100 Oracle Blogs, Websites & Influencers in 2021 & Top 80 Database Blogs, Websites & Influencers in 2021.

Check the cool content ->

Top 100 Oracle Blogs : https://blog.feedspot.com/database_blogs/

Top 80 Database Blogs : https://blog.feedspot.com/oracle_blogs/

2 records ( 1 for blog and 1 for forum) in both list. Not bad isn't it:)

Sincere thanks to my readers, supporters and forum subscribers.

Erman Arslan's Oracle Forum -- Feburary 2021 - "Questions and Answers Series"

Question: How much time do you spend/lose?

Answer: Well, how much time I gain? :) 

Remember, you can ask questions and get remote support using my forum.
Just click on the link named "Erman Arslan's Oracle Forum is available now.
Click here to ask a question", which is available on the main page of Erman Arslan's Oracle Blog -- or just use the direct link:

https://ermanarslan.blogspot.com/p/forum.html

 Come on, let's see what we've been up to in Feburary. (Do not forget to read the blog posts too :)

Question on Enabling CDC/Log Miner on Oracle Database

Can we run autoconfig Without APPS Password in 12.2 EBS?

Admin ativities which can take place when patching cycle is open

adapcctl.sh: exiting with status 204 after running adconfigclone appstier

adop prepare phase stuck after applying 12.2.10 RUP

Questions on exp/imp - best practices and all that

Post PDB creation task failing with ERRORMSG: Failed to stop CDB listenerg

Question on EBS 12.2.X and 19.3 DB

ORDS on weblogic - 404 Not Found in ords url

Dblink from oracle to MS Sql server

Internet Developer Suite issues

Tech/CSI issues on Oracle support

Question on Dovecot - mbox for IMAP WF Mailer

BYE Lost mailbox message in Workflow mailer FNDCPGSCnnnnnn.txt file

cannot send test mail : Invalid role, or role does not have notification preference set

Editionable Views in Custom Tables

Opening A Cloned EBS Instance In Enterprise Manager Hangs Before Log In

Issues in connection after configuring tls in 12..1.3 ebs

Instance "mydb", status UNKNOWN

questions regarding encryption and decryption in JAVA - decrypt function

Public synonyms in Upgrade to 12.2.10

OEM 12 can not discover EBS

Oracle Linux - KVM -- VM network on Broadcom bond devices fail -- actually OS fails adding a Broadcom bnxt_en bond to a bridge

Recently dealed with a problem on an Oracle Linux KVM.. Customer was trying to implement Oracle Linux KVM using Oracle Linux Virtualization Manager (OLVM) , but failing in network configuration. OS was Oracle Linux 7.9 64 bit...

The issue was about virtual machine network .. That network could not be assigned to the relevant bond device using OLVM.. Bond device was configured with 2 slaves, and the configuration was correct, the bonding mode was appropriate and the slaved and the master (bond) were active in the OS layer.. But! somehow OLVM could not assign the vm network ( created by the customer using OLVM) to the relevant bond device. 

No errors were seen on OLVM, no errors in the OLVM logs (for instance, in engine.log), but I saw the following log messages on Oracle Linux syslod.. (/var/log/messages) ;

server01 kernel: VLAN2: port 1(bond1.10) entered blocking state

server01 kernel: VLAN2: port 1(bond1.10) entered disabled state

That VLAN2 shown in the logs above was actually a bridge.. As you may already know, when we have the vm network in the picture, we rely on the bridges on Linux layer..  So, it was clear that we had a bridge problem.. kernel was disabling the relevant path..

So this was the cause that prevents OLVM assigning vm network to the bond device.

When we tried to add that bond to that bridge, the following error was shown in the log;

server01 network: Bringing up interface bond1.10: can't add bond1.10 to bridge VLAN2: No data available

After doing some more analysis, I concluded that the problem wasn't on Oracle Linux KVM.. The problem should have been on Oracle Linux kernel or the device driver associated with the ethernet devices.. (in this case Broadcom bnxt_en)

With this in mind, I made more specific research and found similar bugs on Redhat.. 

In the redhat support,  I could see a bug, which had the exact similar symptoms ->  Bug 1860479 - Unable to attach VLAN-based logical networks to a bond..

The bug was recorded for Redhat 8 , but it seemed we had the same bug in Oracle Linux 7.9.. Actually, rather than the OS version, the kernel version was the key..

The fix was upgrading the kernel, but the workaround was downgrading it.. (according to Redhat). 

I was trying to get a quick win in this case, so I had to use a lower version kernel, than I decided to use Redhat compatible kernel instead of using the UEK kernel.. As you can imagine, the server was rebooted with the redhat compatible kernel (installed as an alternative kernel in Oracle Linux)  and problem solved! After booting with that kernel (a lower version kernel when compared to the UEK kernel), customer could assign the vm network to the relevant bond device using OLVM.

Note that, this bug appears when we configure the bond-slaves on 2 network ports belonging to 2 different Broadcom networks cards .. Bug doesn't appear when we configure the bond-slaves on the same network cards ..

That 's it .. I hope, you find this article useful.