In this post I will explore the common causes of performance issues in database , optimizer statistics , table/index statistics  and table maintenance

Common Causes of Performance issues in Database:

• Database Design : Poor system performance usually results from a poor database design. Consider partitioning, creating materialized views, proper indexes, etc.

• Poorly written  SQL : In about 80% of the cases, this is the cause of the slowness of the queries and load operations.

• Memory : Improper shared pool, buffer cache, log buffer sizes, can consider pining large objects to memory or creating stored outlines.

• Disk I/O : Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc

• Database Contention: Study database locks, latches and wait events carefully and eliminate where possible.

Optimizer Statistics

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:

• Table statistics
  • Number of rows
  • Number of blocks
  • Average row length

• Column statistics
  • Number of Distinct values in column
  • Number of Nulls in column
  • Data distribution

• Index statistics
  • Number of leaf blocks
  • Levels
  • Clustering factors

• System statistics
  • I/O Performance utilization
  • CPU performance and utilization

Statistics on tables, indexes, and columns are stored in the oracle data dictionary:

• DBA_TABLES
• DBA_OBJECT_TABLES
• DBA_TAB_STATISTICS
• DBA_TAB_COL_STATISTICS
• DBA_TAB_HISTOGRAMS
• DBA_INDEXES
• DBA_IND_STATISTICS
• DBA_CLUSTERS
• DBA_TAB_PARTITIONS
• DBA_TAB_SUBPARTITIONS
• DBA_IND_PARTITIONS
• DBA_IND_SUBPARTITIONS
• DBA_PART_COL_STATISTICS
• DBA_PART_HISTOGRAMS
• DBA_SUBPART_COL_STATISTICS
• DBA_SUBPART_HISTOGRAMS

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

• Rule Based Optimizer (RBO) – This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favored by Oracle and will be de-supported in future releases.

• Cost Based Optimizer (CBO) – This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

Table / Index Statistics

For optimizer to choose best execution plan, it is necessary to maintain up-to-date table level and index statistics.

Check Statistics

To check whether we have latest table / index stats or not, run the following queries:
SELECT owner, table_name, last_analyzed FROM dba_tables WHERE owner =’XFL’;

SELECT owner, index_name, last_analyzed FROM dba_indexes WHERE owner =’XFL’;

Analyze Statement

The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows.

Analyze individual Table:

ANALYZE TABLE emp COMPUTE STATISTICS;

ANALYZE INDEX emp_pk COMPUTE STATISTICS;

ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 2000 ROWS;

ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 25 PERCENT;

Schema / Database Statistics

The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement:

EXEC DBMS_UTILITY.analyze_schema (‘TEST’,'COMPUTE’);

EXEC DBMS_UTILITY.analyze_schema (‘TEST’,'ESTIMATE’, estimate_rows => 100);

EXEC DBMS_UTILITY.analyze_database (‘COMPUTE’);

EXEC DBMS_UTILITY.analyze_database (‘ESTIMATE’, estimate_rows => 100);

EXEC DBMS_UTILITY.analyze_database (‘ESTIMATE’, estimate_percent => 15);

Scheduling a Maintenance Job

We can create a maintenance job, which can keep all the database / schema stats up-to date.  Frequency of the job is dependent on – how frequently data is modified in base tables (insert / update).

SET SERVEROUTPUT ON

DECLARE

Test_job  NUMBER;

BEGIN

DBMS_JOB.submit (Test_job,

‘BEGIN DBMS_STATS.gather_schema_stats(”TEST”); END;’,

SYSDATE,

‘SYSDATE + 1′);

COMMIT;

DBMS_OUTPUT.put_line(‘Job: ‘ || Test_job );

END;

/

General Guidelines

• Usually it is good practice to schedule gather stats job on daily or at least weekly basis on an active database.
• As this job is resource (CPU) conducive – Schedule this one at low database activity timings to have lowest impact on users.

Table Maintenance

Table Fragmentation

For database which has only inserts, there will not be any fragmentation.
Fragmentation may occur when we have DML operations like update/delete data in table. The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

Issues with Fragmentation

• Slow response time (for queries like “Select * from table)
• High number of chained (actually migrated) rows
• Free space not getting used.

Note: For queries which uses index – re-org of tables may not be beneficial.

Table Re-organization

There are three ways to reorganize fragmented tables:

• Export, drop and import
• Alter table move
• Oracle 10g shrink command.

Example of shrink command

Pre-requisite:

• Tables must be present in the tablespace which have automatic segment management.

• Table row movement must be enabled.

Alter table test enable row movement;

Method 1:

Step 1: Re-Arrange table (All DML can happen at this time)

Alter table test shrink space compact;

Step 2:  Reset HWM (High Water Mark) (No DML can happen.

Alter table test shrink space;

Method 2:

Both rearrange and resetting HWM happens in one statement:

Alter table test shrink space;

Index Maintenance

Index Fragmentation

• The fragmentation of an index specifies how well the space is used in the index. The smaller the space that is actually used, the more fragmented the index (for B-tree). In this case, the index may also be called unbalanced or degenerated.

• Index fragmentation may bring a higher penalty to application performance. When accessing data through an index and an index range scan, Oracle must read each block in the specified range to retrieve the indexed values. If the index is highly fragmented, Oracle may have to search many more blocks, and possibly levels, to get this information.

• Improper sizing or increased growth (delete / updates) can produce index fragmentation.

Index Re-organization

Oracle options to reduce fragmentation includes following:

• Drop & Create index
• Rebuild
• Coalesce

Coalesce

The purpose of the COALESCE option is to reduce free space within the Leaf Blocks of an index. This is achieved by effectively performing a Full Index Scan of the leaf blocks, comparing the free space available in neighboring blocks. I

      ALTER INDEX test_idx COALESCE;

Rebuilding Indexes

While rebuilding indexes using this option we can change the storage characteristics for the indexes (like tablespaces).  Rebuilding indexes removes the intra-block fragmentation.

      ALTER INDEX emp_name REBUILD;

      ALTER INDEX REBUILD ONLINE;

Monitoring Index usage

To check whether a particular index is used in your query or not,  we can follow below procedure to first enable index monitoring, running select (or job) and then checking index usage in database table.

• To start monitoring the usage of an index, issue this statement:
• The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used.
• Later, issue the following statement to stop the monitoring:
• Finding un-used indexes in oracle database:

               ALTER INDEX idx_test MONITORING USAGE;

               ALTER INDEX idx_test NOMONITORING USAGE;

col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Index|Usage|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2

select
p.object_name c1,
p.operation   c2,
p.options     c3,
count(1)      c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> ‘SYS’
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;

Data Dictionary views for Indexes

View	Description
DBA_INDEXES ALL_INDEXES USER_INDEXES	DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS	These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS USER_IND_EXPRESSIONS	These views describe the expressions of function-based indexes on tables.
INDEX_STATS	Stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.
INDEX_HISTOGRAM	Stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.
V$OBJECT_USAGE	Contains index usage information produced by the ALTER INDEX ... MONITORING USAGE functionality.

Generic SQL Tuning Guidelines

• For faster data access – create indexes on all the necessary columns and only for the necessary columns as excess indexes may impact other DML operations like insert.

• Use proper sequence of column in where clause – Index will be picked only if leading part of the composite index is used in where clause of query.

Example

Table test has composite index on columns A, B, C in the sequence it is shown below:

A       1

C       2

B       3

• If we use C, B in the where clause – it may not pick index as leading part of the index is not used.

• If we use A, B, C in the where clause – it will not use index as order in the where clause does not match the index sequence.

• Use table aliases and a standard approach to table aliases. If two identical SQL statements differ because an identical table has two different aliases, then the SQL is different and will not be shared.

• If a column is using a function in the where clause – create function based indexes as normal B-tree or Bitmap indexes may not be used.

• Use of EXISTS versus IN for Sub queries

• Write Separate SQL Statements for Specific Tasks

• Make use of the oracle optimized hints to direct to specific query plan for maximize performance.

• Use Query re-write feature for materialized views.

• Consider using stored-outlines and in-memory pinning feature.