Blog

EBS 11i forms LOV experiencing inconsistent performance

Symptoms:

Customer complains that a particular module form LOV is inconsistently slow

Solution:

Enable tracing using below steps and generate tkprof file

a) Create the following procedure as APPS:

CREATE OR REPLACE PROCEDURE set_trace_param AS
BEGIN
FND_CTL.FND_SESS_CTL(”, ”, ”, ‘TRUE’, ”, ‘ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED’);
FND_CTL.FND_SESS_CTL(”, ”, ”, ‘TRUE’, ”, ‘ALTER SESSION SET TIMED_STATISTICS=TRUE’);
FND_CTL.FND_SESS_CTL(”, ”, ”, ‘TRUE’, ”, ‘ALTER SESSION SET STATISTICS_LEVEL=ALL’);
END;
/

–Copy the above, including the “/” in the last line

b) Set the following profile option at USER level, only for the USER who will reproduce the issue :

Profile option: Initialization SQL Statement – Custom
Value: begin set_trace_param; end;

–Copy the above, including the “;” after the “end”

IMPORTANT ==> If the user was logged to the application, please make sure he/she LOGOUT and relogin after the profile option is set.

c) Navigate to the screen to reproduce the issue. Do NOT reproduce the issue yet.

d) Just before reproducing the issue (i.e.: before clicking on FIND button) go to Help->Diagnostics->Trace->Trace With Waits and Binds
———————————————-

e) Use a stopwatch/timer while reproducing the issue. The time that you get should match or at least be similar to the time reflected in the trace that you will provide.
Reproduce the issue (i.e.: Click on FIND button)

f) Turn off trace and your stopwatch/timer immediately after you get the response from the screen, and EXIT the application completely (You must logout from the Personal Home Page too)
(To turn off trace: Help->Diagnostics->Trace->No Trace)g6) Run tkprof as follows for the trace file generated:

tkprof <trace file> <output file> sort=exeela,prsela,fchela explain=apps/<password>

IMPORTANT: Please make sure you are using tkprof from DB Oracle Home, and that you logged out from the application. If you are still in the same session in the application, the rows column in tkprof might be all zeros.

g) IMPORTANT: Do not forget to set the profile option “Initialization SQL Statement – Custom” back to BLANK afterwardsh

h) Once you have the raw trace and sorted tkprof trace files, review them for most expensive sql (sql with most no of of fetches). Extract the sql id and plash has value for the sql with most number of fetches (most expensive SQL)

i) If you have just the sql_id from an AWR report, for example, you can use the statement below to find the plan_hash_value select sql_id from v$sql where hash_value = ‘hash value from the trace’;

j) Having the sql_id, we can obtain the “plan_hash_value” running the statement below. Note that if there is more than one plan for the same SQL, it will show multiples per the example below.

WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM(‘&&sql_id.’)
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = TRIM(‘&&sql_id.’)
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
 WHERE sql_id = TRIM(‘&&sql_id.’)
   AND executions > 0
 GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM(‘&&sql_id.’)
   AND executions_total > 0
 GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
 WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
 ORDER BY
       avg_et_secs NULLS LAST;

k) Download sqltxplain utility from

SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly Document 215187.1

The script named coe_xfr_sql_profile.sql is provided under the /utl directory.

l) Having the sql_id and the “desired” plan_hash_id, run the following:

sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

This will generate a script named like:
coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

For example:
coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql

This file contains the stored profile from the database that you obtained it from.  You can now use this script to apply the same plan to another database:

m) Just execute this script in the DB where you want to create this profilesqlplus / as sysdba
SQL> START coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sqn

Metalink Reference: Using Sqltxplain To Create A ‘SQL Profile’ To Consistently Reproduce a Good SQL Plan (Doc ID 1487302.1)

ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run 

Symptoms

Non-cdb to pdb conversion on VMDB failing with ASM errors

Inside setASMEnvironment()…
=============================
Environment set
ORACLE_HOME : /u01/app/19.0.0.0/grid
ORACLE_SID : +ASM1
Executing ASM command: sh -c “/u01/app/19.0.0.0/grid/bin/asmcmd –privilege sysdba ls +DATA/DBATKI_TRGT”

Connected to an idle instance.
ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

=============================
Inside unsetASMEnvironment()…
=============================
Environment set
ORACLE_HOME : /u01/app/oracle/product/12.1.0.2/dbhome_1
ORACLE_SID :
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/12.1.0.2/dbhome_1/appsutil/bin/txkCreatePDB.pl)
TIME : Mon Oct 25 09:57:46 2021
FUNCTION: main::validateASMDataDir [ Level 1 ]
ERRORMSG: Data top directory +DATA/DBATKI_TRGT does not exists on ASM.
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/12.1.0.2/dbhome_1/appsutil/bin/txkCreatePDB.pl)
TIME : Mon Oct 25 09:57:46 2021
FUNCTION: main::validateASMDataDir [ Level 1 ]
ERRORMSG: Data top directory +DATA/DBATKI_TRGT does not exists on ASM.

The EBS script “txkCreatePDB.pl” is failing with below errors:

========
Executing ASM command: sh -c “/u01/app/19.0.0.0/grid/bin/asmcmd –privilege sysdba ls +DATA/DBATKI_TRGT”

Connected to an idle instance.
ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run <<<<<<<


FUNCTION: main::validateASMDataDir [ Level 1 ]
ERRORMSG: Data top directory +DATA/DBATKI_TRGT does not exists on ASM.============================================This shows that this DISk doesn’t exist so first check the ASM diskgroups.

We are able to connected to the asm instance and able to see the disk groups, but from the script it is not able to connect

ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 8388608 5536604 0 5536604 0 Y DATA/
MOUNTED EXTERN N 512 512 4096 4194304 1679360 1669196 0 1669196 0 N RECO/
ASMCMD> exit
[grid@db-test ~]$ sqlplus ‘/as sysasm’

SQL*Plus: Release 19.0.0.0.0 – Production on Mon Oct 25 12:34:50 2021
Version 19.12.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.12.0.0.0

SQL> select name, state from v$asm_diskgroup;

NAME STATE
—————————— ———–
DATA MOUNTED
RECO MOUNTED

SQL>

Solution:

oracle user was missing asmadmin group

so we added asmadmin group to oracle user

[grid@db-test ~]$ id
uid=102(grid) gid=1001(oinstall) groups=1001(oinstall),1002(dbaoper),1004(asmadmin),1005(asmoper),1006(asmdba)
[grid@db-test ~]$

[oracle@db-test bin]$ id
uid=101(oracle) gid=1001(oinstall) groups=1001(oinstall),1002(dbaoper),1003(dba),1006(asmdba)
[oracle@db-test bin]$
usermod -a -G asmadmin oracle

Chown results input/output error on s3fs-fuse mount point

Symptoms:

Change the ownership several backup pieces under s3fs-fuse mount point worked

However now after few attempts, it is giving “Input/output error

[root@db-test bucket]# chown -R oracle:oinstall /mnt/bucket/backup_db_TEMTKI_S_159_P_1_T_1084020846

Solution:

Chown won’t work here, but other users can access the bucket contents.

To allow others users to see the bucket: –
1) /usr/bin/s3fs -o allow_other mybucket /mnt
2) or from /etc/fstab: s3fs#mybucket /mnt fuse _netdev,allow_other 0 0

ADOP fs_clone is failing after R12.2.10 upgrade driver on OCI Compute

Symptoms

  1. adop phase=fs_clone is failing
    oracle.as.clone.process.CloningExecutionProcess.execute(CloningExecutionProcess.java:131)
    at oracle.as.clone.process.CloningExecutionProcess.execute(CloningExecutionProcess.java:114)
    at oracle.as.clone.client.CloningClient.executeT2PCommand(CloningClient.java:236)
    at oracle.as.clone.client.CloningClient.main(CloningClient.java:124)
    Caused by: java.lang.Exception: Unable to start opmnserver of the instance /u01/install/APPS/fs2/FMW_Home/webtier/instances/EBS_web_OHS1 . Check opmn log.
    at oracle.as.clone.provisioning.implementations.ASInstanceProvImpl.startInstance(ASInstanceProvImpl.java:380)
    at oracle.as.clone.provisioning.implementations.ASInstanceProvImpl.createInstanceAndStart(ASInstanceProvImpl.java:160)
    at oracle.as.clone.util.ASInstanceUtil.createStartAndregisterASInstance(ASInstanceUtil.java:318)
    … 10 more
    Caused by: oracle.as.management.opmn.optic.OpticException: Error in starti
  2. opmnctl services coming up fine in run filesystem but failing in patch filesystem.

Cause:

ETCC MT report shows to apply the missing bug fix

++++++++
===============================================================================
Oracle Fusion Middleware (FMW) – Web Tier
===============================================================================
Now examining product Oracle Fusion Middleware (FMW) – Web Tier.

Oracle Home = /u01/install/APPS/fs1/FMW_Home/webtier.
Product Version = 11.1.1.9.0
Checking required bugfixes for FMW – Web Tier 11.1.1.9.0.
Missing Bugfix: 23716938 -> Patch 23716938
Missing Bugfix: 23716938 -> Patch 23716938
The above list shows missing bugfixes for FMW – Web Tier.
These results have been stored in the database.

DST ts home /u01/install/APPS/fs1/FMW_Home/webtier.

Solution:

Applied the missing bug fix 23716938 CHANGE THE CERTIFICATE IN THE DEFAULT WALLET OF OPMN TO USE SHA-2 (Patch) and reran the fs_clone which execute smoothly

Use Burstable VMs for low-CPU workloads

I just read about the burstable VMs and felt motivated to write about it in my personal blog.

As the name speaks, burstable VMs provides an opportunity to use a fraction of CPU with an ability to occasionally burst up to 100% of CPU but the highlight is that it costs you less than regular VMs. Hence you can run low-CPU workloads cost effectively on Oracle Cloud Infrastrusture(OCI) using burstable VMs.

While with flexible VM, we choose the no of CPU cores and memory to best suit the business workload. Here, we can use 100% of workload all the time thus ideal for workloads that consistently needs 100% of CPU core all the time. On the other side, when workloads needs small amount of CPU most of the time but occasionally need a higher amount of CPU, burstable VMs sounds more cost effective with an additional flexibility of choosing the fraction of CPU needed.

We can select OCPU between 1-64 cores, the memory between 1-64GB per core(up to a maximum of 768 GB), and the baseline OCPU utilisation as 12.5% or 50% of the total OCPUs. The baseline determines the minimum fraction of the CPU resources always available to the VM. A burstable VM with one OCPU and a 12.5% baseline can run at a sustained CPU performance of 12.5% of one core, or it can run lower than 12.5% and occasionally burst up to 100% of the core for short periods of time.

The beauty of burstable VMs is that they are charged at Oracle’s standard OCPU per hour price but only for the baseline OCPU chosen. This ensures that burstable VMs are much cheaper than non-burstable VMs. For a one-core, 12.5% baseline VM, you’re charged for 0.125 OCPU each hour, whether you use 12.5% or less of the CPU core or if you burst and use 100% of the core. Burstable VM’s flexibility and simple pricing make it a great choice to run your low CPU workloads.