1) Denormalize your tables where appropriate.
2) Proper use of index columns: An index based on numeric fields is more efficient than an index based on character columns.
3) Reduce the number of columns that make up a composite key.
4) Proper partitioning of tablespaces and create a special tablespace for special data types like CLOB, BLOB etc.
5) Data access performance can be tuned by using stored procedures to crunch data in the database server to reduce the network overhead and also caching data within your application to reduce the number of accesses.
2) Proper use of index columns: An index based on numeric fields is more efficient than an index based on character columns.
3) Reduce the number of columns that make up a composite key.
4) Proper partitioning of tablespaces and create a special tablespace for special data types like CLOB, BLOB etc.
5) Data access performance can be tuned by using stored procedures to crunch data in the database server to reduce the network overhead and also caching data within your application to reduce the number of accesses.
Q. Can you give some database performance tuning tips based on your experience?
1. Materialized views are one of the important SQL tuning tools in Oracle. Instead of the entire company accessing a single database server, user load can be distributed across multiple database servers with the help of materialized views in Oracle. Through the use of multi tier materialized views, you can create materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.
a) Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data.
b) It allows you to pre-join complex views and pre-compute summaries for super-fast response times. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
c) You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
d) A materialized view can be stored in the same database as its base tables (improves query performance through query rewrite) or in a different database.
b) It allows you to pre-join complex views and pre-compute summaries for super-fast response times. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
c) You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
d) A materialized view can be stored in the same database as its base tables (improves query performance through query rewrite) or in a different database.
2. As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that increases in one direction (i.e. monotonic) such as an identity column, or some other column where the value is increasing and is unique. In many cases, the primary key is the ideal column for a clustered index. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.
3. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.
4. Only return the columns and the rows you need.
5. Avoid full table scan where possible. The full table scan can be caused by
a) No WHERE condition.
b) No index on any type of field in the WHERE clause.
c) NOT IN predicate that is easier to write (replace NOT IN with a left outer join).
d) WHERE clauses like column_name is not null, condition 1 or condition 2, column_name between … and …, not equality comparisons
e) Use of SQL “LIKE clause” operator to find a string within a large table column (e.g. VARCHAR(2000), CLOB, BLOB).
DISTINCT, ANY, and ALL.
b) No index on any type of field in the WHERE clause.
c) NOT IN predicate that is easier to write (replace NOT IN with a left outer join).
d) WHERE clauses like column_name is not null, condition 1 or condition 2, column_name between … and …, not equality comparisons
e) Use of SQL “LIKE clause” operator to find a string within a large table column (e.g. VARCHAR(2000), CLOB, BLOB).
DISTINCT, ANY, and ALL.
It is also worth noting that this capability may not suit best for too frequent activities as in online transaction processing (i.e. OLTP) environments. In other databases equivalent functionalities can be achieved through triggers on base tables that would update/insert aggregate or dimension tables and queries can be executed against these aggregated or dimension tables as oppose to the base tables.
