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