OraNA :: Oracle News Aggregator

I have written a number of entries on this blog about updating database statistics on tables during Application Engine processes.

• Statistics Management for PeopleSoft Temporary Records in Application Engine Programs, discussed management of statistics on GTTs, and the use of Optimizer Dynamic sampling.
• Statistics Management for Partitioned Objects in PeopleSoft discussed an enhancement to the wrapper so that it only gathered stale statistics on partitioned objects.
• %UpdateStats() -v- Optimizer Dynamic Sampling noted that I had found some situations where Oracle's Optimizer Dynamic Sampling, even at the highest level, does not produce the best execution plan and there is no alternative but to collect statistics on temporary tables.
  

I proposed a PL/SQL package (wrapper.sql) called from the DDL model to intercept the call from the %UpdateStats macro in Application Engine. By default it

• Gathers statistics on regular tables.
• Refreshed only stale statistics on partitioned tables.
• Does not gather statistics on Global Temporary Records.

I have now published an enhanced version: wrappermeta848.sql.

• A table PS_GFC_STATS_OVRD holds meta-data to override the default behaviour of the script for certain records. The meta-data can also specify the size of the sample, and the options to control the collection of histograms.
• If a private instance of a Temporary Records is a Global Temporary Tables, the wrapper may still collect statistics (normally this would be suppressed because of the risk of one session using the statistics collected by another session, but this will not happen for these tables).

Now, it is possible to specify the few tables where statistics must still be explicitly gathered, or whether to do this only if the current statistics on the table are stale. The DBA is probably the person best placed to decide whether and how to collect statistics on which tables, and these decisions can be implemented with the meta-data, but without code change.

Thus, it is possible to

• Reduce the number of calls to dbms_stats,
• to reduce the overhead of the remaining calls
• and at least preserve, if not improve performance of batch processes without making any code changes.

Oracle today launched its much awaited Fusion Middleware 11g  

Main component in Oracle Fusion Middleware 11g

1) Oracle WebLogic Server (J2EE Application Server)
2) Oracle WebCenter (Composer, WC Framework, WC Services, WC Spaces)
3) Oracle SOA Suite (Adapters, Service Bus, Business Rules, BAM, B2B, BPEL)
4) Oracle Identity Management (OID, DIP, Federation, OVD, Role Manager, OAAM, Entitlements, Directory Services Manager)
5) Oracle Business Intelligence (Discoverer, Portal, Reports, Forms) 
6) Oracle Application Component(Dev Tools, HTTP Server, Webcache, Web Services, DAS)
7)  Oracle Metadata Repository  

_____________ 

You can download Oracle Fusion Middleware 11g  from here

If you are confused about which component to download, check Oracle Fusion Middleware 11g download FAQ 

Share This

Years ago, when I was a much younger man…wearing bell-bottom jeans so large I had to step twice before the pant leg moved at all…people would great each other with the phrase “What’s happening?” Seems like a good time to resurrect that greeting and give ya’all some answers:

• For all the middleware types out there, the Oracle Fusion Middleware 11g Launch event is July 1^st at 10 am EST (US). If you’re not attending the event live in Washington D.C., you can catch the here.

• The Oracle Mix “Vote-A-Session” for this year’s Oracle OpenWorld wraps up voting on July 3^rd. So get yourself over to the Mix voting site and cast votes for the sessions you’d most like to see (Oracle Mix login required).

So it looks like we’ll all be busy for the next few day. What's happening? Tons o' stuff.

To begin with, I would like to apologize for the long duration where I could not send posts, I got a lot of emails from people asking about the reason, to share with you it was due to a change which was keeping me occupied. In my blog,I have talked about so many trends and changes that organization faces in today's environment.
Today I would be talking about a change of different kind, I am making a change in my career from HR Technology to HR consulting. While this change was something i had been planning (also looking forward to) for sometime now, it has happened now. This would be great news for the patrons of the blog as you can expect a lot more new and interesting insights into HR issues,ofcourse not to miss on how technology continues to influence HR as a function.

Would look forward to your continued patronage and ofcourse the silence is now broken, so you would see some posts in the coming days.
Stay tuned !!!
There is a lot changing in the blog apart from the content....

I have been working on a system where many Application Engine programs are running throughout the day, and are frequently collecting Optimizer statistics with the %UpdateStats macro on many working storage tables. Concurrent calls to dbms_stats are typical.

There are two new behaviours in Oracle 10g RDBMS that can in extreme cases, in combination with a system that calls dbms_stats very frequently, create a significant performance overhead.

From Oracle 10g, histograms may, by default, be collected automatically. That means that rows are concurrently deleted from and inserted into histgrm$ and hist_head$, leading to contention and consistent read.

• Also from Oracle 10g, every time you collect statistics on a table the old statistics are retained in the SYS.WRI$_OPTSTAT%HISTORY tables. If histograms have previously been collected, these are also copied. DBMS_STATS has the additional overhead of writing this history. I found in excess of 10,000 versions of previous statistics for some tables, because the batch processes have updated statistics on working storage tables that many times.
  

• dbms_stats also appears to be responsible for purging history older than the retention limit. The default retention period is 31 days. I have seen concurrent calls to dbms_stats blocked on row level locks on the statistics history tables. For me, this occurred 31 days after the system went live on a significantly increased volume.

Statistics history was designed to work in conjunction with schema wide statistics jobs that only refreshed stale statistics. There is an option on gather_schema_stats to collect only statistics on tables where the current statistics are stale. However, there is no such option on gather_table_stats. If you have decided to call this procedure for a particular table, then it is assumed you know you need to refresh the statistics. However, by calling dbms_stats from a batch program you can end up calling it much more frequently than is really necessary.

Recommendations

• Disable statistics history by setting the retention period to zero. Unfortunately this can only be set at database level. The statistics history is there in case you want to revert to a previous version of the statistics should a new set of statistics produce a problem, but it is only used rarely, and I think this is a necessary sacrafice.
  

• Use Oracle Optimizer Dynamic Sampling. However, I suggest increasing the level from the default of 2 to 4 to increase the situations in which it is used.
  

• Introduce the new version of the PL/SQL wrapper package for dbms_stats so that you can specify the records for which statistics will be explicitly collected, and whether histograms are to be collect. Thus you can reduce the number of calls to dbms_stats.

Just to update everyone those who ere waiting for IDM 10.1.4.3 patchset results. We have successfully implemented this is our test environment and the issue which were reported during 10.1.4.0.1 installation with Kerberose related to "Administer Partner Application" is resolved in 10.1.4.3 patch. Moreover one more issue we found in older version, there were huge error files generation in $ORACLE_HOME/hostname_domain.com_IDM10143/sysman/recv/errors which we didn't found in this patch. I guess those who were waiting can install this one.

Happy Troubleshooting !!!

Today we learnt how to read raw trace files and manually calculate what HotSoS profiler does programmatically.

Metalink Note 39817.1 explains the same. In Oracle 10g we have a new package DBMS_MONITOR through which trace should be done.

We learnt to analyze trace files and did various exercises with profiler reports.

Ric said, he has stopped working with tkprof ever since he got introduced to HotSos profiler and that if he ever were to go independent, he would sell his soul for keeping the HotSos profiler in his bag of tools.

HotSoS profiler is the best. I have compared the outputs of other profilers like Oracle's trace analyzer and OraSRP. However none of the alternatives provides the depth of HotSoS profiler.

Today we learnt how to collect trace data with proper instrumentation, Hotsos free package ILO for implementing instrumentation in PL/SQL code was shown. We learnt how to read trace files.

One of the students, Rob had brought a production trace file. Our instructor, Ric, generated the profiler report for that trace file. There were 3 statements:

1. UPDATE 30% time
2. INSERT 25% time
3. UPDATE 27% time

Ric said that the first statement looked optimized as it was doing 1 LIO per execution which is very good. However, it was doing this thousands of time. In HotSos World, the best way to optimize something is not to do it. Rob explained that they were updating all the rows with very same data everytime. Ric told Rob to explore MERGE command which will combine INSERT and UPDATE and avoid the unnecessary UPDATE of very same data again.

Eventhough Oracle forced everyone to move to Cost Based Optimizer (CBO), their own tools continue to use RBO. RMAN is a classic example. Refer to Metalink Note 375386.1 which describes performance problems selecting from V$RMAN_STATUS. The cause is given as bug 5247609 and workaround provided is:

Add the following to your rman script:

sql "alter session set optimizer_mode=RULE";

A few customers have found that using RULE does not workaround this but gathering fixed statistics does:

SQL> exec dbms_stats.gather_fixed_objects_stats()

I helped some folks the other day with an issue that had the potential to be very serious for them; exposure of production data without someone needing to login to the production system. Not good.

The Problem

For testing purposes they have copies of their production PeopleSoft databases; one for Financials and one for HCM. These copies are refreshed regularly and there are scripts run against them to handle some cleaning/sanitizing of the production data, so that the database can be used for testing.

They have some people that they allow to login to these test environments with any account so that they can confirm the results of basic processes. The accounts don't have default passwords, so this access was limited to the people that they authorized to do testing, but testers with access to these cloned environments could login as anyone.

One of the testers (we'll call him Fred) was logged in to the Financials test environment as some other user (we'll call her Sally) to test some process that would normally be initiated by Sally in the production system.

While doing this testing, Fred wanted to enter his timesheet data so he clicked on a link to go to the production HCM system for time entry. Much to his surprise, he was logged in to HCM as Sally! So Fred goes back to the test Financials environment, logs in as a different user, clicks the link into production HCM and sure enough he is logged in as that user in production HCM. To Fred's credit, Fred alerted the PeopleSoft support team there instead of snooping around in HCM.

How did that happen?

The problem comes from the fact that the production environments were configured to trust each other for PeopleSoft Single Signon, but the node names and node passwords were not changed as part of the environment cloning logic.

So when Fred signed on to the cloned Financials environment, the PS_TOKEN cookies generated are identical to what the production environment would generate (the details of PS_TOKEN cookie are documented in PeopleBooks, but the node name and node password are the important pieces here).

How could this be prevented?

There are several different ways of preventing this from happening. Let's take the pragmatic ones first.

1) Change the node password as part of the refresh script.

This is the easiest, most expedient thing to do. It is the absolute minimum way to solve this problem.

Although this solves the security problem, it introduces a headache for security auditors because the app server logs in the production environment will be full of warnings about failed logins from bad node passwords. That means that testers accessing production while logged in to the test environment will be indistinguishable from someone trying to break in by generating their own PS_TOKEN cookies.

It's not nice to annoy your security auditors by purposely creating log entries that look like break-in attempts, but are actually OK :-)

2) Change the node name.

Changing the node name when you clone environments will also solve the problem (because then the production environments will no longer trust the cloned nodes since they don't recognize the new names). This also solves the log problem mentioned in the previous item.

Changing the node name can have an impact on Integration Broker testing, but that just means that your integration test scripts need to be aware of this. Not a huge drawback in my opinion.

3) Use distinct passwords for each account in the test environment.

This is easy enough to do, it's mainly a question of distributing the passwords to the testers so that they can get in and do their jobs. Depending on your organization, this may or may not be easy.

4) Don't provide passwords to the testers, but allow them to reset them on demand.

Similar to the previous item, but gets around the distribution issue by making it an after the fact auditing issue. If Fred requests the CEO's password in the test system, it's very easy to audit for that and force Fred to explain why he felt that was necessary.

Computer security is typically focused on preventing people from doing things, but in some cases this model of auditing and disciplining after the fact may be OK.

This model reminds me of when we took our dog to a dog trainer a long time ago that recommended leaving a steak or something else on the kitchen counter for the dog to find. This was in conjunction with hiding around the corner so that at the moment the dog put his paws up to grab the steak, you would jump out and impress upon the dog that it wouldn't be wise to do that again. A real-time security audit if you will :-)

What are some of the less pragmatic options?

5) Have a new version of PeopleTools that can detect when a database is copied, and automatically scrub key information like node passwords, GUID, etc.

The implementation of this would be platform specific, but do-able. It would complicate things like having a hot database backup server though. I'm sure those sorts of issues could be addressed though.

6) Embed additional information beyond just node name and node password (like database name) in the PS_TOKEN cookie.

One issue that I can think of with this is that older PeopleTools versions would not be able to interoperate with this, but it could be a flag on the node definition as to whether interoperability with earlier versions of PeopleTools is required. If interoperability was required, then the existing PS_TOKEN format would be used, otherwise the newer format would be used.

Why didn't they know about this?

PeopleSoft Single Signon is documented in PeopleBooks, but there aren't any great writeups out there on the implications of it when cloning a PeopleSoft environment.

There are lots of writeups out there on "How to Clone a PeopleSoft Environment", and some discuss the importance of getting the node configuration correct, but they typically come at from the perspective of just making sure the environment works; not the security implications of it.

PeopleSoft Single Signon is well documented in PeopleBooks, but it just doesn't seem to jump out at people when thinking about cloning. I will give a shout-out here to Brent Martin of ERP Associates. Brent's writeup on cloning a PeopleSoft database is the only one out there that I could find that mentions this issue at all.

On a recent project there was a requirement to search a directory (and all sub-directories) for any files (all files were images) changed in the last 7 days and load them into a database table. The code was being written in Groovy and being lazy I just used the *nix find command and processed the command output. I wanted to avoid writing the code to recurse the directories thinking it would be easier to write, read and maintain if I just processed the find command output.

I took a look today and found out how easy Groovy makes this, so when I have a chance I'll rewrite using the code below to make it pure Groovy and portable between *nix and Windows.

File rootDir = new File("d:/temp")            // arg[0] == d:/temp
long checkTime = new Date().minus(7).time    // arg[1] == 7
rootDir.eachFileRecurse {
    if (it.isFile() && it.lastModified() >= checkTime) {
        println "${it} [${new Date(it.lastModified())}]"
        // call the method to load this image file...
    }
}

I am attending the world famous 3 day training on Oracle Performance from Hotsos. Our instructor is Ric Van Dyke. We have 6 students in the class. Ric mentioned that he was our instructor and not a teacher as an instructor presents the information, but a teacher makes sure that the student learns whatever is taught. 3 days is a short period to teach.

Each autumn the customers, partners, vendors, and others that make up the Oracle ecosystem descend on downtown San Francisco.  Oracle’s insatiable appetite for acquisitions has made the OpenWorld conference almost unmanageably large.  Last October over 40,000 people came making it the largest event held within the city.  Roughly 2,000 of them shared an interest in [...]

Oracle Application Management Pack (AMP) for Oracle E-Business Suite : is management pack which sits on top of Oracle Enterprise Manager (OEM) Grid Control to manage (Monitor/System alerting, Cloning, Data Scrambling …) Oracle Applications/E-Business Suite (11i/R12).

This post covers installation of Oracle Application Management Pack (AMP) on existing Oracle Enterprise Manager Grid Control Component (Oracle Management Service & Oracle Management Agent) . To know more about OEM Grid Control components click here

OMS- Oracle Management Service component of OEM Grid Control communicates with Agents and upload data to repository. OMS is also responsible to display data from repository to console (end user’s browser)

OMA- Oracle Management Agent sits on node (machine) which you wish to monitor using OEM Grid Control, OMA uploads managed data to OEM repository (database) via OMS.
.

Points good to know for AMP 3.0 (Application Management Pack) Installation

1.Latest version of Application Management Pack (AMP) is 3.0 and available via Patch 8333939 on Oracle My Support(pka Metalink)

2. Oracle Enterprise Manager (OEM) Grid Control should already be running and minimum version of OEM Grid control should be 10.2.0.3 (latest OEM grid control version as of 21 June is 10.2.0.5)

3. If you don’t have OEM grid control, first install OEM Grid control 10.2.0.1 and then upgrade it to 10.2.0.3, 10.2.0.4 or 10.2.0.5

4. There are two parts in Application Management Pack 3.0
i) Grid Control Plug-in for Oracle E-Business Suite Management Pack
ii) Management Agent Plug-in for Oracle E-Business Suite Management Pack

Install “Grid Control Plug-in for Oracle E-Business Suite Management Pack” in OMS (Oracle Management Service) ORACLE_HOME of OEM Grid Control.

Install “Management Agent Plug-in for Oracle E-Business Suite Management Pack” in Management Agent (OMA) ORACLE_HOME of OEM Grid Control.

To understand this better assume below setup
a. Oracle E-Business Suite Database and Concurrent Manager is running on node1
b. Oracle E-Business Suite Form Server is running on node2
c. Oracle E-Business Suite Web Server is running on node3
d. OEM Grid Control 10.2.0.3 or higher(Management Repository, Management Service and Management Agent) is running on node4 then to monitor Oracle E-Business Suite using OEM Grid control follow this process

i) Install OEM Grid Management Agent on Node1, Node2, Node3
ii) Install “Grid Control Plug-in for Oracle E-Business Suite Management Pack” part of AMP 3.0 in OMS_HOME on node4
iii) Install “Management Agent Plug-in for Oracle E-Business Suite Management Pack” part of AMP 3.0 in AGENT_HOME on node1, node2 and node3

5. After installation of AMP 3.0, Discover Oracle E-Business Systems in OEM Grid Control. There are two ways to discover Oracle E-Business Suite System in OEM Grid.
i) Using Discovery Wizard in OEM Grid Console : Target -> Oracle Applications -> Discovery Wizard (in Related Links section)

ii) Command line using ebsdiscovery.sh on OMS HOME of Grid Control : $OMS_HOME/ sysman/ admin/ scripts/ ebs/ cli/ amp/ disc/ ebsdiscovery.sh

6.“Oracle Applications” subtab in OEM Grid Control console will be visible only to SYSMAN user. To enable this link to other users, click on Preferences -> Target Subtabs : move “Oracle Applications” from “Available Target” to “Selected Target”

.

Install Application (11i/R12) Management Pack 3.0 with OEM Grid Control

Prereq. apply Interoperability patches according to yourE-Business Suite version. For complete list check Metalink Note 812315.1 [thanks to Ashish for pointing this out]

1. Setup Provisioning Framework Software Library  (Directory on Management Service Server) Login to OEM Grid Control Console -> Deployments -> Provisioning -> Software Library Configuration

To know more about Software Library click here

2. Install Grid Control Plug-in for Oracle E-Business Suite Management Pack (repeat this in all OMS_HOME of OEM Grid)
2.1 Set ORACLE_HOME to OMS_HOME

2.2 Upload patch 8333939 to OMS Server and unzip patch

2.3 Start Installation from $UNZIP_Location/Disk1/runInstaller

2.4 Select “Grid Control Plug-in for Oracle E-Business Suite Management Pack” from Product List

2.5 In Destination Home select existing OMS home (Install Grid Control Plug-in in existing OMS HOME)
.

3. Install Management Agent Plug-in for Oracle E-Business Suite Management Pack (repeat this in all AGENT_HOME)

3.1 Set ORACLE_HOME to AGENT_HOME

3.2 Upload patch 8333939 to Server where agent is running and unzip patch

3.3 Start Installation from $UNZIP_Location/Disk1/runInstaller

3.4 Select “Management Agent Plug-in for Oracle E-Business Suite Management Pack” from Product List

3.5 In Destination Home select existing AGENT home (Install Plug-in in existing AGENT HOME)

.

Related/References

• 753584.1  Enterprise Manager Grid Control with Application Management Pack for Oracle E-Business Suite Installation Guide
• 812315.1  Oracle Application Management Pack for Oracle E-Business Suite, Release 3.0
• Oracle Application Management Pack for Oracle E-Business Suite Installation Guide
• Oracle Application Management Pack for Oracle E-Business Suite User’s Guide
• Steven Chan’s blog

Share This