Generating Automatic Workload Repository Reports:

An AWR Report shows data captured between two snapshots (or two points in time).The AWR reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections.

You can generate AWR reports using Oracle Enterprise Manager and by running SQL scripts, as described in the following sections:

A. Generating an AWR Report

B. Generating an Oracle RAC AWR Report

C. Generating an AWR Report on a Specific Database Instance

D. Generating an Oracle RAC AWR Report on Specific Database Instance

E. Generating an AWR Report for a SQL Statement

F. Generating an AWR Report for a SQL Statement on a Specific Database Instance

To run these scripts, you must be granted the DBA role.

A. Generating an AWR Report

At the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_26142_26164.html

B. Generating an Oracle RAC AWR Report

At the SQL prompt, enter:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_RAC_26142_26164.html

C. Generating an AWR Report on a Specific Database Instance

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

  1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Using 1328410674 for database Id

Enter value for inst_num: 1

Using 1 for instance number

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_26142_26164.html

D. Generating an Oracle RAC AWR Report on Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

* 1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Enter value for dbid: 1328410674

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_RAC_26142_26164.html

E. Generating an AWR Report for a SQL Statement

SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for sql_id: 2b064udsjh1l

Enter value for report_name: awrrpt_1_26142_26164.html

F. Generating an AWR Report for a SQL Statment on a Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

  1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Enter value for dbid: 1328410674

Enter value for inst_num: 1

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for sql_id: 2b064udsjh1l

Enter value for report_name: awrrpt_1_26142_26164.html

Generating Automatic Workload Repository Compare Periods Reports:

While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time priods.

A. Generating an AWR Compare Periods Report

B. Generating an Oracle RAC AWR Compare Periods Report

C. Generating an AWR Compare Periods Report on a Specific Database Instance

D. Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instance

A. Generating an AWR Compare Periods Report

SQL>@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Specify the number of days for which you want to list snapshot IDs in the second time period.

Enter value for num_days2: 1

Enter value for begin_snap2: 26156

Enter value for end_snap2: 26164

Enter value for report_name: awrdiff_1_26142_26164.html

B. Generating an Oracle RAC AWR Compare Periods Report

SQL>@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Specify the number of days for which you want to list snapshot IDs in the second time period.

Enter value for num_days2: 1

Enter value for begin_snap2: 26156

Enter value for end_snap2: 26164

Enter value for report_name: awrracdiff_1_26142_26164.html

C. Generating an AWR Compare Periods Report on a Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

Enter value for report_type: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

  1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Database Id and Instance Number for the First Pair of Snapshots

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for dbid: 1328410674

Enter value for inst_num: 1

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for dbid2: 1328410674

Enter value for inst_num2: 2

Enter value for num_days2: 2

Enter value for begin_snap2: 26152

Enter value for end_snap2: 26165

Enter value for report_name: awrdiff_1_26142_26164.html

D. Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql

Enter value for report_type: html

Enter value for dbid: 1328410674

Enter value for instance_numbers_or_all: 1

Enter value for num_days: 2

Enter value for begin_snap: 26138

Enter value for end_snap: 26165

Enter value for dbid2: 1328410674

Enter value for instance_numbers_or_all2: 2

Enter value for num_days2: 2

Enter value for begin_snap: 26138

Enter value for end_snap: 26165

Enter value for report_name: awrracdiff_1_26142_26164.html

enq: TM - contention

The waits on the enq: TM (Table modification) – contention event for the sessions that are waiting to perform insert operations are almost always due to an unindexed foreign key constraint.. This happens when a dependent or child table’s foreign key constraint that references a parent table is missing an index on the associated key. Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. Note that these are full table locks (TM), and not row-level locks (TX)—thus, these locks aren’t restricted to a row but to the entire table.

You can find unindex foreign key constraints with blew script:

SELECT tbl.*,
       'CREATE INDEX IDXFK_' || substr(tbl.column_name, 1, 23) || ' ON ' ||
       tbl.table_name || ' ( ' || tbl.column_name ||
       ' ) tablespace main_index ; '

  FROM (SELECT c.owner,
               c.table_name,
               cc.column_name,
               cc.position column_position
      
          FROM DBA_constraints c, DBA_cons_columns cc
         WHERE c.constraint_name = cc.constraint_name
           and c.OWNER = 'Yuor Username'
           AND c.constraint_type = 'R'
           and c.owner not in ('SYS', 'SYSMAN', 'SYSTEM')
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
          FROM DBA_indexes i, DBA_ind_columns ic
         WHERE i.index_name = ic.index_name) tbl
 ORDER BY table_name;

enq: TX - row lock contention

enq: TX - row lock contention:

This is indicative of a session waiting for a row lock held by another session.

There are several situations of TX enqueue:

In mode 6:

Waits for TX in mode 6 occur when a session is waiting for a row level lock that is already held by another session. This occurs when one application is updating or deleting a row that another session is also trying to update or delete. This will generate a wait event "enq: TX - row lock contention". To solve this particular instance, the session holding the lock must perform a COMMIT or ROLLBACK.

In mode 4:we have 3 reasons:

  1.     Unique key contention:

     In mode 4, a TX wait can occur if there is a potential duplicate in a unique index. When two sessions try to insert the same key value the second session must wait to see if an ORA-001 should be raised. This can cause the "enq: TX - row lock contention" wait event. This wait event can be handled by having the session holding the lock perform a COMMIT or ROLLBACK.

  2.     Foreign Key contention:

If referential constraints have been defined between tables and the foreign key columns in the child table are not indexed, then modifications to the parent table's key cause locks on the child table, and if the foreign key columns are not indexed this can cause contention.

  3.     Bitmap index contention:

The wait event “enq: TX – row lock contention” can also occur in mode 4 when a session is waiting on a shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same index fragment, then the second session must wait for the first session to perform either a COMMIT or a ROLLBACK by waiting for the TX lock in mode 4, otherwise it will generate the “enq: TX – row lock contention” wait event.  This index is suitable for data warehouse environments.

enq: HW - contention                  Configuration

enq: SQ - contention                   Configuration

enq: SS - contention                    Configuration

enq: ST - contention                   Configuration

enq: TM - contention                  Application

enq: TW - contention                  Administrative

enq: TX - allocate ITL entry       Configuration

enq: TX - index contention         Concurrency

enq: TX - row lock contention      Application

enq: TX – contention                    Application

select
           event, 
           p1,
           mod(p1,16)  as "mode"
    from v$active_session_history
    where event like 'enq:%‘;

select        count(*) cnt, 
                 session_id sid,
                 substr(event,1,30) event, 
                 mod(p1,16)  as lm,
                 sql_id,
                 CURRENT_OBJ# || ' ' || object_name obj
               , o.object_type type
               , CURRENT_FILE# file#
               , CURRENT_BLOCK#  block#
               , blocking_session bsid
    from v$active_session_history ash,
         all_objects o
    where
            event  like 'enq: T%'
      and o.object_id (+)= ash.current_obj#
   group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
   order by  count(*);

  select    substr(event,1,30) event, sql_id,
             CURRENT_OBJ# || ' ' || object_name obj
           , o.object_type type
           , CURRENT_FILE# file#
           , CURRENT_BLOCK#  block#
    from v$active_session_history ash,
             ( select a.object_name, 
                      a.object_id,
                      decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
               from all_objects a, all_indexes i where 
               a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
    where
            event  like 'enq: TX%'
      and o.object_id (+)= ash.current_obj#
    order by sample_time;

Find Foreign Keys with no Index

for finding foreign keys with no index you could put Owner and Tablespace name in the script and then view your result:


SELECT tbl.*,
       'CREATE INDEX IDXFK_' || substr(tbl.column_name, 1, 23) || ' ON ' ||
       tbl.table_name || ' ( ' || tbl.column_name ||
       ' ) tablespace main_index ; '

  FROM (SELECT c.owner,
               c.table_name,
               cc.column_name,
               cc.position column_position
     
          FROM DBA_constraints c, DBA_cons_columns cc
         WHERE c.constraint_name = cc.constraint_name
           and c.OWNER = 'Yuor Username'
           AND c.constraint_type = 'R'
           and c.owner not in ('SYS', 'SYSMAN', 'SYSTEM')
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
          FROM DBA_indexes i, DBA_ind_columns ic
         WHERE i.index_name = ic.index_name) tbl
 ORDER BY table_name;