An application issues many expensive join aggregations type queries.
Examine the Exhibit to view the queries.
Which two could help improve the performance of these SQL statements without changing application code?
Exhibit:
A. Create B*-Tree indexes on the join columns.
B. Create a materialized view with query rewrite enabled for the first statement and nested MVIEWs for the other statements.
C. Collect histogram statistics on columns for which aggregating functions are performed.
D. Create an STS for these queries and use SQL Access Advisor, which may generate advice about MVIEWs.
E. Create an STS for these queries and use SQL Performance Analyzer, which may generate advice about MVIEWs.
Correct Answer: BD
Explanation/Reference:
B:Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries.
D:
* STS - SQL tuning set.
*A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and execution context. You can use the set as an input source for various advisors, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer.
* SQL Access Advisor:
Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant.
The SQL Access Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.
Note:
*Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems.Abitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.
*By default, the Oracle creates a b_tree index. In a b-tree, you walk the branches until you get to the node that has the data you want to use. In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data.
Incorrect:
not E:SQL Performance Analyzer enables you to assess the performance impact of any system
change resulting in changes to SQL execution plans and performance characteristics. Examples of common system changes for which you can use SQL Performance Analyzer include:
Database upgrade
Configuration changes to the operating system, hardware, or database
Database initialization parameter changes
Schema changes, for example, adding new indexes or materialized views
Gathering optimizer statistics
Validating SQL tuning actions, for example, creating SQL profiles or implementing partitioning
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
