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.
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.
No comments:
Post a Comment