You identified some DSS queries that perform expensive join and aggregation operations. The queries access historical data from noncurrent partition of the fact tables. What three actions could you…

Question

Options

• ASet the QUERY_REWRITE_ENABLED to TRUE at the session level.
• BCreate an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated
• CSet QUERY_REWRITE_ENABLED to TRUE at the instance level.
• DCreate an STS for the statements, run SQL Access Advisor for the STS, and implement any generated
• ESet QUERY_REWRITE_INTEGRITY to ENFORCED at the instance level.

• A
  7% (3)
• B
  76% (32)
• E
  17% (7)

Explanation

A: * QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for Oracle does not use rewrite. Oracle costs the query with rewrite and without rewrite and chooses the method with the lower Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization. C: You can use SQL Tuning Advisor to tune one or more SQL statements D: Using the SQL Access Advisor Wizard or API, you can do the following: ?Recommend materialized views and indexes based on collected or hypothetical workload ?Manage workloads. ?Mark, update, and remove recommendations. * 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. E: QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships. Oracle enforces and guarantees consistency and integrity. Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data

No community discussion yet for this question.