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