Feed aggregator

PeopleSoft and Docker's value proposition

Javier Delgado - Sun, 2014-11-16 12:58
If you haven't heard yet about Docker and/or container technologies, you will soon do. Docker has made one of the biggest impacts in the IT industry in 2014. Since the release of its 1.0 version on past June, it has captured the attention of many big IT vendors, including Google, Microsoft and Amazon. As far as I'm aware, Oracle has not announced any initiative with Docker, except for the Oracle Linux container. Still, Docker can be used with PeopleSoft, and it can actually simplify your PeopleSoft system administration. Let's see how.

What is Container Technology?
Docker is an open platform to build, ship, and run distributed applications. Docker enables apps to be quickly assembled from components and eliminates the friction between development, QA, and production environments. As a result, IT can ship faster and run the same app, unchanged, on laptops, data center VMs, and any cloud.

In a way, it is similar to virtualization technologies like VMWare or Virtualbox where you can get an image of a machine and run it anywhere you have the player installed. Docker is similar except that it just virtualizes the application and its dependencies, not the full machine.

Docker virtual machines are called containers. They run as an isolated process in userspace on the host operating system, sharing the kernel with other containers. Thus, it enjoys the resource isolation and allocation benefits of VMs but is much more portable and efficient.

Docker uses a layered file system for its containers, in a way that they can be updated by just including the changes since the last update. This greatly reduces the volume of information that needs to be shipped to deliver an update.

How can it be used with PeopleSoft?
As we have seen, Docker containers are much easier to deploy than an entire virtual machine. This means that activities such as installations can be greatly simplified. All you need is to have Docker installed and then download the PeopleSoft container. Of course, this requires that you first do an installation within a Docker container, but this is not more complex than doing an usual installation, it just requires some Docker knowledge in order to take advantage of all its features. Under my point of view, if you are doing a new installation, you should seriously consider Docker. At BNB we have prepared containers with the latest PeopleSoft HCM and FSCM installations so we can quickly deploy them to our customers.

Also, when you make a change to a Docker container, just the incremental changes are applied to existing running instances. This poses a great advantage when you apply a patch or run a PeopleTools upgrade. If you want to apply the patches to a new environments, you just need to make sure that you apply the latest container changes in all the servers running the environment.

Isolation between running instances is also a major advantage when you have multiple environments in the same server. Suppose you want to apply the later Tuxedo patch just in the Development environment, which coexists with other environments on the same server. Unless you had one Tuxedo installation for each environment (which is possible but normally unlikely), you would need to go ahead and hope the patch did not break anything (to be honest, this happens very rarely with Tuxedo, but some other product patches are not so reliable). If you have a separate container for the Development environment you can apply the patch just to it and later deploy the changes to the rest of environments.

Last but not least, the reduced size of Docker containers compared to an entire virtual machine greatly simplifies the distribution to and from the cloud. Docker is of great help if you want to move your on premise infrastructure to the cloud (or the other way around). This is even applicable when you want to keep a contingency system in the cloud, as delivering the incremental container changes made to your on premise system requires less time than using other methods.

Not only that, Docker can be hosted in most operating systems. This means that moving a container from one public cloud facility to another is significantly easier than it was with previous technologies. Exporting a virtual machine from Amazon EC2 to Google Cloud was quite complex (and under some circumstances even not possible).


Limitations
But as any other technology, Docker is no panacea. It has some limitations that may restrict its adoption for your PeopleSoft installation. The main ones I can think of are:

  • Currently there is no support for containers using Windows as a guest operating system. This is not surprising, as Docker in intimately linked to Unix/Linux capabilities. Still, Microsoft has announced a partnership with Docker that will hopefully help to overcome this limitation. For the moment, you will not be able to use Docker for certain PeopleSoft components, such as the PSNT Process Scheduler, which is bad news if you are still using Crystal Reports or Winword reports. Also, if you are using Microsoft SQL Server as your database, this may be a major limitation.


  • Docker is most useful when used for applications, but not data. Logs, traces and databases should normally be kept out of the Docker container.


Conclusions
Although container technology is still in its initial steps, significant benefits are evident for maintaining and deploying applications, PeopleSoft included. Surely enough, the innovation coming on this area will have a big impact in the way PeopleSoft systems are administered.

PS: I would like to thank Nicolás Zocco for his invaluable research on this topic, particularly in installing the proof of concept using PeopleSoft and Docker.

Interstellar Madness

FeuerThoughts - Sun, 2014-11-16 09:19
Saw Interstellar last night. Only had to wait through TWENTY MINUTES of trailers. Had to put fingers in my ears for much of it. So loud, so invasive, so manipulative. Anyway....

I don't watch TV anymore, rarely watch a movie or read a novel. So when I do subject myself to high-resolution artificial input to my brain, it is a jarring experience.

And enjoyable. I haven't stopped watching TV because I don't like it. I have stopped watching TV because I can't help but "like" it, be drawn to it. I am a product of millions of years of evolution, and both Madison Ave (marketeers) and Hollywood know it, and take advantage of it.

Anyway....

I enjoyed watching Interstellar, with its time-traveling plot ridiculousnesses and plenty of engaging human drama. 

But one line really ticked me off. The movie is, to a large extent, a propaganda campaign to get Americans excited about being "explorers and pioneers" again. 

Cooper (McConaughey) complains that "Now we're a generation of caretakers." and asserts that:

"Mankind was born on earth. It was never meant to die here."

That is the worst sort of human species-ism. It is a statement of incredible arrogance. And it is an encouragement to humans to continue to despoil this planet, because don't worry! 

Science and technology can and will save us! Right? 'Cause it sure has done the trick so far. We are feeding more people, clothing more people, putting more people in cars and inside homes with air conditioners, getting iPhones in the hands of more and more humans. 

Go, science, go!

And if we can't figure out how to grow food for 10 billion and then 20 billion people, if we totally exhaust this planet trying to keep every human alive and healthy into old age, not to worry! There are lots of other planets out there and, statistically, lots and lots of them should be able to support human life. Just have to find them and, oh, right, get there.

But there's no way to get there without a drastic acceleration of consumption of resources of our own planet. Traveling to space is, shall we say, resource-intensive.

Where and how did we (the self-aware sliver of human organisms) go so wrong? 

I think it goes back to the development of recorded knowledge (writing, essentially or, more broadly, culture). As long as humans were constrained by the ability to transmit information only orally, the damage we could do was relatively limited, though still quite destructive.

Once, however, we could write down what we knew, then we could build upon that knowledge, generation after generation, never losing anything but a sense of responsibility about how best to use that knowledge.

That sense of responsibility might also be termed "wisdom", and unfortunately wisdom is something that humans acquire through experience in the world, not by reading a book or a webpage. 

Mankind was born on earth and there is no reason at all to think that we - the entire species - shouldn't live and die right here on earth. Especially if we recognize that the price to be paid for leaving earth is the destruction of large swaths of earth and our co-inhabitants and....

Being the moral creatures that we like to think we are, we decide that this price is unacceptable.


Categories: Development

Card Flip Effect with Oracle Alta UI

Shay Shmeltzer - Fri, 2014-11-14 17:00

The Oracle Alta UI focuses on reducing clatter in the user interface. So one of the first thing you'll try and do when creating an Alta UI is decide which information is not that important and can be removed from the page.

But what happens if you still have semi-important information that the user would like to see, but you don't want it to overcrowd the initial page UI? You can put it on the other side of the page - or in the Alta UI approach - create a flip card.

Think of a flip card as an area that switches the shown content to reveal more information - and with ADF's support for animation you can make a flip effect.

In the demo below I show you how to create this flip card effect using the new af:deck and af:transition components in ADF Faces. 

A few other minor things you can see here:

  • Use conditional ELs and viewScope variables - specifically the code I use is 
#{viewScope.box eq 'box2' ? 'box2' : 'box1'} 
  • Add additional field to a collection after you initially drag and dropped it onto a page - using the binding tab
  • Setting up partialSubmit and PartialTriggers for updates to the page without full refresh 

Categories: Development

Did You Know that You Can Buy OUM?

Jan Kettenis - Thu, 2014-11-13 09:18
The Oracle Unified Method (OUM) Customer Program has been changed in that, next to the already existing option to get it by involving Oracle Consulting, you now also can buy it if (for some reason) you don't want to involve Consulting.

Next to that there also is the option to purchase a subscription (initial for 3 years, after which it can be renewed annually) allowing to download updates for OUM.

OUM aims at supporting the entire Enterprise IT lifecycle, including the Cloud.

Making Copies of Copies with Oracle RMAN

Don Seiler - Wed, 2014-11-12 10:13
I recently had need to make a copy of an image copy in Oracle rman. Since it wasn't immediately obvious to me, I thought it was worth sharing once I had it sorted out. I was familiar with making a backup of a backup, but had never thought about making a copy of a copy.

First you need to create an image copy of your database or tablespace. For the sake of example, I'll make a copy of the FOO tablespace. The key is to assign a tag to it that you can use for later reference. I'll use the tag "DTSCOPYTEST":

backup as copy 
    tablespace foo 
    tag 'DTSCOPYTEST'
    format '+DG1';

So I have my image copy in the DG1 tablespace. Now say we want to make copy of that for some testing purpose and put it in a different diskgroup. For that, we need the "BACKUP AS COPY COPY" command, and we'll want to specify the copy we just took by using the tag that was used:

backup as copy
    copy of tablespace foo
    from tag 'DTSCOPYTEST'
    tag 'DTSCOPYTEST2'
    format '+DG2';

As you'd guess, RMAN makes a copy of the first copy, writing it to the specified format location.

As always, hope this helps!
Categories: DBA Blogs

Mystery of java.sql.SQLRecoverableException: IO Error: Socket read timed out during adop/adpatch

Vikram Das - Tue, 2014-11-11 21:19
While applying the R12.2 upgrade driver, we faced the issue of WFXLoad.class failing in adworker log but showing up as running on adctrl

        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     2  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     3  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     4  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     5  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     6  Run       AutoPatch R120 pl                              Wait        
     7  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     8  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     9  Run       AutoPatch R120 pl  WFXLoad.class               Running      
    10  Run       AutoPatch R120 pl                              Wait        

adworker log shows:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Socket read timed out
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:482)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:678)
        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:238)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at oracle.apps.ad.worker.AdJavaWorker.getAppsConnection(AdJavaWorker.java:1041)
        at oracle.apps.ad.worker.AdJavaWorker.main(AdJavaWorker.java:276)
Caused by: oracle.net.ns.NetException: Socket read timed out
        at oracle.net.ns.Packet.receive(Packet.java:341)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:308)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1222)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:330)
        ... 8 more

This was happening again and again. The DBAs were suspecting network issue, cluster issue, server issue and all the usual suspects.  In Database alert log we saw these errors coming every few seconds:

Fatal NI connect error 12537, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 11-NOV-2014 19:58:19
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (26388) as a result of ORA-609


We tried changing the parameters in sqlnet.ora and listener.ora as instructed in the article:
Troubleshooting Guide for ORA-12537 / TNS-12537 TNS:Connection Closed (Doc ID 555609.1)

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

However, the errors continued.  To rule out any issues in network, I also restarted the network service on Linux:

service network restart

One thing which I noticed was the extra amount of time that the connect was taking 4 seconds:

21:17:38 SQL> conn apps/apps
Connected.
21:17:42 SQL> 

Checked from remote app tier and it was same 4 seconds.

Stopped listener and checked on DB server that uses bequeath protocol:

21:18:47 SQL> conn / as sysdba
Connected.
21:18:51 SQL> conn / as sysdba
Connected.

Again it took 4 seconds.

A few days back, I had seen that connect time had increased after turning setting the DB initialization parameter pre_page_sga to true in a different instance.  On a hunch, I checked this and indeed pre_page_sga was set to true.  I set this back to false:

alter system set pre_page_sga=false scope=spfile;
shutdown immediate;
exit
sqlplus /nolog
conn / as sysdba
startup
SQL> set time on
22:09:46 SQL> conn / as sysdba
Connected.
22:09:49 SQL>

The connections were happening instantly.  So I went ahead and resumed the patch after setting:

update fnd_install_processes 
set control_code='W', status='W';

commit;

I restarted the patch and all the workers completed successfully.  And the patch was running significantly faster.  So I did a search on support.oracle.com to substantiate my solution with official Oracle documentation.  I found the following articles:

Slow Connection or ORA-12170 During Connect when PRE_PAGE_SGA init.ora Parameter is Set (Doc ID 289585.1)
Health Check Alert: Consider setting PRE_PAGE_SGA to FALSE (Doc ID 957525.1)

The first article (289585.1) says:
PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off. The advantage that PRE_PAGE_SGA can afford depends on page size.

The second article (957525.1) says:
Having the PRE_PAGE_SGA initialization parameter set to TRUE can significantly increase the time required to establish database connections.

The golden words here are "Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.".  That is exactly what happens when you do adpatch or adop.

Keep this in mind, whenever you do adpatch or adop, make sure that pre_page_sga is set to false.  It is possible that you may get the error "java.sql.SQLRecoverableException: IO Error: Socket read timed out" if you don't.  Also the patch will run significantly slower if pre_page_sga is set to true.  So set it to false and avoid these issues.



Categories: APPS Blogs

Is Oracle Database 12c (12.1.0.2.0) Faster Than Previous Releases?

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.Is Oracle Database 12c (12.1.0.2.0) Faster Than Previous Releases?
I was wondering if the new Oracle Database 12c version 12.1.0.2.0 in-memory column store feature will SLOW performance when it is NOT being used. I think this is a fair question because most Oracle Database systems will NOT be using this feature.

While the new in-memory column store feature is awesome and significant, with each new Oracle feature there is additional kernel code. And if Oracle is not extremely careful, these new lines of Oracle kernel code can slow down the core of Oracle processing, that is, buffer processing in Oracle's buffer cache.

Look at it this way, if a new Oracle release requires 100 more lines of kernel code to be executed to process a single buffer, that will be reflected in how many buffers Oracle can process per second.

To put bluntly, this article is the result of my research comparing core buffer processing rates between Oracle Database versions 11.2.0.2.0, 12.1.0.1.0 and 12.1.0.2.0.

With postings like this, it is very important for everyone to understand the results I publish are based on a very specific and targeted test and not on a real production load. Do not use my results in making a "should I upgrade decision." That would be stupid and an inappropriate use of the my experimental results. But because I publish every aspect of my experiment and it is easily reproducable it is valid data point with which to have a discussion and also highlight various situations that DBAs need to know about.

There are two interesting results from this research project. This article is about the first discovery and my next article will focus on the second. The second is by far the most interesting!

FYI. Back in August of 2013 performed a similar experiment where I compared Oracle database versions 11.2.0.2.0 with 12.1.0.1.0. I posted the article HERE.

Why "Faster" Means More Buffer Gets Processed Per Second
For this experiment when I say "faster" I am referring to raw buffered block processing. When a buffer is touched in the buffer cache it is sometimes called a buffer get or a logical IO. But regardless of the name, every buffer get increases the instance statistic, session logical reads.

I like raw logical IO processing experiments because they are central to all Oracle Database processing. Plus with each new Oracle release, as additional functionality is inserted it is likely more lines of Oracle kernel code will exist. To maintain performance with added functionality is an incredible feat. It's more likely the core buffer processing will be slower because of the new features. Is this case with Oracle's in-memory column store?

How I Setup The Experiment
I have included all the detailed output, scripts, R commands and output, data plots and more in the Analysis Pack that can be downloaded HERE.

There are a lot of ways I could have run this experiment. But two key items must exist for a fare comparison. First, all the processing must be in cache. There can be no physical read activity. Second, the same SQL must be run during the experiment and have the same execution plan. This implies the Oracle 12c column store will NOT be used. A different execution plan is considered "cheating" as a bad plan will clearly loose. Again, this is a very targeted and specific experiment.

The experiment compares the buffer get rates for a given SQL statement. For each Oracle version, I gathered 33 samples and excluded the first two, just to ensure caching was not an issue. The SQL statement runs for around 10 seconds, processes around 10.2M rows and touches around 5M buffers. I checked to ensure the execution plans are the same for each Oracle version. (Again, all the details are in the Analysis Pack for your reading pleasure.)

I ran the experiment on a Dell server. Here are the details:
$ uname -a
Linux sixcore 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
To make this easier for myself, to perform the test I used my CPU Speed Test tool (version 1i). I blogged about this last month HERE. The latest version of this tool can be downloaded HERE.

The Results, Statistically
Shown below are the experimental results. Remember, the statistic I'm measuring is buffer gets per millisecond.


Details about the above table: The "Normal" column is about if the statistical distribution of the 31 samples is normal. If the p-value (far right column) is greater than 0.05 then I'll say they are normal. In all three cases, the p-value is less than 0.05. If fact, if you look at the histograms contained in the Analysis Pack every histogram is visually clearly not normal. As you would expect the "Average" and the "Median" are the statistical mean and median. The "Max" is the largest value in the sample set. The "Std Dev" is the standard deviation, which is doesn't mean much since our sample sets are not normally distributed.

As I blogged about before the Oracle Database 12c buffer processing is faster than Oracle Database 11g. However, the interesting part is Oracle version with in-memory column store 12.1.0.2.0 is slower then the previous version of 12c, 12.1.0.1.0. In fact, in my experiment the in-memory column store version is around 5.5% slower! This means version 12.1.0.1.0 "out of the box" can process logical buffers around 5.5% faster! Interesting.

In case you're wondering, I used the default out-of-the-box in-memory column store settings for version 12.1.0.2.0. I checked the in-memory size parameter, inmemory_size and it was indeed set to zero. Also, when I startup the Oracle instance there is no mention of the in-memory column store.

Statistically Comparing Each Version
As an important side bar, I did statistically compare the Oracle Database versions. Why? Because while a 5.5% decrease in buffer throughput may seem important, it may not be statistically significant, meaning this difference can not be explained with our sample sets.

So going around saying version 12.1.0.2.0 is "slower" by 5.5% would be misleading. But in my experiment, it would NOT be misleading because the differences in buffer processing are statistically significant. The relevant experimental details are shown below.

Version A Version B Statistical p-value
Difference
---------- ---------- ----------- -------
11.2.0.1.0 12.1.0.1.0 YES 0.0000
11.2.0.1.0 12.1.0.2.0 YES 0.0000
12.1.0.1.0 12.1.0.2.0 YES 0.0000

In all three cases the p-value was less than 0.05 signifying the two sample sets are statistically
different. Again, all the details are in the Analysis Pack.

The chart above shows the histograms of both Oracle Database 12c version sample sets together. Visually they look very separated and different with no data crossover. So from both a numeric and visual perspective there is a real difference between 12.1.0.1.0 and 12.1.0.2.0.


What Does This Mean To Me
To me this is surprising. First, there is a clear buffer processing gain upgrading from Oracle 11g to 12c. That is awesome news! But I was not expecting a statistically significant 5.5% buffer processing decrease upgrading to the more recent 12.1.0.2.0 version. Second, this has caused me to do a little digging to perhaps understand the performance decrease. The results of my experimental journey are really interesting...I think more interesting than this posting! But I'll save the details for my next article.

Remember, if you have any questions or concerns about my experiment you can run the experiment yourself. Plus all the details of my experiment are included in the Analysis Pack.

All the best in your Oracle performance tuning work!

Craig.





Categories: DBA Blogs

Blog : Cloud Community France

Jean-Philippe Pinte - Fri, 2014-11-07 01:46
Intéressé par les solutions Cloud (IaaS & PaaS / Public & Privé) d'Oracle ?
Suivez le blog : https://blogs.oracle.com/Cloud-Community-France/

Evènement : Oracle & Mirantis

Jean-Philippe Pinte - Thu, 2014-11-06 07:47
Vous souhaitez
  • connaitre le partenariat Oracle & Mirantis
  • découvrir les actualités Oracle OpenWorld 2014 - OpenStack Summit
  • comprendre la proposition de valeur Oracle & OpenStack Mirantis
  • avoir une démonstration
  • etc  
Inscrivez-vous à l'évènement Openstack du 20 novembre prochain.

Understanding PeopleSoft Global Payroll Identification

Javier Delgado - Wed, 2014-11-05 08:49
The first stage in PeopleSoft Global Payroll processing is the identification of the employees to be calculated. Several criteria are used to determine which employees should be selected. Understanding why an employee is selected is not always evident to users. In this post I'm sharing how I normally determine the identification reason.

Once you run the identification stage, the employees to be processed are stored in the GP_PYE_PRC_STAT table. This table not only shows which employees are going to be calculated, but also indicates which calendars will be considered. This is particularly important when running retroactive calculations, as it allows you understanding the impact of this type of calculations.

In any case, going back to the identification, in this table you will find the SEL_RSN field, which contains a code that translates into the reason behind the employee identification. The valid values that this field may take are:

  • 01: The employee is active during the calendar period and included in the Payee List associated to the calendar.
  • 02: The employee is inactive (but was active before the start of the calendar period) and included in the Payee List associated to the calendar.
  • 03: The employee is active during the calendar period and has a positive input associated to him/her.
  • 04: The employee is active during the calendar period and has a retro trigger associated to him/her.
  • 05: The employee is active during the calendar period and associated to the calendar pay group.
  • 06: The employee is inactive during the calendar period and associated to a positive input in the current calendar.
  • 07: The employee is inactive (but still associated to the calendar pay group) and has a retro trigger associated to him/her.
  • 08: The employee is inactive but has a retroactive calculation delta from a previous calendar which has not been picked yet.
  • 09: The employee is inactive but has a retroactive calculation correction from a previous calendar which has not been picked yet.
  • 0A: The employee is active and linked to the calendar using an override.
  • 0B: The employee is inactive and linked to the calendar using an override.

From a technical standpoint, you can check the SQL used to select each reason by check the stored statement under the name GPPIDNT2_I_PRCnn, when nn is the SEL_RSN value.

Do you use other way to understand why was an employee identified? If so, please feel free to share your method in the comments, as I'm afraid my approach is a little bit too technical. ;)

The Importance of Backups (A Cautionary Block Recovery Tale)

Don Seiler - Tue, 2014-11-04 22:28
Just wanted to share a quick story with everyone. As I was in the airport waiting to fly to Oracle OpenWorld this year, I noticed a flurry of emails indicating that part of our storage infrastructure for our standby production database had failed. Long story short, my co-workers did a brilliant job of stabilizing things and keeping recovery working. However, we ended up with more than a few block corruptions.

Using the RMAN command "validate database", we could then see the list of corrupt blocks in the v$database_block_corruption view. All that was needed was to run "recover corruption list" in RMAN, which will dig into datafile copies and backups to do what it can to repair or replace the corrupt blocks and then recover the datafiles. Of course, nothing is ever that easy for us!

The storage we were writing our weekly backups to had been having problems and the latest weekly had failed. We ended up having to back 2 weeks into backups to get the datafile blocks and archivelogs to eventually complete the corruption recovery. I also immediately moved our backups to more reliable storage as well so that we're never in the situation of wondering whether or not we have the backups we need.

So, triple-check your backup plan, validate your backups and TEST RECOVERY SCENARIOS! You can't say your backups are valid until you use them to perform a restore/recovery, and you don't want to find out the hard way that you forgot something.
Categories: DBA Blogs

PeopleSoft's paths to the Cloud - Part II

Javier Delgado - Tue, 2014-11-04 00:01
In my previous post, I've covered some ways in which cloud computing features could be used with PeopleSoft, particularly around Infrastructure as a Service (IaaS) and non-Production environments. Now, I'm going to discuss how cloud technologies bring value to PeopleSoft Production environments.

Gain Flexibility



Some of the advantages of hosting PeopleSoft Production environments using an IaaS provider were also mentioned in the my past article as they are also valid for Non Production environments:

  • Ability to adjust processing power (CPU) and memory according to peak usage.
  • Storage may be enlarged at any time to cope with increasing requirements.
  • Possibility of replicating the existing servers for contingency purposes.

In terms of cost, hosting the Production environment in IaaS may not always be cheaper than the on premise alternative (this needs to be analyzed on a case by case basis). However, the possibility to add more CPU, memory and storage on the run gives IaaS solutions an unprecedented flexibility. It is true that you can obtain similar flexibility with in house virtualized environments, but not many in-house data centers have the available horsepower of Amazon, IBM or Oracle data centers, to name a few.

Be Elastic



Adding additional power to the existing servers may not be the best way to scale up. An alternative way is to add a new server to the PeopleSoft architecture. This type of architecture is called elastic (actually, Amazon EC2 stands for Elastic Computing), as the architecture can elastically grow or shrink in order to adapt to the user load.

Many PeopleSoft customers use Production environments with multiple servers for high availability purposes. You may have two web servers, two application servers, two process schedulers, and so on. This architecture guarantees a better system availability in case one of the nodes fails. Using an elastic architecture means that we can add, for instance, a third application server not only to increase redundancy, but also the application performance.

In order to implement an elastic architecture, you need to fulfill two requirements:

  1. You should be able to quickly deploy an additional instance of any part of the architecture. 
  2. Once the instance is created, it should be plugged in the rest of the components, without disrupting the system availability.

The first point is easily covered by creating an Amazon AMI which can be instantiated at any moment. I've discussed the basics about AMIs in my previous post, but there is plenty of information from Amazon.

The second point is a bit trickier. Let's assume we are adding a new application server instance. If you do not declare this application server in the web servers configuration.properties file, it will not be used.

Of course you can do this manually, but my suggestion is that you try to automate these tasks, as it is this automation which will eventually bring elasticity to your architecture. You need to plan the automation not only for enlarging the architecture, but also for potential reduction (in case you covered a usage peak by increasing the instances and then you want to go back to the original situation).

At BNB we have built a generic elastic architecture, covering all layers of a normal PeopleSoft architecture. If you are planning to move to a cloud infrastructure and you need assistance, we would be happy to help.

Coming Next...

In my next post on this topic, I will cover how Database as a Service could be used to host PeopleSoft databases and what value it brings to PeopleSoft customers.

Filtering PeopleTools SQL from Performance Monitor Traces

David Kurtz - Mon, 2014-11-03 15:01

I have been doing some on-line performance tuning on a PeopleSoft Financials system using PeopleSoft Performance Monitor (PPM).  End-users have collect verbose PPM traces. Usually, when I use PPM in a production system, all the components are fully cached by the normal activity of the user (except when the application server caches have recently been cleared).  However, when working in a user test environment it is common to find that the components are not fully cached. This presents two problems.
  • The application servers spend quite a lot of time executing queries on the PeopleTools tables to load the components, pages and PeopleCode into their caches. We can see in the screenshot of the component trace that there is a warning message that component objects are not fully cached, and that these  cache misses skew timings.
  • In verbose mode, the PPM traces collect a lot of additional transactions capturing executions and fetches against PeopleTools tables. The PPM analytic components cannot always manage the resultant volume of transactions.
    Figure 1. Component trace as collected by PPMFigure 1. Component trace as collected by PPMIf I go further down the same page and look in the SQL Summary, I can see SQL operations against PeopleTools tables (they are easily identifiable in that they generally do not have an underscore in the third character). Not only are 5 of the top 8 SQL operations related to PeopleTools tables, we can also see that they also account for over 13000 executions, which means there are at least 13000 rows of additional data to be read from PSPMTRANSHIST.
    Figure 2. SQL Summary of PPM trace with PeopleTools SQLFigure 2. SQL Summary of PPM trace with PeopleTools SQLWhen I open the longest running server round trip (this is also referred to as a Performance Monitoring Unit or PMU), I can only load 1001 rows before I get a message warning that the maximum row limit has been reached. The duration summary and the number of executions and fetches cannot be calculated and hence 0 is displayed.
     Details of longest PMU with PeopleTools SQLFigure 3: Details of longest PMU with PeopleTools SQL
    Another consequence of the PeopleTools data is that it can take a long time to open the PMU tree. There is no screenshot of the PMU tree here because in this case I had so much data that I couldn't open it before the transaction timed out!
    Solution My solution to this problem is to delete the transactions that relate to PeopleTools SQL and correct the durations, and the number of executions and fetches held in summary transactions. The rationale is that these transactions would not normally occur in significant quantities in a real production system, and there is not much I can do about them when they do.
    The first step is to clone the trace. I could work on the trace directly, but I want to preserve the original data.
    PPM transactions are held in the table PSPMTRANSHIST. They have a unique identifier PM_INSTANCE_ID. A single server round trip, also called a Performance Monitoring Unit (PMU), will consist of many transactions. They can be shown as a tree and each transaction has another field PM_PARENT_INST_ID which holds the instance of the parent. This links the data together and we can use hierarchical queries in Oracle SQL to walk the tree. Another field PM_TOP_INST_ID identifies the root transaction in the tree.
    Cloning a PPM trace is simply a matter of inserting data into PSPMTRANSHIST. However, when I clone a PPM trace I have to make sure that the instance numbers are distinct but still link correctly. In my system I can take a very simple approach. All the instance numbers actually collected by PPM are greater than 1016. So, I will simply use the modulus function to consistently alter the instances to be different. This approach may break down in future, but it will do for now.
    On an Oracle database, PL/SQL is a simple and effective way to write simple procedural processes.  I have written two anonymous blocks of code.
    Note that the cloned trace will be purged from PPM like any other data by the delivered PPM archive process.

    REM xPT.sql
    BEGIN --duplicate PPM traces
    FOR i IN (
    SELECT h.*
    FROM pspmtranshist h
    WHERE pm_perf_trace != ' ' /*rows must have a trace name*/
    -- AND pm_perf_trace = '9b. XXXXXXXXXX' /*I could specify a specific trace by name*/
    AND pm_instance_id > 1E16 /*only look at instance > 1e16 so I do not clone cloned traces*/
    ) LOOP
    INSERT INTO pspmtranshist
    (PM_INSTANCE_ID, PM_TRANS_DEFN_SET, PM_TRANS_DEFN_ID, PM_AGENTID, PM_TRANS_STATUS,
    OPRID, PM_PERF_TRACE, PM_CONTEXT_VALUE1, PM_CONTEXT_VALUE2, PM_CONTEXT_VALUE3,
    PM_CONTEXTID_1, PM_CONTEXTID_2, PM_CONTEXTID_3, PM_PROCESS_ID, PM_AGENT_STRT_DTTM,
    PM_MON_STRT_DTTM, PM_TRANS_DURATION, PM_PARENT_INST_ID, PM_TOP_INST_ID, PM_METRIC_VALUE1,
    PM_METRIC_VALUE2, PM_METRIC_VALUE3, PM_METRIC_VALUE4, PM_METRIC_VALUE5, PM_METRIC_VALUE6,
    PM_METRIC_VALUE7, PM_ADDTNL_DESCR)
    VALUES
    (MOD(i.PM_INSTANCE_ID,1E16) /*apply modulus to instance number*/
    ,i.PM_TRANS_DEFN_SET, i.PM_TRANS_DEFN_ID, i.PM_AGENTID, i.PM_TRANS_STATUS,
    i.OPRID,
    SUBSTR('xPT'||i.PM_PERF_TRACE,1,30) /*adjust trace name*/,
    i.PM_CONTEXT_VALUE1, i.PM_CONTEXT_VALUE2, i.PM_CONTEXT_VALUE3,
    i.PM_CONTEXTID_1, i.PM_CONTEXTID_2, i.PM_CONTEXTID_3, i.PM_PROCESS_ID, i.PM_AGENT_STRT_DTTM,
    i.PM_MON_STRT_DTTM, i.PM_TRANS_DURATION,
    MOD(i.PM_PARENT_INST_ID,1E16), MOD(i.PM_TOP_INST_ID,1E16), /*apply modulus to parent and top instance number*/
    i.PM_METRIC_VALUE1, i.PM_METRIC_VALUE2, i.PM_METRIC_VALUE3, i.PM_METRIC_VALUE4, i.PM_METRIC_VALUE5,
    i.PM_METRIC_VALUE6, i.PM_METRIC_VALUE7, i.PM_ADDTNL_DESCR);
    END LOOP;
    COMMIT;
    END;
    /
    Now I will work on the cloned trace. I want to remove certain transaction.
    • PeopleTools SQL. Metric value 7 reports the SQL operation and SQL table name. So if the first word is SELECT and the second word is a PeopleTools table name then it is a PeopleTools SQL operation. A list of PeopleTools tables can be obtained from the object security table PSOBJGROUP.
    • Implicit Commit transactions. This is easy - it is just transaction type 425. 
    Having deleted the PeopleTools transactions, I must also
    • Correct transaction duration for any parents of transaction. I work up the hierarchy of transactions and deduct the duration of the transaction that I am deleting from all of the parent.
    • Transaction types 400, 427 and 428 all record PeopleTools SQL time (metric 66). When I come to that transaction I also deduct the duration of the deleted transaction from the PeopleTools SQL time metric in an parent transaction.
    • Delete any children of the transactions that I delete. 
    • I must also count each PeopleTools SQL Execution transaction (type 408) and each PeopleTools SQL Fetch transaction (type 414) that I delete. These counts are also deducted from the summaries on the parent transaction 400. 
    The summaries in transaction 400 are used on the 'Round Trip Details' components, and if they are not adjusted you can get misleading results. Without the adjustments, I have encountered PMUs where more than 100% of the total duration is spent in SQL - which is obviously impossible.
    Although this technique of first cloning the whole trace and then deleting the PeopleTools operations can be quite slow, it is not something that you are going to do very often. 
    REM xPT.sql
    REM (c)Go-Faster Consultancy Ltd. 2014
    set serveroutput on echo on
    DECLARE
    l_pm_instance_id_m4 INTEGER;
    l_fetch_count INTEGER;
    l_exec_count INTEGER;
    BEGIN /*now remove PeopleTools SQL transaction and any children and adjust trans durations*/
    FOR i IN (
    WITH x AS ( /*returns PeopleTools tables as defined in Object security*/
    SELECT o.entname recname
    FROM psobjgroup o
    WHERE o.objgroupid = 'PEOPLETOOLS'
    AND o.enttype = 'R'
    )
    SELECT h.pm_instance_id, h.pm_parent_inst_id, h.pm_trans_duration, h.pm_trans_defn_id
    FROM pspmtranshist h
    LEFT OUTER JOIN x
    ON h.pm_metric_value7 LIKE 'SELECT '||x.recname||'%'
    AND x.recname = upper(regexp_substr(pm_metric_value7,'[^ ,]+',8,1)) /*first word after select*/
    WHERE pm_perf_trace like 'xPT%' /*restrict to cloned traces*/
    -- AND pm_perf_trace = 'xPT9b. XXXXXXXXXX' /*work on a specific trace*/
    AND pm_instance_id < 1E16 /*restrict to cloned traces*/
    AND ( x.recname IS NOT NULL
    OR h.pm_trans_defn_id IN(425 /*Implicit Commit*/))
    ORDER BY pm_instance_id DESC
    ) LOOP
    l_pm_instance_id_m4 := TO_NUMBER(NULL);
     
        IF i.pm_parent_inst_id>0 AND i.pm_trans_duration>0 THEN
    FOR j IN(
    SELECT h.pm_instance_id, h.pm_parent_inst_id, h.pm_top_inst_id, h.pm_trans_defn_id
    , d.pm_metricid_3, d.pm_metricid_4
    FROM pspmtranshist h
    INNER JOIN pspmtransdefn d
    ON d.pm_trans_defn_set = h.pm_trans_defn_set
    AND d.pm_trans_defn_id = h.pm_trans_Defn_id
    START WITH h.pm_instance_id = i.pm_parent_inst_id
    CONNECT BY prior h.pm_parent_inst_id = h.pm_instance_id
    ) LOOP
    /*decrement parent transaction times*/
    IF j.pm_metricid_4 = 66 /*PeopleTools SQL Time (ms)*/ THEN --decrement metric 4 on transaction 400
    --dbms_output.put_line('ID:'||i.pm_instance_id||' Type:'||i.pm_trans_defn_id||' decrement metric_value4 by '||i.pm_trans_duration);
    UPDATE pspmtranshist
    SET pm_metric_value4 = pm_metric_value4 - i.pm_trans_duration
    WHERE pm_instance_id = j.pm_instance_id
    AND pm_trans_Defn_id = j.pm_trans_defn_id
    AND pm_metric_value4 >= i.pm_trans_duration
    RETURNING pm_instance_id INTO l_pm_instance_id_m4;
    ELSIF j.pm_metricid_3 = 66 /*PeopleTools SQL Time (ms)*/ THEN --SQL time on serialisation
    --dbms_output.put_line('ID:'||i.pm_instance_id||' Type:'||i.pm_trans_defn_id||' decrement metric_value3 by '||i.pm_trans_duration);
    UPDATE pspmtranshist
    SET pm_metric_value3 = pm_metric_value3 - i.pm_trans_duration
    WHERE pm_instance_id = j.pm_instance_id
    AND pm_trans_Defn_id = j.pm_trans_defn_id
    AND pm_metric_value3 >= i.pm_trans_duration;
    END IF;

    UPDATE pspmtranshist
    SET pm_trans_duration = pm_trans_duration - i.pm_trans_duration
    WHERE pm_instance_id = j.pm_instance_id
    AND pm_trans_duration >= i.pm_trans_duration;
    END LOOP;
    END IF;

    l_fetch_count := 0;
    l_exec_count := 0;
    FOR j IN( /*identify transaction to be deleted and any children*/
    SELECT pm_instance_id, pm_parent_inst_id, pm_top_inst_id, pm_trans_defn_id, pm_metric_value3
    FROM pspmtranshist
    START WITH pm_instance_id = i.pm_instance_id
    CONNECT BY PRIOR pm_instance_id = pm_parent_inst_id
    ) LOOP
    IF j.pm_trans_defn_id = 408 THEN /*if PeopleTools SQL*/
    l_exec_count := l_exec_count + 1;
    ELSIF j.pm_trans_defn_id = 414 THEN /*if PeopleTools SQL Fetch*/
    l_fetch_count := l_fetch_count + j.pm_metric_value3;
    END IF;
    DELETE FROM pspmtranshist h /*delete tools transaction*/
    WHERE h.pm_instance_id = j.pm_instance_id;
    END LOOP;

    IF l_pm_instance_id_m4 > 0 THEN
    --dbms_output.put_line('ID:'||l_pm_instance_id_m4||' Decrement '||l_exec_Count||' executions, '||l_fetch_count||' fetches');
    UPDATE pspmtranshist
    SET pm_metric_value5 = pm_metric_value5 - l_exec_count
    , pm_metric_value6 = pm_metric_value6 - l_fetch_count
    WHERE pm_instance_id = l_pm_instance_id_m4;
    l_fetch_count := 0;
    l_exec_count := 0;
    END IF;

    END LOOP;
    END;
    /
    Now, I have a second PPM trace that I can open in the analytic component.
     Original and Cloned PPM tracesFigure 4: Original and Cloned PPM traces

    When I open the cloned trace, both timings in the duration summary have reduced as have the number of executions and fetches.  The durations of the individual server round trips have also reduced.
     Component Trace without PeopleTools transactionsFigure 5: Component Trace without PeopleTools transactions
    All of the PeopleTools SQL operations have disappeared from the SQL summary.
     SQL Summary of PPM trace after removing PeopleTools SQL transactionsFigure 6: SQL Summary of PPM trace after removing PeopleTools SQL transactions
    The SQL summary now only has 125 rows of data.
    Figure 7: SQL Summary of PMU without PeopleTools SQL
    Now, the PPM tree component opens quickly and without error.
     PMU Tree after removing PeopleTools SQLFigure 8: PMU Tree after removing PeopleTools SQL
    There may still be more transactions in a PMU than I can show in a screenshot, but I can now find the statement that took the most time quite quickly.

     Long SQL transaction further down same PMU treeFigure 9: Long SQL transaction further down same PMU tree
    Conclusions I think that it is reasonable and useful to remove PeopleTools SQL operations from a PPM trace.
    In normal production operation, components will mostly be cached, and this approach renders traces collected in non-production environments both usable in the PPM analytic components and more realistic for performance tuning. However, it is essential that when deleting some transactions from a PMU, that summary data held in other transactions in the same PMU are also corrected so that the metrics remain consistent.

    Annonce : Web Séminaire DBaaS

    Jean-Philippe Pinte - Mon, 2014-11-03 03:32

    Accélérer considérablement vos délais de mise à disposition des services DB avec une infrastructure de Cloud Privé pour vos bases de données
    (Database as a service).

    Mardi 18 novembre 2014 ; de 11h à 12h

    Les solutions Oracle pour bâtir un Cloud Privé permettent de transformer les organisations IT en apportant une architecture agile, évolutive et mutualisée permettant de répondre à leurs besoins et objectifs IT.
    Inscrivez-vous au webinar du 18 novembre 2014 pour comprendre la solution de cloud privé Database, les caractéristique, les enjeux, les bénéfices, etc...
    En effet, lors de ce webinar, vous nous présenterons:
    • Les principales fonctionnalités de la solution : automatisation, portail libre service, catalogue de services, snapclone, mesure et facturation de l’usage.
    • Les principaux bénéfices :  Réduction du délai de mise à disposition des services DB : jours/semaines è minutes/heures, réduction drastique des volumétries Hors Production, Plus d’agilité, Meilleur contrôle des ressources, Consolidation.
    • Une démonstration de notre solution DBaaS Private Cloud.
    • Des exemples Clients.
    Ce webinar s’adresse à toute personne impliquée dans la planification, le déploiement et l’administration d’un cloud privé mais également les équipes Hors Production exprimant le besoin de services DB : DBAs, Administrateurs systèmes, Architectes techniques, Ingénieurs qualité, Développeurs, Chefs de projet applicatif/Web, Testeurs etc...

    Inscrivez-vous dès maintenant en envoyant un e-mail à alexandre.lacreuse@oracle.com .

    Science needs to explain this?

    FeuerThoughts - Sun, 2014-11-02 08:57
    Christopher Nolan of Dark Knight fame releasing new sci-fi movie: Interstellar.

    In a Chicago Tribune interview, he says:

    I could be wrong, but science needs to cross a threshold and explain why a monkey typing infinitely would never type the works of Shakespeare.

    Well, I could be wrong, but maybe Nolan is a bit of an idiot when it comes to science.

    Please, Mr. Nolan, tell me which scientists make this claim?

    I guess he read somewhere about infinity and how incredibly awesome and big and never-ending it is, and so eventually anything would be done by anybody or anything and so even monkeys would "eventually" write Shakespeare and and and....

    Produce a movie called Interstellar with Matthew McConaughey. 

    In fact, maybe Chris Nolan is actually a monkey who crossed over from that obelisk in 2001, and got super smart and so a monkey already has produced a movie called Interstellar.

    Damn, that is just so cool and so weird and it's like, that's never going to happen, man, no way.

    So scientists had better figure out WHY that is not going to happen when they obviously really believe that it WILL happen (go, monkey, go!).

    And to do that, they are going to have a cross a threshold, 'cause clearly science has hit its limit here. Just like with souls. Science can't explain souls, so I guess scientists had better cross over - maybe into a parallel universe -

    Because really what could be cooler than parallel universes?




    Categories: Development

    Does Increasing An Oracle Background Process OS Priority Improve Performance?

    This page has been permanently moved. Please CLICK HERE to be redirected.

    Thanks, Craig.Does Increasing An Oracle Background Process OS Priority Improve Performance?
    Does increasing an Oracle Database background process operating system priority improve performance? As you might expect, the answer is, "It depends."

    In this posting I will explain the results of an experiment where I increase the Oracle Database 12c log writer background processes operating system priority.

    In my experiment I created a clear CPU bottleneck and the top wait event was log file parallel write. I gathered some data. Next I increased all the log writer background process priorities. Again, I gathered some data and then I analyzed the two data sets. The results were disappointing, not surprising, but a fundamental rule in performance tuning was demonstrated and reinforced.

    You can download the "analysis pack" which contains the raw experimental data, histogram, statistical R results and the R statical package commands HERE. You can download total time delta reporting script (ttpctx.sql) I show below, which is contained within my OraPub System Monitor (OSM) Toolkit HERE.

    Before I could gather some data, I needed to create the appropriate system load, the data gather scripts and the data analysis scripts. Here's a quick overview of each.

    Increasing The LGWR Processes PriorityIf you are not familiar with changing Oracle Database background OS process priority, I blogged about how to do this HERE.

    My experiment consisted of creating two situations and statistically comparing them to see if increasing the LGWR background process OS priority increased performance. The only difference in the "two situations" was the instance parameter, _high_priority_processes. For the "normal priority" part of the experiment, the default "LMS*|VKTM" was used. For the "high priority" part of the experiment the parameter was changed to "LMS*|VKTM|LG*". The "LG*" caused the increase in the Linux OS priority of all log writer processes from the default 19 to 41.

    Ready for some version specifics? Oracle continues to make OS priority related instance parameter changes... even within 12c releases. Since this experiment was done with Oracle Database version 12.1.0.1.0 the parameter default was "LMS*|VKTM" not "LMS*" as with version 12.1.0.2.0. Also, in 12.1.0.2.0 VKTM is placed into a new parameter, _highest_priority_processes.

    Generating The DML With CPU Bottleneck Load
    To generate the DML workload, I used my OP Load Generator. You can download it HERE. It's quick, easy and I'm familiar with it. I kept increasing the number of DML processes until the CPU utilization was pegged at 100%. While the database server was clearly out of CPU power, the log writer background processes were rarely seen as the top CPU consuming process. I used the "top" program to monitor the process activity. The top CPU consuming processes were almost always the Oracle server/foreground/shadow processes. As I'll explain in the Results section below, this has significant ramifications on the results.

    Oracle Time Based Analysis Summary
    The data collection script was similar to the one I used back in 2012 when collecting data regarding Oracle's commit write facility. Essentially, I collected multiple three minute samples of the delta elapsed time, user commits, total non-idle wait time and CPU consumption. In both the normal and high priority runs, around 99% of the database time was CPU consumption and 1% of the time Oracle non-idle wait time. Also the top wait event (~70%) was log file parallel write (display name: log file redo write).

    If you want master an Oracle Time Based Analysis, check out my online seminar, Tuning Oracle Using An AWR Report. It will teach you how to using an AWR report to optimize Oracle performance so users will feel the difference.

    The OraPub System Monitor Toolkit script ttpctx.sql report below was taken during the "normal priority" log writer load.

    SQL> @ttpctx

    Database: prod35 16-MAY-14 06:07pm
    Report: ttpctx.sql OSM by OraPub, Inc. Page 1
    Total Time Activity (39 sec interval)

    Avg Time Time Wait
    Time Component % TT % WT Waited (ms) (sec) Count(k)
    ------------------------------------- ------- ------- ----------- ----------- --------
    CPU consumption: Oracle SP + BG procs 98.91 0.00 0.000 238.716 0
    log file redo write 0.73 67.56 8.082 1.770 0
    control file parallel write 0.18 16.41 33.077 0.430 0
    target log write size 0.05 4.20 6.111 0.110 0
    oracle thread bootstrap 0.03 3.05 40.000 0.080 0
    os thread creation 0.02 1.53 20.000 0.040 0
    commit: log file sync 0.01 0.76 10.000 0.020 0
    enq: CR - block range reuse ckpt 0.01 0.76 20.000 0.020 0
    Disk file operations I/O 0.00 0.00 0.000 0.000 0

    The OSM report below was taken during the "high priority" log writer load.

    SQL> @ttpctx

    Database: prod35 16-MAY-14 09:25pm
    Report: ttpctx.sql OSM by OraPub, Inc. Page 1
    Total Time Activity (41 sec interval)

    Avg Time Time Wait
    Time Component % TT % WT Waited (ms) (sec) Count(k)
    ------------------------------------- ------- ------- ----------- ----------- --------
    CPU consumption: Oracle SP + BG procs 98.92 0.00 0.000 238.733 0
    log file redo write 0.83 77.01 8.272 2.010 0
    control file parallel write 0.08 7.28 14.615 0.190 0
    target log write size 0.05 4.98 5.909 0.130 0
    oracle thread bootstrap 0.03 3.07 40.000 0.080 0
    os thread creation 0.02 1.92 25.000 0.050 0
    commit: log file sync 0.01 0.77 10.000 0.020 0
    enq: CR - block range reuse ckpt 0.01 0.77 20.000 0.020 0
    enq: RO - fast object reuse 0.00 0.38 10.000 0.010 0


    Data Collection
    For the normal priority load 27 three minute samples where collected. For the high priority situation there were 30 three minute samples collected. (I forgot why there was only 27 samples collected for the normal priority.)  I collected the elapsed time, total non-idle wait time, total CPU consumption (v$sys_time_model: db_cpu + background cpu time) and total user commits.

    In this experiment more user commits processed per second means better performance.


    Experimental Results
    I used the free statistics package "R" (www.r-project.org) to analyze the data. I demonstrate how to get, install and use "R" in my online video seminar, Using Skewed Data To Your Advantage.

    With the normal log writer process priority, an average of 984.5 commits/sec and a median of 983.0 commits/sec occurred. With the LG* high process priority, an average of 993.6 commits/sec and a median of 991.0 commits/sec occurred. While the "high priority" situation was able to process more commits per second, is this statistically significant?

    The red "smoothed" histogram is the normal priority situation and the blue smoothed histogram is when the log writers were set to the higher priority. The more separated the two histograms the more "different" the sample sets, the more likely there is a statistically significant difference and the more likely a user would feel the difference. Looking at the above histograms plot, there does not appear to be a real difference. But let's do a proper significance test!

    Because both sample sets are normally distributed (details are in the Analysis Pack), I could use a simple t-test. R produced a p-value of 0.04451. To be statistically "different" I want the p-value to be less than 0.05 and it is. What does this mean?

    While statistically and numerically the commit rates are different, I wouldn't expect any special performance tuning award! In fact, the hassles with cycling a production instance and setting underscore/hidden parameters would make it very unlikely I would increase the OS priority of the log writer background processes. I want to see a big performance difference.

    To Summarize... What I Learned... Again
    This situation is a perfect example of focusing on the wrong thing! While there is a clear operating system CPU bottleneck and the top wait event is about redo, the log writers are not suffering from a want/need of CPU resources. I suspect the server processes want more CPU resources, but they are NOT the processes we increased their OS priority.

    If the log writers were suffering from a lack of CPU resources and fighting for CPU resources, I would expect to see them consuming CPU resources along with the Oracle server processes. And I would definitely expect to see them near the top of the "top" process monitor... especially when their priority has been increased!

    Because of this "misguided" tuning effort, this experiment does not build a case for or against changing the log writer priority. What it reinforces is in our quest to optimize performance, make sure we focus on the right thing.

    As a side note, this is a great statistical analysis example for two reasons. First, our samples sets look similar, but statistically they are not. Second, while they are statistically different, the performance impact will not be very different. And my guess is the users won't feel a thing... except frustration and anger.

    To Super Summarize
    When considering increasing a background process's operating system priority, make sure the process is in need of CPU and is not able to get it. In this situation, the DBA could have been mislead by the CPU bottleneck. But upon closer inspection of the log writers from an operating system perspective and knowing the wait event "log file parallel write" is probably more about IO than CPU (Oracle perspective) it would be unlikely that increasing the log writer processes OS priority would help increase the commits per second.

    Thanks for reading!

    Craig.












    Categories: DBA Blogs

    Work-around Instance Migration Limits of BPM Suite 11g

    Jan Kettenis - Thu, 2014-10-30 13:46
    The following describes a work-around for 2 situations for which instance patching and migration is not supported, being changing the level of an activity, and removal of an embedded sub-process. In short this work-around consists of re-implementation of the activities to move, and emptying the reusable sub-process.

    There are a couple of restrictions for the Oracle BPM Suite that can make that process instances cannot be patched (deployment using same revision number) or migrated (deployment using new revision number, and then move from old to new revision). Two of them are that you cannot change the scope of an activity (like moving it in our out of an embedded sub-process) or removal of an embedded sub-process. For both situations there is a work-around, that I can demonstrate with one case.

    More information about instance patching and migration can be found here for BPM 11g (11.1.1.7) and here for BPM 12c (12.1.3).
    The Work-Around
    Suppose you have a process (A) like this:





    And you want to change it to this (in real life you may want to move the activity inside to a totally different location, or even remove the embedded sub-process altogether) to this model (D):
    As migration of running instances is not supported:
    1. Because of changing the scope of the activity, and
    2. Because of removal of the embedded sub-process
    you will get an error like this when trying to do so:

     However, what you can do, is change the model like this (C):

    The trick is that the Say Goodbye activity has not been moved outside, but re-implemented. In this case I created a new activity with the same name, and reused the existing task definition. So I only had to redo the data mappings.

    You won't win any prize for most beautiful process model with this but it works. In practice you want to collapse the embedded sub-process and rename it to something like "Empty".
    The Proof of the PuddingTo make sure it actually works for running processes I used an extra step between (A) and (C), being this model (B):
     First I started with the first model, created 2 instances with one in Say Hello, and the other in Say Goodbye. I then deployed the last model with "Keep running instances" checked (instance patching). The result was that both instances were automatically migrated (i.e. just kept on running).

    Then I created a start situation of 3 instances, each of them being in a different activity. The most interesting is the one in Say Goodbye in the re-usable sub-process, as in this case the token is in an activity that is going to be removed.
     In line with the documentation, when deploying model (C) all instances were put I status Pending Migration. Using Alter Flow I was able to migrate the instances in the first and last activity as-is. After that I could successfully complete them.

    The interesting one though, is in the second activity:

    I was able to migrate that with Alter Flow by moving the token from the embedded sub-process (not the activity inside the embedded sub-process!) to the last activity:

    The engine never knew what hit it ;-)

    AIOUG annual Oracle conference - SANGAM14

    Syed Jaffar - Wed, 2014-10-29 00:43
    All India Oracle User Group (AIOUG) annual Oracle conference Sangam14 is less than 10 days away. This is the largest Oracle conference that take place every year in different cities of India with thousand's of attendees plus over 100 different topics by many Oracle experts across the globe.

    This year's SANGAM is scheduled on Nov 7,8,9 in Bangalore city. Don't let the opportunity go vain, avail/grab the opportunity if you are in India. I am super excited about the conference and look forward attending Tom Kyte's 'Optimizer master class', a full day class and also Maria's 'Oracle database in-memory option' session.

    My sessions are as follow:



    For more details on agenda, speakers, enrollment, visit http://sangam14.info/.

    Look forward to seeing you in-person at the conference.

    PeopleSoft's paths to the Cloud - Part I

    Javier Delgado - Tue, 2014-10-28 14:54

    Nowadays, all paths seem to lead to cloud computing. In the business applications world, Oracle is pushing hard to position the Oracle Cloud Applications in an increasingly competitive market. The reasons that favor Software as a Service (SaaS) applications over their on premise counterparties are significant, even though there are still a good number of circumstances under which the latter should normally be the preferred option.

    Our beloved PeopleSoft (yes, I like PeopleSoft, so what?) is clearly not a SaaS application. Still, my point of view is that we can still benefit of many cloud computing features without migrating to another application.

    On this post, and a few more to come, I will focus on the aspects of cloud computing could be incorporated to your PeopleSoft application.

    Infrastructure as a Service

    Infrastructure as a Service (IaaS) is a provision model in which an organization outsources the equipment used to support operations, including storage, hardware, servers and networking components. The service provider owns the equipment and is responsible for housing, running and maintaining it. The client typically pays on a per-use basis.

    Probably the best known service in this category is Amazon EC2, but there are many other providers with similar features. We have installed PeopleSoft quite a few times under Amazon EC2, and the advantages are visible immediately:

    • CPU, memory and disk space can be dynamically allocated. This is particularly useful when facing system usage peaks, for instance close to the evaluations submission deadline when using the PeopleSoft ePerformance module.
    • Servers can be seamlessly cloned, which enormously reduces the time needed to set up new environments.
    • The instance cloning can also take place between different geographical areas, providing a perfect solution for contingency environments.
    • As mentioned before, the allocated servers are paid on a per-use basis. The only exception is storage, for which you will get charged even if the server is down (and assuming you still keep the storage space busy for the next time the instance is booted).

    Use Case: Development Environments

    One of the most typical uses of IaaS with PeopleSoft is for non-production environments. In many cases, these environments do not need to be up and running 24x7, so the solution provided by Infrastructure as a Service is not only more flexible, but also normally more cost effective.

    The flexibility of IaaS is major advantage when a sandbox environment is needed. Cloning any existing environment just takes a few minutes allowing the developers to build prototypes on a new and isolated environment that is out of the migration path.

    Use Case: Test a New Release

    Another functionality of IaaS is the ability to use templates that could be rapidly be used to create a new instance based on it. The Amazon name for these templates is AMI. In the past, Oracle used to provide AMIs for PeopleSoft 9.1, so if you wanted to test that release, it was just a couple of minutes away.

    However, currently there are no AMIs provided by Oracle for PeopleSoft 9.2. Luckily, you may still contact consulting companies like BNB to provide you the AMI, as long as you have a valid PeopleSoft license (the Oracle provided AMIs are under a trial license, so even if you are not currently a PeopleSoft customer you can use them).

    Note: An alternative way to test a new release is to download the latest PeopleSoft Update Manager image, but it takes considerable time to do it due to the size of the files (over 30 Gb).

    Use Case: Training

    IaaS can also be used to quickly deploy PeopleSoft instances for internal user training. We actually use this approach at BNB for training our consultants. We have created an AMI for each course, so before the training session starts, we create one instance per student, so they have a completely isolated environment to learn and play with.

    Coming Next...

    In the next post, I will cover the value that cloud computing brings to PeopleSoft Production environments. But that's not the end of it, so stay tuned.






    How To Change The Priority Of Oracle Background Processes

    This page has been permanently moved. Please CLICK HERE to be redirected.

    Thanks, Craig.How To Change The Priority Of Oracle Background Processes

    Before you get in a huf, it can be done! You can change an Oracle Database background process

    priority through an instance parameter! I'm not saying it's a good idea, but it can be done.

    In this post I explore how to make the change, just how far you can take it and when you may want to consider changing an Oracle background process priority.

    To get your adrenaline going, check out the instance parameter _high_priority_processes from one of your production Oracle system with a version of 11 or greater. Here is an example using my OSM tool, ipx.sql on my Oracle Database version 12.1.0.2.0.
    SQL> @ipx _highest_priority_processes
    Database: prod40 27-OCT-14 02:22pm
    Report: ipx.sql OSM by OraPub, Inc. Page 1
    Display ALL Instance Parameters

    Instance Parameter and Value Description Dflt?
    -------------------------------------------------- -------------------- -----
    _highest_priority_processes = VKTM Highest Priority TRUE
    Process Name Mask
    Then at the Linux prompt, I did:
    $ ps -eo pid,class,pri,nice,time,args | grep prod40
    2879 TS 19 0 00:00:00 ora_pmon_prod40
    2881 TS 19 0 00:00:01 ora_psp0_prod40
    2883 RR 41 - 00:02:31 ora_vktm_prod40
    2889 TS 19 0 00:00:01 ora_mman_prod40
    2903 TS 19 0 00:00:00 ora_lgwr_prod40
    2905 TS 19 0 00:00:01 ora_ckpt_prod40
    2907 TS 19 0 00:00:00 ora_lg00_prod40
    2911 TS 19 0 00:00:00 ora_lg01_prod40
    ...
    Notice the "pri" for priority of the ora_vktm_prod40 process? It is set to 41 while all the rest of the Oracle background processes are set to the default of 19. Very cool, eh?

    Surprised By What I Found
    Surprised? Yes, surprised because changing Oracle process priority is a pandoras box. Just imagine if an Oracle server (i.e., foreground) process has its priority lowered just a little and then attempts to acquire a latch or a mutex? If it doesn't get the latch quickly, I might never ever get it!

    From a user experience perspective, sometimes performance really quick and other times the application just hangs.

    This actually happened to a customer of mine years ago when the OS started reducing a process's priority after it consumed a certain amount of CPU. I learned that when it comes to Oracle processes, they are programed to expect an even process priority playing field. If you try to "game" the situation, do so at your own risk... not Oracle's.

    Then why did Oracle Corporation allow background process priority to be changed. And why did Oracle Corporation actually change a background processes priority?!

    Doing A Little Exploration
    It turns out there are a number of "priority" related underscore instance parameters! On my 11.2.0.1.0 system there 6 "priority" parameters. On my 12.1.0.1.0 system there are 8 "priority" parameters. On my 12.1.0.2.0 system there are 13 "priority" parameters! So clearly Oracle is making changes! In all cases, the parameter I'm focusing on, "_high_priority_processes" exists.

    In this posting, I'm going to focus on my Oracle Database 12c version 12.1.0.2.0 system. While you may see something different in your environment, the theme will be the same.

    While I'll be blogging about all four of the below parameters, in this posting my focus will be on the _high_priority_processes parameter. Below are the defaults on my system:
    _high_priority_processes        LMS*
    _highest_priority_processes VKTM
    _os_sched_high_priority 1
    _os_sched_highest_priority 1

    Messing With The LGWR Background Processes
    I'm not testing this on a RAC system, so I don't have an LMS background process. When I saw the "LMS*" I immediately thought, "regular expression." Hmmm... I wonder if I can change the LGWR background process. So I made the instance parameter change and recycled the instance. Below shows the instance parameter change:
    SQL> @ipx _high_priority_processes
    Database: prod40 27-OCT-14 02:36pm
    Report: ipx.sql OSM by OraPub, Inc. Page 1
    Display ALL Instance Parameters

    Instance Parameter and Value Description Dflt?
    -------------------------------------------------- -------------------- -----
    _high_priority_processes = LMS*|LGWR High Priority FALSE
    Process Name Mask

    Below is an operating system perspective using the ps command:

    ps -eo pid,class,pri,nice,time,args | grep prod40
    ...
    5521 RR 41 - 00:00:00 ora_vktm_prod40
    5539 TS 19 0 00:00:00 ora_dbw0_prod40
    5541 RR 41 - 00:00:00 ora_lgwr_prod40
    5545 TS 19 0 00:00:00 ora_ckpt_prod40
    5547 TS 19 0 00:00:00 ora_lg00_prod40
    5551 TS 19 0 00:00:00 ora_lg01_prod40
    ...

    How Far Can I Take This?
    At this point in my journey, my mind was a blaze! The log file sync wait event can be really difficult to deal with and especially so when there is a CPU bottleneck. Hmmm... Perhaps I can increase the priority of all the log writer background processes?

    So I made the instance parameter change and recycled the instance. Below shows the instance parameter change:
    SQL> @ipx _high_priority_processes
    Database: prod40 27-OCT-14 02:44pm
    Report: ipx.sql OSM by OraPub, Inc. Page 1
    Display ALL Instance Parameters

    Instance Parameter and Value Description Dflt?
    -------------------------------------------------- -------------------- -----
    _high_priority_processes = LMS*|LG* High Priority FALSE
    Process Name Mask

    Below is an operating system perspective using the ps command:

    ps -eo pid,class,pri,nice,time,args | grep prod40
    ...
    5974 TS 19 0 00:00:00 ora_psp0_prod40
    5976 RR 41 - 00:00:00 ora_vktm_prod40
    5994 TS 19 0 00:00:00 ora_dbw0_prod40
    5996 RR 41 - 00:00:00 ora_lgwr_prod40
    6000 TS 19 0 00:00:00 ora_ckpt_prod40
    6002 RR 41 - 00:00:00 ora_lg00_prod40
    6008 RR 41 - 00:00:00 ora_lg01_prod40
    6014 TS 19 0 00:00:00 ora_lreg_prod40
    ...

    So now all the log writer background processes have a high priority. My hope would be that if there is an OS CPU bottleneck and the log writer background processes wanted more CPU, I now have the power to give that to them! Another tool in my performance tuning arsenal!

    Security Hole?
    At this point, my exuberance began to turn into paranoia. I thought, "Perhaps I can increase the priority of an Oracle server process or perhaps any process." If so, that would be a major Oracle Database security hole.

    With fingers trembling, I changed the instance parameters to match an Oracle server process and recycled the instance. Below shows the instance parameter change:

    SQL> @ipx _high_priority_processes
    Database: prod40 27-OCT-14 02:52pm
    Report: ipx.sql OSM by OraPub, Inc. Page 1
    Display ALL Instance Parameters

    Instance Parameter and Value Description Dflt?
    -------------------------------------------------- -------------------- -----
    _high_priority_processes = High Priority FALSE
    LMS*|LG*|oracleprod40 Process Name Mask

    Below is an operating system perspective using the ps command:

    $ ps -eo pid,class,pri,nice,time,args | grep prod40
    ...
    6360 TS 19 0 00:00:00 ora_psp0_prod40
    6362 RR 41 - 00:00:00 ora_vktm_prod40
    6366 TS 19 0 00:00:00 ora_gen0_prod40
    6382 RR 41 - 00:00:00 ora_lgwr_prod40
    6386 TS 19 0 00:00:00 ora_ckpt_prod40
    6388 RR 41 - 00:00:00 ora_lg00_prod40
    6394 RR 41 - 00:00:00 ora_lg01_prod40
    6398 TS 19 0 00:00:00 ora_reco_prod40
    ...
    6644 TS 19 0 00:00:00 oracleprod40...
    ...

    OK, that didn't work so how about this?

    SQL> @ipx _high_priority_processes
    Database: prod40 27-OCT-14 02:55pm
    Report: ipx.sql OSM by OraPub, Inc. Page 1
    Display ALL Instance Parameters

    Instance Parameter and Value Description Dflt?
    -------------------------------------------------- -------------------- -----
    _high_priority_processes = High Priority FALSE
    LMS*|LG*|*oracle* Process Name Mask

    Let's see what happened at the OS.

    $ ps -eo pid,class,pri,nice,time,args | grep prod40
    ...
    6701 RR 41 - 00:00:00 ora_vktm_prod40
    6705 RR 41 - 00:00:00 ora_gen0_prod40
    6709 RR 41 - 00:00:00 ora_mman_prod40
    6717 RR 41 - 00:00:00 ora_diag_prod40
    6721 RR 41 - 00:00:00 ora_dbrm_prod40
    6725 RR 41 - 00:00:00 ora_vkrm_prod40
    6729 RR 41 - 00:00:00 ora_dia0_prod40
    6733 RR 41 - 00:00:00 ora_dbw0_prod40
    ...
    6927 RR 41 - 00:00:00 ora_p00m_prod40
    6931 RR 41 - 00:00:00 ora_p00n_prod40
    7122 TS 19 0 00:00:00 oracleprod40 ...
    7124 RR 41 - 00:00:00 ora_qm02_prod40
    7128 RR 41 - 00:00:00 ora_qm03_prod40

    Oh Oh... That's not good! Now EVERY Oracle background process has a higher priority and my Oracle server process does not.

    So my "*" wildcard caused all the Oracle processes to be included. If all the processes a high prioirty, then the log writer processes have no advantage over the others. And to make matters even worse, my goal of increasing the server process priority did not occur.

    However, this is actually very good news because it appears this is not an Oracle Database security hole! To me, it looks like the priority parameter is applied during the instance startup for just the background processes. Since my server process was started after the instance was started and for sure not included in the list of background processes, its priority was not affected. Good news for security, not as good of news for a performance optimizing fanatic such as myself.

    Should I Ever Increase A Background Process Priority?
    Now that we know how to increase an Oracle Database background process priority, when would we ever want to do this? The short answer is probably never. But the long answer is the classic, "it depends."

    Let me give you an example. Suppose there is an OS CPU bottleneck and the log writer background processes are consuming lots of CPU while handling all the associated memory management when server process issues a commit. In this situation, performance may benefit by making it easier for the log writer processes to get CPU cycles, therefore improving performance. But don't even think about doing this unless there is a CPU bottleneck. And even then, be very very careful.

    In my next block posting, I'll detail an experiment where I changed the log writer background processes priority.

    Thanks for reading!

    Craig.



    Categories: DBA Blogs

    Pages

    Subscribe to Oracle FAQ aggregator