Oracle Facts

Answers for Oracle Interview Questions

2012-06-08T20:29:00.004-07:00

177)Whats the difference between a scalar sub Query and an Inline View?
A subquery in the FROM clause is called an inline view and this sub Query may return a number of columns and rows.A subquery in the WHERE clause is called a nested subquery.

A Scalar Sub Query is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

178)Can we lock statistics on a Table?
Yes, using the LOCK_STATS Procedure of the DBMS_STATS Package.

179)Will the use of a DB Link hinder the performance of a Query?
The Use of DB Links cause a performance degradation becasue of the Network Lag in pushing the huge amount of data from the remote site to the Local site. So the performance of the Query depends upon the activity which is carried out on the data and also on the amount of data which is carried out on the remote site.

The driving site hint forces query execution to be done at a different site than the initiating instance. If your SQL performs a sort, be aware that the sort will be performed on the LOCAL database.In distributed databases when a table has been partitioned into separate instances, a parallel query can be invoked to read the remote tables simultaneously.

182)Any idea on the OPTIMIZER_DYNAMIC_SAMPLING Parameter?
The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter controls the number of blocks read by the dynamic sampling query. The parameter can be set to a value from 0 to 10.

Optimizer dynamic sampling refers to the ability of the SQL optimizer to take a sample of rows from a table to calculate missing statistics.

Some of the sampling levels that can be used;

level 0 - do not use dynamic sampling

level 1 - Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

level 2 (default)- Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

level 4 - Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

levels 5, 6, 7, 8, and 9 - Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

level 10 - sample all blocks in the table

If you are using the default (which is 2) for OPTIMIZER_DYNAMIC_SAMPLING then dynamic sampling is only used for unanalyzed tables.
Setting it to 0 (off) will result in a default value of stats for unanalyzed tables.

Since Dynamic Sampling is used to analyze the un analyzed Tables and therefore facilitates the optimizer in getting the optimized execution plan based on the statistics availble on the tables. Since 9i, the Oracle optimizer is a cost based optimizer and therfore relys on the statistics of the table in finding the optimized Execution plan.

183)What does cost refer to in a cost based optimizer?
COST - The cost of the operation as estimated by the optimizer’s cost-based approach. The value of this column does not have any particular unit of measurement; it is merely a weight value used to compare costs of execution plans.

BYTES - The cost-based approach’s estimate of the number of bytes accessed by the operation.

Tip - The BYTES column is extremely important when evaluating how to tune a query. When an index is used and the number of bytes is great, it implies that doing a full table scan would perhaps be more efficient (i.e., reading the index and data is more costly than just reading the data in a full table scan). Also, the number of bytes helps us to determine which table should be accessed first in the query (driving table), because one table may limit the number of bytes needed from another. See Chapter 9 for tips on choosing the driving table.

Tip - Remember that both the COST and BYTES values in a query are estimates; it is quite possible for a version of a query with a higher estimated cost or bytes to run faster than another with a lower value.

184)What do you mean by stored outlines in Oracle?
Using Stored Outlines
=====================
Up until recently, the chief use for execution plans was to determine what Oracle was doing with queries at run time as a tuning tool. A fairly new facility called STORED OUTLINES allows a query to use a predetermined execution plan every time that query is run, no matter where the query is run from. People sometimes speak of the STORED OUTLINES as storing an execution plan, but this is not really what happens. Instead, Oracle stores a series of hints—instructions to the database to execute a query in a precise way—to duplicate the execution plan as saved during a recording session.

The following privileges are required to use STORED OUTLINES:
CREATE ANY OUTLINE
EXECUTE_CATALOG (to use the DBMS_OUTLN package)
PLUSTRACE (to use AUTOTRACE, if applicable)

You should create the outline tables in your own schema (which is a good idea); if you don’t, the outlines will be stored in the SYSTEM tablespace (which is almost always a mistake). The outline tables can be created in the current schema by running the DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES procedure with the following command:
exec dbms_outln_edit.create_edit_tables

There are two kinds of stored outlines: private, which are session-specific, and public, which can affect the entire database.
Which kind is being used is controlled by the USE_PRIVATE_ OUTLINES session parameter setting. If this setting is TRUE, then private outlines are used.

185)What are the caveats of Bitmap join Indexes?
You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.

186)What do you mean by pipelined table functions?
Pipelined table functions are functions that produce a collection of rows (such as a nested table) that can be queried like a physical database table or assigned to a PL/SQL collection variable. You can use a table function in place of the name of a database table in the FROM clause of a query or in place of a column name in the SELECT list of a query.

187)What is Consistent gets in Oracle?
The number of blocks read from the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as logical reads (all reads cached in memory). These are usually the CURRENT version of the block, but it can also be a Consistent Read (CR) version.

188)What is the Sequence Caching in Oracle?
The CACHE clause for a sequence determines the number of sequence values the database must cache in the SGA.

189)Will Data type conversion hinder the performance of a Query?
Yes, It acts as a overhead to the oracle optimizer to convert the values used in the WHERE Condition.

190)What are the different values for the STATISTICS_LEVEL parameter?
BASIC
TYPICAL
ALL

191)Any idea on the DB_FILE_MULTIBLOCK_READ_COUNT parameter?
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans thereby reducing the time taken for the execution of a Query. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The parameter db_file_multiblock_read_count is only applicable for tables/indexes that are full scanned, but it also effects the SQL optimizer in its calculation of the cost of a full-table scan.

If DB_FILE_MULTIBLOCK_READ_COUNT is not set or is set to 0 (zero), then the optimizer uses a value of 8 for costing.

Before 10g R2, DBA's used the db_file_multiblock_read_count initialization parameter to tell Oracle how many block to retrieve in the single I/O operation.

192)When does Oracle go in for a FULL Table scan?
When the Oracle optimizer feels that a Full Table scan is less costly to making use of the indexes to retrieve the data and also when there are no indexes available on the table which forces the optimizer to go in for a full table scan. Also Forcing the Optimizer to go in for a FULL Table Scan when there is a Hint applied to the Query

Answers for Oracle Interview Questions

2012-06-08T20:28:00.001-07:00

151)What is meant by Row Migration?
We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the "forwarding address". So, the original block just has the rowid of the new block and the entire row is moved.

When we FULL SCAN a table, we actually *ignore* the forwarding addresses (the head rowpiece we call it for a row). We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless. Oh sure, the forwarding address is consuming a couple of bytes on the block -- it is overhead -- but frankly, it is meaningless.

When we INDEX READ into a table -- then a migrated row will cause additional IO's. That is because the index will tell us "goto file X, block Y, slot Z to find this row". But when we get there we find a message that says "well, really goto file A, block B, slot C to find this row". We have to do another IO (logical or physical) to find the row.

152)When does a row get chained and migrated?
When a single row is stored in two or more blocks, this is called row chaining.

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the "forwarding address". So, the original block just has the rowid of the new block and the entire row is moved.

153)How do you verify whether there the rows are chained or migrated in a table?
select * from chained_rows gives information on the chained rows. Even DBA_Tables gives information on chained_cnt.

154)What is meant by Paging?
When paging occurs, information that is not currently being used is moved from memory to disk. This allows memory to be used by a process that currently needs it. If paging happens a lot, the system will experience decreases in performance, causing processes to take longer to run.

155)What is meant by swapping?
When swapping occurs, an active process is moved from memory to disk temporarily so that another active process that also desires memory can run. Swapping is based on system cycle time. If swapping happens a lot, your system is dead. Depending on the amount of memory available, an SGA that is too large can cause swapping.

156)When does this paging and swapping occur?
During Sorting operation when the Sort involves Disk Sorting.

157)How do you overcome Paging and Swapping?
By Increasing the SORT_AREA_SIZE for the during of the session which involves the sort operation.

158)Will a "!" negate the use of Indexes?
USE OF "NOT" CLAUSE WILL NEGATE THE USE OF BTREE INDEXES
========================================================

You can effectively use the NOT clause several ways:

*    Comparison operators ('<>', '!=', '^=')
*    NOT IN
*    NOT LIKE

159)What happens on the backend when we do a COMMIT statement?
When USER issues a COMMIT Statement, all the redo log buffer contents are written to the Redo log files.

160)How Often do you COMMIT in your application?
Reduce the Frequency of COMMITS in your application to increase the performance of the application.

161)Will the frequency of COMMIT hinder the performance of an application?
Since a COMMIT Statement issues a Disk I/O on the backend , Increase in the frequency of COMMIT's will decrease the performance of the application

162)Why Should we need to index a foreign Key?
Indexing a Foreign Key is necessary becasue a foreign key would have been created with the CASCADE clause, Which states that whenever a parent key column is deleted the corresponding child entries in the child table would be deleted or set to NULL depending on the CASCADE Clause. Since this delete or SET NULL Operation requires scanning the child table for finding the child entries, indexing the foreign key column will help in index searching the child entries instead of doing a full table scan during the CASCADE Operation thereby reducing the time required to complete the operation.

163)How do you identify whether Indexes are used by the application?
By Applying the MONITORING USAGE Clause in the ALTER INDEX Statement.

164)Whats the use of the QUERY REWRITE in materialized views in Oracle?
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).

165)Any Idea about Histograms?
Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order.

If your database exclusively uses bind variables, Oracle recommends deleting any existing Oracle histograms and disabling Oracle histogram generation (method opt) for any future dbms_stats analysis. This approach will use the number if distinct values to determine the selectivity of a column.

Most Oracle experts only recommend scheduled re-analysis for highly dynamic databases, and most shops save one very-deep sample (with histograms), storing the statistic with the dbms_stats.export_schema_stats procedure. The only exceptions are highly-volatile systems (i.e. lab research systems) where a table is huge one-day and small the next.

For periodic re-analysis, many shops us the table "monitoring" option and also method_opt "auto" after they are confident that all histograms are in-place.

Oracle histograms statistics can be created when you have a highly skewed index, where some values have a disproportional number of rows. In the real world, this is quite rare, and one of the most common mistakes with the CBO is the unnecessary introduction of histograms in the CBO statistics. As a general rule, histograms are used when a column's values warrant a change to the execution plan.

166)How Does Collecting Histograms influence the performance of an Query?
Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order.

167)Whats the use of the KEEP and RECYCLE Buffer Pools in Oracle?
DB_KEEP_CACHE_SIZE
==================

db_cache_size + db_keep_cache_size + db_recycle_cache_size = totally buffer cache size.

This parameter states the size which is required for pinning the objects to the shared pool so that they are not aged out of memory. Small frequently used look up tables can be pinned into the memory so that cost incurred in making a physical read frequently is reduced because of the data accessed from the cache.

DB_RECYCLE_CACHE_SIZE
=====================

DB_RECYCLE_CACHE_SIZE specifies the size of the RECYCLE buffer pool. This pool is reserved for table blocks of large tables on which full table scans are performed.

168)What is the HIGH WATER MARK LEVEL w.r.t. Oracle?
The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

169)Whats the use of the Bitmap Join Indexes?
The use of bitmap join indexes is restricted; you can only index the columns in the dimension tables.The columns used for the join must be primary key or unique constraints in the dimension tables, and if it is a composite primary key, you must use each of the columns in your join.
You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.

170)What are prefixed and non prefixed Indexes?

Prefixed Indexes
================

Prefixed indexes are indexes that contain keys from the partitioning key as the leading edge of the index.

Non-Prefixed Indexes
====================

Non-prefixed indexes are indexes that do not have the leading column of the partitioning key as the leading column of the index.

171)How do you shrink Un used Space?
You can shrink the Un used space of the table using the SHRINK SPACE Clause in the ALTER TABLE Statement

You can instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via the COMPACT clause—for example:
SQL> alter table inv shrink space compact;

172)When do you think that a Index needs to be rebuild?
Rebuilding an index has a key advantage over re-creating an index from scratch, as well as a key disadvantage. The advantage of rebuilding an index is that the existing index is in place until the rebuild operation is complete, so it can therefore be used by queries that are run concurrently with the rebuild process. The main disadvantage of the index rebuild process is that you will need space for both indexes, which is required during the rebuild process. Some of the key reasons to rebuild an index include the following:

*    Rebuilding an index that has become fragmented over time
*    Rebuilding an index after a large, direct-path load of data
*    You want to move an index to a different tablespace
*    The index is in an unusable state due to a partition-level operation on the associated table.

173)Any idea on Compressed Indexes in Oracle?
This is really an option to a normal b-tree index. It results in fewer leaf nodes, so less overall I/O and less to cache. All of this means Oracle's optimizer will be just a little more likely to use these than a normal uncompressed index. There is a cost to all of this, in CPU to uncompress when you access these. Also, in reading about how the optimizer uses these, and choosing the proper compression level, it begins to look daunting. Your mileage may vary.

174)What is Virtual Column in Oracle 11G?
It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Fucntional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column. Also Virtual column concept can applied only for Oracle Releases after 11G.A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.

Several caveats are associated with virtual columns:

*    You can define a virtual column only on a regular heap-organized table. You can't define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.
*    Virtual columns can't reference other virtual columns.
*    Virtual columns can reference columns only from the table in which the virtual column is defined.
*    The output of a virtual column must be a scalar value (a single value, not a set of values).

175)What are the benefits of using Virtual Column in terms of indexing and partitioning?
It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Fucntional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column.

Answers for Oracle Interview Questions

2012-06-08T20:27:00.003-07:00

146)What are the parameters which influence the sorting of Query Results?
SORT_AREA_SIZE
Temporary Tablespace and its size

147)Why and When Sorting Hinders the performance of an application?
Avoid Sorting

Disk Sorting Excessive I/O which severs as the main purpose for Performance Hindrance when i do a sort via the Order by clause and which is not able to fit inside the sort_Area_size allocated in the PGA.Sorting starts in Oracle PGA RAM (defined by the limits of sort_area_size and pga_aggregate_target 5% session limit), and a "disk sort" is invoked when the sort exceeds the maximum PGA allowed for the session.

A disk sort can far outweigh the unwanted effects of regular disk I/O read activity because a disk sort involves both physical reads and physical writes. First, Oracle must perform physical writes to a session's temporary tablespace for the sort activity it cannot handle in memory. Then, the database must turn right around and read that information back from disk to build the result set requested by the SQL query. So in essence, it's a double whammy especially for large result sets that are the product of heavy sort requests.

Oracle states that "The new sort algorithm shows more performance improvement for in-memory sorts.".Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources.For sorting in Oracle 10g release 2 there is a hidden parameter called "_newsort_enabled" that turns-on the new sorting algorithm.
We also see that in-memory sorts are CPU-intensive, and that faster processors will also improve in-memory sort performance.

Disk operations are about 10,000 times slower than a row's access in the data buffers.

If the clustering factor-an integer-roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.

In large active tables with a large number of index scans, row resequencing can triple the performance of queries.

Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer.

Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.

SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes. Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

SORT_AREA_SIZEs are not allocated until they are needed (and GROW to be sort_area_size bytes in size -- they don't start there) and are freed the second the sort is over.

Oracle will always use the cheapest method for sequencing a result set, and the optimizer will use index retrieval (extracting the rows in sorted order) if it consumes fewer resources than a back-end sort. Remember, a sort that cannot fit into RAM will have to be done in the TEMP tablespace, very slow with lots of disk I/O.

As a general rule, the sort_area_size should be large enough that only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.

A Query on the V$sysstat gives info on the sorts performed both in memory and disk sorts and also the number of rows that was sorted.

In general, Oracle sorting occurs under the following circumstances:

*    SQL using the ORDER BY clause
*    SQL using the GROUP BY clause
*    When an index is created
*    When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

IN MEMORY , ONE PASS AND MULTI PASS SORT
========================================

Explaination by Tom Kyte

In memory sort Everything fits in memory, no disk is needed, everything done in ram.

one pass sort We read some amount of data and sort it, the sort workarea fills up, we write that to disk. We do it again (read more, sort it) - this too spills to disk. We do this over and over until we've read the entire set of data and sorted each of the chunks. Now we need read a bit of each of the chunks we've sorted on disk and start returning data from there. In the one pass sort, we can read a bit of each chunk and start returning data. If we cannot read a bit of EVERY chunk then....

we are in the realm of the multi-pass sort. We read and merge the first few chunks - resulting in a new set. We read and merge another set of chunks - resulting in a new set. And so on - until we've processed all of the output from the first pass and created a "second pass" of more merged (more sorted) data. Then we merge those in the sort area and start returning data.

Please do have a look at this article
=====================================

http://www.nocoug.org/download/2003-08/how_cbo_works.ppt

148)Whats the use of temporary Tablespace w.r.t. sorting?
Disk Sorting Excessive I/O which severs as the main purpose for Performance Hindrance when i do a sort via the Order by clause and which is not able to fit inside the sort_Area_size allocated in the PGA.Sorting starts in Oracle PGA RAM (defined by the limits of sort_area_size and pga_aggregate_target 5% session limit), and a "disk sort" is invoked when the sort exceeds the maximum PGA allowed for the session.

A disk sort can far outweigh the unwanted effects of regular disk I/O read activity because a disk sort involves both physical reads and physical writes. First, Oracle must perform physical writes to a session's temporary tablespace for the sort activity it cannot handle in memory. Then, the database must turn right around and read that information back from disk to build the result set requested by the SQL query. So in essence, it's a double whammy especially for large result sets that are the product of heavy sort requests.

149)What are the different operations which involve disk I/O?
In Addition to SORTING ,Other SQL operations that might require disk sorting are:
*    ANALYZE
*    Select DISTINCT
*    UNION causes a SORT to eliminate the duplicates whereas UNIONALL Does not
*    INTERSECT
*    MINUS
*    Sort-Merge joins.

150)What is meant by Row chaining?
When a single row is stored in two or more blocks, this is called row chaining. This can cause potential performance issues because Oracle will have to retrieve data from multiple blocks (instead of one) when retrieving a chained row.

One of the methods of removing Row Chaining which hinders Performance is by Use of the MOVE Command
===================================================================================================

One method for resolving the row chaining within a table is to use the MOVE statement. When you move a table, Oracle requires an exclusive lock on the table; therefore you should perform this operation when there are no active transactions associated with the table being moved.

Also, as part of a MOVE operation, all of the rows are assigned a new ROWID. This will invalidate any indexes that are associated with the table. Therefore, as part of the move operation, you should rebuild all indexes associated with the table being moved. This example moves the EMP table:

SQL> alter table <Table_name> move

Answers for Oracle Interview Questions

2012-06-08T20:26:00.004-07:00

123)What is the basic Structure of a PLSQL Block?
Declare
Begin
Exception
End

124)What are the different types of PLSQL Objects that you have come across?
Stored Procedures
Stored Functions
Packages

125)What is the use of a Package?
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

126)When should you go in for a Package?
Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.

Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.

Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.

Added Functionality
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database.

Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the implementation of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.

127)What is the difference between a Procedure and a Function?
A Procedure can have out values but a Function cannot.
A Fucntion can return a value whereas a procedure cannot.
A User defined Function can be used in a SQL Statement whereas a procedure cannot.

128)Can a Function return more than one value?
No

129)Can a Function have OUT Parameters?
No

130)What is meant by a Fucntion Based Index?
A function-based index allows you to match any WHERE clause in an SQL statement and remove unnecessary large-table full-table scans with super-fast index range scans.

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 Funcions dont 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

131)What is benefit of using Functions?
The use of the RESULT_CACHE parameter while declaring a function helps in caching the results of the user defined functions for the input values. The Function should be declared determinstic for caching the results of the function and making use of a functional index on the user defined function.

132)Can we create an index on an user defined Fucntions?
Yes, the user defined function needs to be declared determinstic.

133)What is the use of the RESULT_CACHE Keyword in Functions?
This parameter is applicable to the user defined functions in oracle for caching the results of the function along with the input parameters.

134)What do you mean by a global Parameter?
Any Parameter which is defined in the package specification instead of defining them within the procedure or function while defining the body of the package are defined as global variables.

135)Can a Global parameter be referenced by some other packages or PLSQL?
Yes, by refering the parameter using the pacakage name in which the parameter is defined.

136)What is the use of an UNDO Tablespace?
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:
*    Roll back transactions when a ROLLBACK statement is issued
*    Recover the database
*    Provide read consistency
*    Analyze data as of an earlier point in time by using Oracle Flashback Query
*    Recover from logical corruptions using Oracle Flashback features

137)What is meant by FLASHBACK?
Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
With flashback features, you can do the following:
*    Perform queries that return past data
*    Perform queries that return metadata that shows a detailed history of changes to the database
*    Recover tables or rows to a previous point in time
*    Automatically track and archive transactional data changes
*    Roll back a transaction and its dependent transactions while the database remains online

138)How do you flashback a Query?
Using the "AS OF TIMESTAMP" clause, you will be able to flashback a Query.
Eg:-
SELECT * FROM <Table_name>
AS OF TIMESTAMP
   TO_TIMESTAMP('2012-06-06 00:00:00', 'YYYY-MM-DD HH:MI:SS')

139)Can we flashback a Table and database?
You can flashback a Table which is dropped to before drop using the "FLASHBACK TABLE TO BEFORE DROP".

You can flashback the Database using the "TO TIMESTAMP" Clause after shutting the Database and opening it in the MOUNT Mode.
Eg:-
SHUTDOWN DATABASE
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-2;

140)What is Snapshot too Old Error?
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving "snapshot too old" error, it doesn't guarantee that the database won't overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren't running in the database, to avoid these errors.

Regardless, while you can minimize the occurrence of "snapshot too old" errors with these approaches, you can't completely eliminate such errors without specifying the guaranteed undo retention feature. Oracle will keep new DML statements from executing when you set up guaranteed undo retention.

The "guarantee" part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period.

141)What is the main use of a Global Temporary Table?
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

Also the data residing within the Global Temporary Tables can be session specific or transaction specific. Also Global Temporary Tables allows the creation of indexes on the temporary data.

142)What do you mean by Cursor Sharing?
EXACT: The database doesn't replace any literals, and the optimizer sees the query as it's presented to the optimizer. The optimizer generates a different plan for each execution of the statement, based on the literal values in the statement. The plan would thus be an optimal one, but each statement has its own parent cursor, and therefore a statement that's executed numerous times can use a considerable amount of space in the shared pool. This could potentially lead to latch contention and a slowdown in performance.

FORCE: Regardless of whether there's a histogram, the optimizer will replace the literal values with a bind value and optimize this query as if it were in the following form:

select * from <Table_name> where <Column_name>=:b

The optimizer uses a single plan for each SQL statement, regardless of the literal values. Thus, the execution plan won't be optimal, as the plan is generic, and not based on the literal values. If a query uses literal values, the optimizer will use those values to find the most efficient execution plan. If there are no literals in the SQL statement, it's very hard for the optimizer to figure out the best execution plan. By "peeking" at the value of the bind variables, the optimizer can get a better idea of the selectivity of the where clause condition—it is almost as if literals had been used in the SQL statement. The optimizer peeks at the bind values during the hard parse state. Since the execution plan is based on the specific value of the bind variable that the optimizer happened to peek at, the execution plan may not be optimal for all possible values of the bind variable.

In this example, the optimizer uses bind peeking based on the specific value of the JOB column it sees. In this case, the optimizer uses the value Clerk to estimate the cardinality for the query. When it executes the same statement (with a different value in the JOB column, say, Manager), the optimizer will use the same plan that it generated the first time (JOB=Clerk). Since there is only one parent cursor and just child cursors for the distinct statements, there's less pressure on the shared pool. Note that a child cursor uses far less space in the shared pool than a parent cursor. Often, setting the cursor_sharing parameter to FORCE immediately resolves serious latch contention in the database, making this one of the few magic bullets that can help you quickly reduce latch contention.

SIMILAR (without a histogram on the JOB column): The database will use literal replacement—it uses a system-generated bind value instead of the literal value for the JOB column (Clerk). This is because the absence of a histogram on the JOB column tells the optimizer that the data in the JOB column isn't skewed, and therefore the optimizer chooses the same plan for each execution of the statement, even though the literal values are different. The optimizer thinks it shouldn't make any changes to the execution plans for the statements that differ only in literal values because the data is uniformly distributed. The SIMILAR setting without a histogram on the columns in a query provides the same query performance and a similar impact on the shared pool as when you specify the FORCE setting.

SIMILAR (with a histogram on the JOB column): When the optimizer sees the histogram in the JOB column, it realizes that the column is skewed—this tells the optimizer that the results of the query may vary widely depending on the literal value of the JOB column. Consequently, the optimizer generates a different plan for each statement based on the literal values—thus the plans are very efficient, as in the case when you specify the EXACT setting. The SIMILAR option with a histogram in place does use more space in the shared pool, but not as much as when you use the EXACT setting. The reason for this is that each statement has its own child cursor instead of a parent cursor.

The choice among the various settings of the cursor_sharing parameter really boils down to an assessment of what's more critical to database performance: using the default EXACT setting or SIMILAR (with a histogram on the relevant column) does provide better query performance but leads to the generation of numerous parent cursors (EXACT setting) or child cursors (SIMILAR setting). If there's a severe pressure in the shared pool, and consequent latch contention, the entire database will perform poorly. Under these circumstances, you're better off implementing a system-wide solution by setting the cursor_sharing parameter to FORCE, as this guarantees that there's only a single child cursor for each SQL statement. If you're concerned about the impact of a single SQL statement, just drop the histogram on the relevant columns used in the SQL statement and set the cursor_sharing parameter to FORCE—this will ensure that the optimizer uses system-generated bind values for the column(s) and ensures that the SQL statement uses much less space in the shared pool.

Answers for Oracle Interview Questions

2012-06-08T20:25:00.004-07:00

101)Will Context Switching hinder the performance of an application?
SQL statements are called from PL/SQL in a loop, so the execution will switch back and forth between the PL/SQL engine and the SQL engine. This switch between two environments is known as a context switch. Context switches increase elapsed time of your programs and introduce unnecessary CPU overhead. You should reduce the number of context switches by eliminating or reducing the switching between these two environments.

102)How will you overcome Context Switching in Oracle?

By reducing the number of sql and plsql calls back and forth from the sql and plsql environments.

103)What is meant by BULK COLLECT?
One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches. The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.

104)What is the use of the FORALL Clause in Oracle?
The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time.

105)What are the different types of collections available in Oracle?
NESTED TABLES
ASSOCIATIVE ARRAYS(INDEX BY TABLES)
VARRAYS

Please refer to the Oracle Documentation for more information on each of the collections.

106)Can you explain the collection types one by one along with their pitfalls?

Please refer to the Oracle Documentation for more information on each of the collections.

107)What are the different memory issues encountered when using a BULK COLLECT?
It is recommended you never issue a FETCH BULK COLLECT on a resultset without a LIMIT clause when you do not know ahead of time the size (or approximate size) of the resultset.You may be thinking that the database initialization parameter PGA_AGGREGATE_TARGET will insulate your system from such problems. This is not correct. This parameter only applies to memory allocations that the database can adjust internally as required, such as memory for sorting or hashing. If you ask for 100GB of PGA for PL/SQL collection memory, THE DATABASE WILL TRY TO HONOR THAT REQUEST, NO MATTER HOW MUCH TROUBLE THAT MAY CAUSE.

108)What is the use of a LIMIT Clause?
SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.

109)What is AWR?
The Oracle database uses AWR for problem detection and analysis as well as for self-tuning. A number of different statistics are collected by the AWR, including wait events, time model statistics, active session history statistics, various system- and session-level statistics, object usage statistics, and information on the most resource-intensive SQL statements.

The AWR is used to collect performance statistics including:

*    Wait events used to identify performance problems.
*    Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
*    Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
*    Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
*    Object usage statistics.
*    Resource intensive SQL statements.

110)What is ADDM?
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 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

111)What is the use of ADDM Report and Recommendations?
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 RECOMMENDATIONs 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 do not have to apply all the recommendations to solve a specific problem.

112)What is SQL Performance Analyzer?

Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

*    Database, operating system, or hardware upgrades.
*    Database, operating system, or hardware configuration changes.
*    Database initialization parameter changes.
*    Schema changes, such as adding indexes or materialized views.
*    Refreshing optimizer statistics.
*    Creating or changing SQL profiles.

113)When exactly do you go in for a SQL Performance Analyzer?

Whenever we want compare the performance of the statements in a tuning set before and after a database change whether major or minor, we can make use of the SQL Performance Analyzer.

114)What is ASH?

Retrieving ASH information is necessary if you need to get session information more current than you can retrieve from the AWR report.Again, AWR information is generated only hourly by default. ASH information is gathered every second from V$SESSION, and stores the most useful session information to help gauge database performance at any given moment.

You may need to view the DBA_HIST_ACTIVE_SESS_HISTORY historical view in order to get the ASH information you need if your database is very active.The MMON background process, which manages the AWR hourly snapshots, also flushes ASH information to the historical view at the same time.If there is heavy activity on the database, and the buffer fills between the hourly AWR snapshots, the MMNL background process will wake up and flush the ASH data to the historical view.

115)What are the different Views that can be Queried for getting info on ASH?
ASH information is gathered every second from V$SESSION, and stores the most useful session information to help gauge database performance at any given moment.
You may need to view the DBA_HIST_ACTIVE_SESS_HISTORY historical view in order to get the ASH information you need if your database is very active.

116)What is meant by Latch?

Latches are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects, and files.
Latches are locks designed to be held for extremely short periods of time¿for example, the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool. Latches are typically requested internally in a 'willing to wait' mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an 'immediate' mode, which is similar in concept to a SELECT FOR UPDATE NOWAIT, meaning that the process will go do something else, such as try to grab an equivalent sibling latch that may be free, rather than sit and wait for this latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the luck of the draw, if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters¿just a mob of waiters constantly retrying.
Oracle uses atomic instructions like 'test and set' and 'compare and swap' for operating on latches. Since the instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets to test and set the latch even though many processes may be going for it simultaneously. Since the instruction is only one instruction, it can be quite fast. Latches are held for short periods of time and provide a mechanism for cleanup in case a latch holder 'dies' abnormally while holding it. This cleanup process would be performed by PMON.

Latch Spinning

Waiting for a latch can be an expensive operation. If the latch is not available immediately and we are willing to wait for it, as we likely are most of the time, then on a multi-CPU machine our session will spin trying over and over, in a loop, to get the latch. The reasoning behind this is that context switching (i.e., getting 'kicked off' the CPU and having to get back on the CPU) is expensive. So, if the process cannot get a latch immediately, we¿ll stay on the CPU and try again immediately rather than just going to sleep, giving up the CPU, and trying later when we¿ll have to get scheduled back on the CPU. The hope is that the holder of the latch is busy processing on the other CPU (and since latches are designed to be held for very short periods of time, this is likely) and will give it up soon. If after spinning and constantly trying to get the latch, we still fail to obtain it, only then will our process sleep, or take itself off of the CPU, and let some other work take place.

117)How do you overcome Latches in Oracle?
We can overcome latch contention by setting the DB_BLOCK_LRU_LATCHES parameter, increasing the number of latches to service when needed thus decreasing the latch contention.

118)What is the use of 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.

119)How do you accept a Profile Recommended by ADDM?
To configure automatic SQL tuning, run the SET_TUNING_TASK_PARAMETER procedure in the DBMS_SQLTUNE package:
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SQL_TUNING_TASK',
    parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
In this example, the automatic SQL tuning task is configured to automatically accept SQL profiles recommended by the SQL Tuning Advisor.

120)Any idea on the Database Bufffer Cache?
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The database buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.

Oracle Database uses the buffer cache to store blocks read from disk. ORACLE DATABASE BYPASSES THE BUFFER CACHE FOR PARTICULAR OPERATIONS, SUCH AS SORTING AND PARALLEL READS.

The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.

If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

121)Can you throw some light on the Data Dictionary and the Library Cache?
The library cache includes the shared SQL areas, private SQL areas (in the case of a multiple transaction server), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.

122)How do you overcome Data Dictionary and Library Cache Contention?
Whenever new statements are added to the library cache area, this type of latch must be acquired by Oracle as part of the operation. Oracle scans the library cache area during the parse phase for matching SQL statements. If one is not found, then Oracle will complete the task, obtain the library cache latchfor the SQL statement and then insert it into the library cache area for future usage by the Oracle database.

Contention due to the library cache latches as well as shared pool latches is usually due to applications not using bind variables.You can set the CURSOR_SHARING parameter to force Oracle to use bind variables, even if your application hasn't specified them in the code. You can choose between a setting of FORCE or SIMILAR for this parameter to force the substituting of bind variables for hard-coded values of variables. The default setting for this parameter is EXACT, which means that the database won't substitute bind variables for literal values. When you set the CURSOR_SHARING parameter to FORCE, Oracle converts all literals to bind variables. The SIMILAR setting causes a statement to use bind variables only if doing so doesn't change a statement's execution plan. Thus, the SIMILAR setting seems a safer way to go about forcing the database to use bind variables instead of literals.

The library cache contention usually disappears once you set the CURSOR_SHARING parameter to FORCE or to SIMILAR.

There are two types of latches: “willing to wait” latches (example is a library cache latch) and “not willing to wait” latches (an example is a redo copy latch). A process that is willing to wait will try to acquire a latch. If none are available, it will spin and then request the latch again. It will continue to do this up to the _SPIN_COUNT initialization parameter (note that spinning costs CPU). If it can’t get a latch after spinning up to the _SPIN_COUNT, it will go to sleep, not do anything for a while, and then will wake up after one centisecond (one hundredth of a second). It will do this twice. It will then start this process again, spinning up to the _SPIN_ COUNT and then sleeping for twice as long (two centiseconds). After doing this again, it will double again. So the pattern is 1, 1, 2, 2, 4, 4, etc. It will do this until it gets the latch. Every time the latch sleeps, it will create a latch sleep wait. An example of a “willing to wait” latch is a library cache latch.

Data Dictionary Cache Contention

The "row cache objects" is a serialization latch that protects the access to the data dictionary cache in the SGA. This latch is acquired whenever you are referencing metadata objects in the data dictionary cache.

The Row Cache Objects Latch is a correlated latch to the shared pool latch is the row cache objects latch. The purpose of this latch is to provide data protection for user processes that access the cached data dictionary area within Oracle.

The row cache object latch can also indicate SQL with excessive hard parsing or excessive reliance on data dictionary information such as views row-level security, synonyms, etc. The general solution for row cache latch waits is to increase shared_pool_size.

Answers for Oracle Interview Questions

2012-06-08T20:24:00.002-07:00

82)What is meant by External Tables in Oracle?
The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.

Prior to Oracle Database 10g, external tables were read-only. However, as of Oracle Database 10g, external tables can also be written to.

A feature has been added to external tables that allows users to preprocess input data before it is sent to the access driver.Preprocessing also requires some special precautions on the part of the DBA. Because the executables called by preprocessing programs will be executed under the privileges of the Oracle software owner and malicious executable code can cause a lot of damage, the DBA should be extremely careful in monitoring executables for potentially harmful code.

with external tables you can

o merge a flat file with an existing table in one statement.
o sort a flat file on the way into a table you want compressed nicely.
o do a parallel direct path load -- without splitting up the input file, writing
umpteen scripts and so on
o run sqlldr in effect from a stored procedure or trigger (insert is not sqlldr)
o do multi-table inserts
o flow the data through a pipelined plsql function for cleansing/transformation

Differences you find between SQL Loader and External Tables
===========================================================

*    If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles.

Unloading in the context of external tables means creating an external table using the CTAS method.

83)Can we fire DML Statements on External Tables?
NO DML and Indexing is supported for External tables even in 11G.

84)When should you go in for an External Table?
External tables has wide variety of advantages when compare to sql loader and also in the case of external tables, the data resides outside the database where as the data loaded using sql loader will reside inside the database. So when space is a constraint, then prolly going in for an external table is much better than loading data using sql loader.

Also data in the external table doesn't need an oracle backup since the flat file resides outside the database and a normal file backup is fine which can be taken care by the OS Admin.

85)What is meant by PLSQL Profiler?
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.

86)What is the use of SQL Profiler Tool in Oracle?
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks.With this interface, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level. This information includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.

87)Have you heard of SQL Tuning Advisor and SQL Access Advisor?

Use the SQL Tuning Advisor for tuning SQL statements. Typically, you can run this advisor in response to an ADDM performance finding that recommends its use.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, as well as on a SQL workload.

The SQL Access Advisor helps define appropriate access structures such as indexes and materialized views to optimize SQL queries. The advisor takes a SQL workload as an input and recommends which indexes, materialized views, or logs to create, drop, or retain for faster performance. You can select your workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.

The recommendations that this advisor makes include possible indexes, materialized views, or materialized view logs that can improve your query performance for the given workload.

90)What is the use of the SQL Performance Analyzer?

Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

•Database, operating system, or hardware upgrades.
•Database, operating system, or hardware configuration changes.
•Database initialization parameter changes.
•Schema changes, such as adding indexes or materialized views.
•Refreshing optimizer statistics.
•Creating or changing SQL profiles.

91)What do you mean by Fragmentation?

Tom kyte's defininition of fragmentation is that you have many "small" holes (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape)

92)What is meant by Shared pool Fragmentation?

Performance degradation occurs in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.Fragmentation of the shared pool can lead to memory not being allocated.

Shared Pool Latch Contention is almost always due to Shared pool fragmentation. Process will hold the shared pool latch for a long time while searching for a free chunk of memory when the shared pool is fragmented.The shared pool may be fragmented into many smaller free chunks. But whenever there is a request for a large piece of memory, then the recreatable or freeable chunks of memory have to flushed from the shared pool so that they can be coalesced to a large chunk of free memory which is able to statisfy the request.That coalescing might involve walking thro shared pool freelists many times and flushing freeable chunks until a large chunk able to statisfy the request is created.

93)What happens on the Oracle Backend whenever you fire a Query?

94)What is parsing?

Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user knowing what is happening to the statement that was fired. This process is known as Parsing.

Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.

95)Can you explain the Parse, Bind , Execute and Fetch Phases?

We can refer to the DBA Study guide for more information.

96)What is a Cursor?
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.

Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

97)What are the different Types of Cursors?
Implicit Cursor
Explicit Cursor
REF Cursor
BULK COLLECT and FOR ALL Cursor
Parameterized Cursor

98)What is a parameterized Cursor?

99)What is a reference Cursor?

A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query. Cursor variables have several advantages over explicit cursors including:

*    Cursor variables can point to a variety of queries provided the queries have a suitable return type. In contrast, explicit cursors are tied to individual queries.
*    Cursor variables allow cursors to be opened independently of being processed.
*    Cursor variables can be passed as parameters between application layers, as well as between server side components.
*    Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.

100)What is meant by Context Switching?
When we consider "CONTEXT SWITCHING" in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.

WHEN A SQL STATEMENT IS WRAPPED IN A PL/SQL FUNCTION AND CALLED FROM SQL QUERIES, THE COST OF CONTEXT-SWITCHING IS AUTOMATICALLY DOUBLED—IT NOW OCCURS BOTH INSIDE AND OUTSIDE THE FUNCTION.

SQL statements are called from PL/SQL in a loop, so the execution will switch back and forth between the PL/SQL engine and the SQL engine. This switch between two environments is known as a context switch. Context switches increase elapsed time of your programs and introduce unnecessary CPU overhead. You should reduce the number of context switches by eliminating or reducing the switching between these two environments.

Answers for Oracle Interview Questions

2012-06-08T20:23:00.003-07:00

75)What is the difference that you find with a Locally managed and a Dictionary managed Tablespace?

Benefits of LMTs
Below are the key benefits offered by LMTs. Not all are achievable when migrating to LMTs.

Dictionary contention is reduced.

Extent management in DMTs is maintained and carried out at the data dictionary level. This requires exclusive locks on dictionary tables. Heavy data processing that results in extent allocation/deallocation may sometimes result in contentions in the dictionary.

Extents are managed at the datafile level in LMTs. Dictionary tables are no longer used for storing extent allocation/deallocation information. The only information still maintained in the dictionary for LMTs is the tablespace quota for users.

Space wastage removed.

In DMTs, there is no implied mechanism to enforce uniform extent sizes. The extent sizes may vary depending on the storage clause provided at the object level or the tablespace level, resulting in space wastage and fragmentation.

Oracle enforces the uniform extents allocation in the LMTs (when created with UNIFORM SIZE clause). Space wastage is removed, as this would result in all the same sized extents in the tablespace.

No Rollback generated.

In DMTs, all extent allocations and deallocations are recorded in the data dictionary. This generates undo information thus using vital resources and may compete with other processes.

In LMTs, no rollback is generated for space allocation and deallocation activities.

ST enqueue contention reduced.

In DMTs, Space Transaction (ST) enqueue is acquired when there is a need for extent allocations in DMTs. It is also exclusively acquired by SMON process for coalescing free space in DMTs. Only one such enqueue exists per instance, and may sometimes result in contention and performance issues if heavy extent processing is being carried out. The following error is common in such scenario.

ORA-01575: timeout warning for space management resource
As ST enqueue is not used by LMTs it reduces the overall ST enqueue contention.

Recursive space management operations removed.

In DMTs, SMON process wakes up every 5 minutes for coalescing free space in DMTs. Optionally, the ALTER TABLESPACE <tablespace name> COALESCE command is also used to coalesce DMTs and reduce fragmentation.

On the other hand, LMTs avoid recursive space management operations and automatically track adjacent free space, thus eliminating the need to coalesce free extents. This further reduces fragmentation.

Fragmentation reduced.

Fragmentation is reduced in LMTs but not completely eliminated. Since adjacent free spaces are automatically tracked, there is no need to do coalescing, as is required in the case of DMTs.

Storage Parameters in DMT's

Storage parameters are used in DMTs to specify the object sizing. These parameters are not of much importance in UNIFORM type LMTs but play a role in deciding the initial allocation of space.

76)What do you mean by ASSM?

Prior to Oracle 9i, each block had to be read so the freelist could be checked to see if there was room in the block. In 9i, the bitmap can be checked reducing the number of blocks read unneccessarily.

Starting from Oracle9i, the PCTUSED, FREELISTS, and FREELIST GROUPS parameters are ignored with locally managed tablespaces and ASSM.The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

In an LMT, Oracle moves the tablespace information out of the data dictionary tablespace and stores it directly within the tablespace itself. The second major tablespace enhancement in Oracle9i, was automatic segment space management (ASSM). a.k.a. bitmap freelists.   With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals.

Automatic free space management is only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bitmapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.

With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals. Usage of ASSM causes some performance hindrance but this has been certainly overcome with the 11G Release 2.ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction. When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time.

With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.Segment space management auto is a good method for handling objects with varying row sizes.

Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM FTS tablespaces are consistently slower than freelist FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications unless partitioning is used with Oracle Parallel Query.

Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes. You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.

Only locally managed tablespaces can use bitmap segment management.

When an object such as a table or index is created using the locally managed tablespace, with automatic segment-space management enabled, there is no need to specify the pctused or freelists.

PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks.

FREELIST
This is a list of blocks kept in the segment header that may be used for new rows being inserted into a table. When an insert is being done, Oracle gets the next block on the freelist and uses it for the insert. When multiple inserts are requested from multiple processes, there is the potential for a high level of contention since the multiple processes will be getting the same block from the freelist, until it is full, and inserting into it. Depending on how much contention you can live with, you need to determine how many freelists you need so that the multiple processes can access their own freelist.

AUTO - This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

The package DBMS_SPACE contains a procedure called SPACE_USAGE that gives information about how space is being used within blocks under the segment high water mark.

77)How come the performance of an Query increase by setting the Segment Space Management as AUTO?

With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.Segment space management auto is a good method for handling objects with varying row sizes.

78)What is the memory architecture of Oracle?

You can explore the same from the DBA Study guide.

79)What is SGA and PGA?

SGA ( System Global Area )

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:
*    Database buffer cache
*    Redo log buffer
*    Shared pool
*    Java pool
*    Large pool (optional)
*    Streams pool

PGA ( Program Global Area )

Oracle Database allocates a program global area (PGA) for each server process. The PGA is used to process SQL statements and to hold logon and other session information. For the purposes of memory management, the collection of all PGAs is known as the instance PGA. Using an initialization parameter, you set the size of the instance PGA, and the database distributes memory to individual PGAs as needed.

The content of the PGA memory varies, depending on whether or not the instance is running the shared server option. Generally speaking, the PGA memory is divided into the following areas:
*    Session Memory
*    Private SQL Area

The Private SQL Area section includes the following topics:
*    Cursors and SQL Areas
*    Private SQL Area Components
*    SQL Work Areas

80)What are the components of the SGA and the PGA?

The SGA contains the following data structures:
*    Database buffer cache
*    Redo log buffer
*    Shared pool
*    Java pool
*    Large pool (optional)
*    Streams pool

The content of the PGA memory varies, depending on whether or not the instance is running the shared server option. Generally speaking, the PGA memory is divided into the following areas:
*    Session Memory
*    Private SQL Area

The Private SQL Area section includes the following topics:
*    Cursors and SQL Areas
*    Private SQL Area Components
*    SQL Work Areas

81)What is meant by sga_target and pga_aggregate_target and memory_target and what are the benefits of the same?

*    If you're using automatic memory management by setting the MEMORY_TARGET parameter, Oracle allocates 0.25% of the MEMORY_TARGET parameter's value to the server result cache.
*    If you're using automatic shared memory management with the SGA_TARGET parameter, the allocation is 0.5% of the SGA_TARGET parameter.
*    If you're using manual memory management by setting the SHARED_POOL_SIZE parameter, the allocation is 1% of the SHARED_POOL_SIZE parameter.

Answers for Oracle Interview Questions

2012-06-08T20:22:00.000-07:00

61)What is meant by Partitioning?

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

62)What are local and Global Indexes?

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes).

Global Index: A global index is a one-to-many relationship, allowing one index partition to map to many table partitions. A global index can only be a range partition!

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

The index remains available and online throughout the operation. Hence no other applications are affected by this operation. The index doesn't have to be rebuilt after the operation.

GLOBAL NONPARTITIONED INDEXES behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.

Local Index: A local index is a one-to-one mapping between a index partition and a table partition. In general, local indexes allow for a cleaner “divide and conquer” approach for generating fast SQL execution plans with partition pruning.A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

63)What are the different Types of Partitioning Available?
Range Partitioning
List Partitioning
Hash Partitioning
Composite Partitioning

64)What are the Paritioning methods which are available in Oracle 11G Onwards?
Extended Composite Partitioning
Interval Partitioning
System Partitioning
Reference Partitioning
Virtual Column-Based Partitioning

65)How do you say that partitioning increases the performance of the Queries?

Partitioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons are:

Partition Pruning
Partition-wise Joins
Parallel DML

Partition Pruning
The Oracle server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

Partition-wise Joins
A partition-wise join is a join optimization that you can use when joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that they minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data distribution.

Parallel DML
Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.

66)What is meant by Skip Scan Indexes?
When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.

67)What is meant by composite partitioning?
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:

*    Range-Hash (available since 8i)
*    Range-List (available since 9i)
*    Range-Range
*    List-Range
*    List-Hash
*    List-List

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:

*    Interval-Hash
*    Interval-List
*    Interval-Range

68)What are the Different combinations of partitioning possible?

In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:

*    Range-Hash (available since 8i)
*    Range-List (available since 9i)
*    Range-Range
*    List-Range
*    List-Hash
*    List-List

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:

*    Interval-Hash
*    Interval-List
*    Interval-Range

69)What do you mean by SQL Profiles?

SQL profiles are optionally generated corrections and improvements to statistics. The recommendation (and code) to implement a SQL profile is manifested through the output of the SQL Tuning Advisor. You can manually enable SQL profiles or configure them to be automatically accepted. SQL profiles help the optimizer derive better execution plans.

When working with tuning advice and SQL profiles, ensure that the database account you're using has the ADMINISTER SQL MANAGEMENT OBJECT system privilege granted to it. This privilege contains all of the privileges required to manage tuning tasks and SQL profiles.

How do you know if a SQL profile is being used by the optimizer?
Set AUTOTRACE on and view the execution plan with the profile enabled and then disabled. You should see a lower-cost execution plan being used when the profile is enabled. Additionally, consider inspecting the SQL_PROFILE column of V$SQL.

70)What are the Automatic Tuning tools available in Oracle?
SQL TUNING ADVISOR
SQL ACCESS ADVISOR
SQL PERFORMANCE ANALYZER
ADDM (Automatic Database Diagnostic Monitor) Reports

71)What is meant by a Global Temporary Table?
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables.

72)What is meant by Scalar SubQuery Caching?

It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

Oracle makes use of a HASH Table for caching the results of the Scalar subQuery so that it doesn't have to fire the same scalar subQuery each time for the same value passsed to the Scalar sub Query.The HASH Table is capable of storing values from 1..255 (Oracle 10G and 11G) and will look in that hash table slot to see if the answer exists. So Depending on the distinct number of input values passed to a Scalar Sub Query, Oracle tries to fetch the data from the Hash Table instead of firing the Sub Query again but this is limited to the number of entries permitted to be stored in the HASH Table.

73)What is Subquery Factoring?
The SQL WITH clause only works on Oracle 9i release 2 and beyond.Formally, the WITH clause was called subquery factoring.

Depending on the release of Oracle in use, the global temporary tables (GTT) might be a better solution than the WITH clause because indexes can be created on the GTT for faster performance.

Making use of the With Clause , Oracle makes use of a optimization approach where by the Query specified in the With Clause is execued only once and this data is stored in the temporary Tablespace( Similar to creating a Global Temporary Table). So irrespective of the number of times the Query within the WITH CLAUSE is called, it will be Executed only once thereby limiting the number of times the data is accessed from disk thereby reducing the execution time of the Query.

On tiny datasets, the time involved in the temporary table setup can take longer than the original query itself so is not a particularly useful mechanism.

Any subqueries we define must be referenced at least once otherwise Oracle will raise an Exception.

74)What do you mean by a locally managed Tablespace?
Oracle maintains a bitmap in each datafile to track used and free space availability in an LMT. The initial blocks in the datafiles are allocated as File Space Bitmap blocks to maintain the extent allocation information present in the datafile. Each bit stored in the bitmap corresponds to a block or a group of blocks. Whenever the extents are allocated or freed, oracle changes the bitmap values to reflect the new status. Such updates in the bitmap header do not generate any rollback information.

The number of blocks that a bit represents in a bitmap depends on the database block size and the uniform extent size allocated to the tablespace. For example, if the DB_BLOCK_SIZE parameter is set to 8K, and the tablespace is created with uniform extent sizing of 64K, then 1 bit will map to one 64K extent, i.e., 64K (extent size)/8K (block size) = 8 database blocks.

Answers for Oracle Interview Questions

2012-06-08T20:20:00.004-07:00

51)Will Oracle make use of a Hint if it is specified in the Query?
Not necessarily use the hint, if the optimizer sees a better optimized path than the one specified by means of the hint.

52)Can we specify more than one Hint in a Query?
yes.Multiple hints are separated with a space. Specifying multiple hints that conflict with each other causes the query to use none of the hints that are conflicting.

53)What are the different Hints that you have come across?
Using the FIRST_ROWS hint to generally force the use of indexes
Using the ALL_ROWS hint to generally force a full table scan
Using the FULL hint to force a full table scan
Using the INDEX hint to force the use of an index
Using the NO_INDEX hint to disallow a specified index from being used
Using the INDEX_JOIN hint to allow the merging of indexes on a single table
Using the INDEX_ASC hint to use an index ordered in ascending order
Using the INDEX_DESC hint to use an index ordered in descending order
Using the AND_EQUAL hint to access multiple b-tree indexes
Using the INDEX_COMBINE hint to access multiple bitmap indexes
Forcing fast full scans with the INDEX_FFS hint
Using the ORDERED hint to specify the driving order of tables
Using the LEADING hint to specify just the first driving table
Using the NO_EXPAND hint to eliminate OR expansion
Queries involving multiple locations and the DRIVING_SITE hint
Using the USE_MERGE hint to change how tables are joined internally
Forcing the subquery to process earlier with PUSH_SUBQ
Using the parallel query option and using PARALLEL and NO_PARALLEL
Using APPEND and NOAPPEND with parallel options
Caching and pinning a table into memory with the CACHE hint
Forcing clustering with the CLUSTER hint
Forcing cluster hashing with the HASH hint
Overriding the CURSOR_SHARING setting with the CURSOR_SHARING_EXACT hint

54)What are the different types of Joins available in Oracle?
Inner join
Outer Join
Cartesian join
Full join

55)Explain where each type of Join can be used w.r.t. Different Sceanarios?
NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed or have a highly selective non-unique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched.

If the driving row source (the records retrieved from the driving table or the outer table) is quite large, other join methods may be more efficient.

HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index. As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins.

SORT-MERGE Join - Requires a sort on both tables. It is built for best optimal throughput and does not return the first row until all rows are found.
HASH Join - Can require a large amount of memory for the hash table to be built. Does not return the first rows quickly. Can be extremely slow if it must do the operation on disk. Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. It is usually faster than a SORT-MERGE.

56)What is meant by Reverse Key Indexes?
It is as simple as that -- instead of all inserts hitting one side of the index, producing mass contention on one side of the index -- a reverse key index will tend to distribute the inserts throughout the entire structure more evenly.

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

Reverse key indexes void the ability of an index range scan.They only work with exact equality.

With a Reverse Key Index, Oracle will reverse the bytes and insert the values sorted by the reverse bytes into the index

57)What and where do you make use of a Reverse Key Index?
For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

58)Will a Reverse Key Index increase the performance of a Query?
Reverse key indexes void the ability of an index range scan.They only work with exact equality.Also RBO doesn't make use of a Reverse Key Index. So the Large Batch Inserts making use of Sequences benefit from the use of a Reverse Key Index

59)How do you read an Explain Plan?

The Explain plan shows the following

The row source tree is the core of the execution plan. It shows the following information:

*    An ordering of the tables referenced by the statement
*    An access method for each table mentioned in the statement
*    A join method for tables affected by join operations in the statement
*    Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

*    Optimization, such as the cost and cardinality of each operation
*    Partitioning, such as the set of accessed partitions
*    Parallel execution, such as the distribution method of join inputs

Examining an explain plan lets you look for throw-away in cases such as the following:

*    Full scans
*    Unselective range scans
*    Late predicate filters
*    Wrong join order
*    Late filter operations

60)What are the different types of Joins used by the optimizer?
The following are the joins used by the optimizer

NESTED LOOP JOIN
SORT MERGE JOIN
HASH JOIN
ANTI JOIN and
SEMI JOIN

Answers for Oracle Interview Questions

2012-05-20T08:23:00.000-07:00

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

2012-05-20T08:20:00.002-07:00

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

2012-05-20T08:17:00.001-07:00

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

2012-05-20T08:15:00.002-07:00

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

2012-05-16T09:00:00.000-07:00

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

2012-05-12T10:06:00.002-07:00

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

2012-05-12T10:02:00.001-07:00

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

2012-05-12T09:57:00.001-07:00

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

2012-05-12T09:54:00.004-07:00

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.

Index Organized Tables

2012-04-29T19:49:00.001-07:00

INDEX ORGANIZED TABLES

Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure.Primarily used with high-updates tables, Oracle Index Organized tables structure reduces table fragmentation.An index-organized table must have a primary key. Oracle index-organized tables are best suited for use with queries based on primary key values.

Disadvantages of IOT

It takes longer to insert into the IOT
You don't have good secondary index capabilities

It takes longer to put the data away in the IOT (mostly regardless of the arrival order) but it is retrieved much faster with less resources (assuming you have something like the stock quotes or document management system examples) .It is best to use an IOT when you only query the table's contents by specifying the primary key in the WHERE clause.If you query other, non-PK columns in the WHERE clause, you may find the IOT will slow down your performance.

As there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.All non-key columns beyond the column specified in the INCLUDING clause are stored in the overflow segment.

Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

Key compression
Overflow storage area and specific column placement
Secondary indexes, including bitmap indexes.

Scalar Sub Query Caching

2012-04-29T19:46:00.000-07:00

SCALAR SUB QUERY CACHING

A subquery in the FROM clause is called an inline view.A subquery in the WHERE clause is called a nested subquery.

The SQL WITH clause only works on Oracle 9i release 2 and beyond.
Formally, the WITH clause was called subquery factoring.

Depending on the release of Oracle in use, the global temporary tables (GTT) might be a better solution than the WITH clause because indexes can be created on the GTT for faster performance.

What exactly is a scalar subquery?

It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

Oracle makes use of a HASH Table for caching the results of the Scalar subQuery so that it doesn't have to fire the same scalar subQuery each time for the same value passed to the Scalar sub Query.The HASH Table is capable of storing values from 1..255 (Oracle 10G and 11G) and will look in that hash table slot to see if the answer exists. So Depending on the distinct number of input values passed to a Scalar Sub Query, Oracle tries to fetch the data from the Hash Table instead of firing the Sub Query again but this is limited to the number of entries permitted to be stored in the HASH Table.

The Scalar Sub Query Caching helps in reducing the number of times plsql Functions are called from a SQL. So there occurs a context switching between the SQL Engine and the PLSQL Engine. Context Switching is one the reasons for Performance Hindrance.

But Scalar SubQueries involving PLSQL Functions requires the PLSQL Function to be deterministic so that the PLSQL Functions always returns the same output for the same input.

SUB QUERY FACTORING

Making use of the With Clause , Oracle makes use of a optimization approach where by the Query specified in the With Clause is executed only once and this data is stored in the temporary Tablespace( Similar to creating a Global Temporary Table). So irrespective of the number of times the Query within the WITH CLAUSE is called, it will be Executed only once thereby limiting the number of times the data is accessed from disk thereby reducing the execution time of the Query.

On tiny datasets, the time involved in the temporary table setup can take longer than the original query itself so is not a particularly useful mechanism.

Any subqueries we define must be referenced at least once otherwise Oracle will raise an Exception.

SUBQUERY FACTORING AND DML

Subquery factoring is part of the SELECT statement itself, which means it can be used anywhere a SELECT is used. For example, the following statement formats are all valid.

INSERT INTO table_name
WITH subquery_name AS (
        SELECT ...
        FROM   ...
        )
SELECT ...
FROM   subquery_name;

UPDATE table_name
SET    column_name = ( WITH subquery_name AS (
                               SELECT ...
                               FROM   ...
                               )
                       SELECT ...
                       FROM   subquery_name );

DELETE
FROM   table_name
WHERE column_name IN ( WITH subquery_name AS (
                                SELECT ...
                                FROM   ...
                                )
                        SELECT ...
                        FROM   subquery_name );

INSERT INTO table_name
VALUES ( (WITH subquery_name AS (
                  SELECT ...
                  FROM   ...
                  )
          SELECT ...
          FROM   subquery_name) );


SUB QUERIES CAN BE INCLUDED IN VIEWS but there is a small "gotcha". Some DDL-generators wrap the view's SQL in parentheses and this raises an exception when subqueries are used, as follows.

SQL> CREATE VIEW view_with_subquery
2 AS
3     (
4      WITH subquery_name AS (
5         SELECT SYSDATE AS date_column
6         FROM   dual
7         )
8      SELECT date_column
9      FROM   subquery_name
10     );
   )
   *
ERROR at line 10:
ORA-32034: unsupported use of WITH clause
The correct way to code this is to remove the outer parentheses from the SQL, as follows.

SQL> CREATE OR REPLACE VIEW view_with_subquery
2 AS
3     WITH subquery_name AS (
4        SELECT SYSDATE AS date_column
5        FROM   dual
6        )
7     SELECT date_column
8     FROM   subquery_name;

View created.

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

The above link gives info on the Scalar Sub query Caching in Oracle Explained by Tom Kyte.

Avoid Sorting

2012-04-29T19:41:00.004-07:00

AVOID SORTING

Disk Sorting Excessive I/O which severs as the main purpose for Performance Hindrance when i do a sort via the Order by clause and which is not able to fit inside the sort_Area_size allocated in the PGA.Sorting starts in Oracle PGA RAM (defined by the limits of sort_area_size and pga_aggregate_target 5% session limit), and a "disk sort" is invoked when the sort exceeds the maximum PGA allowed for the session.

A disk sort can far outweigh the unwanted effects of regular disk I/O read activity because a disk sort involves both physical reads and physical writes. First, Oracle must perform physical writes to a session's temporary tablespace for the sort activity it cannot handle in memory. Then, the database must turn right around and read that information back from disk to build the result set requested by the SQL query. So in essence, it's a double whammy especially for large result sets that are the product of heavy sort requests.

Oracle states that "The new sort algorithm shows more performance improvement for in-memory sorts.".Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources.For sorting in Oracle 10g release 2 there is a hidden parameter called "_newsort_enabled" that turns-on the new sorting algorithm.
We also see that in-memory sorts are CPU-intensive, and that faster processors will also improve in-memory sort performance.

Disk operations are about 10,000 times slower than a row's access in the data buffers.

If the clustering factor-an integer-roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.

In large active tables with a large number of index scans, row re-sequencing can triple the performance of queries.

Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer.

Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.

SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes. Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

SORT_AREA_SIZE's are not allocated until they are needed (and GROW to be sort_area_size bytes in size -- they don't start there) and are freed the second the sort is over.

Oracle will always use the cheapest method for sequencing a result set, and the optimizer will use index retrieval (extracting the rows in sorted order) if it consumes fewer resources than a back-end sort. Remember, a sort that cannot fit into RAM will have to be done in the TEMP tablespace, very slow with lots of disk I/O.

As a general rule, the sort_area_size should be large enough that only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.

A Query on the V$sysstat gives info on the sorts performed both in memory and disk sorts and also the number of rows that was sorted.

In general, Oracle sorting occurs under the following circumstances:

SQL using the ORDER BY clause
SQL using the GROUP BY clause
When an index is created
When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

Other SQL operations that might require disk sorting are:

ANALYZE
Select DISTINCT
UNION causes a SORT to eliminate the duplicates whereas UNIONALL Does not
INTERSECT
MINUS
Sort-Merge joins.

IN MEMORY , ONE PASS AND MULTI PASS SORT

Explanation by Tom Kyte

In memory sort Everything fits in memory, no disk is needed, everything done in ram.

one pass sort We read some amount of data and sort it, the sort work area fills up, we write that to disk. We do it again (read more, sort it) - this too spills to disk. We do this over and over until we've read the entire set of data and sorted each of the chunks. Now we need read a bit of each of the chunks we've sorted on disk and start returning data from there. In the one pass sort, we can read a bit of each chunk and start returning data. If we cannot read a bit of EVERY chunk then....

we are in the realm of the multi-pass sort. We read and merge the first few chunks - resulting in a new set. We read and merge another set of chunks - resulting in a new set. And so on - until we've processed all of the output from the first pass and created a "second pass" of more merged (more sorted) data. Then we merge those in the sort area and start returning data.

Row Chaining

2012-04-29T19:36:00.001-07:00

ROW CHAINING

The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

Improper settings for PCTUSED (e.g., set too small) can cause huge degradations in the performance of SQL insert statements. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation where the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data blocks for the insert.

With a true "chained row", part of the DATA of a row is on one block and part of it is on another block. So, instead of just having a forwarding address on one block -- and the data on another (thats a migrated row), we have data on two or more blocks....

In the case of Row Chaining, Oracle may hinder performance issues only when the requested data is not present in the current block and Oracle has to fetch the requested data from the chained block.

select * from chained_rows gives information on the chained rows. Even DBA_Tables gives information on chained_cnt.

Any table with a long/long raw will have chained rows.When a table has more than 255 columns, rows that have data after the 255th column are likely to
be chained within the same block. This is called intra-block chaining.With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

ROW MIGRATION

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the "forwarding address". So, the original block just has the rowid of the new block and the entire row is moved.

When we FULL SCAN a table, we actually *ignore* the forwarding addresses (the head row piece we call it for a row). We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless. Oh sure, the forwarding address is consuming a couple of bytes on the block -- it is overhead -- but frankly, it is meaningless.

When we INDEX READ into a table -- then a migrated row will cause additional IO's. That is because the index will tell us "goto file X, block Y, slot Z to find this row". But when we get there we find a message that says "well, really goto file A, block B, slot C to find this row". We have to do another IO (logical or physical) to find the row.

HOW TO IDENTIFY ROW CHAINING AND ROW MIGRATION

we can detect the Migrated or chained rows in a TABLE or CLUSTER by using the following methods.

Using ANALYZE,REPORT.TXT and V$VIEWS.

Analyze

Before doing this Analyze create the table that can hold chained rows.Execute UTLCHAIN.SQL script found in the ($ORACLE_HOME)/rdbms/admin directory, this can be run to create the CHAINED_ROWS table or else create a similar table with same column and datatypes and sizes as the CHAINED_ROWS table.

SQL>ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED ROWS;

SQL>SELECT owner_name,table_name,head_rowid from chained_rows where TABLE_NAME = 'YOUR_TABLE_NAME';

REPORT.TXT and V$VIEWS

The Migrated or chained rows can be detected by checking the "table fetch continued row" statistic in V$SYSSTAT or in REPORT.TXT.

Note:REPORT.TXT is a file created during UTLBSTAT and UTLESTAT analyze.

HOW TO ELIMINATE MIGRATED AND CHAINED ROWS

It is VERY important to understand the distinction between migrated and chained rows. Row chaining is UNAVOIDABLE but row migration can be MANAGED and resolved through reorganization.

Note:Chaining if its going to be in all Tables then it becomes design issue and properly the DBlock size is done.

Else to avoid row chaining for very few occasions if you increase the DBlock size the effects of it as follows.

For Eg:

Space in the Buffer cache will be wasted if you are doing random access to small rows and have a large block size .For Eg an Block size of 8 KB and a 50 byte row size the wasted space will be 7950 bytes in the buffer cache in an random access.

The steps involved are:

1)Analyze the table ....list chained rows
2)Copy the rows to another table
3)Delete the rows from the original table
4)insert the rows from step 2 back to original table.

Step 4 eliminates the Migrated rows because Migration only occurs during an UPDATE.

QUERY TO DETECT TABLES WITH MIGRATED OR CHAINED ROWS

select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
   where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc
;

ASSM

2012-04-29T19:29:00.000-07:00

AUTOMATIC SEGMENT SPACE MANAGEMENT

Prior to Oracle 9i, each block had to be read so the freelist could be checked to see if there was room in the block. In 9i, the bitmap can be checked reducing the number of blocks read unnecessarily.

Starting from Oracle9i, the PCTUSED, FREELISTS, and FREELIST GROUPS parameters are ignored with locally managed tablespaces and ASSM.The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

In an Locally Managed Tablespace, Oracle moves the tablespace information out of the data dictionary tablespace and stores it directly within the tablespace itself. The second major tablespace enhancement in Oracle9i, was automatic segment space management (ASSM). a.k.a. bitmap freelists. With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals.

Automatic free space management is only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bit mapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.

With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals. Usage of ASSM causes some performance hindrance but this has been certainly overcome with the 11G Release 2.ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction. When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time.

With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.Segment space management auto is a good method for handling objects with varying row sizes.

Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes. You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.

Only locally managed tablespaces can use bitmap segment management.

FREELIST

This is a list of blocks kept in the segment header that may be used for new rows being inserted into a table. When an insert is being done, Oracle gets the next block on the freelist and uses it for the insert. When multiple inserts are requested from multiple processes, there is the potential for a high level of contention since the multiple processes will be getting the same block from the freelist, until it is full, and inserting into it. Depending on how much contention you can live with, you need to determine how many freelists you need so that the multiple processes can access their own freelist.

AUTO - This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

The package DBMS_SPACE contains a procedure called SPACE_USAGE that gives information about how space is being used within blocks under the segment high water mark.

LOCALLY MANAGED TABLESPACES

Movement of the space management out of the data dictionary and into the tablespace have two benefits. First, the tablespace become independent and can be transportable (transportable tablespaces). Second, locally managed tablespaces remove the O/O contention away from the SYS tablespace.

Locally managed tablespaces with ASSM ignore any specified values for PCTUSED, NEXT, and FREELISTS specified in the table definition.

DB Links

2012-04-29T19:24:00.002-07:00

DATABASE LINKS

The Use of DB Links cause a performance degradation becasue of the Network Lag in pushing the huge amount of data from the remote site to the Local site. So the performance of the Query depends upon the activity which is carried out on the data and also on the amount of data which is carried out on the remote site.

The driving site hint forces query execution to be done at a different site than the initiating instance. If your SQL performs a sort, be aware that the sort will be performed on the LOCAL database.In distributed databases when a table has been partitioned into separate instances, a parallel query can be invoked to read the remote tables simultaneously.

Partitioning

2012-04-21T23:26:00.000-07:00

PARTITIONING

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Here are some suggestions for when to partition a table:

Tables greater than 2 GB should always be considered as candidates for partitioning.
Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
When the contents of a table need to be distributed across different types of storage devices.

Here are some suggestions for when to consider partitioning an index:

Avoid rebuilding the entire index when data is removed.
Perform maintenance on parts of the data without invalidating the entire index.
Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

PARTITIONING FOR PERFORMANCE

By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. These features include:

Partition Pruning
Partition-Wise Joins

Oracle partition pruning only accesses those data blocks required by the query.Partitioning Pruning refers to the act of eliminating partitions which are not of concern and scanning only the partitions which are of concern and containing the data which is requested thereby eliminating the need to scan the entire table to search for the requested data and scanning only the partitions on which the data falls.Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined together and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy.Storing different partitions in different tablespaces allows the database administrator to do backup and recovery operations on each individual partition, independent of the other partitions in the table.