31). How do you collect statistics for a table, schema and Database ?

Using DBMS_STATS package to gather Oracle dictionary statistics.

HOW-TO: http://emarcel.com/database/157-dbmsstats10g

32). Can you make collection of Statistics for tables automatically ?

YES. Oracle database has default, scheduled job “gather_stats_job” that analyses stats on a daily basis during the maintenance window time.

There are two scheduled activities related to the collection of Oracle “statistics”:

  • AWR statistics:  Oracle has an automatic method to collect AWR “snapshots” of data that is used to create elapsed-time performance reports.
  • Optimizer statistics:  Oracle has an automatic job to collect statistics to help the optimizer make intelligent decisions about the best access method to fetch the desired rows.

This job can be disabled with this command: exec dbms_scheduler.disable(’SYS.GATHER_STATS_JOB’);

Oracle collects optimizer statistics for SQL via the default of autostats_target = auto.

33). On which columns you should create Indexes ?

In general, you should create an index on a column in any of the following situations:

  • The column is queried frequently
  • A referential integrity constraint exists on the column
  • A UNIQUE key integrity constraint exists on the column

The following list gives guidelines in choosing columns to index:

  • You should create indexes on columns that are used frequently in WHERE clauses
  • Are used frequently to join tables
  • Are used frequently in ORDER BY clauses
  • On columns that have few of the same values or unique values in the table

34). What type of Indexes are available in Oracle ?

There are many index types within Oracle:

B*Tree Indexes – common indexes in Oracle. They are similar construct to a binary tree, they provide fast access by key, to an individual row or range of rows, normally requiring very few reads to find the correct row.

The B*Tree index has several subtypes:

  • Index Organised Tables – A table stored in a B*Tree structure
  • B*Tree Cluster Indexes – They are used to index the cluster keys
  • Reverse Key Indexes – The bytes in the key are reversed. This is used to stop sequential keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999, 300999 thus these would be located on different blocks.
  • Descending Indexes – They allow data to be sorted from big to small (descending) instead of small to big (ascending).

Bitmap Indexes – With a bitmap index , a single index entry uses a bitmap to point to many rows simultaneously, they are used with low data that is mostly read-only. Schould be avoided in OLTP systems.

Function Based Indexes – These are B*Tree or bitmap indexes that store the computed result of a function on a row(s) (for example sorted results)- not the column data itself.

Application Domain Indexes – These are indexes you build and store yuorself, either in Oracle or outside of Oracle

interMedia Text Indexes – This is a specialised index built into Oracle to allow for keyword searching of large bodies of text.

35). What is B-Tree Index ?

A B-Tree index is a data structure in the form of a tree, but it is a tree of database blocks, not rows.

Note: “B” is not for binary; it’s balanced.

36). A table is having few rows, should you create indexes on this table

Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large. 

You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.

37). A Column is having many repeated values which type of index you should create on this column, if you have to ?

For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. Each column contains less than 100 distinct values by themselves, and a b-tree index would be fairly useless in a database of 20 million vehicles.

38). When should you rebuilt indexes ?

In 90% cases – NEVER.

When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based.

Also index blevel is one of the key indicators of performance of sql queries doing Index range scans.

39). Can you built indexes online ?

YES. You can create and rebuild indexes online.

This enables you to update base tables at the same time you are building or rebuilding indexes on that table.

You can perform DML operations while the index build is taking place, but DDL operations are not allowed.

Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

40). Can you see Execution Plan of a statement ?

YES. In many ways, for example from GUI based tools like TOAD, Oracle SQL Developer.

Configuring AUTOTRACE, a SQL*Plus facility

AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we’ve executed, and the resources they used.

Once the PLAN_TABLE has been installed in the database, You can control the report by setting the AUTOTRACE system variable.

  • SET AUTOTRACE OFF – No AUTOTRACE report is generated. This is the default.
  • SET AUTOTRACE ON EXPLAIN – The AUTOTRACE report shows only the optimizer execution path.
  • SET AUTOTRACE ON STATISTICS – The AUTOTRACE report shows only the SQL statement execution statistics.
  • SET AUTOTRACE ON – The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

SET AUTOTRACE TRACEONLY – Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any.