Feed aggregator

Multi-Element Arrangements

OracleApps Epicenter - Sat, 2015-11-07 07:10
Multi-element arrangement aka occurs when a vendor agrees to provide more than one product or a combination of products and services to a customer in an arrangement. Multi-element arrangements may include additional software products, rights to purchase additional software products at a significant incremental discount, specified upgrades or enhancements, hardware, PCS or other services. Multiple-element […]
Categories: APPS Blogs

Roman numerals to decimal in SQL

RDBMS Insight - Sat, 2015-11-07 06:38

Earlier this week I got tangled up doing a Roman Numeral conversion in my head. So of course my second thought, right after “Doh!”, was “I bet I can write a SQL statement to do this for me next time.”

The algorithm to convert roman numerals to decimal numbers is straightforward.

    For each character, starting from the RIGHT (lowest value Roman numeral):

  • Convert the character into the value it represents
  • If the character’s value is greater than or equal to the previous one, add the value to the running total
  • If character’s value is less than the previous one, subtract the value from the running total

Let’s look at a couple of examples:

    IX
    Starting from the right:

  • X = 10; Running total = 10
  • I = 1; 1 < 10; Running total = 9
  • Final result: 9
    MCMLXXVII
    Starting from the right:

  • I = 1; Running total = 1
  • I = 1; Running total = 2
  • V = 5; Running total = 7
  • X = 10; Running total = 17
  • X = 10; Running total = 27
  • L = 50; Running total = 77
  • M = 1000; Running total = 1077
  • C = 100; 100 < 1000; Running total = 977
  • M = 1000; Running total = 1977

Here’s an online converter

This is a lovely place to use Recursive With in SQL. We’ll use a CTE, aka recursive subquery, to chomp the string one character at a time from the right, keeping a running total as we go; first decode the character, then add or subtract as appropriate to the running total until we’re out of characters.

See my previous post on Recursion with Recursive With. From that post, here’s the recursive WITH basic syntax:

WITH Tablename (col1, col2, col3...) AS
(SELECT A, B, C... FROM dual                         --anchor member
UNION ALL
SELECT newA, newB, newC... FROM Tablename WHERE...   --recursive member
)
SELECT ... FROM Tablename WHERE ...

Let’s start with just identifying the characters and substrings we want to work on, using substr and length.
Substr: http://docs.oracle.com/database/121/SQLRF/functions196.htm#SQLRF06114
Length: http://docs.oracle.com/database/121/SQLRF/functions100.htm#SQLRF00658

WITH Roman (Numeral) AS
(SELECT 'MDCCVIII' AS Numeral FROM dual),
RomToDec (ThisOne, Remaining, Pos) AS
(SELECT CAST(NULL AS varchar2(4000)) AS ThisOne, Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT  
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos > 0
)
SELECT ThisOne, Remaining, Pos FROM RomToDec
;
VAL THISONE  REMAINING     POS
0     -       MDCCVIII     8
0     I       MDCCVII      7
0     I       MDCCVI       6
0     I       MDCCV        5
0     V       MDCC         4
0     C       MDC          3
0     C       MD           2
0     D       M            1
0     M       -            0
0     -       -            -

Now we use CASE to turn the Roman Numeral character into a number.
Case: http://docs.oracle.com/database/121/SQLRF/expressions004.htm#SQLRF20037

WITH Roman (ThisOne) AS
(SELECT 'M' AS ThisOne FROM dual)
SELECT 
CASE ThisOne 
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec
FROM Roman;

I’m only going to deal with values of less than 5000 so M will be the largest we need. I’m going to patch that in to my query-in-progress, and also add a “Val” column to hold the running total:

WITH Roman (Numeral) AS
(SELECT 'MDCCVIII' AS Numeral FROM dual),
RomToDec (Val, ThisOne, ThisDec, Remaining, Pos) AS
(SELECT 0 AS Val, CAST(NULL AS varchar2(4000)) AS ThisOne, 0 AS ThisDec, Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
RomToDec.Val, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
CASE substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos > 0
)
SELECT Val, ThisOne, ThisDec, Remaining, Pos FROM RomToDec
;
VAL    THISONE  THISDEC     REMAINING    POS
0      -        0           MDCCVIII     8
0      I        1           MDCCVII      7
0      I        1           MDCCVI       6
0      I        1           MDCCV        5
0      V        5           MDCC         4
0      C      100           MDC          3
0      C      100           MD           2
0      D      500           M            1
0      M     1000           -            0

An alternative to using CASE here would be to add another inline table in the WITH clause and join with it:

WITH Roman (Numeral) AS
(SELECT 'MDCCVIII' AS Numeral FROM dual),
CharValues (Numeral, VALUE) AS (
SELECT 'M', 1000 FROM dual UNION ALL
SELECT 'D', 500  FROM dual UNION ALL
SELECT 'C', 100  FROM dual UNION ALL
SELECT 'L', 50   FROM dual UNION ALL
SELECT 'X', 10   FROM dual UNION ALL
SELECT 'V', 5    FROM dual UNION ALL
SELECT 'I', 1    FROM dual ),
RomToDec (Val, ThisOne, ThisDec, Remaining, Pos) AS
(SELECT 0 AS Val, CAST(NULL AS varchar2(4000)) AS ThisOne, 0 AS ThisDec, Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
RomToDec.Val, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
nvl(CharValues.Value,0) ThisDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec LEFT OUTER JOIN CharValues ON ( substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1) = CharValues.Numeral )                      
WHERE RomToDec.Pos > 0 
)
SELECT Val, ThisOne, ThisDec, Remaining, Pos FROM RomToDec
;

Now finally we can add or subtract ThisDec from the running total using the rule:

    For each character starting from the left,

  • If character’s value is >= previous character’s value, add it to the running total
  • If character’s value is < previous character's value, subtract it from the running total

To implement this, I added another column to the recursive member, LastDec, which just holds the previous value of the ThisDec column. I also could have used the LAST analytic function here, but since we’re using recursive subquery it’s trivially easy to just populate the LastDec column with RomToDec.ThisDec .

WITH Roman (Numeral) AS
(SELECT 'MCMLXXXVII' AS Numeral FROM dual),
RomToDec (ThisVal, ThisOne, ThisDec, LastDec, Remaining, Pos) AS
(SELECT 0 AS ThisVal, CAST(NULL AS varchar2(4000)) AS ThisOne, 
0 AS ThisDec, 0 AS LastDec,
Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
CASE 
  WHEN RomToDec.ThisDec >= RomToDec.LastDec THEN RomToDec.ThisVal + ThisDec 
  ELSE RomToDec.ThisVal-ThisDec
END AS ThisVal, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
CASE substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec,
RomToDec.ThisDec AS LastDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos >= 0
)
SELECT * FROM RomToDec ;

And the final result:

WITH Roman (Numeral) AS
(SELECT 'MCMLXXXVII' AS Numeral FROM dual),
RomToDec (ThisVal, ThisOne, ThisDec, LastDec, Remaining, Pos) AS
(SELECT 0 AS ThisVal, CAST(NULL AS varchar2(4000)) AS ThisOne, 
0 AS ThisDec, 0 AS LastDec,
Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
CASE 
  WHEN RomToDec.ThisDec >= RomToDec.LastDec THEN RomToDec.ThisVal + ThisDec 
  ELSE RomToDec.ThisVal-ThisDec
END AS ThisVal, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
CASE substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec,
RomToDec.ThisDec AS LastDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos >= 0
)
SELECT thisVal
FROM RomToDec 
WHERE pos IS NULL
;
THISVAL
1987

By the way, you can run these queries right in LiveSQL rather than starting up a test DB.

LiveSQL

Here’s a direct link to the final query in LiveSQL, ready to run!

Note that this SQL doesn’t validate the Roman numeral. For example, IXVIII is an invalid Roman numeral, but this SQL statement will return 17 instead of giving an error.

Today’s image is a photograph by Staci Bigelow

Categories: DBA Blogs

JDBC connection samples in Oracle Enterprise Data Quality (OEDQ) to Oracle Service Name and MS SQL SSL

Ittichai Chammavanijakul - Fri, 2015-11-06 16:46

This post is just a quick note to myself on how to configure JBDC on Oracle Enterprise Data Quality (OEDQ) to connect to different databases.

First let’s talk about connecting to Oracle database. By default, OEDQ’s Data Store can only connect Oracle database by SID, but not by Service Name.

 

Fortunately, the JDBC connection option is available to take advantage of the connection string that can support Service Name and multiple hosts if needed.

EDQ_datastore_JDBC

 

Sample:

Driver class name: weblogic.jdbc.oracle.OracleDriver
JDBC URL:  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = host3)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = srvname)))

 

Another sample is to connect Microsoft SQL Server which is requires SSL connection. By default if using Server > Database > Microsoft SQL Server (2000-2008), you will get this error.

[FMWGEN][SQLServer JDBC Driver] The SQL Server login requires an SSL connection. (Code 2,011)

EDQ_MSSQL_SSL_error

Again, let’s use the JDBC connection instead.

Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL:  jdbc:weblogic:sqlserver://host:1433;databaseName=DBNAME;EncryptionMethod=SSL;ValidateServerCertificate=false

EDQ_MSSQL_SSL_jdbc

Categories: DBA Blogs

OpenWorld 2015 Presentations Available

Jim Marion - Fri, 2015-11-06 12:06

OpenWorld 2015 presentations are now available in the content catalog. My PeopleTools Tips and Techniques session presentation is available here.

What's new in Forms 12c?

Gerd Volberg - Fri, 2015-11-06 02:13
Here are some of the differences which I found in the new Forms 12c version. And some things, that didn't change.

Let's start with the Object Navigator. Left side is Forms 11g, right is Forms 12c.

There are absolutely no changes between the two versions.




Same picture when we look at the menu and toolbar. No changes.



And now we see the big difference between 11g and 12c. The last decades Forms was known as "Forms Builder". And now we have a rebranding to "Form Builder".

Maybe it is only a typo. Let's wait what name it has in Release 2.


After looking at the layout I searched deeper. The help system! The global Content only changed one thing.


Oracle Forms User Interface was renamed to Oracle Form Builder. In this submenu you can now find Installation and Configuration.




Last but not least I compared the index of both help systems.

New help is available for:

  • Compiler Output
  • Form_Query_Only
  • Forms_Abterm_Cleanup
  • Forms_C_Stack_Trace_To_File
  • Forms_RUEI_Send_Form_Name
  • Hide PL/SQL Compile Dialog
  • Max_Event_Wait
  • Record_Count_Displayed
  • Show URL Parameters
  • System.Last_Event
  • System.Notification_Value
  • System.RecMgr_MAlloced
  • System.RecMgr_Mapped_In_Use
  • System.RecMgr_Mapped_Reserved
  • System.RecMgr_Mapped
  • System.RecMgr_Written_In_Use
  • System.RecMgr_Written
  • User_IP_Address


That's it
Gerd

Whitepaper List as at November 2015

Anthony Shorten - Thu, 2015-11-05 16:55
The following Oracle Utilities Application Framework technical whitepapers are available from My Oracle Support at the Doc Id's mentioned below. Some have been updated in the last few months to reflect new advice and new features.

Unless otherwise marked the technical whitepapers in the table below are applicable for the following products (with versions):

Doc Id Document Title Contents ConfigLab Design Guidelines This whitepaper outlines how to design and implement a data management solution using the ConfigLab facility.
This whitepaper currently only applies to the following products:
Technical Best Practices for Oracle Utilities Application Framework Based Products Whitepaper summarizing common technical best practices used by partners, implementation teams and customers. Performance Troubleshooting Guideline Series A set of whitepapers on tracking performance at each tier in the framework. The individual whitepapers are as follows:
  • Concepts - General Concepts and Performance Troublehooting processes
  • Client Troubleshooting - General troubleshooting of the browser client with common issues and resolutions.
  • Network Troubleshooting - General troubleshooting of the network with common issues and resolutions.
  • Web Application Server Troubleshooting - General troubleshooting of the Web Application Server with common issues and resolutions.
  • Server Troubleshooting - General troubleshooting of the Operating system with common issues and resolutions.
  • Database Troubleshooting - General troubleshooting of the database with common issues and resolutions.
  • Batch Troubleshooting - General troubleshooting of the background processing component of the product with common issues and resolutions.
Software Configuration Management Series
A set of whitepapers on how to manage customization (code and data) using the tools provided with the framework. Topics include Revision Control, SDK Migration/Utilities, Bundling and Configuration Migration Assistant. The individual whitepapers are as follows:
  • Concepts - General concepts and introduction.
  • Environment Management - Principles and techniques for creating and managing environments.
  • Version Management - Integration of Version control and version management of configuration items.
  • Release Management - Packaging configuration items into a release.
  • Distribution - Distribution and installation of releases across environments
  • Change Management - Generic change management processes for product implementations.
  • Status Accounting - Status reporting techniques using product facilities.
  • Defect Management - Generic defect management processes for product implementations.
  • Implementing Single Fixes - Discussion on the single fix architecture and how to use it in an implementation.
  • Implementing Service Packs - Discussion on the service packs and how to use them in an implementation.
  • Implementing Upgrades - Discussion on the the upgrade process and common techniques for minimizing the impact of upgrades.
Oracle Utilities Application Framework Security Overview A whitepaper summarizing the security facilities in the framework. Now includes references to other Oracle security products supported. LDAP Integration for Oracle Utilities Application Framework based products A generic whitepaper summarizing how to integrate an external LDAP based security repository with the framework. Oracle Utilities Application Framework Integration Overview A whitepaper summarizing all the various common integration techniques used with the product (with case studies). Single Sign On Integration for Oracle Utilities Application Framework based products A whitepaper outlining a generic process for integrating an SSO product with the framework. Oracle Utilities Application Framework Architecture Guidelines This whitepaper outlines the different variations of architecture that can be considered. Each variation will include advice on configuration and other considerations. Batch Best Practices This whitepaper outlines the common and best practices implemented by sites all over the world. Technical Best Practices V1 Addendum Addendum to Technical Best Practices for Oracle Utilities Customer Care And Billing V1.x only. XAI Best Practices This whitepaper outlines the common integration tasks and best practices for the Web Services Integration provided by the Oracle Utilities Application Framework. Oracle Identity Manager Integration Overview This whitepaper outlines the principals of the prebuilt intergration between Oracle Utilities Application Framework Based Products and Oracle Identity Manager used to provision user and user group security information. For Fw4.x customers use whitepaper 1375600.1 instead. Production Environment Configuration Guidelines A whitepaper outlining common production level settings for the products based upon benchmarks and customer feedback. 1177265.1 What's New In Oracle Utilities Application Framework V4?  Whitepaper outlining the major changes to the framework since Oracle Utilities Application Framework V2.2. 1290700.1 Database Vault Integration Whitepaper outlining the Database Vault Integration solution provided with Oracle Utilities Application Framework V4.1.0 and above. 1299732.1 BI Publisher Guidelines for Oracle Utilities Application Framework Whitepaper outlining the interface between BI Publisher and the Oracle Utilities Application Framework 1308161.1 Oracle SOA Suite Integration with Oracle Utilities Application Framework based products This whitepaper outlines common design patterns and guidelines for using Oracle SOA Suite with Oracle Utilities Application Framework based products. 1308165.1 MPL Best Practices
This is a guidelines whitepaper for products shipping with the Multi-Purpose Listener.
This whitepaper currently only applies to the following products:
1308181.1 Oracle WebLogic JMS Integration with the Oracle Utilities Application Framework This whitepaper covers the native integration between Oracle WebLogic JMS with Oracle Utilities Application Framework using the new Message Driven Bean functionality and real time JMS adapters. 1334558.1 Oracle WebLogic Clustering for Oracle Utilities Application Framework This whitepaper covers process for implementing clustering using Oracle WebLogic for Oracle Utilities Application Framework based products. 1359369.1 IBM WebSphere Clustering for Oracle Utilities Application Framework This whitepaper covers process for implementing clustering using IBM WebSphere for Oracle Utilities Application Framework based products 1375600.1 Oracle Identity Management Suite Integration with the Oracle Utilities Application Framework This whitepaper covers the integration between Oracle Utilities Application Framework and Oracle Identity Management Suite components such as Oracle Identity Manager, Oracle Access Manager, Oracle Adaptive Access Manager, Oracle Internet Directory and Oracle Virtual Directory. 1375615.1 Advanced Security for the Oracle Utilities Application Framework This whitepaper covers common security requirements and how to meet those requirements using Oracle Utilities Application Framework native security facilities, security provided with the J2EE Web Application and/or facilities available in Oracle Identity Management Suite. 1486886.1 Implementing Oracle Exadata with Oracle Utilities Customer Care and Billing This whitepaper covers some advice when implementing Oracle ExaData for Oracle Utilities Customer Care And Billing. 878212.1 Oracle Utilities Application FW Available Service Packs This entry outlines ALL the service packs available for the Oracle Utilities Application Framework. 1454143.1 Certification Matrix for Oracle Utilities Products This entry outlines the software certifications for all the Oracle Utilities products. 1474435.1 Oracle Application Management Pack for Oracle Utilities Overview This whitepaper covers the Oracle Application Management Pack for Oracle Utilities. This is a pack for Oracle Enterprise Manager. 1506830.1 Configuration Migration Assistant Overview
This whitepaper covers the Configuration Migration Assistant available for Oracle Utilities Application Framework V4.2.0.0.0. This replaces ConfigLab for some products.
1506855.1 Integration Reference Solutions
This whitepaper covers the various Oracle technologies you can use with the Oracle Utilities Application Framework. 1544969.1 Native Installation Oracle Utilities Application Framework This whitepaper describes the process of installing Oracle Utilities Application Framework based products natively within Oracle WebLogic. 1558279.1 Oracle Service Bus Integration  This whitepaper describes direct integration with Oracle Service Bus including the new Oracle Service Bus protocol adapters available. Customers using the MPL should read this whitepaper as the Oracle Service Bus replaces MPL in the future and this whitepaper outlines how to manually migrate your MPL configuration into Oracle Service Bus.

Note: In Oracle Utilities Application Framework V4.2.0.1.0 and above, Oracle Service Bus Adapters for Outbound Messages and Notification/Workflow are available 1561930.1 Using Oracle Text for Fuzzy Searching This whitepaper describes how to use the Name Matching and  fuzzy operator facilities in Oracle Text to implemement fuzzy searching using the @fuzzy helper fucntion available in Oracle Utilities Application Framework V4.2.0.0.0 1606764.1
Audit Vault Integration This whitepaper describes the integration with Oracle Audit Vault to centralize and separate Audit information from OUAF products. Audit Vault integration is available in OUAF 4.2.0.1.0 and above only.
1644914.1
Migrating XAI to IWS
Migration from XML Application Integration to the new native Inbound Web Services in Oracle Utilities Application Framework 4.2.0.2.0 and above.
1643845.1
Private Cloud Planning Guide
Planning Guide for implementing Oracle Utilities products on Private Clouds using Oracle's Cloud Foundation set of products.
1682436.1
ILM Planning Guide
Planning Guide for Oracle Utilities new ILM based data management and archiving solution.
1682442.1
ILM Implementation Guide for Oracle Utilities Customer Care and Billing
Implementation Guide for the ILM based solution for the Oracle Utilities Customer Care And Billing.
207303.1
Client / Server Interoperability Support Matrix
Certification Matrix.
1965395.1
Cache Nodes Configuration using BatchEdit utility
Using the new Batch Edit Wizard to configure batch quickly and easily
1628358.1
Overview and Guidelines for Managing Business Exceptions and Errors
Best Practices for To Do Management
2014163.1
Oracle Functional/Load Testing Advanced Pack for Oracle Utilities Overview
Overview of the new Oracle Utilities testing solution. Updated for 5.0.0.1.0.
1929040.1
ConfigTools Best Practices
Best Practices for using the configuration tools facility
2014161.1
Oracle Utilities Application Framework - Keystore Configuration
Managing the keystore

Oracle Utilities on Oracle Technology Network

Anthony Shorten - Thu, 2015-11-05 15:45

Information about the Oracle Utilities solutions is now available from the Oracle Technology Network.

This site includes links to download software, find documentation, connect to the user community and learn more about our solutions.

Oracle Priority Support Infogram for 05-NOV-2015

Oracle Infogram - Thu, 2015-11-05 15:19

RDBMS

Database Insider - November 2015 issue now available, from Exadata Partner Community – EMEA.

SQL


Forms

Browser-less access to Forms 12c, from Proactive Support - Oracle Development Tools.

And from the same source:  Forms 12c and Webstart

Solaris


Exalytics



WebLogic


Java


From the same source: Oracle JET Meets AngularJS

MAF



SOA

SOA Suite Handbook preview, from SOA & BPM Partner Community Blog.

And from the same source: SOA Suite 12c – What You Need to Know

BI


EBS

From the Oracle E-Business Suite Support blog:




From the Oracle E-Business Suite Technology blog:




SQL Tuning: Thinking in Sets / How and When to be Bushy

Dominic Brooks - Thu, 2015-11-05 11:48

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best option.

SELECT bd.trade_id
,      bdp.portfolio_id
,      bd.deal_id 
,      bd.book_id
,      pd.deal_status   prev_deal_status
FROM   deals            bd
,      portfolios       bdp
,      deals            pd
,      portfolios       pdp
-- today's data
WHERE  bd.business_date         = :t_date
AND    bd.src_bus_date          < :t_date
AND    bd.type                  = 'Trade'
AND    bdp.ref_portfolio_id     = bd.book_id
-- yesterday's data
AND    pd.business_date         = :y_date
AND    pd.type                  = 'Trade'
AND    pdp.ref_portfolio_id     = pd.book_id
-- some join columns
AND    bd.trade_id              = pd.trade_id
AND    bdp.portfolio_id         = pdp.portfolio_id;

There is no particular problem with how the SQL statement is written per se.

It is written in what seems to be a standard developer way.

Call it the “lay everything on the table” approach.

This is a common developer attitude:

“Let’s just write a flat SQL structure and let Oracle figure the best way out.”

Hmmm… Let’s look at why this can be a problem.

First, what is the essential requirement of the SQL?

Compare information (deal status) that we had yesterday for a subset of deals/trades

Something like that anyway…

So … What is the problem?

The Optimizer tends to rewrite and transform any SQL we give it anyway and tries to flatten it out.

The SQL above is already flat so isn’t that a good thing? Shouldn’t there be less work for the optimizer to do?

No, not necessarily. Flat SQL immediately restricts our permutations.

The problem comes with how Oracle can take this flat SQL and join the relevant row sources to efficiently get to the relevant data.

Driving Rowsource

Let’s assume that we should drive from today’s deal statuses (where we actually drive from will depend on what the optimizer estimates / costs).

SELECT ...
FROM   deals            bd
,      portfolio        bdp
...
-- today's data
WHERE  bd.business_date         = :t_date
AND    bd.src_bus_date          < :t_date
AND    bd.type                  = 'Trade'
AND    bdp.ref_portfolio_id     = bd.book_id
....
Where do we go from here?

We want to join from today’s deals to yesterdays deals.

But the data for the two sets of deals data is established via the two table join (DEALS & PORTFOLIOS).

thinking_in_Sets_venn

We want to join on TRADE_ID which comes from the two DEALS tables and PORTFOLIO_ID which comes from the two PORTFOLIOS tables.

SELECT ...
FROM   ...
,      deals            pd
,      portfolios       pdp
WHERE  ...
-- yesterday's data
AND    pd.business_date         = :y_date
AND    pd.type                  = 'Trade'
AND    pdp.ref_portfolio_id     = pd.book_id
...

And joined to via:

AND    bd.trade_id              = pd.trade_id
AND    bdp.portfolio_id         = pdp.portfolio_id

So from our starting point of today’s business deals, we can either go to PD or to PDP, but not to both at the same time.

Hang on? What do you mean not to both at the same time?

For any multi-table join involving more than two tables, the Optimizer evaluates the different join tree permutations.

Left-Deep Tree

Oracle has a tendency to choose what is called a left-deep tree.

If you think about a join between two rowsources (left and right), a left-deep is one where the second child (the right input) is always a table.

NESTED LOOPS are always left-deep.

HASH JOINS can be left-deep or right-deep (normally left-deep as already mentioned)

Zigzags are also possible, a mixture of left-deep and right-deep.

Below is an image of a left-based tree based on the four table join above.

left_tree

Here is an execution plan which that left-deep tree might represent:

---------------------------------------------------------------
| Id  | Operation                               | Name        |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |
|   1 |  NESTED LOOPS                         |               |
|   2 |   NESTED LOOPS                        |               |
|   3 |    NESTED LOOPS                       |               |
|   4 |     NESTED LOOPS                      |               |
|*  5 |      TABLE ACCESS BY ROWID            | DEALS         |
|*  6 |       INDEX RANGE SCAN                | DEALS_IDX01   |
|   7 |      TABLE ACCESS BY INDEX ROWID      | PORTFOLIOS    |
|*  8 |       INDEX UNIQUE SCAN               | PK_PORTFOLIOS |
|*  9 |     TABLE ACCESS BY INDEX ROWID       | DEALS         |
|* 10 |      INDEX RANGE SCAN                 | DEALS_IDX01   |
|* 11 |    INDEX UNIQUE SCAN                  | PK_PORTFOLIOS |
|* 12 |   TABLE ACCESS BY INDEX ROWID         | PORTFOLIOS    |
---------------------------------------------------------------

Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   5 - filter("BD"."TYPE"='Trade' AND "BD"."SRC_BUS_DATE"<:t_date) 
   6 - access("BD"."BUSINESS_DATE"=:t_date) 
   8 - access("BD"."BOOK_ID"="BDP"."REF_PORTFOLIO_ID") 
   9 - filter(("BD"."TYPE"='Trade' AND "BD"."TRADE_ID"="PD"."TRADE_ID")) 
  10 - access("PD"."BUSINESS_DATE"=:y_date) 
  11 - access("PD"."BOOK_ID"="PDP"."REF_PORTFOLIO_ID") 
  12 - filter("BDP"."PORTFOLIO_ID"="PDP"."PORTFOLIO_ID")

Right-Deep Tree

A right-deep tree is one where the first child, the left input, is a table.

Illustration not specific to the SQL above:

right_tree

Bushy Tree

For this particular SQL, this is more what we are looking for:

bushy_tree

The essence of the problem is that we cannot get what is called bushy join, not with the original flat SQL.

The Optimizer cannot do this by default. And this isn’t an approach that we can get at by hinting (nor would we want to if we could, of course!).

Rewrite Required

To get this bushy plan, we need to rewrite our SQL to be more explicit around the set-based approach required.

WITH subq_curr_deal AS
     (SELECT /*+ no_merge */
             bd.trade_id
      ,      bd.deal_id
      ,      bd.book_id
      ,      bdp.portfolio_id
      FROM   deals      bd
      ,      portfolios bdp
      WHERE  bd.business_date         = :t_date
      AND    bd.src_bus_date          < :t_date
      AND    bd.type                  = 'Trade'
      AND    bdp.ref_portfolio_id     = bd.book_id)
,    subq_prev_deal AS
     (SELECT /*+ no_merge */
             pd.trade_id 
      ,      pd.deal_status
      ,      pdp.portfolio_id
      FROM   deals      pd
      ,      portfolios pdp
      WHERE  pd.business_date         = :y_date
      AND    pd.type                  = 'Trade'
      AND    pdp.ref_portfolio_id     = pd.book_id)
SELECT cd.trade_id
,      cd.portfolio_id
,      cd.deal_id
,      cd.book_id 
,      pd.deal_status prev_deal_status
FROM   subq_curr_deal cd
,      subq_prev_deal pd
WHERE  cd.trade_id             = pd.trade_id
AND    cd.portfolio_id         = pd.portfolio_id;
How exactly does the rewrite help?

By writing the SQL deliberately with this structure, by using WITH to create subqueries in conjunction with no_merge, we are deliberately forcing the bushy join.

This is an example execution plan that this bushy tree might represent.

----------------------------------------------------------
| Id  | Operation                        | Name          |
----------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |
|*  1 |  HASH JOIN                       |               |
|   2 |   VIEW                           |               |
|   3 |    NESTED LOOPS                  |               |
|   4 |     NESTED LOOPS                 |               |
|*  5 |      TABLE ACCESS BY INDEX ROWID | DEALS         |
|*  6 |       INDEX RANGE SCAN           | DEALS_IDX01   |
|*  7 |      INDEX UNIQUE SCAN           | PK_PORTFOLIOS |
|   8 |     TABLE ACCESS BY INDEX ROWID  | PORTFOLIOS    |
|   9 |   VIEW                           |               |
|  10 |    NESTED LOOPS                  |               |
|  11 |     NESTED LOOPS                 |               |
|* 12 |      TABLE ACCESS BY INDEX ROWID | DEALS         |
|* 13 |       INDEX RANGE SCAN           | DEALS_IDX01   |
|* 14 |      INDEX UNIQUE SCAN           | PK_PORTFOLIOS |
|  15 |     TABLE ACCESS BY INDEX ROWID  | PORTFOLIOS    |
----------------------------------------------------------
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
                                 
   1 - access("CD"."TRADE_ID"="PD"."TRADE_ID" AND "CD"."PORTFOLIO_ID"="PD"."PORTFOLIO_ID")
   5 - filter(("BD"."TYPE"='Trade' AND "BD"."SRC_BUS_DATE"<:t_date)) 
   6 - access("BD"."BUSINESS_DATE"=:t_date ) 
   7 - access("BD"."BOOK_ID"="BDP"."REF_PORTFOLIO_ID") 
  12 - filter("PD"."TYPE"='Trade') 
  13 - access("PD"."BUSINESS_DATE"=:y_date) 
  14 - access("PDP"."REF_PORTFOLIO_ID"="PD"."BOOK_ID")
Is this a recommendation to go use WITH everywhere?

No.

What about the no_merge hint?

No.
The no_merge hint is a tricky one. This is not necessarily a recommendation but its usage here prevents the Optimizer from flattening. I often find it goes hand-in-hand with this sort of deliberately structured SQL for that reason, and similar goes for push_pred.

Do developers need to know about left deep, right deep and bushy?

No, not at all.

Takeaways?

It helps to think in sets and about what sets of data you are joining and recognise when SQL should be deliberately structured.

Further Reading

http://www.oaktable.net/content/right-deep-left-deep-and-bushy-joins
https://tonyhasler.wordpress.com/2008/12/27/bushy-joins/
https://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/06/16/hitchhikers-guide-to-explain-plan-8
https://jonathanlewis.wordpress.com/2007/01/24/left-deep-trees/
https://antognini.ch/top/


Flow Builder/OpenScript and Oracle Test Manager

Anthony Shorten - Wed, 2015-11-04 19:32

The Oracle Functional Testing Advanced Pack for Oracle Utilities provides the content to use Oracle Application Testing Suite to verify your business processes via flows using your test data for multiple scenarios.

Customers have asked the relationship with the tools in the Oracle Application Testing Suite. Here is the clarification:

  • The Oracle Functional Testing Advanced Pack for Oracle Utilities is loaded into the Oracle Flow Builder component of the Oracle Application Testing Suite Functional Testing product. The components represent all the functions within the product.
  • You build a flow, using drag and drop, sequencing the provided components that matches the business process you want to verify. If there is no component we ship that covers your extensions then you can use a supplied component builder to build a meta data based service component. If you want to model a user interface then OpenScript can be used to record a component to add to the component library.
  • You then can attach your test data. There are a number of techniques available for that. You can natively input the data if you wish into the component, generate a spreadsheet to fill in the data (and attach it after you filled it out) or supply a CSV data file that represents the data in the flow.
  • You generate (not code) a script. No additional coding is required. As part of the license you can code OpenScript if  you have developers for any work if you wish but it is typically not required.
  • You then have a choice to execute the script.
    • You can execute the script from the OpenScript tool in Eclipse (if you are a developer for example).
    • You can execute the script from a command line (for example you can do this from a third party test manager if you wish)
    • You can automatically execute the script from Oracle Test Manager. This will allow groups of script to be scheduled and it reports on the results for you.
    • You can load the script into the Oracle Load Testing toolset and include it in a performance test suite of tests.

Remember our testing pack is service based not user interface based. You will need to make sure that the Web Services Accelerator is installed.

Essentially Flow Builder builds the flow and script from the components in the pack, Openscript or Oracle Test Manager executes it for you.

SQL Needs a Sister (Broken Link Corrected)

Gerger Consulting - Wed, 2015-11-04 01:28
I've just published an article on Medium about the missing sister of SQL. I think there is a fundamental mistake we’ve been making in using SQL. We use it both to ask a question and format the answer and I think this is just wrong. You can read the article at this link. (Sorry for the broken link in the previous post and a big thank you to the person who sent us a comment about the issue. :-) )
Categories: Development

Hide and Seek

Scott Spendolini - Tue, 2015-11-03 14:30

In migrating SERT from 4.2 to 5.0, there's a number of challenges that I'm facing. This has to do with the fact that I am also migrating a custom theme to the Universal Theme, as almost 100% of the application just worked if I chose to leave it alone. I didn't. More on that journey in a longer post later.

In any case, some of the IR filters that I have on by default can get a bit... ugly. Even in the Universal Theme:

2015 11 03 15 25 18

In APEX 4.2, you could click on the little arrow, and it would collapse the region entirely, leaving only a small trace that there's a filter. That's no longer the case:

2015 11 03 15 25 31

So what to do... Enter CSS & the Universal Theme.

Simply edit the page and add the following to the Inline CSS region (or add the CSS to the Theme Roller if you want this change to impact all IRs):

.a-IRR-reportSummary-item { display: none; }

This will cause most of the region to simply not display at all - until you click on the small triangle icon, which will expand the entire set of filters for the IR. Clicking it again makes it go away. Problem solved with literally three words (and some punctuation).

Updated Testing Advanced Pack Overview Whitepaper

Anthony Shorten - Mon, 2015-11-02 13:00

An updated version of the Oracle Functional/Load Testing Advanced Pack for Oracle Utilities has been released on My Oracle Support at Doc Id: 2014163.1. The updates to the whitepaper include updates for the 5.0.0.0.0 release with the following information:

  •  Updates for the new content for Oracle Mobile Workforce Management, Oracle Real Time Scheduler, Oracle Utilities Application Framework, Oracle Utilities Customer Care and Billing and Oracle Work And Asset Management.
  • Updates for the new Component Builder and Component Verifier utilities that allow the addition of new custom components.
  • Updates to the Frequently Asked Questions covering licensing and the common implementation questions you may have about the Advanced Pack.

This new version now only adds new content and new capabilities but utilizes the power and facilities of the Oracle Application Testing Suite (Oracle Flow Builder, OpenScript and Oracle Load Testing) to deliver the ability to rapidly test, implement and upgrade Oracle Utilities products.

Show me the money – Business Intelligence

Nilesh Jethwa - Mon, 2015-11-02 06:33

This Business Intelligence article discusses one very important question “Have we lost the true meaning and purpose of Business Intelligence?”

In summary, it says that the bottom line for any Business Intelligence is to increase profit and performance and if done wrong it is absolute waste of time and money.

So if your management is hooked on the Big-Data wagon and fantasizing about the hidden gold behind the untapped data sources such as social media, click stream, web logs etc then it is time to keep the focus on the main purpose of “Business Intelligence”. All these systems help us to analyze big data or small data but whether they really impact our bottom line is the question that management needs to keep it as the top priority.

Every decision should flow and trickle down from this focus point “Show me the money!!”

Click here to read more about what business intelligence really means and what should be the core focus for management

Cryptowall, USB's, emailed malware and "honor among thieves"

PeopleSoft Technology Blog - Mon, 2015-11-02 02:23

 I'm sure many of you have heard recently of the rising prevalence of ransomware, particularly Cryptolock/Cryptowall and the growing prominence of walk-in Bitcoin exchanges and Buttonwood meetups.

 Cryptowall is a case in point of the malware. Spread mainly by infected email and "found" or shared USB memory sticks.

==============================
What is CryptoWall?
https://www.pcrisk.com/removal-guides/7844-cryptowall-virus

What is CryptoWall?
The CryptoWall ransomware virus infiltrates users' operating systems via infected email messages and fake downloads (for example, rogue video players or fake Flash updates). After successful infiltration, this malicious program encrypts files stored on users' computers (*.doc, *.docx, *.xls, *.ppt, *.psd, *.pdf, *.eps, *.ai, *.cdr, *.jpg, etc.) and demands payment of a $500 ransom (in Bitcoins) to decrypt them. Cyber criminals responsible for releasing this rogue program, ensure that it executes on all Windows versions (Windows XP, Windows Vista, Windows 7, and Windows 8). CryptoWall ransomware creates HELP_DECRYPT.PNG, HELP_DECRYPT.HTML and HELP_DECRYPT.TXT files within each folder containing the encrypted files.


"Note that at time of writing, there were no known tools capable of decrypting files encrypted by CryptoWall without paying the ransom." 12 September 2015 
==============================

There is generally no good business reason why typical corporate workstation/laptop users should:
- have local administrator access
- have enabled USB ports
- need to open office productivity tools documents containing macros

These features should be disabled.

My "honor among thieves" statement relates to the tendency, as observed, that payment of the "ransom" in Bitcoins has been the only current successful way of retrieving the encrypted data/disks. The alternative is frequent backups.

Goodbye Old Bike, Hello Old Bike

FeuerThoughts - Sat, 2015-10-31 09:53
In 1977, one of my best buddies told me about a friend he had, who had just custom-built a bicycle for him, from components. This guy would do the same thing for me. "How cool is that?" thought I.

A few weeks later, for (according to my admittedly leaky memory) about $200, I received a bicycle that became one of my all-time favorite machines.

It was built around a black Raleigh Competition Reynolds 531 double butted steel tubing. It was powered by a lovely, engraved, all-metal Shimano 600 Arabesque derailleur ("One of the most decorative groups Shimano ever released"):


The bike was a beautiful ride and all-around excellent transportation. It came to Chicago with me in 1981. It took me downtown for my first job as a programmer in Chicago, at First National Bank (I commuted by bike whenever possible). It accompanied me on day trips all over Chicagoland. It took me back and forth to the CISPES office, a cause and organization that consumed way too much of my life for too little in return. Whatever foolishness I chose to engage in, this bike helped me do it in style (my style: it was scratched up and usually dirty, but the chain and gears were clean and lubricated).

But for some reason (yes, that's right, I do not recall), about fifteen years ago, I decided to buy a new bicycle, and chose a Jamis Coda. I hung the black bike (I don't know what else to call it. It used to be a Raleigh Competition, but that was long before I took possession) in the back of my shed, and very much enjoyed the Coda. It was new, it was shiny, and it was a hybrid, better suited for city riding. After a while it wasn't new or shiny anymore - just my style, but it was still a great ride. I didn't think very often about my black bike, even when I noticed it hanging behind garden gear and abandoned kids' toys.

Well, a couple months ago, I got careless with my Coda and it was stolen. Arrrrggggh.

I figured I would buy a new bicycle, which I dreaded in part because everything has gotten so over-built, optimized for convenience, unnecessarily fancy, etc. Not my style. Started looking around and found myself at Bucephalus Bikes, where they do a brisk, proud business in reconditioning older bicycles to better-than-new.

That got me thinking: maybe I should bring in the Reynolds 531 frame (by this time, I was thinking that was all that was left of the bike) and see what it might cost to make it a beauty, once again.

I hauled it out of the shed and was delighted to see it was intact as a bicycle, but with understandably flat tires, rusty chain, and who knows what else wrong with it. I did, after all, choose to stop riding it long ago.

Feeling slightly guilty at not returning to Bucephalus, I brought the black bike to my neighborhood bike shop, Roberts Cycle. They made appreciative noises. They probably do that with all their customers. :-)

Well, in any case, a few weeks went by and yesterday I rode the bike home - smoooothly. Here are the parts Roberts needed to properly restore the bike:
  • Chain
  • Front and rear brake cables and housing
  • Front and rear shift cables
  • Seat binder bolt
  • Schwalbe 27-1/4 K-guard (Kevlar) tires
  • Sealed bottom bracket
  • Headset bearings (29, loose, had to be individually packed in)
  • Rear wheel bearings (22, loose, had to be individually packed in)
  • Front alloy wheel
  • Used rear derailleur
Yes, you read that right. They removed the Shimano 600 and gave me a "good enough" replacement for a very fair price. He never asked if I wanted to replace it. WTF? The guy who runs the shop explained that the 600 was actually not compatible with the rear gear assembly on the bicycle; I couldn't get to all the gears using it. So he swapped it out.

Gee, I'd never really noticed any problem. I rarely needed the very low settings. I started to feel aggrieved and righteous about the whole thing, but he so sincerely seemed to consider it an act of necessity. So in the end I accepted with grace (or so I told myself) the Shimano 600 (and every other part, including the old ball bearings - !!) in a plastic bag.

I tried not to be too sad. It really is pretty.

No, I won't tell you how much all those parts (and the accompanying, thoroughgoing overhaul) cost me. I suppose I could've gotten a new bicycle for the amount, but nothing as elegant or satisfying as my black bike. 

And as I said in an earlier post, "New is BadBuying things new is the way to consume the most resources and have the worst impact on our world. So I am going to make every effort to avoid buying new things and instead by used."

Hurray! I acted on my principles. I have lots of principles and I never feel like I act enough in accordance with them. I also am not sure how many I've forgotten.

So this act of reuse and redemption felt - and feels - really good. 

Now if only it would stop raining so I could go out for a ride!

Categories: Development

An Index or Disaster, You Choose (It’s The End Of The World As We Know It)

Richard Foote - Thu, 2015-10-29 23:42
This come up recently with a customer and I saw a related question on OTN that made me realize I haven’t previously discussed the SKIP_UNUSABLE_INDEXES parameter. Let me start by making a very simple point. All it potentially takes to make your entire database effectively inoperable or indeed to maybe bring down a RAC instance is […]
Categories: DBA Blogs

Oracle Priority Support Infogram for 29-OCT-2015

Oracle Infogram - Thu, 2015-10-29 11:25

Security



Performance

Just found an Oracle corporate blog about performance. Here’s a typical posting from them: Real-Time Enterprise: SPARC T7-1 Faster Than x86 E5 v3, from BestPerf.

MySQL



MySQL Enterprise Monitor 3.1 GA has been released, from the MySQL Enterprise Tools Blog.

SuperCluster

New whitepaper: Database Consolidation on Oracle SuperCluster, from Exadata Partner Community – EMEA.

PL/SQL

Transforming one table to another: SQL or PL/SQL?, from Oracle Database PL/SQL and EBR.

From the same source: Why use PL/SQL?

Java

JVM hang with CMS collector, from Poonam Bajaj’s blog.

A New Way to Program in Java EE 8, from The Java Source.

WebLogic

Dynamic Debug Patches in WebLogic Server 12.2.1, from The WebLogic Server Blog.

SOA

SOA Suite 12.2.1 is available for download, from the SOA & BPM Partner Community Blog.

BI

Predictive and Advanced Analytics with Oracle BI, from BI & EPM Partner Community EMEA.

Oracle Coherence


OVM

Oracle VM Server for SPARC 3.3 Released!, from Oracle's Virtualization Blog.


EBS

From the Oracle E-Business Suite Support blog:



From the Oracle E-Business Suite Technology blog:





Limitations

Floyd Teter - Wed, 2015-10-28 09:34
I've noticed a trend lately.  In working with various organizations in the early stages of evaluating SaaS, I'm hearing vigorous defense of limitations. "We can't go to the cloud because our business is so unique."  "We can't consider cloud because our data is too complex to migrate." "We can't entrust our data to a 3rd party."  While there are plenty of additional reasons, I'm sure you've noticed the two important words forming the trend:  "We can't".

One of my favorite authors is Richard Bach.  Yeah, the guy who wrote "Johnathan Livingston Seagull", "Illusions", and "Travels with Puff".  More evidence that I'm an old hippie at heart.  Bach deals with the metaphysical and the spiritual.  It can be some rather deep and mind-bending stuff.  But he also throws out some pearls that stick with the reader.  One of his pearls that stuck with me: "Argue for your limitations and, sure enough, they're yours."  Meaning that those who vigorously defend their limitations rarely move forward in significant ways.  It's the opposing force to innovation, disruption and improvement.

If you're part of an organization considering a move to SaaS, the strategic factors to weigh involve elements like building value through improving the balance sheet and/or lowering operational costs; increasing product or service share and/or quality; increasing agility through reductions in business process or development cycle times.  In other words, "better faster cheaper".  If SaaS delivers for you in those areas, then the limitations simply become challenge to be dealt with on the road to achieving the value offered by SaaS.

"Argue for your limitation and, sure enough, they're yours."  When you begin to hear those two words, "We can't", that's exactly what you're doing.  Don't do it.  Step back and change your perspective.

EM12c Agent generating heapDump_*.hprof

Arun Bavera - Tue, 2015-10-27 14:43
By default Em12c Agent has enabled -XX:+HeapDumpOnOutOfMemoryError. We need to change the log directory to other directory or disable the Dump before it fills up Agent partition.
  --  -XX:-HeapDumpOnOutOfMemoryError   or  -XX:HeapDumpPath=/home/oracle/logs/
 
You can put these entries in AGENT_INST/sysman/config/s_jvm_options.opt
Agent has an auto tuning feature which increases Xmx automatically whenever it goes out of memory and dumps the memory.
We  disabled the Dump by putting this entry   -XX:-HeapDumpOnOutOfMemoryError     in file  AGENT_INST/sysman/config/s_jvm_options.opt and did emctl stop agent;emctl start agent.
 
Also, if you think if the agent memory auto tuning is not working you can disable that .. if you see a frequent restart or failing to start due to outOfMemory errors.
You can change  in emd.properties
#Enable auto tuning out of the box
enableAutoTuning=true false

Change the default Xmx
# These are the optional Java flags for the agent
#
agentJavaDefines=-Xmx512M -XX:MaxPermSize=96M
Note: This is not recommended by Oracle Dev team.








Categories: Development

Pages

Subscribe to Oracle FAQ aggregator