Home » Oracle » 1z0-117 » What three actions could you perform to improve the response time of the queries without modifying the SQL statements?
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 perform to improve the response time of the queries without modifying the SQL statements?
A. Set the QUERY_REWRITE_ENABLED to TRUE at the session level.
B. Create an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated recommendations for materialized views.
C. Set QUERY_REWRITE_ENABLED to TRUE at the instance level.
D. Create an STS for the statements, run SQL Access Advisor for the STS, and implement any generated recommendations for materialized views.
E. Set QUERY_REWRITE_INTEGRITY to ENFORCED at the instance level.
Correct Answer: BCD
Explanation/Reference:
A:*QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database.
Values:
false
Oracle does not use rewrite. true
Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.
force
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 important.
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:
Note:
* 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.
Incorrect:
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.
Values:
enforced
Oracle enforces and guarantees consistency and integrity.
trusted
Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.
stale_tolerated
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
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
