DBA Interview questions

When to rebuild the indexes in oracle?

The need to rebuild b-tree indexes is very rare, basically because a b-tree index is for a very large amount self-managed or self-balanced. Commonly the reasons for rebuilding an index are:

- index becomes fragmented
- index grows and grows - deleted space is not re-used
- index clustering factor becomes out of sync

If the index value were to have monotonically increasing values then any deleted space could be a problem as this space may not be reused.

Another case would be an index that has deletions without subsequent inserts.

To ascertain index fragmentation, the following SQL statement can be used:

SQL>analyze index index_name validate structure;

SQL>SELECT name,del_lf_rows,lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
FROM index_stats where name ='index_name';


if 15-20% of the table data changes, then you may consider rebuilding the index.

...................................................................................................................

What are Recursive Calls ?

Recursive calls occur when Oracle itself must issue a SQL statement in addition to the SQL statement issued by a user process. The most common causes of recursive calls are:
Misses in the data dictionary cache.

Dynamic storage extension.

Execution of DDL statements, the enforcement of referential integrity constraints, use of PL/SQL.

Recursive calls can impair the performance of the database system and should be minimized when possible.

The recursive call ratio is calculated as Recursive calls/User calls. If the number of Recursive calls is greater than the number of User calls, then you should start a detailed examination. Check the data dictionary cache hit ratio and average parse ratio. Increasing shared_pool_size should help.

Dynamic storage extension occurs when a database object (a table or index) must extend beyond its allocated space (that is, a new extent is allocated).

As in the case of the cache hit ratios, the value for recursive calls will be high after database instance startup. Since the data dictionary cache is at first empty, all calls needed to load information into working memory will be recursive.

.........................................................................................................................................

Some SQL which has been running fine suddenly runs very poorly. You have made no changes to the data, the SQL, or the statistics for the objects involved. What could be the reason?

One reason for this behaviour may be explained by the use of a feature introduced in 9.x called bind peeking.

With this feature the query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, based on this value just as if a literal had been used instead of a bind variable. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

Thus if the first set of bind values that happen to be used when the cursor is first hard-parsed are not representative then the plan may be inappropriate for subsequent executions.

.........................................................................................................................................
•What is a raw device?

Raw device are character devices which allows byte level access to the device. Character devices (/dev/raw/raw9) are *UN-BUFFERRED** devices. This means that there is not need to pass attributes like O_DIRECT when accessing character devices since all I/O is unbufferred IO.

•What is block device?

Block devices provide block level access to the device. Block devices unlike character devices (/dev/sde9) are **BUFFERED** devices. This means the application program has to explicitly pass the O_DIRECT attribute to turn off caching. If the O_DIRECT attribute is not passed, I/O will be cached (linux buffer cache).




What is Direct-Path INSERT  ?

Direct-Path INSERT

No comments:

Post a Comment