Installing Oracle 11g Express edition on windows

Recently we inducted a group of college graduates into our team. My colleague Rahul and I were asked to give them few sessions or oracle performance engineering. Once we were done with the training our managers asked us to evaluate their performance by checking how they approach and solve some basic performance issues.

We realized that they need oracle databases to be installed on their individual PCs to simulate and solve these performance issues. But it was not practical for us to go and install a database in each one of their machines, so we created a PPT to show them how to download and install oracle 11g express edition on their desktops.

Here is a step by step process of installing oracle 11g R2 express edition from OTN with screenshots. Before going to install this version we need to know what oracle 11g Express edition is and what features are available. Oracle has a detailed documentation on this in their website and I will point some important points you should know in the flowing paragraphs.

Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  It’s free to develop, deploy, and distribute; fast to download; and simple to administer.

With respect to growing needs for storage, Oracle Database 11g XE now offers a full 11GBfor user data alone, which is almost threefold increase since previous 4GB limit in Oracle Database 10g XE. Other hardware restrictions remain the same with CPU usage capped at one physical core and memory at 1GB.

1. Go to Oracle Technology Network (OTN) website http://www.otn.oracle.com. Log in if you already has an account or create a new account.

Picture1

Picture2

2. Go to downloads tab and click on database menu. You will find a submenu titled as “Database 11g Express Edition”, click on it.

Picture3

Picture4

3. Accept the license agreement and download the version according to the version of OS you have. Here we downloaded the windows 32 bit version.

Picture5

4. Once the download is completed, unzip the file and double click/right click on the setup.msi file and run it as administrator.

Picture6

Picture7

5. After launching the setup.msi file, you see the Install Shield wizard screen while the file loads, like this

Picture8

6. Click the Next button to start the installation.

Picture9

7. The second dialog box is the license information. Click the I accept the terms in the license agreement and then the Next button.

Picture10

8. The third dialog box asks where you want to install the software or provide an override location. If you accept the default location, click the Next button to continue.

Picture11

9. The fourth dialog box lets you specify the TNS, MTS, and HTTP Ports.

Picture12

10. The fifth dialog box lets you enter the password for the SYS and SYSTEM users.

Picture17

11. The sixth dialog box lets you see the configuration options you’ve chosen. If you are okay with click next.

Picture13

12. The seventh dialog box lets you see the progress of your installation.

Picture14

13. The last dialog box lets you see that you’ve installed the product. Click the Finish button to complete the installation.

Picture15

14. You’ve now installed Oracle Database 11g Express Edition. You can start the command line by typing sqlplus system/password in command shell or the Get Started option in your Program Menus, as shown below.

Picture18

15. You should see this console when you launch Get Started page.

Picture16

16. Now you can navigate to the screens you want and start to work on oracle !!

AWR report analysis – part 1

This is the first part of my AWR report analysis blogs. This is my understanding and how I approach an AWR report for proactive/reactive performance tuning. If you find any errors or issues in the articles, please leave a comment below.

AWR is an acronym for Automatic Workload Repository which provides vital database information for analyzing and troubleshooting. Just like any other software products oracle also have instrumentation code integrated to it. Even though this adds a little bit of overhead it helps us to troubleshoot the product efficiently.

AWR is introduced in oracle 10g and prior to that Statspack reports were used for database troubleshooting. And the list of metrics captured by oracle is listed in v$metricname.

The AWR report contains the following set of information

  1. Wait events information.
  2. Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  3. Active Session History statistics from the V$ACTIVE_SESSION_HISTORY view.
  4. System and session statistics from the V$SYSSTAT and V$SESSTAT views.
  5. Object usage statistics.
  6. Resource intensive SQL statements.

Many oracle troubleshooting utilities use AWR data for troubleshooting. Oracle utilities using AWR generated information are

  1. Automatic Database Diagnostic Monitor
  2. SQL Tuning Advisor
  3. Undo Advisor
  4. Segment Advisor

Oracle Snapshots

Oracle AWR reports uses snapshots to collect/record data from database. A snapshot is a set of information regarding performance and resource usage of the database over a period of time.

Oracle collects information all the while and save this data while the snapshot happens. Users can alter the snapshot interval based on their requirements. Users can also generate custom snapshots by explicitly running the snapshot command either through OEM or any oracle client software.

We can check the AWR snapshot interval and retention time by using the following command.

SELECT * FROM DBA_HIST_WR_CONTROL

Untitled

Retention Time – Time for which the snapshot information will be stored in the Database.

Default Snapshot Interval time – 1Hr

Default Retention Time – 7 days

Oracle Process responsible for taking snapshots  is MMON (Memory Monitor).

 Baselines

A baseline is defined a range of snapshots that can be used to compare to other pairs of snapshots. The Oracle database server will exempt the snapshots assigned to a specific baseline from the automated purge routine. Thus, the main purpose of a baseline is to preserve typical runtime statistics in the AWR repository. We can run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR.

Settings

We need to enable some basic parameters and some privilege for running the AWR report.

Parameters to be set:

TIMED_STATISTICS = TRUE

STATISTICS_LEVEL = TYPICAL or ALL

Required Privileges:

EXECUTE on DBMS_WORKLOAD_REPOSITORY Package

SELECT_CATALOG_ROLE

We can modify the snapshot and baseline setting using the following commands. You need privilege to run these commands. You can run these commands from any oracle clients.

MODIFY_SNAPSHOT_SETTINGS

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

(   retention   IN  NUMBER DEFAULT NULL,

interval    IN  NUMBER DEFAULT NULL,

dbid        IN  NUMBER DEFAULT NULL);

CREATE_SNAPSHOT

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

(   flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’) RETURN NUMBER;

FLUSH_LEVEL – Typical or All

CREATE_BASELINE

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

(   start_snap_id    IN  NUMBER,

end_snap_id      IN  NUMBER,

baseline_name    IN  VARCHAR2,

dbid             IN  NUMBER DEFAULT NULL);

Statistics gathering issue

This issue came up few weeks ago. I was asked to look into a time consuming batch job and the job was running for 5-6 hours every day and client wanted to reduce the execution time. This is what the job does every day

  1. Drop the indexes
  2. Load data into the tables
  3. Recreate the indexes
  4. Gather statistics

This looked fine to me at first. The stats procedure was running for a few hours and the client was getting worried on the execution time. Once I tuned the queries in loading procedure I decided to work on the stats gathering script.

Here is a copy of stats gathering script I got. I could see a lot of issues with the way it is written and executing.

stats_before

Some of the issues are

  1. The procedure collects the stats of all the tables irrespective of whether it is really changed or not.
  2. It does the same with all indexes
  3. Here index stats are collected twice; once when the indexes are created and secondly by the procedure.
  4. Parallelism is not used.

Here is the modified script and know what it got completed a lot faster!!

stats_after

Oracle Cardinality hint

Recently I came across a query where the cost was comparatively high. I was wondering what could be the issue for a simple query to have such a high cost. This query has been called many times during the execution and this has considerably increased over all execution time of the process. The query was placed inside a procedure which has a collection as input parameter. This collection is used to filter data from a table with 544K rows.

The business logic was such that we can pass only 5 members to the collection at a time. I was surprised to see oracle does a FTS on the table to fetch 5 rows from an indexed column. I thought of digging a little bit deeper and find the root cause.

Original Query

pic1

Plan

pic2

Here, oracle calculates the size of collection as 8168 but we have only 5 elements in the collection. I thought of adding the cardinality hint and provide the optimizer some more information.

Modified Query with cardinality hint.

pic3

Plan

pic4

We can clearly see that oracle is calculating the collection size properly and able to generate the best plan. we were able to get a great performance difference with this small changes.

How oracle plan baselines saved our day!!

We had an interesting performance issue in our project yesterday. Our senior performance DBA Joe was able to fix it very quickly. I will explain what the issue was and how Joe analyzed it step by step and resolved it using Oracle plan baselines.

It all happened when we noticed one of our batch job is using too much of CPU and time, a quick OEM analysis proved that we had considerably high resource usage while the batch job was running. Most of the resource was consumed by a SQL query

pic1

The plan control told us that we have two execution plans and oracle is picking the cheaper plan.

Oracle used the plan with least cost but it was using the wrong index to scan the tables.  And once he found the problems it is about devising the solutions.

He had 3 options

  1. Ask developers to rewrite the code and deploy it.
  2. Add a hint and direct oracle to use the correct index
  3. Use plan baseline to make oracle use the right plan

The first two options were ruled out because and code change has to follow a deployment lifecycle and might get weeks to hit production. So he immediately chose the third option.

pic2

So he just asked oracle to choose the second plan using the SQL plan baseline, and Bingo!!  The query started running smoothly.

You can read more about the plan baselines below

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABEAFGG

Second day..

I’m learning this stuff very slowly. It’s the time to make my blog look good. Adding a few images and background, I’m linking all my favorite blogs with mine. So if you are bored with my rants you can read some good stuff from masters. !!

My first blog..

Well, this my first blog. I have been thinking about blogging for some time and finally decided to give it a try. I consider this more of a journal than a blog.  I’m a database performance engineer and i face many performance issues in my work, i want to chronicle all of them for my future reference. I will be really happy if my blog helps some one. After all “Knowledge grows when shared.”