TUNING Q & A Page - 7

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 7

61..Data dictionary follows which  optimizer mode?

RBO

62..When should one rebuild an index?

You can run the ‘ANALYZE INDEX VALIDATE STRUCTURE’ command on the affected indexes – each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The ‘badness’ of the index can then be judged by the ratio of ‘DEL_LF_ROWS’ to ‘LF_ROWS’.

63..How do you collect statistics of a user / schema?

exec dbms_stats.gather_schema_statas(scott)

64..Which Tables Should Be Indexed?

                                                                       

Use the following guidelines to decide which tables to index:

A. Index tables when queries select only a small number of rows. Queries that select a

large number of rows defeat the purpose of the index. Index the table when queries

access less than 5 percent of the rows in the table

B. Don’t index tables that are frequently updated. Update, insert, and deletes on indexed

tables incur extra overhead. Base your decision about whether or not to index a table

on the number of updates, inserts, and deletes relative to the total number of queries

to the table.

C.  Index tables that don’t have duplicate values on the columns usually selected in WHERE clauses. Tables for which the selection is based on TRUE/FALSE values are not good candidates for an index.

D. Index tables that are queried with relatively simple WHERE clauses. Complex WHERE

clauses may not be able to take advantage of indexes. You can solve this shortcoming

by creating a complex index, by simplifying the SQL statement, or by using a hint.

65..Why  we need the  CASCADE option with DROP USER command whenever dropping  user and why DROP USER commands fails when we don’t  use it?

If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command roll-back cannot be performed.

66..What and  how to  test in the O/S?

The OS is tested with the RDBMS. If you have to change some specific OS

parameter to increase a limitation, you usually don’t have to retest the performance. Any

limit  change is usually associated with an RDBMS change, and the two can be tested together

67..How can one optimize %XYZ% queries?     

It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.

68..What is the difference between  compute and  estimate?

If you  use compute, the FTS(FULL TABLE SCAN) will happen, if  you  use estimate  just 10% of the  table  will be read.

69..How to analyse the explain plan?

The Oracle EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. The execution plan can help you determine whether you have written the an efficient SQL statement or whether changes can be made to optimize the statement.

To execute an EXPLAIN PLAN statement, you must first create a table with the name plan_table and with the specified plan_table format. The table format and the SQL statement required to create the table are included in Oracle in the SQL script UTXPLAN.SQL. Once this table is created, you can execute the EXPLAIN PLAN statement by issuing these SQL statements followed

by your SQL statements:

EXPLAIN PLAN

SET STATEMENT_ID = ‘NAME’

FOR

In this syntax, NAME specifies a label for the statement in the plan_table table.

70..Which  factors are to be  considered  for creating  index on table? how to select column for index?

Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index. 

It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.