While with Oracle for several years there had been many instances when the database performance had not been optimal. This had nothing to with the configuration of the sever but more with tuning of database. Here are some tips which I had been using

Make sure that database statistics is upto-date. As this is one of the factor which can influence explain plan. We can gather statistics by executing following command:

EXEC DBMS_STATS.gather_schema_stats(‘TDWDBA’);

Where TDWDBA is the schema name.

Problem: For a Oracle (backend) – Java (frontend) system, a query  returning about 500 K rows was running very slow and is taking about 45 mins.  Oracle database is hosted on server (A), has a separate application server (B), and client can access database from client machine (C), using SQLPLUS.  Query is running slow from the clients machine (C) and from the java front end (B).

Basic Checks on dbase:

Make sure to check following with respects to the tables / database in question:

• Latest Database statistics exists for the tables in question.
• Indexes
• Degree of Parallelism at Table Level:
• Optimizer mode

Database configuration parameters  – sga_max_size,  pga_aggregate_target , memory_target, processes Read more…

Some useful queries used in day to day operations

Checking locks in database

SELECT b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username,

a.owner AS object_owner,a.object_name,Decode(b.locked_mode, 0, ‘None’,

1, ‘Null (NULL)’,2, ‘Row-S (SS)’,3, ‘Row-X (SX)’,

4, ‘Share (S)’,5, ‘S/Row-X (SSX)’,6, ‘Exclusive (X)’,

b.locked_mode) locked_mode,b.os_user_name

FROM   dba_objects a,v$locked_object b

WHERE  a.object_id = b.object_idORDER BY 1, 2, 3, 4;

Memory allocation per session: Read more…

A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.

Oracle provides SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing and evaluating materialized views for query rewrite

A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.

Oracle provides SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing and evaluating materialized views for query rewrite

Key Features

• Can define independent tablespace, storage parameters to Materialized views.
• Use Index & Partition
• Use query re-write feature – Process of modifying a query to use the view rather than the base table is called query rewrite.

Types of materialized views

• Materialized view with Aggregates – The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX
• Materialized views containing only joins
• Nested Materialized views – materialized view whose definition is based on another materialized view Read more…

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. Read more…

Further to may last post , Oracle 11g new Features – Part 1 , I am covering some more features here.

Client Query Result Cache

Oracle 11g allows client’s memory to cache query result sets which significantly improves the performance of repetitive queries by OCI-based clients.

Benefits of Client Cache feature

• Extending server-side query caching to client side memory, by leveraging cheaper client-side memory and caching each application’s working set locally.
• Ensuring better performance by eliminating round trips to the server.
• Improving server scalability by saving server resources
• offering transparent cache management: memory management, concurrent access of result-sets.

To start using client cache – set following initialization parameter:
CLIENT_RESULT_CACHE_SIZE = 1G  Read more…

Oracle 11g has some pretty cool new features. I am covering some of the Read Only tables and Server result cache features in this post

Read only Tables

In 10g or prior oracle releases,  table can be made read only by granting the  SELECT object privilege to them. Oracle 11g allows tables to be marked as read-only by ALTER TABLE command.
Example:

Create table
CREATE TABLE orders (
id  NUMBER
);

Mark table as read-only
ALTER TABLE orders READ ONLY;

DML operations
INSERT INTO orders VALUES (2) ;
UPDATE orders SET id = 2;
DELETE FROM orders; Read more…

This post gives an overview and understanding of  AWR and ADDM in Oracle 10g

AWR Architecture

Oracle 10g - awrArchitecture

Read more…