Answers for Oracle Interview Questions

37)What is the optimizer mode which is currently followed?
Cost Based Optimizer since oracle 9i.

38)What are statistics in Oracle?
The term "Oracle statistics" may refer to historical performance statistics that are kept in STATSPACK and AWR, but a more common use of the term "Oracle statistics" is about Oracle optimizer "Metadata statistics" in order to provide the cost-based SQL optimizer with the information about the nature of the tables.

Oracle statistics tell you the size of the tables, the distribution of values within a columns, and other important information so that SQL statements will always generate the "best" execution plans when the optimizer is a cost based one.

39)Will Statistics affect the execution plan for a Query?
Since Oracle 9i, the Optimizer is a cost based optimizer which relies on the metadata statistics of a database object to determine the best execution path when executing a Query.

40)How will you collect statistics on a table?
Statistics on a table can be collected by making use of the ANALYZE Clause.In order to make use of the different options available while collecting statistics on a table, then make use of the DBMS_STATS package provided by oracle.

41)What are the different database objects on which we can collect statistics?
The following procedures are available under the DBMS_STATS Package for collecting statistics

GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure


42)How do you check that statistics is upto date?
Statistics must be regularly gathered on database objects as those database objects are modified over time. In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table and whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.


The LAST_ANALYZED Column on the data dictionary contains information about when was the last time that statistics was collected on the particular database object.

43)Can we Override indexes on a table in determining the execution plan for a Query?
We can by making use of the hints. The use of Hints overrides the Optimizer in choosing the execution plan for a Query.

44)What is meant by Extended Statistics?
Any statistics you collect for expressions and column groups are called "extended statistics".Expression statistics on functions enable the optimizer to obtain a vastly more accurate selectivity value for predicates that involve expressions.

You can issue the following query to find details about expression statistics on a table's columns:

SQL> select extension_name, extension
     from user_stat_extensions
     where table_name='CUSTOMERS';

45)What is the use of Extended Statistics?
The 11g extended statistics are intended to improve the optimizers guesses for the cardinality of combined columns and columns that are modified by a built-in or user-defined function.Gathering extended statistics allows histograms not only on one column, but multiple columns at a time.

46)What is Estimating Statistics?
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.

You can use dynamic sampling to:
*    Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.
*    Estimate statistics for tables and relevant indexes without statistics.
*    Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust

The following estimate_percent argument is a new way to allow Oracle’s dbms_stats to automatically estimate the best percentage of a segment to sample when gathering statistics:

estimate_percent => dbms_stats.auto_sample_size

47)What is the use of Exporting and Importing Statistics?
Statistics can be exported and imported from the data dictionary to user-owned tables, enabling you to create multiple versions of statistics for the same schema. You can also copy statistics from one database to another database. You may want to do this to copy the statistics from a production database to a scaled-down test database.

The concept of exporting and importing statistics can be used when you wanna export statistics from the prod database to the test database in order to test the performance of an application Query.

48)How do you Export and Import Statistics in Oracle?
After the table is created, then you can export statistics from the data dictionary into your statistics table using the DBMS_STATS.EXPORT_*_STATS procedures. The statistics can then be imported using the DBMS_STATS.IMPORT_*_STATS procedures.

49)What are Oracle Hints?
You can use hints to specify the following:

The optimization approach for a SQL statement
The goal of the cost-based optimizer for a SQL statement
The access path for a table accessed by the statement
The join order for a join statement
A join operation in a join statement

50)When should you go in for Oracle Hints?
You can use hints to specify the following:

The optimization approach for a SQL statement
The goal of the cost-based optimizer for a SQL statement
The access path for a table accessed by the statement
The join order for a join statement
A join operation in a join statement

Answers for Oracle Interview Questions

23)What is the use of system level triggers?
You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:

System events

Database startup and shutdown
Server error message events

User events

User logon and logoff
DDL statements (CREATE, ALTER, and DROP)
DML statements (INSERT, DELETE, and UPDATE)

24)What is an Exception in Oracle?
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

25)What is the difference between an error and an Exception?
Error: Any departure from the expected behavior of the system or program, which stops the working of the system is an error.
Exception:Any error or problem which one can handle and continue to work normally.


26)What is the use of an Exception Clause in Oracle?
We make use of the exception block whenever any error or problem which occurs, has to be by passed and continue the normal workflow of the application.


27)Will the use of an Exception clause hinder the performance?
It all depends on the exception routine and the operations performed within the exception routine.

28)What are the different types of Exceptions in Oracle?
System Defined Exceptions
user defined Exceptions
Un-named Exceptions

29)What is the use of the PRAGMA EXCEPTION INIT Clause?
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration.

30)What is the Usual error(Number) raised when using a BULK COLLECT Clause?
ORA-24381


31)How can you raise an Exception?
You can raise an exception using the RAISE and RAISE Application Error Clause.

32)What is the use of Re raising an Exception?
Re Raising an exception within the exception block occurs whenever some kind of auditing is required and when the audit information is recorded and the raised exception needs to be handled by the calling block or application.

33)Can we Use Exception clause for Auditing purposes? If so, How?
By making use of the RAISE Application Error Clause within the exception block after recording the audit information.

34)What is the use of SAVE Exceptions clause?
Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing.  Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute.  This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – Holds the iteration (not the subscript) of the original FORALL statement that raised the exception.  In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE – Holds the exceptions error code.

The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised.  The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.

35)Can we use the SAVE Exceptions clause in a normal sceanario even without using a BULK COLLECT?
No, SAVE Exceptions clause comes along with FORALL Clause.

36)What are the different Optimizer modes available in Oracle?
ALL_ROWS
FIRST_ROWS
FIRST_ROWS_n
CHOOSE

Answers for Oracle Interview Questions

10)What is a View?
A View is a Database Object which hides the complexity of the Query and it invokes the indexes on the base tables. Any DML Operation on the view applies on the base tables on which the view is based on. We make use of a INSTEAD OF TRIGGER on the view so that any DML operation on the view can applied on the base tables where is no definite correlation between the columns on the view and the columns on the base tables.Also when a View is Queried , then the underlying Query is run at that time.

11)What is a materialized View?
A Materialized View Precomputes the data or executes the Query behind the view and has its results precomputed. This is very different from the normal Oracle Views which executes the Query on run time. Since the Query results are already precomputed, Accessing Data from the Materialized View is much faster when compared to Oracle Views. Materialized Views favours the creation of indexes on the precomputed data which is not possible in the case of Oracle Views which uses the indexes on the base tables on which the Oracle View is created.

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.

Enabling of query rewrite is subject to the following restrictions:

•You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
•You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).

12)Why should we go for creating a View and a materialized View?
When there is a need to hide the complexity of a Query, then we should go for creating a View for the underlying Query. On the Other hand, when you want the results of the underlying Query to be pre computed , then prolly we should go in for creating a materialized view. Also when there is a need to create indexes on the underlying view apart from the indexes on the underlying tables, we should go in for creating a materialized view.Also Materialized View is a way of replicating data from one database to another.

13)What are the different refresh techniques associated with Materialized Views?
COMPLETE
FAST
FORCE

14)Is there anything like a Parameterized View?
There is nothing like a parameteized view in oracle, but we can replicate the same by making use of Oracle Context.

15)What is Context with respect to Oracle?
There is no concept of a parameterized Views in Oracle as to parameterized Cursors in Oracle. But the Concept of a Parameterized Views can be achived
by way of a context Creation and treat the Attributes in the Context as to parameters which are passed to Parameterized Views. The View is initally created with
the help of the context attribute been used in the where clauses as filter conditions to the data retrieved. Then the calling Program should first set the attribute values
in the context and then make a call to the View.

16)What is a Trigger?
In Order to enforce a business contraint, we make use of a trigger in Oracle.Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.

You can write triggers that fire whenever one of the following operations occurs: DML statements on a particular schema object, DDL statements issued within a schema or database, user logon or logoff events, server errors, database startup, or instance shutdown.Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

17)Will the use of a Trigger hinder the performance of an application?
Yes, because each time when the triggering event occurs, the procedure within the trigger needs to be executed apart from the event which triggered it.The performance hindrance also depends upon whether its a row level or a statement level trigger.

18)What are the different types of Triggers?
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
INSTEAD OF Triggers
Triggers on System Events and User Events

19)What is meant by an Instead of Trigger?
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

20)What are statement level and Row Level Triggers?
A ROW TRIGGER is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

A STATEMENT TRIGGER is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

21)Can we commit inside a Trigger?
Yes we can commit inside a trigger. We can make use of the PRAGMA AUTONOMOUS TRANSACTION inside the trigger to state that the transactions made inside the trigger are independent of the calling block.

22)What is meant by Pragma?
PRAGMA is a compiler directive.Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.

Answers for Oracle Interview Questions

1)What is RDBMS?
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model.

2)What is an index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

4)What are the different types of Indexes?
BTree Index
Bitmap Index
Functional Index
Composite Index
Prefixed Index
Non Prefixed Index
Context Index
Partitioned Index
Clustered Index
Non Clustered Index
Reverse Key Index
Compressed Index
Invisible Index
Bitmap Join Index

5)When should you go in for a Bitmap Index?
With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)

With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).

We usually go in for Bitmap Index when the cardinality of the column is very low which is the count of distinct values in the column is low.

6)What is the backend architecture of Bitmap Indexes?
The real power of the bitmap index is seen when a table contains multiple bitmap indexes. With multiple bitmap indexes available, Oracle has the ability to merge the result sets from each of the bitmap indexes to quickly eliminate the unwanted data.

7)What are the different initialization parameters relating to the use of Indexes?
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ


8)What is the Use of a Functional Index?
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.

Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index

9)What are the different initialization parameters which are considered important?
BACKGROUND_DUMP_DEST
BUFFER_POOL_KEEP
BUFFER_POOL_RECYCLE
COMPATIBLE
CONTROL_FILES
CONTROL_FILE_RECORD_KEEP_TIME
CPU_COUNT
CURSOR_SHARING
DBWR_IO_SLAVES
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_CREATE_FILE_DEST
DB_FILE_MULTIBLOCK_READ_COUNT
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_WRITER_PROCESSES
FAST_START_MTTR_TARGET
HASH_AREA_SIZE
HASH_JOIN_ENABLED
LARGE_POOL_SIZE
JOB_QUEUE_PROCESSES
LOG_ARCHIVE_DEST
LOG_FILE_NAME_CONVERT
OPEN_CURSORS
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
OS_AUTHENT_PREFIX
PGA_AGGREGATE_TARGET
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_OS_AUTHENT
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
SESSION_CACHED_CURSORS
SGA_MAX_SIZE
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
STATISTICS_LEVEL
TRACE_ENABLED
UNDO_MANAGEMENT
UNDO_RETENTION
UNDO_TABLESPACE
UTL_FILE_DIR
WORKAREA_SIZE_POLICY

Common Oracle Interview Questions

1)What is RDBMS?
2)What is an index?
3)What is the use of an Index?
4)What are the different types of Indexes?
5)When should you go in for a Bitmap Index?
6)What is the back end architecture of Bitmap Indexes?
7)What are the different initialization parameters relating to the use of Indexes?
8)What is the Use of a Functional Index?
9)What are the different initialization parameters which are considered important?
10)What is a View?
11)What is a materialized View?
12)Why should we go for creating a View and a materialized View?
13)What are the different refresh techniques associated with Materialized Views?
14)Is there anything like a Parameterized View?
15)What is Context with respect to Oracle?
16)What is a Trigger?
17)Will the use of a Trigger hinder the performance of an application?
18)What are the different types of Triggers?
19)What is meant by an Instead of Trigger?
20)What are statement level and Row Level Triggers?
21)Can we commit inside a Trigger?
22)What is meant by Pragma?
23)What is the use of system level triggers?
24)What is an Exception in Oracle?
25)What is the difference between an error and an Exception?
26)What is the use of an Exception Clause in Oracle?
27)Will the use of an Exception clause hinder the performance?
28)What are the different types of Exceptions in Oracle?
29)What is the use of the PRAGMA EXCEPTION INIT Clause?
30)What is the Usual error(Number) raised when using a BULK COLLECT Clause?
31)How can you raise an Exception?
32)What is the use of Re raising an Exception?
33)Can we Use Exception clause for Auditing purposes? If so, How?
34)What is the use of SAVE Exceptions clause?
35)Can we use the SAVE Exceptions clause in a normal scenario even without using a BULK COLLECT?
36)What are the different Optimizer modes available in Oracle?
37)What is the optimizer mode which is currently followed?
38)What is statistics in Oracle?
39)Will Statistics affect the execution plan for a Query?
40)How will you collect statistics on a table?
41)What are the different database objects on which we can collect statistics?
42)How do you check that statistics is up to date?
43)Can we Override statistics on a table in determining the execution plan for a Query?
44)What is meant by Extended Statistics?
45)What is the use of Extended Statistics?
46)What is Estimated Stats Collection?
47)What is the use of Exporting and Importing Statistics?
48)How do you Export and Import Statistics in Oracle?
49)What are Oracle Hints?
50)When should you go in for Oracle Hints?
51)Will Oracle make use of a Hint if it is specified in the Query?
52)Can we specify more than one Hint in a Query?
53)What are the different Hints that you have come across?
54)What are the different types of Joins available in Oracle?
55)Explain where each type of Join can be used w.r.t. Different Scenarios?
56)What is meant by Reverse Key Indexes?
57)What and where do you make use of a Reverse Key Index?
58)Will a Reverse Key Index increase the performance of a Query?
59)How do you read an Explain Plan?
60)What are the different types of Joins used by the optimizer?
61)What is meant by Partitioning?
62)What are local and Global Indexes?
63)What are the different Types of Partitioning Available?
64)What are the Partitioning methods which are available in Oracle 11G Onwards?
65)How do you say that partitioning increases the performance of the Queries?
66)What is meant by Skip Scan Indexes?
67)What is meant by composite partitioning?
68)What are the Different combinations of partitioning possible?
69)What do you mean by SQL Profiles?
70)What are the Automatic Tuning tools available in Oracle?
71)What is meant by a Global Temporary Table?
72)What is meant by Scalar Sub Query Caching?
73)What is Subquery Factoring?
74)What do you mean by a locally managed Tablespace?
75)What is the difference that you find with a Locally managed and a Dictionary managed Tablespace?
76)What do you mean by ASSM?
77)How come the performance of an Query increase by setting the Segment Space Management as AUTO?
78)What is the memory architecture of Oracle?
79)What is SGA and PGA?
80)What are the components of the SGA and the PGA?
81)What is meant by sga_target and pga_aggregate_target and memory_target and what are the benefits of the same?
82)What is meant by External Tables in Oracle?
83)Can we fire DML Statements on External Tables?
84)When should you go in for an External Table?
85)What is meant by SQL Profiler?
86)What is the use of SQL Profiler Tool in Oracle?
87)Have you heard of SQL Tuning Advisor and SQL Access Advisor?
89)Have you worked on the above tools?
90)What is the use of the SQL Performance Analyzer?
91)What do you mean by Fragmentation?
92)What is meant by Shared pool Fragmentation?
93)What happens on the Oracle Back end whenever you fire a Query?
94)What is parsing?
95)Can you explain the Parse, Bind , Execute and Fetch Phases?
96)What is a Cursor?
97)What are the different Types of Cursors?
98)What is a parametrized Cursor?
99)What is a reference Cursor?
100)What is meant by Context Switching?
101)Will Context Switching hinder the performance of an application?
102)How will you overcome Context Switching in Oracle?
103)What is meant by BULK COLLECT?
104)What is the use of the FORALL Clause in Oracle?
105)What are the different types of collections available in Oracle?
106)Can you explain the collection types one by one along with their pitfalls?
107)What are the different memory issues encountered when using a BULK COLLECT?
108)What is the use of a LIMIT Clause?
109)What is AWR?
110)What is ADDM?
111)What is the use of ADDM Report and Recommendations?
112)What is SQL Performance Analyzer?
113)When exactly do you go in for a SQL Performance Analyzer?
114)What is ASH?
115)What are the different Views that can be Queried for getting info on ASH?
116)What is meant by Latch?
117)How do you overcome Latches in Oracle?
118)What is the use of Database Replay?
119)How do you accept a Profile Recommended by ADDM?
120)Any idea on the Database Buffer Cache?
121)Can you throw some light on the Data Dictionary and the Library Cache?
122)How do you overcome Data Dictionary and Library Cache Contention?
123)What is the basic Structure of a PLSQL Block?
124)What are the different types of PLSQL Objects that you have come across?
125)What is the use of a Package?
126)When should you go in for a Package?
127)What is the difference between a Procedure and a Function?
128)Can a Function return more than one value?
129)Can a Function have OUT Parameters?
130)What is meant by a Function Based Index?
131)What is benefit of using Functions?
132)Can we create an index on an user defined Functions?
133)What is the use of the RESULT_CACHE Keyword in Functions?
134)What do you mean by a global Parameter?
135)Can a Global parameter be referenced by some other packages or PLSQL?
136)What is the use of an UNDO Tablespace?
137)What is meant by FLASHBACK?
138)How do you flashback a Query?
139)Can we flashback a Table and database?
140)What is Snapshot too Old Error?
141)What is the main use of a Global Temporary Table?
143)What do you mean by Cursor Sharing?
144)What do you mean by Adaptive Cursor Sharing?
145)What are the different values for the Cursor Sharing Parameter?
146)What are the parameters which influence influence the sorting of Query Results?
147)Why and When Sorting Hinders the performance of an application?
148)Whats the use of temporary Tablespace w.r.t. sorting?
149)What are the different operations which involve disk I/O?
150)What is meant by Row chaining?
151)What is meant by Row Migration?
152)When does a row get chained and migrated?
153)How do you verify whether there the rows are chained or migrated in a table?
154)What is meant by Paging?
155)What is meant by swapping?
156)When does this paging and swapping occur?
157)How do you overcome Paging and Swapping?
158)Will a "!" negate the use of Indexes?
159)What happens on the back end when we do a COMMIT statement?
160)How Often do you COMMIT in your application?
161)Will the frequency of COMMIT hinder the performance of an application?
162)Why Should we need to index a foreign Key?
163)How do you identify whether Indexes are used by the application?
164)Whats the use of the QUERY REWRITE in materialized views in Oracle?
165)Any Idea about Histograms?
166)How Does Collecting Histograms influence the performance of an Query?
167)Whats the use of the KEEP and RECYCLE Buffer Pools in Oracle?
168)What is the HIGH WATER MARK LEVEL w.r.t. Oracle?
169)Whats the use of the Bitmap Join Indexes?
170)What are prefixed and non prefixed Indexes?
171)How do you shrink Unused Space?
172)When do you think that a Index needs to be rebuilt?
173)Any idea on Compressed Indexes in Oracle?
174)What is Virtual Column in Oracle 11G?
175)What are the benefits of using Virtual Column in terms of indexing and partitioning?
176)What is the "Maximum number of cursors exceeded" and how do you overcome it?
177)Whats the difference between a scalar sub Query and an Inline View?
178)Can we lock statistics on a Table?
179)Will the use of a DB Link hinder the performance of a Query?
180)What are the things to be looked for in a AWR Report?
181)What is meant by SQL Trace?
182)Any idea on the OPTIMIZER_DYNAMIC_SAMPLING Parameter?
183)What does cost refer to in a cost based optimizer?
184)What do you mean by stored outlines in Oracle?
185)What are the caveats of Bitmap join Indexes?
186)What do you mean by pipe lined table functions?
187)What is Consistent gets in Oracle?
188)What is the Sequence Caching in Oracle?
189)Will Data type conversion hinder the performance of a Query?
190)What are the different values for the STATISTICS_LEVEL parameter?
191)Any idea on the DB_FILE_MULTIBLOCK_READ_COUNT parameter?
192)When does Oracle go in for a FULL Table scan?
193)Will a FULL Table scan always hinder performance?
194)What does "B" in a Btree Index denote?
195)Any idea on the height of an index and what does that denote?
196)What is meant by Invisible Indexes in Oracle 11G?
197)What is meant by a Cartesian join in Oracle?
198)What is meant by partitioning pruning?
199)Any idea on IN MEMORY , ONE PASS AND MULTI PASS SORT?
200)What is the use of the overflow segment while creating an Index Organized tables?
201)What are the disadvantages of IOT?
202)Can we create secondary indexes on IOT's?
203)Whats the use of a reverse key indexes and where do you make use of a reverse key indexes?
204)What are the processes involved in a Database Replay?
205)What are the processes involved in a SQL Performance Analyzer?
206)What do you mean by session cached cursor?
207)Whats the use of a LARGE POOL?
208)What is SCN?
209)Can we flashback a DB on a particular SCN?
210)What is an Oracle Sub Type?
211)What is an Oracle Super Type?
212)What's the difference between an Oracle Type and Oracle Sub Type?
213)What's the use of PLSQL WARNINGS?
214)What's the use of Oracle Referenece Datatypes?
215)What's the use of RECORD in Oracle PLSQL?
216)Does an Oracle Type has a Body?
217)Whats the use of an Oracle Type?
218)Where exactly do you make use of an Oracle Type?
219)Do we need to create an Oracle Collection after creating an Oracle Object?
220)Can we use an Oracle Object without specifying the Collection for the Object created?If So , Then in what scenarios?
221)What do you mean by nested table?
222)Where exactly do you make use of a nested table?
223)What do you mean by CONSTRUCTOR FUNCTION and MEMBER FUNCTION?
224)CAn we ALTER a type which is already created?
225)Can we add an attribute to an Object already Created?What Option do we use? (Hint CASCADE OPTION)
226)What's the use of the FINAL Keyword while defining an Object?
227)Whats the use of USER_PLSQL_OBJECT_SETTINGS like PLSQL_OPTIMIZE_LEVEL , PLSQL_DEBUG etc.
228)What does the word deterministic mean?
229)Whats the use of DBMS_ALERT?
230)What is a pipelined function?
231)Can a function have an OUT Parameter?
232)Can we use any type of file as an external file for loading into a table as an External Table?
233)Can we load the contents of a zip file into an External Table?
234)What is the cost factor on the explain plan denote?
235)Whats the Difference that you find between a Nested Table Collection and an Associative Array Collection.
236)How to Initialize an Oracle Collection?
237)Whats the difference between dense and sparse collections?
238)How do you classify the Nested Tables, Associative Arrays and Varrays as sparse or Dense Collections?
239)Can we Assign Values to a collection before initializing it?
240)Can we Assign one collection to another?
241)Can we scan the contents of a collection from LAST to FIRST?
242)What is a RECORD?
243)Will a Parsing happen when using CURSORS and REFCURSORS?
244)Whats the difference between a Regular CURSORS and REFCURSORS?

Database Replay

DATABASE REPLAY


You can use Database Replay to capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. Database Replay supports workload capture on a system running Oracle Database 10g Release 2 and newer releases. In order to capture a workload on a system running Oracle Database 10g Release 2, the database version can be 10.2.0.4 or higher. Workload replay is only supported on systems running Oracle Database 11g Release 1 and newer releases.

CAPTURING THE WORKLOAD


Capturing a workload involves recording all requests made by external clients to Oracle Database.The capture files contain all relevant information about the client request, such as SQL text, bind values, and transaction information. Background activities and database scheduler jobs are not captured. These capture files are platform independent and can be transported to another system.

Before starting a workload capture, you should have a strategy in place to restore the database on the test system. Before a workload can be replayed, the state of the application data on the replay system should be similar to that of the capture system when replay begins. To accomplish this, consider using one of the following methods:

• Recovery Manager (RMAN) DUPLICATE command
• Snapshot standby
• Data Pump Import and Export

The following types of client requests are not captured in a workload:

• Direct path load of data from external files using utilities such as SQL*Loader
• Shared server requests (Oracle MTS)
• Oracle Streams
• Advanced replication streams
• Non-PL/SQL based Advanced Queuing (AQ)
• Flashback queries
• Oracle Call Interface (OCI) based object navigations
• Non SQL-based object access
• Distributed transactions (any distributed transactions that are captured will be replayed as local transactions)

The user needs the DBA privilege in order to capture the workload.

PRE-PROCESSING THE WORKLOAD


Preprocessing a captured workload transforms the captured data into replay files and creates all necessary metadata.This must be done once for every captured workload before they can be replayed.AFTER THE CAPTURED WORKLOAD IS PREPROCESSED, IT CAN BE REPLAYED REPEATEDLY ON A REPLAY SYSTEM.


Once the workload has been captured, the information in the capture files need to be preprocessed. Preprocessing transforms the captured data into replay files and creates all necessary metadata needed for replaying the workload. This must be done once for every captured workload before they can be replayed. After the captured workload is preprocessed, it can be replayed repeatedly on a replay system running the same version of Oracle Database. Typically, the capture files should be copied to another system for preprocessing. As workload preprocessing can be time consuming and resource intensive, it is recommended that this step be performed on the test system where the workload will be replayed.

REPLAYING THE PREPROCESSED WORKLOAD


After the workload capture is preprocessed and the test system is properly prepared, the replay data can be initialized.Initializing replay data loads the necessary metadata into tables required by workload replay.

After the replay data is initialized, connection strings used in the workload capture need to be remapped so that user sessions can connect to the appropriate databases and perform external interactions as captured during replay.

After the replay data is initialized and the connections are appropriately remapped, you need to prepare the database for workload replay.

Before a workload can be replayed, the application data state should be logically equivalent to that of the capture system at the start time of workload capture.The primary purpose of Database Replay is to test the effect of system changes on a captured workload.

It is recommended that the SYSTEM TIME ON THE REPLAY SYSTEM HOST be changed to a value that approximately matches the capture start time just before replay is started.Otherwise, an invalid data set may result when replaying time-sensitive workloads.

A captured workload may contain REFERENCES TO EXTERNAL SYSTEMS, such as database links or external tables, Directory Objects , URLs and Emails. Typically, you should reconfigure these external interactions to avoid impacting other production systems during replay

The replay user needs the DBA role and cannot be the SYS user.

After a captured workload has been preprocessed, it can be replayed on a test system. During the workload replay phase, Oracle Database performs the actions recorded during the workload capture phase on the test system by re-creating all captured external client requests with the same timing, concurrency, and transaction dependencies of the production system.

Database Replay uses a client program called the replay client to re-create all external client requests recorded during workload capture. Depending on the captured workload, you may need one or more replay clients to properly replay the workload. A CALIBRATION TOOL is provided to help determine the number of replay clients needed for a particular workload.

ADDM

ADDM(Automatic Database Diagnostic Monitor)


For Oracle systems, the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event, often saving time and resources reproducing a problem.

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The findings (problems) are listed in order of potential impact on database performance, along with recommendations to resolve the issue and the symptoms which lead to it's discovery.

An ADDM analysis is performed every time an AWR snapshot is taken and the results are saved in the database.The obvious place to start viewing ADDM reports is Enterprise Manager.

The value for the SET LONG command should be adjusted to allow the whole report to be displayed.
SET LONG 100000000

In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. ADDM provides the following benefits:

• Automatic performance diagnostic report every hour by default
• Problem diagnosis based on decades of tuning expertise
• Time-based quantification of problem impacts and recommendation benefits
• Identification of root cause, not symptoms
• Recommendations for treating the root causes of problems
• Identification of non-problem areas of the system
• Minimal overhead to the system during the diagnostic process

The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database server in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

The types of problems that ADDM considers include the following:

• CPU bottlenecks - Is the system CPU bound by Oracle or some other application?
• Undersized Memory Structures - Are the Oracle memory structures, such as the SGA, PGA, and buffer cache, adequately sized?
• I/O capacity issues - Is the I/O subsystem performing as expected?
• High load SQL statements - Are there any SQL statements which are consuming excessive system resources?
• High load PL/SQL execution and compilation, as well as high load Java usage
• RAC specific issues - What are the global cache hot blocks and objects; are there any interconnect latency issues?
• Sub-optimal use of Oracle by the application - Are there problems with poor connection management, excessive parsing, or application level lock contention?
• Database configuration issues - Is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or sub-optimal parameter settings?
• Concurrency issues - Are there buffer busy problems? Hot objects and top SQL for various problem areas

A problem finding can be associated with a list of Recommendation's for reducing the impact of the performance problem. Each recommendation has a benefit which is an estimate of the portion of DB time that can be saved if the recommendation is implemented. A list of recommendations can contain various alternatives for solving the same problem; you not have to apply all the recommendations to solve a specific problem.

Automatic database diagnostic monitoring is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable the automatic database diagnostic monitoring.


To diagnose database performance issues, ADDM analysis can be performed across any two AWR snapshots as long as the following requirements are met:

• Both the snapshots did not encounter any errors during creation and both have not yet been purged. 
• There were NO SHUTDOWN AND STARTUP actions between the two snapshots.

Typically, you would view output and information from the automatic database diagnostic monitor through Oracle Enterprise Manager or ADDM reports. However, you can display ADDM information through the DBA_ADVISOR views. This group of views includes:

DBA_ADVISOR_TASKS

This view provides basic information about existing tasks, such as the task Id, task name, and when created.

DBA_ADVISOR_LOG

This view contains the current task information, such as status, progress, error messages, and execution times.

DBA_ADVISOR_RECOMMENDATIONS

This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

DBA_ADVISOR_FINDINGS

This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.

ACTIVE SESSION HISTORY


With Oracle Database 10g, there is a new view introduced called V$ACTIVE_SESSION_HISTORY, which maintains data from active sessions, capturing the wait events and their wait times, the SQL_ID, and session information for the waiting session.In addition, the view V$SESSION_WAIT_HISTORY will provide the last 10 wait events for an active session.

With Oracle Database 10g, there are now over 700 wait events, primarily due to the fact that many previous wait events have been broken down into more granular events to make diagnostics more precise. To make these wait events easier to interpret, they are categorized into wait classes, with each wait class pointing to a high-level category of problem for a particular wait event


ASH stores the history of a recent session's activity and facilitates the analysis of the system performance at the current time. ASH is designed as a rolling buffer in memory, and earlier information is overwritten when needed. ASH uses the memory of the SGA.

SQL Profiles

SQL PROFILES


The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

A SQL Profile is stored persistently in the data dictionary, so it does not require any application code changes.After the SQL profiling completes, the query optimizer uses the information stored in the SQL profile, in conjunction with regular database statistics, to generate execution plans.


Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view. By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

SQL Profiles apply to the following statement types:

*    SELECT statements
*    UPDATE statements
*    INSERT statements (only with a SELECT clause)
*    DELETE statements
*    CREATE TABLE statements (only with the AS SELECT clause)
*    MERGE statements (the update or insert operations)

So, sql profiles simply take the query - execute bits of it, figure out how many rows are actually returned (or look at the execution HISTORY to see how many rows are actually flowing from the row sources as well) and STORE that information as extended statistics - and use that information the next time the query is hard parsed

stored outlines are a set of hints that say "use this index, do this table first, do that next, use this access path, perform this filter then that filter"....

sql profiles are more like extended statistics - they are the result of "analyzing a query", the information provided to the optimizer is not HOW to perform the query - but rather better information about how many rows will flow out of a step in the plan, how selective something is.

say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won't use it, the stored outline says "use this index - query that table - then do this". Since the GREAT index did not exist when the outline was generated - it won't be used.

Profiles only take effect when resource limits are "turned on" for the database as a whole.
Specify the RESOURCE_LIMIT initialization parameter.
RESOURCE_LIMIT = TRUE


STORED OUTLINES


A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.

Despite the effectiveness of using SQL Outlines, it has the following Disadvantages

1.The biggest flaw is that the behavior of Stored Outlines in association to cursor sharing is totally dependent of the cursor_sharing parameter, which means that if cursor_sharing is set to exact (which is the default), and we need to force a hint to a statement that doesn't use bind variables, creating an outline for all the different literals can range from aggravating to impossible.Note that there is a one-to-one correspondence between SQL text and its outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in applications with bind variables.
 

2.It has some performance implications since when outlines are enabled Oracle will check if an outline exists for each new statement it parses.
3.And in top of it all, the implementation and usage of the feature are unintuitive and often clumsy.

SQL Performance Analyzer

SQL PERFORMANCE ANALYZER

SQL Performance Analyzer compares the performance of SQL statements before and after the change and produces a report identifying any changes in execution plans or performance of the SQL statements.SQL Performance Analyzer measures the impact of system changes both on the overall response time of the SQL workload and of every individual SQL statement in the workload.

Capturing the Workload

You can store captured SQL statements in a SQL tuning set and use it as an input source for SQL Performance Analyzer. A SQL tuning set is a database object that includes one or more SQL statements, along with their execution statistics and execution context.SQL statements can be loaded into a SQL tuning set from different sources, including the cursor cache, Automatic Workload Repository (AWR), and existing SQL tuning sets.

The captured SQL statements should include the following information:
*    SQL text
*    Execution environment
        *    SQL binds, which are bind values needed to execute a SQL statement and generate accurate execution statistics
        *    Parsing schema under which a SQL statement can be compiled
        *    Compilation environment, including initialization parameters under which a SQL statement is executed
*    Number of times a SQL statement was executed


Setting up the Test System

You should configure the test database environment to match the database environment of the production system as closely as possible. In this way, SQL Performance Analyzer can more accurately forecast the effect of the system change on SQL performance.

After the test system is properly configured, export the SQL tuning set from the production system to a staging table, then import it from the staging table into the test system.

Creating a SQL Performance Analyzer Task

To run SQL Performance Analyzer, you must first create a SQL Performance Analyzer task. A task is a container that encapsulates all of the data about a complete SQL Performance Analyzer analysis.When creating a SQL Performance Analyzer task, you will need to select a SQL tuning set as its input source.The SQL tuning set remains constant in the SQL Performance Analyzer task and is executed in isolation during each SQL trial. Thus, performance differences between trials are caused by environmental differences.

Measuring the Pre-Change SQL Performance

Executing a SQL workload runs each of the SQL statements contained in the workload to completion. Each SQL statement in the SQL tuning set is executed once—one at a time—separately from other SQL statements without preserving their initial order of execution or concurrency.During execution, SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload.

Depending on its size, executing a SQL workload can be time and resource intensive.When executing a SQL workload, you can choose to generate execution plans only, without collecting execution statistics saving time and resource consumption.

Making a System Change

Make the change whose effect on SQL performance you intend to measure. SQL Performance Analyzer can analyze the effect of many types of system changes.

Measuring the Post-Change SQL Performance

After performing the system change, execute the SQL workload again to create post-change SQL trial. SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload a second time, resulting in a new set of performance data that can be used to compare to the pre-change version.

Comparing Performance Measurements

By default, SQL Performance Analyzer uses elapsed time as a metric for comparison. Alternatively, you can choose the metric for comparison from a variety of available SQL runtime statistics, including:
*    CPU time
*    Buffer gets
*    Disk reads
*    Disk writes
*    Any combination of these metrics in the form of an expression

Fixing Regressed SQL Statements

If the performance analysis performed by SQL Performance Analyzer reveals regressed SQL statements, then you can make changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor or using SQL plan baselines. You can then repeat the process of executing the SQL statements and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis.