Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment [Index block splits]

Refer below on MOS

Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment. [ID 873243.1]

http://www.confio.com/English/Tips/Index_Block_Split.php

# Show all sessions waiting for any lock:

select event,p1,p2,p3 from v$session_wait where wait_time=0 and event='enqueue';

# From 10g a different more descriptive event name exists for the more frequent enqueues and you can query the TX wait event as follows:

select sid,p1raw, p2, p3 from v$session_wait

where wait_time=0 and event='enq: TX - row lock contention';

# Show sessions waiting for a TX lock:

select * from v$lock where type='TX' and request>0;

# Show sessions holding a TX lock:

select * from v$lock where type='TX' and lmode>0;

Oracle EventsTracing

Initialization parameter:
EVENT="\
10210 trace name context forever, level 10:\
10211 trace name context forever, level 10:\
10231 trace name context forever, level 10:\
10232 trace name context forever, level 10"

You can specify almost all EVENT settings at the session level using the ALTER SESSION command or a call to the DBMS_SYSYTEM.SET_EV( ) procedure; doing so

does not require an instance bounce for the EVENT to take effect.

grant alter session to test;
alter session set events '10032 trace name context forever';

GRANT EXECUTE ON DBMS_SYSTEM TO username; --Not recommended to grant system privilege to user
CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;

EXECUTE SYS.dbms_system.set_ev (42, 45529,10046,12,'');
or
EXECUTE SYS.dbms_system.set_sql_trace_in_session (42, 45529, TRUE);


In SPFILE:
------------
ALTER SYSTEM SET event='10235 trace name context forever,
level 2','27072 trace name errorstack level 3' COMMENT='TEST' SCOPE=SPFILE;

ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ; //Remove all events

In MEMORY:
----------------
ALTER SYSTEM SET events='10235 trace name context forever,
level 2:27072 trace name errorstack level 3';

{ alter system set event = | alter session set events [=] }
" trace name context {forever, level | off}"

alter session set events [=] {
"immediate trace name
{ heapdump | blockdump | treedump | controlf | systemstate | buffers } level "
| " trace name errorstack level [; name processstate level ]"
}

{ alter system set event = | alter session set events [=] }
" trace name context {forever, level | off}"

alter session set events [=] {
"immediate trace name
{ heapdump | blockdump | treedump | controlf | systemstate | buffers } level "
| " trace name errorstack level [; name processstate level ]"
}

Script to check which events are set:

declare
lvl number;
begin
for n in 10000..10999 loop
 sys.dbms_system.read_ev(n,lvl);
 if (lvl > 0) then
 dbms_output.put_line('Event: ' || to_char(n) || ', Level: '
                      || to_char(lvl ));
 end if;
end loop;
end;
/

alter session set events '10046 trace name context off';

References:
https://netfiles.uiuc.edu/jstrode/www/orapack/DBMS_SYSTEM.html

When and how to rebuild index?

I would like to put before you when to rebuild an index...

Indexes are to be rebuilt if more than 20% of their records are changed

create table emp ( no number(3), name varchar2(30));
create index emp_ind on emp(no);
insert some records into the table ( eg 10k records)
delete records from table nearly 10%


execution of below command enters 1 record in index_stats view ( validates the structure of the index)

analyze index emp_ind validate structure;

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where
name ='EMP_IND';

if the out put is more than 20, you need to rebuild the index

in your case it will be 10 and u have done 10% deletes on the table, now try to delete another 15% of records from table, this will delete 15% records from index also

again run

analyze index emp_ind validate structure;

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where
name ='EMP_IND';

the result will be 25%
so you need to rebuild the index with below statement,

alter index emp_ind rebuild;

then again try

analyze index emp_ind validate structure;

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where
name ='EMP_IND';

your result is 0

Using autotrace in SQL*Plus

autotrace supports the following options:

• on – Enables all options.
• on explain – Displays returned rows and the explain plan.
• on statistics – Displays returned rows and statistics.
• trace explain – Displays the execution plan for a select statement without actually executing it. (set autotrace trace explain)
• traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics

Prerequisites
The explain plan feature of autotrace requires a plan_table which can be created with $ORACLE_HOME/rdbms/admin/utlxplan.sql

The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

An Oracle installation comes with $ORACLE_HOME/sqlplus/admin/plustrce.sql which installs the role plustrace. plustrace is granted those select rights. If now plustrace is granted to a user, he will then be able to turn autotrace on. Alternatively, plustrace can be granted to public.

SQL> set timing on
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Solution:
To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema

Connect as system/manager

SQL> @?\sqlplus\admin\plustrce.sql;
SQL> grant plustrace to scott;
SQL> conn scott/tiger@sample;
SQL> @?/rdbms/admin/utlxplan.sql;
SQL> set autotrace on explain
SQL> select sysdate from dual;

SYSDATE
---------
22-AUG-08

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


SQL> set autotrace on statistics;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Column Statistics and Histograms

When gathering statistics on a table, DBMS_STATS gathers information about the data distribution of the columns within the table. The most basic information about the data distribution is the maximum value and minimum value of the column. However, this level of statistics may be insufficient for the optimizer's needs if the data within the column is skewed. For skewed data distributions, histograms can also be created as part of the column statistics to describe the data distribution of a given column.

Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedures. Oracle Corporation recommends setting the METHOD_OPT to FOR ALL COLUMNS SIZE AUTO. With this setting, Oracle automatically determines which columns require histograms and the number of buckets (size) of each histogram. You can also manually specify which columns should have histograms and the size of each histogram.

Height-Balanced Histograms

In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall.

Consider a column C with values between 1 and 100 and a histogram with 10 buckets. If the data in C is uniformly distributed, then the histogram looks similar to Figure 14-1, where the numbers are the endpoint values.


The number of rows in each bucket is one tenth the total number of rows in the table. Four-tenths of the rows have values that are between 60 and 100 in this example of uniform distribution.

If the data is not uniformly distributed,in this case, most of the rows have the value 5 for the column. Only 1/10 of the rows have values between 60 and 100.

Height-balanced histograms can be viewed using the *TAB_HISTOGRAMS tables, as shown below:

Viewing Height-Balanced Histogram Statistics

BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand');
END;
/

SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'INVENTORIES' AND column_name = 'QUANTITY_ON_HAND';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND 237 10 HEIGHT BALANCED

SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'INVENTORIES' and column_name = 'QUANTITY_ON_HAND'
ORDER BY endpoint_number;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 0
1 27
2 42
3 57
4 74
5 98
6 123
7 149
8 175
9 202
10 353

In the query output, one row corresponds to one bucket in the histogram.

Frequency Histograms

In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. Frequency histograms can be viewed using the *TAB_HISTOGRAMS tables, as below.

Viewing Frequency Histogram Statistics

BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id');
END;
/

SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'INVENTORIES' AND column_name = 'WAREHOUSE_ID';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
WAREHOUSE_ID 9 9 FREQUENCY

SELECT endpoint_number, endpoint_value
FROM USER_HISTOGRAMS
WHERE table_name = 'INVENTORIES' and column_name = 'WAREHOUSE_ID'
ORDER BY endpoint_number;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
36 1
213 2
261 3
370 4
484 5
692 6
798 7
984 8
1112 9

An Overview of Workload Repository

The AWR adds persistence to the statistics collection facility. On a regular basis, MMON process transfers cumulative statistics in memory to the workload repository tables on disk. This ensures that statistics can survive through instance crashes, and aren’t lost when they are replaced by newer statistics.
Workload repository also ensures that historical data will be available for baseline comparisons. Before AWR, collecting this type of data required manual collection and management using Statspack or custom code. Workload repository data is owned by SYS user and is stored in SYSAUX tablespace. The data is stored in a collection of tables, all of which are named beginning with WR.

SQL> select table_name from dba_tables where tablespace_name=’SYSAUX’
and substr(table_name, 1, 2) = ‘WR’
and rownum<=20 order by 1;

Once in repository, the statistics can be accessed using data dictionary views.

Enabling AWR
To enable AWR, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. If it is set to BASIC, AWR statistics will not be gathered automatically, but they can be gathered manually using procedures in the built-in DBMS_WORKLOAD_REPOSITORY package. Note that manually gathered statistics will not be as complete as statistics gathered automatically through AWR. The workload repository is created automatically at the database creation time. No manual action is required.

AWR Space Considerations
A rough guideline is that an average system with an average of 10 concurrent active sessions will generate 200MB to 300MB of AWR data. This estimate assumes the default retention period of 7 days. The space used is determined by the number of active sessions, the snapshot interval, and the retention period. Space consumption can be reduced by either increasing the snapshot interval (resulting in less snapshots) or decreasing the retention period. Technically, you can also decrease your active sessions, but undoubtedly your users would not appreciate it. By reducing the available statistics, the accuracy and validity of the following components may be reduced as well:

• ADDM
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

It is the responsibility of MMON process to purge data from repository when it has reached the end of the retention period.

Active Session History
In order to provide statistics on current session activity, Oracle 10g has introduced ASH.
Sizing ASH
ASH is actually a FIFO buffer in memory that collects statistics on current session activity. These statistics are gathered by extracting sampled from V$SESSION every second. Because this kind of frequent gathering could quickly overwhelm the system, ASH continually ages out old statistics to make room for new ones. ASH resides in SGA and its size is fixed for the lifetime of the instance. Its size is calculated by using: The lesser of:

• Total number of CPUs * 2MB of memory
• 5 percent of shared pool size

Therefore, two ways to increase the ASH buffer size:
Increase number of CPUs
Increase the shared pool size ASH Statistics

The following types of data are sampled by ASH:
SQL_ID
SID
Client ID, Service ID
Program, module, action
Object, file, block
Wait event number, actual wait time (if session is waiting) NOTE: SQL_ID is a hash value that uniquely identifies a SQL statement in the database. SQL_ID is new to 10g ASH Views

The statistics in ASH can be viewed using the V$ACTIVE_SESSION_HISTORY fixed view. ASH and AWR Because the data in ASH represents a unique set of statistics, Oracle captures some of the ASH statistics to the workload repository for persistent storage. This process is handled in two ways:

• Every 30 minutes, MMON process flushed ASH buffer of all data. In the process, it filters some of the data into the AWR. Due to the high volume of data, MMON process doesn’t filter all of the ASH data into AWR.

• If the ASH buffer fills in less than 30 minutes, MMNL (Memory Monitor Light) process will flush out a portion of the buffer (to make room for new statistics) and filter a portion of data to the AWR. Using AWR The primary interface for AWR is through Oracle EM Database Control. The link to access AWR can be found in Administration page. Under Workload, click Workload Repository link. From this page, you can manage AWR settings and snapshots. From this page, you can manage AWR settings and snapshots.

Oracle also provides DBMS_WORKLOAD_REPOSITORY package. Procedures in this package include:
CREATE_SNAPSHOT -> create manual snapshots
DROP_SNAPSHOT_RANGE ->Drops a range of snapshots at once
CREATE_BASELINE -> Creates a single baseline
DROP_BASELINE -> Drops a single baseline
MODIFY_SNAPSHOT_SETTINGS -> Changes the RETENTION and INTERVAL settings AWR Snapshots AWR collects performance statistics by taking snapshots of the system at regular intervals. Using Snapshots The snapshot pulls information from fixed tables that hold performance statistics in memory. By default, AWR generates performance data snapshots once every hour. This is known as snapshot interval. It also retains the snapshot statistics for seven days before automatically purging them. This is known as retention period. The data from these snapshots is analyzed by the ADDM for problem-detection and self-tuning.
To view, the current AWR settings, you can use the DBA_HIST_WR_CONTROL view, as shown here:
SQL> select snap_interval, retention from dba_hist_wr_control;

Each snapshot is assigned a unique snapshot ID, which is a sequence number guaranteed to be unique within the repository. The only exception to this is when using RAC. In an RAC environment, AWR snapshots will query every node within the cluster. In this situation, the snapshots for all nodes will share a snapshot ID. Instead they can be differentiated by the instance ID.

Creating Snapshots
To create a snapshot manually,

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;

Modifying Snapshot Frequency
To make changes, use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.

Ex:
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;

Dropping Snapshots
Exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 316, HIGH_SNAP_ID => 320);

NOTE: This procedure can also be used to drop individual snapshots by using the same snapshot ID for both LOW_SNAP_ID and HIGH_SNAP_ID parameters.

AWR Baselines
A baseline is defined as a pair of snapshots that denote a significant workload period. This baseline can be retained for comparison to current system performance.

Using Baselines
Baselines can also be used to define threshold settings for Oracle’s server-generated alerts facility. AWR baselines also make an excellent tool for application performance and scalability testing.

Creating Baselines
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
START_SNAP_ID => 42, END_SNAP_ID => 43,
BASELINE_NAME => ‘REPORTS’);
END;

Dropping Baselines
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(‘REPORTS’, FALSE);

DROP_BASELINE procedure parameters
BASELINE_NAME  Name of the baseline to be dropped
CASCADE  Boolean to determine whether associated snapshots will be dropped
DBID  Optional database ID

Using AWR Views
DBA_HIST_ACTIVE_SESS_HISTORY -> displays the session statistics gathered from ASH
DBA_HIST_BASELINE -> displays information on baselines in the repository
DBA_HIST_DATABASE_INSTANCE -> displays database environment data
DBA_HIST_SQL_PLAN -> displays SQL execution path data
DBA_HIST_WR_CONTROL -> displays current AWR settings
DBA_HIST_SNAPSHOT -> displays information regarding snapshots stored in AWR

SQL> select snap_id, begin_interval_time, end_interval_time from
dba_hist_snapshot order by 1;

Using AWR Reports
Oracle offers a standard summary report that can be run at any time against the statistics stored in AWR. This report provides an analysis of system performance over a specified period of time. This report is run through one of the two SQL*Plus scripts:
awrrpt.sql, which generates text file report
awrrpti.sql, which generates an HTML version of report

Granting Privileges needed to use AWR
GRANT SELECT ON SYS.V_$DATABASE TO ...
GRANT SELECT ON SYS.V_$INSTANCE TO ...
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ...
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ...
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ...
GRANT ADVISOR TO ...

PL/SQL Performance init parameters

Configuring PL/SQL for better Performance:
PLSQL_WARNING
PLSQL_DEBUG
PLSQL_OPTIMIZE_MODE
PLSQL_CODE_TYPE: specifies whether to compile PL/SQL code into default interpreted byte code or native machine code.

Create Stat table, Export Stats, and Set table stat

Connect as sysdba and run this procedure to export the statistics to user defined statistics table i.e STATS_EMP in this example.

SQL> exec dbms_stats.export_table_stats ( -
> ownname => 'SCOTT', -
> tabname => 'EMP', -
> partname => NULL, -
> stattab => 'STATS_EMP', -
> statid => NULL, -
> cascade => TRUE, -
> statown => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
SQL> select * from STATS_EMP;

Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

DBMS_STATS.SET_TABLE_STATS
exec DBMS_STATS.SET_TABLE_STATS ( -
ownname => 'SCOTT', -
tabname => 'DEPT', -
stattab => 'STATS_DEPT', -
statid => NULL, -
numrows => NULL, -
numblks => NULL, -
avgrlen => NULL, -
flags => NULL, -
statown => 'SCOTT');

SQL> @/database/test1/scripts/set_table_stats.sql

PL/SQL procedure successfully completed.

[oracle@vinay scripts]$ export ORACLE_SID=test1
[oracle@vinay scripts]$ ./gather_tab_stat.sh
Table: DEPT Completed

SQL> select count(*) from stats_dept;

COUNT(*)
----------
1

Using Explain Plan

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

Examining an explain plan lets you look for throw-away in cases such as the following:

• Full scans
• Unselective range scans
• Late predicate filters
• Wrong join order
• Late filter operations

The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

Creating a PLAN_TABLE
CONNECT HR/your_password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

Running EXPLAIN PLAN
To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR
SELECT last_name FROM employees;

Displaying PLAN_TABLE Output

UTLXPLS.SQL
This script displays the plan table output for serial processing.
EXPLAIN PLAN Output

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


UTLXPLP.SQL
This script displays the plan table output including parallel execution columns.

DBMS_XPLAN.DISPLAY procedure
This procedure accepts options for displaying the plan table output. You can specify:
A plan table name if you are using a table different than PLAN_TABLE
A statement Id if you have set a statement Id with the EXPLAIN PLAN
A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

Sizing Redo Log Files

The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. The following query will recommend optimal log file size in megabytes:
SQL> select optimal_logfile_size from v$instance_recovery;

[OR]

Using OEM Database Control: Redo Log File Size Advisor

Redo Log Groups screen --> Actions drop-down list on right, select Sizing Advice and click Go.

NOTE: In Oracle 10g the fast_start_mttr_target parameter should be set to non-zero to enable redo log advisor.

Table Statistics

How to check for tables which have stale statistics:
select owner,table_name,last_analyzed,sample_size from dba_tables;

Ex: SQL> select owner,table_name,last_analyzed,sample_size from
dba_tables where table_name in ('EMP','DEPT');

How to gather statistics for a schema:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
estimate_percent=>20, cascade=>TRUE);

How to gather statistics for a particular table:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>20, cascade=>TRUE);
( or )
Ex: exec dbms_stats.gather_table_stats(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>NULL,
method_opt=>'FOR ALL INDEXED COLUMNS', degree=>5,
GRANULARITY => 'ALL', CASCADE=>TRUE);

Note: The above gather statistics command gives a more detailed
statistics

To check when a particular table was last analyzed:
Ex: SQL> select table_name, column_name, num_distinct nd,
num_nulls nn, density, last_analyzed from dba_tab_columns
where table_name in ( 'EMP' ) order by table_name, column_name;

GATHER_TABLE_STATS Procedure Parameters

ownname Schema of table to analyze

tabname Name of table

partname Name of partition

estimate_percent Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

block_sample Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt Accepts either of the following options, or both in combination:

* FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
* FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | (extension)

- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
- column_name : name of a column
- extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

degree Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

granularity Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL' - gathers all (subpartition, partition, and global) statistics

'APPROX_GLOBAL AND PARTITION' - similar to 'GLOBAL AND PARTITION' but in this case the global statistics are aggregated from partition level statistics. This option will aggregate all statistics except the number of distinct values for columns and number of distinct keys of indexes. The existing histograms of the columns at the table level are also aggregated.Global statistics are gathered if partname is NULL or if the aggregation cannot be performed (for example, if statistics for one of the partitions is missing).

'AUTO'- determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - gathers global statistics

'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics.

cascade Gathers statistics on the indexes for this table. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not. This is the default. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

stattab User statistics table identifier describing where to save the current statistics

statid Identifier (optional) to associate with these statistics within stattab

statown Schema containing stattab (if different than ownname)

no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force Gather statistics of table even if it is locked

Ex:
exec dbms_stats.gather_table_stats ( -
ownname => OWNER', -
tabname => 'TABLE_NAME, -
estimate_percent => NULL, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
degree => NULL, -
granularity => 'DEFAULT', -
cascade => TRUE, -
no_invalidate => TRUE);

Create User Statistics Table:
exec DBMS_STATS.CREATE_STAT_TABLE( -
ownname => 'SCOTT', -
stattab => 'STATS_EMP', -
tblspace => 'STATTASPC')

desc STATS_EMP

How to read a trace file (10046 trace file)

Event 10046 Raw Output:
~~~~~~~~~~~~~~~~~~~~~~
----------------------------------------------------------------------------
APPNAME mod='%s' mh=%lu act='%s' ah=%lu
----------------------------------------------------------------------------
APPNAME is the application name setting. This only applies to Oracle 7.2
onwards. This can be set by using the DBMS_APPLICATION_INFO package.
See Note:30366.1.

mod  Module name
mh  Module hash value
act  Action
ah  Action hash value

----------------------------------------------------------------------------
PARSING IN CURSOR #%d; len=X dep=X uid=X oct=X lid=X tim=X hv=X ad='X'
END OF STMT
----------------------------------------------------------------------------
CURSOR Cursor Number
len  Length of SQL statement
dep  PGA Depth
uid  Schema user id of parsing user
oct  Oracle Command Type
lid  Privilege user id
tim  Timestamp (100th's of a second)
Can be used to determine times between points in the trace file.
hv  Hash ID
ad  SQLTEXT address (See V$SQLTEXT)

statement The actual SQL statement being parsed

----------------------------------------------------------------------------
PARSE ERROR #%d:len=%ld dep=%d uid=%ld oct=%d lid=%ld tim=%lu err=%d statement...
----------------------------------------------------------------------------

PARSE ERROR In Oracle 7.2+ we report parse errors.

len  length of SQL statement
dep  PGA Depth
uid  User ID
oct  Oracle Command Type (if known)
lid  Privilege user id
tim   Timestamp
err  Error reported

statement The SQL statement that errored. If this contains a password
the statement is truncated as indicated by '...' at the end.

----------------------------------------------------------------------------
PARSE #%d :c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC  #%d:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
FETCH #%d:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
UNMAP #%d:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
----------------------------------------------------------------------------
- OPERATIONS:

PARSE Parse a statement
EXEC  Execute a pre-parsed statement
FETCH Fetch rows from a cursor
UNMAP If the cursor uses a temporary table then when the cursor is
closed you see an UNMAP when we  free up the temporary table
locks. (ie: free the lock, delete the state object, free the
temp segment)
In tkprof UNMAP stats get added to the EXECUTE statistics.
SORT UNMAP
As above but for OS file sorts or TEMP table segments.

c CPU time (100th's of a second)
e Elapsed time (100th's of a second)
p Number of physical reads
cr Number of buffers gotten for CR reads
cu Number of buffers gotten in current mode
mis Cursor missed in the cache
r Number of rows processed
dep Recursive call depth (0 = user SQL, >0 = recursive)
og Optimizer goal:
KKOSHARW 1  All_Rows
KKOSHFRW 2  First_Rows
KKOSHRUL 3  Rule
KKOSHCHO 4  Choose
tim Timestamp (large number in 100th's of a second)
Use this to determine the time between any 2 operations.

----------------------------------------------------------------------------
ERROR #%d:err=%d tim=%lu
----------------------------------------------------------------------------
SQL Error shown after an execution or fetch error.

err  Oracle error code at the top of the stack.
tim  Timestamp

----------------------------------------------------------------------------
STAT #%d id=N cnt=0
----------------------------------------------------------------------------

STAT lines report explain plan statistics for the numbered %d.

%d  Cursor which the statistics apply to
id  Line of the explain plan the row count applies to (starts
at line 1). This is effectively the row source row count
for all row sources in the execution tree.
cnt  Number of rows for this row source.


----------------------------------------------------------------------------
XCTEND rlbk=%d rd_only=%d
----------------------------------------------------------------------------
XCTEND is a transaction end marker.

rlbk 1 if a rollback was performed, 0 if no rollback  (commit)
rd_only 1 if transaction was read only. 0 is changes occurred.

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

==========================================================
Special output option from non default 10046 trace levels:
==========================================================

----------------------------------------------------------------------------
BINDS #%d:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08
bfp=088058a8 bln=22 avl=03 flg=0d
value=1379
----------------------------------------------------------------------------

BIND variables bound to a cursor.
This is only reported if Event:10046 is used with the level 4 bit on
(00000100)

bind N The bind position being bound.
dty  Data type. See Glossary:DataTypes
mxl  Maximum length of the bind variable (private max len in paren)
mal  Array length
scl  Scale
pre  Precision
oacflg See uacdef.h.
UACFIND  0x01   true if using indicators
UACFALN  0x02   true if using length vector
UACFRCP  0x04   true if returning retcodes
UACFBBV  0x08   true if bind by value
UACFBPW  0x10   true if piecewise bind
UACFBLP  0x20   true if blank pad/strip
UACFARR  0x40   true if vector object
UACFIGN  0x80   Ignore this bind/define.
  Used for delayed upi
bfp  Bind address
bln  Bind buffer length
avl  Actual value length (array length too)
flg  See kxs.h
KXSBFBBV 0x01  Bound By Value
KXSBFBBR 0x02  Bound By Reference
KXSBFRBD 0x04  ReBounD
KXSBFARR 0x08  object is an ARRay
KXSBFLNG 0x10  object is LoNG
KXSBFOST 0x20 Out SeT:  used to indicate pl/sql out
 variable set
KXSBFBUC 0x40  Bind used in child cursor
KXSBFPBN 0x80 Parent bind variable

value The actual value of the bind variable.
Numbers show the numeric value, strings show the string
etc...

It is also possible to see "bind 6: (No oacdef for this bind)" if no
separate bind buffer exists.

----------------------------------------------------------------------------
WAIT #%d: nam=eventname ela=0 p1=0 p2=0 p3=0
----------------------------------------------------------------------------

WAIT An event that we waited for.
This is only reported if Event:10046 is used with
the level 8 bit on (00001000)

nam  What is being waited for - See WaitEvents
ela  Elapsed time for the operation
p1  P1 for the given wait event
p2  P2 for the given wait event
p3  P3 for the given wait event
For p1-p3 see the relevent wait-event article.

Example (Full Table Scan):
WAIT #1: nam=db file scattered read; ela= 5 p1=4 p2=1435 p3=25

WAITing under CURSOR no 1
for db file scattered read
We waited 0.05 seconds
For a read of:  File 4, start block 1435, for 25 Oracle blocks

Example (Index Scan):
WAIT #1: nam=db file sequential read; ela= 4 p1=4 p2=1224 p3=1

WAITing under CURSOR no 1
for db file sequential read
We waited 0.04 seconds for a single block read (p3=1)
from file 4, block 1224

Reference:
http://tonguc.yilmaz.googlepages.com/ev10046.txt - the
above data is completely from the link I have given
here. You can refer to above link for a clear formatted
one if the above data is not clear.

How to generate a trace

To generate a trace in the current session:
10046 trace:
alter session set events '10046 trace name context forever, level 8';
(0r)
alter session set events '10046 trace name context forever, level 12';

Note: 12 will trace wait_events and bind variables

10053 trace:
alter session set events '10053 trace name context forever';
(or)
alter session set events '10053 trace name context forever, level 1' ;

Note: To generate a trace in your current session you will need privilege to set trace for yourself. In case the above commands doesn't work please check if you have privileges to set trace for yourself.

To generate a trace for a particular session:
10046 trace:
Ex: EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,8,'');
(or)
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');

10053 trace:
Ex: EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10053,1,'');

Note: You can get the SID, SERIAL# from v$session

To turn-off trace in your current session that you have put trace on:
10046 trace:
alter session set events '10046 trace name context off';

10053 trace:
alter session set events '10053 trace name context off';

To turn-off trace for a particular session that you have put trace on:
10046 trace:
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');

10053 trace:
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10053,0,'');

Note: The SID, SERIAL# are the values of a particular session for which you have put the trace on.

References:
http://oradbatips.blogspot.com/2007/03/tip-38-tracing-session-with-10046-event.html
some commands have been taken from above link and some commands are that I have used at my work.

Using Statspack Performance Monitoring Tool

Statspack was used before Oracle 10g. In Oracle 10g version AWR and ADDM are used to monitor the performance of database.

The default level of collection, level 5, is adequate for most applications. At this level the normal performance statistics are captured along with the high-resource-usage SQL statements. Parameters can be used to set the limits for the SQL statement collection, which will be highly system dependent.

It is also possible to capture statistics from an individual session as part of a snapshot by using the i_session_id parameter to the procedure. The example below will capture session level statistics for the session with a session id (Oracle sid) of 32.
SQL> execute statspack.snap(i_session_id=>32);

There are currently five different levels of statspack snapshots, defined as follows in the table stats$level_description (9i version):


SNAP_LEVEL DESCRIPTION
---------- -----------------------------------------------------------
0 This level captures general statistics, including rollback
segment, row cache, SGA, system events, background events,
session events, system statistics, wait statistics, lock
statistics, and Latch information

5 This level includes capturing high resource usage SQL
Statements, along with all data captured by lower levels

6 This level includes capturing SQL plan and SQL plan usage
information for high resource usage SQL Statements, along
with all data captured by lower levels

7 This level captures segment level statistics, including
logical and physical reads, row lock, itl and buffer busy
waits, along with all data captured by lower levels

10 This level includes capturing Child Latch statistics, along
with all data captured by lower levels

Using Statspack: Performance monitoring tool
----------------------------------------------
1) Run the scripts to install statspack
SQL> @?/rdbms/admin/spcreate.sql

2) Enter password for perfstat, enter default_tablespace (USERS), enter temporary tablespace (TEMP)

3) conn perfstat/perfstat

4) execute statspack.snap

5) Again repeat step 4 after sometime to get another snap

6) @?/rdbms/admin/spreport.sql

7) Enter a value for begin_snap and end_snap

8) Give a name for the report

9) host vi .lst to view the report
Resolving Your Wait Events

DB File Scattered Read
This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.

DB File Sequential Read
This event generally indicates a single block read (an index read, for example). A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is normal for this number to be large for a high-transaction, well-tuned system, but it can indicate problems in some circumstances. You should correlate this wait statistic with other known issues within the Statspack report, such as inefficient SQL. Check to ensure that index scans are necessary, and check join orders for multiple table joins. The DB_CACHE_SIZE will also be a determining factor in how often these waits show up. Problematic hash-area joins should show up in the PGA memory, but they're also memory hogs that could cause high wait numbers for sequential reads. They can also show up as direct path read/write waits.

Free Buffer
This indicates your system is waiting for a buffer in memory, because none is currently available. Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, if all your SQL is tuned. Free buffer waits could also indicate that unselective SQL is causing data to flood the buffer cache with index blocks, leaving none for this particular statement that is waiting for the system to process. This normally indicates that there is a substantial amount of DML (insert/update/delete) being done and that the Database Writer (DBWR) is not writing quickly enough; the buffer cache could be full of multiple versions of the same buffer, causing great inefficiency. To address this, you may want to consider accelerating incremental checkpointing, using more DBWR processes, or increasing the number of physical disks.

Buffer Busy
This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache. Buffer busy waits should not be greater than 1 percent. Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out if the wait is on a segment header. If this is the case, increase the freelist groups or increase the pctused to pctfree gap. If the wait is on an undo header, you can address this by adding rollback segments; if it's on an undo block, you need to reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE. If the wait is on a data block, you can move data to another block to avoid this hot block, increase the freelists on the table, or use Locally Managed Tablespaces (LMTs). If it's on an index block, you should rebuild the index, partition the index, or use a reverse key index. To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as "hot." When a DML (insert/update/ delete) occurs, Oracle Database writes information into the block, including all users who are "interested" in the state of the block (Interested Transaction List, ITL). To decrease waits in this area, you can increase the initrans, which will create the space in the block to allow multiple ITL slots. You can also increase the pctfree on the table where this block exists (this writes the ITL information up to the number specified by maxtrans, when there are not enough slots built with the initrans that is specified).

Uninstall Statspack:
---------------------
1) Drop the user perfstat
drop user perfstat cascade; [cascade will drop all objects in user's schema]

2) @?/rdbms/admin/spdrop.sql;