HOW TO: Analyzing and Interpreting AWR Report


Today I wanted to share with you my personal approach for how to get into the details on AWR Report and where to see and where should be our attention. Keep in mind just two main focuses : the Application and the Database instance

1) Application: Most of the times you should go directly to review how was the SQL statements Performance. That is MANDATORY and the result of your analysis will be the classic TOP 10 SQL with the worst performance. That should be called : Oracle Application’s Performance Tuning .

2) Instance: read once, twice or three times the AWR looking for issues. What kind of issues or metrics ?

For example here I have  a Conclusion and Workaround for one database instance issue regarding performance :

1) Tuning recommendations for PROD-ACME  database instance:

AWR Report – How to get it through EM

We need a database user/pwd


But we can generate it using SQLPLUS as well :

AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.

[oaPROD usa.com01 PROD] $ sqlplus ‘/as sysdba’
SQL> @awrrpt.sql

Current Instance   DB Id    DB Name      Inst Num Instance

HOWTO: Checking options installed within the Database/Oracle Home

1)From Oracle Universal Installer: In OUI select installed products and click on list and expand, you will see a list of options installed in database.

You have to use OUI to check whether Oracle Secure Enterprise Search is installed or not.

TIP: Start Oracle Universal Installer from your system prompt (UNIX) with the following command:


2)From v$option:

SQL> set pages 100

SQL> select * from v$option;

PARAMETER                                                        VALUE

—————————————————————- —–

Partitioning                                                     TRUE

Objects                                                          TRUE

Real Application Clusters                                        FALSE

Advanced replication                                             TRUE

Bit-mapped indexes                                               TRUE

Connection multiplexing                                          TRUE

Connection pooling                                               TRUE

Database queuing                                                 TRUE

Incremental backup and recovery                                  TRUE

Instead-of triggers                                              TRUE

Parallel backup and recovery                                     TRUE

Parallel execution                                               TRUE

Parallel load                                                    TRUE

Point-in-time tablespace recovery                                TRUE

Fine-grained access control                                      TRUE

Proxy authentication/authorization                               TRUE

Forms in socket mode

Algun cliente alguna vez debe haber sufrido de un extraño issue con  los forms y se preguntara si vale la pena cambiarlos a ‘socket’ mode, y él se estará preguntando (con toda razón):

A.      When the forms are changed to socket mode:

1.      Will this affect the EBS application in anyway (will it be slow than now)

2.      Do we need to perform any change on our or the workstation’s side ?


Please look at the Note in Metalink 310976.1. It seems that the Java Console in JInitiator tells you at the beginning which mode you are using.

Verifying the Forms Client Connection to the Forms Server

Once the Applet has initialized and started, it connects to the Forms Listener, using the serverPort, serverHost and connectMode parameters displayed in the Java console.  Steps to verify this are:

HOWTO : Getting User Responsability on EBS 11i using a Database user (BOLINF or APPS)

Find below the SQL statement to generate an extract with active users together with their active responsibilities.

The statement extracts all the active assignments of responsibilities by using the FND_USER_RESP_GROUPS table which combines DIRECT and INDIRECT responsibilities.

If you need only the DIRECT responsibilities than you also have the option to use the seeded view FND_USER_RESP_GROUPS_DIRECT. If you need only the INDIRECT responsibilities (added by roles – like Application Diagnostics and others) than use the seeded view FND_USER_RESP_GROUPS_INDIRECT.

As always adjust the SQL to cover your needs.

FND_USER fuser
AND fuser.USER_ID = furg.USER_ID
AND (to_char(fuser.END_DATE) is null
OR fuser.END_DATE > sysdate)
AND (to_char(furg.END_DATE) is null
OR furg.END_DATE > sysdate)
, fuser.USER_NAME;

Ramakrishnan & Gehrke Sailors script for Oracle database

The chapter 5 of the  book “Database Management Systems” (Ramakrishnan, Raghu & Johannes Gehrke.) introduces the Sailors data model, a set of three tables named Sailors, Boats and Reserves

In this article, the SQL script that implements the creation of these relational structures is developed and adapted  for Oracle Database. This script is based on the script worked by Hjalmtyr Hafsteinsson for PostgreSQL database.

The script does the following tasks

Cleans Sailors Schema and tablespaces of previous script executions

Creates Sailors_data and Sailors_index  tablespaces

Creates Sailors Schema

Grants roles and privileges to Sailors user

Connects to Sailors user

Creates tables, constraints,  in the Sailors Schema

Populates tables

Adds new columns  and new constraints (that are not part of the original data model)

Creates B-tree indexes for columns that are used in WHERE clause

Cambiar logfile del Listener sin tener que detenerlo

Hay veces que el log del listener crece sin parar y puede llegar a pesar varios GB . Una solucion para esto es detener el listener, truncar el archivo y volver a levantarlo, pero hay veces que las base de datos son 24/7 y esta solucion no sirve .

La solucion que voy a postear es una de las muchas que encontre en este link :

La solución que implemente fue en un Oracle y consistia en crear un nuevo log para el listener por cada mes del año con el siguiente formato :listener_yyyymm.log , asi cada mes del año iba a tener su propio log.

Estos son los pasos :

1) Conectarse con el utilitario LSNRCTL y setear el listener actual (Por lo general el nombre default es LISTENER)


LSNRCTL> set current_listener LISTENER

Current Listener is LISTENER

Paquete LT_CTX_PKG inválido (

El otro día revisando los objetos inválidos del SYS de una BD Oracle me encontré que el paquete LT_CTX_PKG estaba inválido por que había un código no identificado dentro del BODY de este.

Buscando bien por internet me encontré con la solución y lo que hay que hacer por si a alguien se le presenta este problema es ejecutar el wrapped package : owmctxb.plb
Un wrapped package es un paquete codificado y no puede ser leído de forma normal como los *.sql.
Este paquete se encuentra en la ruta : $ORACLE_HOME/rdbms/admin
Entonces ejecutamos este paquete como sys  :
sqlplus “/as sysdba” @$ORACLE_HOME/rdbms/admin/owmctxb.plb

Con esto se arreglaría el problema de invalidez del LT_CTX_PKG .
Ademas esto también arreglaría el problema de invalidez de otros paquetes como el LTADM y el LTUTL también pertenecientes al SYS .

Flashback database Oracle 11gR2 after resize datafile

It is very important to read the documentation before to use the Oracle facility of Flashback Database. Furthermore, you have to take a full rman database backup to be protected against any contingency (bugs or restrictions) of flashback database features.

If you create restore points and after that you do a datafile resizing, then you have to create again a new restore point, because you cannot flashback database across a datafile resize.  For example:


For more information, please read the oracle documentation.

Oracle RAC One Node (nuevo desde Oracle 11gR2 EE)

Oracle Real Application Clusters (RAC) One  node es una nueva opción para  Oracle  Database 11g Release 2 Enterprise Edition. Esta opción proporciona alta disponibilidad mejorada  para las bases de datos de una sola instancia, protegiendo tanto del tiempo de inactividad planificado y no planificado.
Oracle RAC proporciona un nodo:
  • Sistema de alta disponibilidad “always on”  para servicios de base de datos de una sola instancia.
  • Mejor consolidación de servidores de bases de datos
  • Virtualización mejorada
  • Menor costo de desarrollo y plataforma de pruebas para full RAC
FireStats icon Powered by FireStats