Tuesday, May 5, 2015

Upgrade Oracle APEX (4.0.2.00.09 to 5.0.0.00.31) In Oracle 11g Express Edition

Hi Friends,

It's been a while with a blogpost, and this time its APEX, One of my favorite product from Oracle.

Oracle 11g XE is quick to download from OTN and install in the laptop for trial & learning purposes and many of us already have this installed in laptop and use it for creating small applications for our own use. 


Scenario:- Today, I had to install a packaged application provided by Oracle into my express edition. By default the APEX version in express edition is 4.0.2.00.09, When I was trying to import the packaged application into my 11g XE database, It was failing with an INCOMPATIBLE error.

I did careful reading through the details of packaged application which clearly mentioned Oracle developed the application in APEX version 4.2 and thus my XE APEX version of 4.0 was incompatible. I did not want to install a 12c R1 database which by default comes with APEX 4.2, Instead decided to upgrade the APEX version in XE to newly released 5.0 version directly. 

This post shows step by step method to upgrade our APEX from version 4.0.2.00.09 to 5.0.0.00.31.


1. Download the latest Oracle APEX (5.0) version from OTN

download

























2. Unzip the downloaded zip file:
  • Linux: Unzip .zip
  • Windows: Double click .zip in Windows Explorer







3. Start CMD prompt and Change your working directory to apex.



4. Start SQL*Plus and connect to the Oracle XE database:

 

5. Install Application Express:



5. Log back into SQL*Plus (as above) and load images:

Confirm is the APEX was upgraded to version 5.0



7. In a Web browser, navigate to the Oracle Application Express Administration Services application:


 











After the APEX 5.0 upgrade my packaged application import was smooth without any issues.

HAPPY LEARNING!

Sunday, March 8, 2015

Oracle Enterprise Manager 12c Grid Control, Jump into SPA- Part III

Hi Friends,

This is the third and final part of this blogpost series, I would strongly recommend to go through the Part-I & Part-II of this series if you have directly landed to this post.

In Part-II, we identified the SQL statements which were the main contributors for USER I/O. In this post we will learn how to reduce the DB_Time considerably. In short, let try to explore the opportunities to reduce the 

Where,

DB_Time = CPU Time + Non-Idle Wait time ( If you are new to these matrices, I would recommend you to get quick web trainings from Craig Shallahamer from OraPub )

We will use SQL Tuning Advisor to find out for some advice. 

1) Below screenshot is again of the ASH Analytics home screen for my database. 


As we can see there are a couple of SQL Statements that cause the major part of the activity. You should see top SQL id.


Select top three SQL's, 


Please Note: Ignore below screenshot which has top five selected and in the following screenshots you will find some other SQL-id's, The reason is that my main main is to show you the way you can use OEM to diaognise performance problems. So, it takes lot of time & effort  to get these screenshots created arranged and post in self explanatory format.

The number of SQLs to tune is different, because by the time i start creating remaining screenshots, the short spikes had gone out of the ASH window and So will be showing the navigation with any top 3 SQL's available for me from then. d


Click Tune "Schedule SQL Tuning Advisor"



2)  Review job detail and for easier identification you can change the name too.

Click Submit


3) The tuning task will run for 5 to 10 minutes. You can see the progress and the cumulative benefits on the screen.



4) As we can see Tuning advisor has given tuning recommendations for us in the form of SQL profiles & indexes that we should investigate further.
 

Click on Show all results.


5) This constitutes the “Fix” or the solution for the problem identified as part of the Find-Fix-Validate performance methodology.

SQL Tuning Advisor has now given us advice to create SQL Profiles & create indexes. We will now implement the advice that will give us most benefits. But since we are not allowed to jeopardize current performance then we need to validate that our advices are risk free to implement. This will be done with SPA Quick Check.

Note:- The largest benefit comes by creating SQL Profiles. New indexes have almost as high a benefit. But indexes will definitely cause performance penalty during insert and updates and can also cause other SQL to regress.

Let’s start with SQL profiles. Will we be able to gain what SQL Tuning Advisor have predicted?
 

Click on “Validate All Profiles with SPA”


6) Enterprise Manager has now created a SQL Performance Analyzer Task for validating the performance.

Click on the SPA Task



7) The SPA Task will run for about 1 minute when “Last Run Status” is “completed click on the Name.




8) There are four trials executed, first and second are only comparing execution plans, third and forth are full executions of regressed SQL statements (subset of the workload)
 

Let’s see the comparison result from the third and fourth trial.
 

Click on the glasses for the second comparison report.




9) As we can see the performance improvement is not in line with what the SQL Tuning Advisor recommended. 

The benefit by SQL Profile is just 4% as compared to 99% Per SQL Tuning Advisor's recommendations,Now we have validated the fix in real time in the same database, So if the Validation was in line with SQL Tuning Advisor's recommendations we can blindly implement the SQL profiles.

As I said in the beginning my aim is to show you the complete navigation and options to use for diagonizing the performance issues and not the actual results because of 2 reasons.

**I am not running this on a prod db, 
** and while creating screenshots as I navigate, the ASH analytics window keeps moving, and even the window I select may just have a spike, which is not a performance issue at all.

However, let’s implement the SQL Profiles. (Which I should not do according to my validation result  :)   )

Click on the breadcrumb for Advisor Central.



10 ) Click on "Advisor Central" breadcrum & In the next screen, Identify your tuning task and click on the name

Click on SQL Profile




11) Click on Implement All SQL Profiles


12) Check “Implement the new profile(s) with forced matching” and click Yes.

13) We have now implemented our new profiles. Let’s see if this had any impact on the workload.
Go to Performance -> ASH Analytics to see if the graph has come down, which means the performance issue was identified-validated & fixed on the fly.



14) Similarly, below screenshots give the navigation of how to go by the Index recommendations.











Hope you have gone through all the three parts of this blogpost, if not please read Part-IPart-II without fail.

HAPPY LEARNING!