SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

Oracle Management Server is Down – Failed to push ldap config data to libOvd for service instance “idstore.ldap”

I have setup Oracle Cloud Control 13c on virtual machine.

Yesterday machine was shut downed forcibly and Cloud Control was unable to start up after that.

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down

Let’s check the following log:

[oracle@oragc log]$ tailf /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out

SEVERE: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default", 
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; lineNumber: 210; 
columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF.
Jan 03, 2017 2:02:17 PM oracle.security.opss.internal.runtime.ServiceContextManagerImpl createContextInternal
WARNING: Service instance "idstore.ldap" cannot be instantiated. 
Reason: oracle.security.jps.service.idstore.IdentityStoreException: 
JPS-02592: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default",
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; 
lineNumber: 210; columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF..
Jan 03, 2017 2:02:17 PM oracle.security.jps.internal.config.OpssCommonStartup start
INFO: Jps startup failed.
<Jan 3, 2017 2:02:17 PM GET> <Error> <Security> 
<BEA-090892> <The loading of an OPSS java security policy provider failed due to an exception. 
See the exception stack trace or the server log file for the root cause. 
If there is no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. 
Error message: null>

I have found the solution on Oracle Community site: https://community.oracle.com/thread/2375509

“Please follow below steps to get you domain up and running
1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

2) Delete $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

3) copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd_11.1.1/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/

4) Restart the server.”

After performing the above steps:

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up