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