Simple Performance Tuning Methodology

In this post I'd like to show a simple methodology for performance tuning using Wait-Event Analysis.
Logically, the process is very simple:

1. Determine the most significant bottleneck
2. Improve/Fix it
3. Repeat it until the performance is good enough

The question is, how can we find the most significant bottleneck for a specific session or for a specific SQL, or even for the entire instance?

Basically, as process in Oracle can be in one of the following states:

• Consuming CPU - which is usually fine and useful
• Non-Idle Wait event - Waiting for something (lock, latch, I/O)
• For example: whenever a process is reading multi blocks in a single I/O operation (usually in cases of a Full Table Scan or Index Fast Full Scan), Oracle will report on a "db file scattered read" wait event
• In order to get a list of all the Non-Idle wait events, execute the following query:

SELECT name

  FROM v$event_name

 WHERE wait_class <> 'Idle'

• Idle Wait Event - Doesn't do anything, indication that the user session is inactive.
• For example: whenever a server process is waiting for the user client to do something, Oracle will report on a "SQL*Net message from client"
• In order to get a list of all the Idle wait events, execute the following query:

SELECT name

  FROM v$event_name

 WHERE wait_class = 'Idle'

Note: when a process is waiting for something it can also consume CPU, for example, in some cases of latch contentions, there will also be a high CPU consumption (due to the latch spinning).

So how can you measure the top non-idle wait events in the database?
There are severl moethods:

Oracle Dynamic Views:

• V$SYSTEM_EVENT - Provides information on all the wait-events which occured in the instance since-startup. You can get all the non-idle wait events, ordered by the time (in seconds) using the following query:

SELECT event, time_waited / 100 "Seconds Waited"

   FROM v$system_event

   WHERE wait_class <> 'Idle'

 ORDER BY 2 DESC

• V$SESSION_EVENT - Provides information on all the wait-events which occudes in a specific session (relevant only for connected sessions, not historical sessions). You can get all the non-idle wait events for a specific session, ordered by the time (in seconds) using the following query:

SELECT event, time_waited / 100 "Seconds Waited"

  FROM v$session_event

 WHERE wait_class <> 'Idle' AND SID = <SID>

ORDER BY 2 DESC

AWR:

The AWR report also holds lots wait-events related information. However, usually the "Top 5  Timed Foreground Events" section in the beginning of the report will provide most of the important information. Following is a screenshot of this section in one of our Oracle instances:

So how can you measure the overall performance?
The answer is, "DB Time" - a statistic which represents the total amount of time that Oracle spent during CPU processing and waiting for non-idle wait events.
DB Time = CPU Time + Non Idle wait-events

Notes:

1. DB Time doesn't take into account background sessions. It takes into account foreground sessions only.
2. It doesn't take into account idle wait-events.

The DB Time could also be measured in a several ways:

Oracle Dynamic Views:

• V$SYSSTAT - Displays instance-level statistics. You can view the value of the DB Time (since instance startup), using the following query:

SELECT value

  FROM v$sysstat

 WHERE name = 'DB time'

• V$SESSTAT - Displays session-level statistics. You can view the value of the DB Time for a specific session (connected session only), using the following query:

SELECT value

  FROM v$sesstat JOIN v$statname USING (statistic#)

 WHERE name = 'DB time' AND sid= <SID>

AWR:
Obviously, the AWR report also hold information about the DB Time.
In the beginning of the report there's a summary of the DB Time for the AWR report duarion:

As you can see, this AWR report is based on a 2 snapshots which their distance is 1 hour - the first snapshot is taken at 13:00, and the second snapshot is taken at 16:00, therefore the value of "Elapsed" is 1 hour. You may now ask yourself, how is it possible that the value of DB time (3 hours) was higher than the enitre duration of the report (which is 1 hour). Is it possible that during a 1-hour period, Oracle spent 3 hours on cpu and non-idle wait events?!
The answer is: absolutely!
Since the DB Time equals to the total amount of CPU time + non-idle wait events, so in machines where you have more than 1 CPU core (in this particular example there are 4 CPU cores), the DB Tme can definitely be higher than the Elapsed Time.

For example:
If during a 10 second-period, all the 4 CPU cores were fully utilized, then the DB Time value will be 40 for those 10 second-period.

After we determined the most significant Wait-Events, i.e. those which impact the database mostly, how can we reduce the wait time or even totally avoid them?

The answer is that it mainly depends on your experience with performance tuning.
There are over 1000 wait-events in Oracle but the good news are the eventually there's a limited number of common wait events, and this number is much smaller than 1000, so eventually, you will be familiar with most of the common wait-events, and you'll know which actions you should take in order to solved them.
If you're encountering performance issues that are related to a specific wait-event which you are not familiar with, you can always ask our best friend, google :)

Why use Performance Dianostics tools?
Some tools can make our life easier by visualizing the workload of the instance.
Some tools can even display the workload filtered by a specific dimension or a combination of dimensions (e.g. SQL Statment, User, Program, Machine, PDB, Session, etc.).
Having the ability to filter the workload for a set of combinations allows performing advanced performance diagnostics research.
For example: viewing all the wait-events and statistics for all the SQL Statements that were executed by user HR using TOAD under PDB (Pluggable Database) PROD.

OEM ASH Analytics was introduced in OEM 12c and it allows you to perform OLAP operations on the database dimensions which makes the performance tuning process much simpler and faster.
Please note that in order to benefit this feature you must have the Enterprise Edition and also the tuning and diagnostics packs.

Foglight for Oracle also provides the ability to view a list of all the dimensions and drill-down to any combination of dimensions, as you can see in the below screenshot:

Foglight for Oracle comes with a full dimension stack:



Here is an example of how easily you can drill-down to all the SQL Statements that were executed from TOAD by the user SALES:

Conclusions:

1. DB Time is an important measurement which represents the total CPU + Non-Idle wait events of foreground sessions.
2. When a DBA wants to improve the performance, the best approach is to focus on the major/top wait-events and reduce/fix them in order to reduce the total DB time until the performance will be good enough.
3. Some tools (Oracle Enterprise Manager, Dell Foglight) allow visualizing the workload of the entire instance or the activity of a specific dimension/set of dimensions. This can reduce dramatically(!) the time it takes for the DBA identifying and solving these issues.

What's the difference between SYSDBA and SYSASM?

Introduction

ASM was first introduced in 2003 with Oracle 10gR1 and since then it's the recommended storage management solution by Oracle and it's being used as a filesystem and volumn manager.

In the "old" 10g days, we used to administer the ASM instance using the as SYSDBA role.

The Problem
The problem with using SYSDBA is that in many organization there is a clear seperation between the DBA to the ASM administrator -the DBA in some organization doesn't suppose to add disks, alter disk groups, etc.

The Solution
The solution for this problem was introduced in Oracle 11gR1 when Oracle introduced a new role, SYSASM that should be used by the ASM administrators to perform administrative tasks, such as CREATE/DROP/ALTER diskgroup, or startup/shutdown the ASM instance.
The SYSDBA should be used by the ASM for read-only operations such as qureying dynamic views (e.g. V$ASM_DISKGROUP, V$ASM_FILE, V$ASM_OPERATION, etc.)

Although SYSASM was introduced in Oracle 11gR1, SYSDBA role still had (in Oracle 11gR1) full administrative permissions, but every time an administrative command was executed (such as starting the ASM instance), a warning was reported in the ASM alert log file:
"WARNING: Deprecated privilege SYSDBA for command <…>"

Starting from Oracle 11gR2, Oracle enforced the seperation between the DBA to the ASM administrator and if you will try to connect as SYSDBA and perform administrative task you will get an error because administrative tasks can only be performed by ASM Administrators who connect AS SYSASM, as you can see in the following screenshot:

Can you use ORA_ROWSCN to determine when was the last modification of a row?

Introduction:
ORA_ROWSCN is a pseudo column that was introduced in Oracle 10g.
This pseudo column will display the SCN of the last time that a row or a the block (in which the row resides) was modified.
You can later use to SCN_TO_TIMESTAMP function to translate the SCN to a timestamp.

Let's see an exmaple:

SQL> create table test (id number, name varchar2(20));
Table created.

SQL> insert into test values (1, 'ABC');
1 row created.

SQL> insert into test values (2, 'XYZ');
1 row created.

SQL> commit;
Commit complete

SQL> update test set name='DEF' where id=1;

SQL> commit;
Commit complete

SQL> select ora_rowscn,scn_to_timestamp(ora_rowscn),id,name from test;

ORA_ROWSCN     TO_TIMESTAMP(ORA_ROWSCN)                   ID           NAME
-----------------   ---------------------------------------  --       --------
  95752977               24-AUG-15 06.45.33.000000000 PM                   1            DEF
  95752977               24-AUG-15 06.45.33.000000000 PM                   2            XYZ

As you can see, the scn of both rows is the same. This is because the table was created without the ROWDEPENDENCIES clause, which ensures that every row in a table has its own SCN, therefore the ORA_ROWSCN displays the scn of the last time the the block was modified.

Let's create the table but this time with the ROWDEPENDENCIES clause:


SQL> drop table test;
Table dropped.

SQL> create table test (id number, name varchar2(20)) ROWDEPENDENCIES;

Table created.

SQL> insert into test values (1, 'ABC');
1 row created.

SQL> insert into test values (2, 'XYZ');
1 row created.

SQL> commit;
Commit complete

SQL> update test set name='DEF' where id=1;

SQL> commit;
Commit complete

SQL> select ora_rowscn,scn_to_timestamp(ora_rowscn),id,name from test;

ORA_ROWSCN     TO_TIMESTAMP(ORA_ROWSCN)                   ID           NAME
-----------------   ---------------------------------------  ---       --------
  95755953               24-AUG-15 07.01.46.000000000 PM                   1            DEF
  95755923               24-AUG-15 07.00.14.000000000 PM                   2            XYZ


In this case, because the table was created with the ROWDEPENDENCIES clause therefore each row has its own SCN and not the same SCN of the last time that the block was modified.
Does it mean that you can use ORA_ROWSCN to track the times of modifications for the rows in your tables? 

The answer is no, because the SCN_TO_TIMESTAMP information is limited to SCN of the last 5 days, and if you will try to translate SCN which is older than the last 5 days you'll get an error:
SQL> select scn_to_timestamp(78199991) from dual;
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

select scn_to_timestamp(78199991) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number

Conclusions:
ORA_ROWSCN is a nice pseudo-column but you need to be aware of its limitations:

1. Unless you specify the ROWDEPENDENCIES clause in the creation of the table, it will report on the last SCN of the block modification and not the row modification.

1. SCN_TO_TIMESTAMP can translate SCN to Timestamps of the last 5 days only.

The title of this article is "Can you use ORA_ROWSCN to determine the last modification of a row?" 
The answer is: no
You'll have to track the change date yourself. I would recommend using one of the following approaches:

• LogMiner
• Auditing

How to detect gaps in your Data Guard environments

In the OTN Data Guard Forum, a user was trying to understand if there's a gap between the Primary and the Standby database so he executed the following query:

select count(*) from v$archived_log where applied = 'NO'

The output of the query was higher than 3000, my response was that the fact that his query returns more than 3000  doesn't necessarily tell that there's any problem becuase when you query v$archived_log it will always report "NO" for the local archive log destinations, therefore he should check for APPLIED = 'NO' and also standby_dest = 'YES'.

Link to the Discussion:

https://community.oracle.com/thread/3781242?sr=stream

I decided to enhance the query even more so it will dipslay per each standby archive log destinaion the following information:

• INST_ID - The Instance ID, relevant for RAC with Data Guard configurations
• DEST_ID - The ID of the Archive Log Destination
• Status - Will indicate whether the standby destination status is valid or not
• Destination - The name of the Archive Log Destination
• Applied_Gap - The applied gap between the Primary and the Standby
• Received_Gap - The received gap between the Primary and the Standby
• Last_Received_Seq - Last archive log received in the standby destination
• Last_Applied_Seq - Last archive log applied in the standby destination

The following query should always be executed on the Primary database:

select ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq
            from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)
               from v$archived_log val, v$database vdb
              where     val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),
            0)
          "applied_gap",
         (SELECT MAX (sequence#) highiest_seq
            from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                 AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)
               from v$archived_log val, v$database vdb
              where     val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'),
            0)
          "received_gap",
       NVL (
          (SELECT MAX (sequence#)
             from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'),
          0)
          "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)
             from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),
          0)
          "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar

Example of the SQL output:

I executed the query in one of our data guard environments.
As you can see from the example output, the sequence# of the last archive log that was sent to the standby destination is 9699 and the last one that was applied is 9698, the applied gap is 1 which is definitely OK because I'm working with a standby redo log therefore the last sequence# should always be the standby redolog that Oracle is currently writing to.

Bottom line:
As long as the applied and the received gap are lower than 2 the it means that there are no gaps in your Data Guard environment.

DBCA and DBUA are unable to start in Oracle 12.1.0.2 on IBM AIX

Problem Description:

Today I performed a new installation of Oracle 12.1.0.2 on IBM AIX 7.1 in a single instance environment. After a successful Oracle Software instllation I tried to execute "dbca" utility (Database Configuration Assistant) in order to create the database and I recevied the following error:

$ ./dbca
UnsatisfiedLinkError exception loading native library: njni12
java.lang.UnsatisfiedLinkError: njni12 (rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
from module /oravl01/oracle/app/product/12.1.0.2/dbhome_1/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
from module /oravl01/oracle/app/product/12.1.0.2/dbhome_1/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-002 fatal error: exiting.)
Exception in thread "main" java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.get(Ljava/lang/String;)Ljava/lang/String;
at oracle.net.config.Config.getNetDir(Unknown Source)
at oracle.net.config.Config.initConfig(Unknown Source)
at oracle.net.config.Config.<init>(Unknown Source)
at oracle.sysman.assistants.util.NetworkUtils.<init>(NetworkUtils.java:298)
at oracle.sysman.assistants.util.step.StepContext.<init>(StepContext.java:388)
at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:1055)
at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:1048)
at oracle.sysman.assistants.dbca.wizard.UIHost.<init>(UIHost.java:241)
at oracle.sysman.assistants.dbca.wizard.InteractiveHost.getInstance(InteractiveHost.java:78)
at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:208)
at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:121)
at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:226)
$ smit aio
Warning: Missing charsets in String to FontSet conversion
Warning: Cannot convert string "-dt-interface system-medium-r-normal-l*-*-*-*-*-*-*-*" to type FontSet
$

 After an investigation in MOS (My Oracle Support) I found the following article: 

AIX 12.1.0.2 Installation Fails with "rtld: 0712-001 Symbol CreateIoCompletionPort was referenced " (Doc ID 1949184.1)

What I found in the article is that this issue is a known issue for Oracle 12.1.0.2 in IBM AIX on POWER Systems (64-bit).

Solution:

The solution, as described in Doc ID 1949184.1 is actually a very simple one, all you need to do is enable to IOCP (I/O Completion Ports). The steps to do that are:

1.

Log in as root and run the following command:
# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.

2. 
Change configured state at system restart from Defined to Available.
Restart the machine

3.
Run the lsdev command to confirm the IOCP status is set to Available:

$ lsdev | grep iocp
iocp0 Available I/O Completion Ports

4.
Relink Oracle Home. In order to do that, connect with the Oracle software owner user and then:

1. Set the ORACLE_HOME environment variable
2. Execute $ORACLE_HOME/bin/relink all

5. 
That's it ! Now the "dbca" and "dbua" runs successfuly :)