1Z0-117 | What Highest Quality 1Z0-117 vce Is?
Q21. View the exhibit and examine the plans in the SQL baseline for a given statement. Which interpretation is correct?
A. A new plan cannot be evolved because SYS_SQL_bbedc41f554c408 is accepted.
B. Plan SYS_SQL_PLAN_bbdc741f554c408 will always be used by the optimizer for the query.
C. A new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before it can be used.
D. Plan SYS_SQL_bbedc741a57b5fc2 can be used by the optimizer if the cost of the query is less than plan SYS_SQL_PLAN_bbedc741f554c408.
E. Plan SYS_SQL_PLAN_bbedc741f554c408 will not be used until it is fixed by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function.
Answer: C
Explanation:
Note:
*
Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.
SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle =>
'SYS_SQL_7b76323ad90440b9')
FROM dual;
* Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.
* fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
Q22. Which four types of column filtering may benefit from partition pruning when accessing tables via partitioned indexes?
A. Equality operates on List-Partitioned Indexes
B. Not Equal operates on a Global Hash-Partitioned Indexes
C. Equality operates on System-Partitioned Tables
D. In-List operates on Range-Partitioned Indexes
E. Not Equal operates on a local Hash-Partitioned Indexes
F. Equality operates on Range-Partitioned Indexes
G. Equality operates on Hash-Partitioned Indexes
Answer: A,D,F,G
Explanation: Oracle Database prunes partitions when you use range, LIKE, equality (A, F), and IN-list (D) predicates on the range or list partitioning columns, and when you use equality (G) and IN-list predicates on the hash partitioning columns.
Reference: Oracle Database VLDB and Partitioning Guide 11g, Information that can be Used for Partition Pruning
Q23. You are administering a database supporting an OLTP workload where the users perform frequent queries for fetching a new rows as possible, involving join operations on recently inserted data. In addition at night, a few DSS queries are also performed. Examine the initialization parameters for the instance:
Which two options would you use for the optimizer?
A. Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.
B. Add the hint ALL_ROWS in the DOS queries.
C. Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0.
D. Add a hint INDEX_COMBINE in all DSS queries.
E. Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.
Answer: A,E
Explanation: The last appended rows are more likely to be found quickly with FIRST_ROWS_n.
E: Make it not to prioritize index instead if table scan. OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index
access path look half as expensive as normal.
Q24. Examine the Exhibit.
Which two statements are true about the bloom filter in the execution plan?
A. The bloom filter prevents all rows from table T1 that do not join T2 from being needlessly distributed.
B. The bloom filter prevents all rows from table T2 that do not join table T1 from being needlessly distributed.
C. The bloom filter prevents some rows from table T2 that do not join table T1 from being needlessly distributed.
D. The bloom filter is created in parallel by the set of parallel execution processes that scanned table T2.
E. The bloom filter is created in parallel by the set of parallel execution processes that later perform join.
F. The bloom filter is created in parallel by the set of parallel execution processes that scanned table T1.
Answer: C,E
Q25. Examine the Exhibit to view the structure of an indexes for the SALES table.
The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values.
What would you do to influence the optimizer for better selectivity?
A. Drop bitmap index and create balanced B*Tree index on the CUST_ID column.
B. Create a height-balanced histogram for the CUST_ID column.
C. Gather statistics for the indexes on the SALES table.
D. Use the ALL_ROWS hint in the query.
Answer: D
Explanation: OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance. Values: FIRST_ROWS_N - The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000). FIRST_ROWS - The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows. ALL_ROWS - The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
Q26. Which type of SQL statement would be selected for tuning by the automatic SQL framework?
A. Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement
B. Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.
C. Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.
D. Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.
E. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.
Answer: A
Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning
Q27. 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.
Answer: A,C,D
Explanation: * 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: . Recommend materialized views and indexes based on collected or hypothetical
workload information.
. Manage workloads.
. Mark, update, and remove recommendations.
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.
Q28. An application accessing your database got the following error in response to SQL query:
ORA-12827: insufficient parallel query slaves available
View the parallel parameters for your instance:
No hints are used and the session use default parallel settings.
What four changes could you make to help avoid the error and ensure that the query executes in parallel?
A. Set PARELLEL_DEGREE_POLICY to AUTO.
B. Increase the value of PARELLEL_MAX_SERVERS.
C. Increase PARELLEL_SERVERS_TARGET.
D. Decrease PARELLEL_MIN_PERCENT.
E. Increase PARELLEL_MIN_SERVERS.
F. Decrease PARELLEL_MIN_TIME_THRESHOLD.
G. Increase PARELLEL__MIN_TIME_THRESHOLD.
Answer: A,C,D,G
Explanation: C: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.
By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.
D: Note: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves
A, G: PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
Q29. View Exhibit1 and examine the structure and indexes for the MYSALES table.
The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million
rows.
View exhibit 2 and examine one of the queries and its autotrace output.
Which two methods can examine one of the queries and its autotrace output?
A. Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes.
B. Use the INDEX_COMBINE hint in the query.
C. Create a composite index involving the CUST_ID and PROD_ID columns.
D. Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute.
E. Collect histogram statistics for the CUST_ID and PROD_ID columns.
Answer: B,C
Explanation: B: The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint.
C: Combining the CUST_ID and PROD_ID columns into an composite index would improve performance.
Q30. You have created some materialized views to improve the performance of several queries.
Which four must be true to enable sessions to benefit from improved response time made possible by these materialized views?
A. Query rewrite must be enabled for the sessions.
B. Bitmap indexes must exist on all the columns involved in the join operations for the defining query of the MVIEWs.
C. All or part of the query results must be obtainable from one or more MVIEWs.
D. Bitmap join indexes must exist on all the columns involved in the join operations.
E. Session users must have query rewrite privilege.
F. The MVIEWs must be enabled for query rewrite.
G. All or part of the query results must be obtainable from one MVIEW.
Answer: A,B,C,F
Explanation: A: For a given user's session, ALTER SESSION can be used to disable or enable query rewrite for that session only.
B: Bitmap indexes on the join columns would improve performance.
C (not G) : One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.
F:
*
A materialized view is only eligible for query rewrite if the ENABLE QUERY REWRITE clause has been specified, either initially when the materialized view was first created or subsequently with an ALTER MATERIALIZED VIEW statement.
*
Enabling or disabling query rewrite: by the CREATE or ALTER statement for individual materialized views by the initialization parameter QUERY_REWRITE_ENABLED by the REWRITE and NOREWRITE hints in SQL statements
Note:
*
A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.