Actualtests 1Z0-117 Questions are updated and all 1Z0-117 answers are verified by experts. Once you have completely prepared with our 1Z0-117 exam prep kits you will be ready for the real 1Z0-117 exam without a problem. We have Updated Oracle 1Z0-117 dumps study guide. PASSED 1Z0-117 First attempt! Here What I Did.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-117 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Examine the following SQL statement:
Examine the exhibit to view the execution plan.
Which statement is true about the execution plan?
A. The EXPLAIN PLAN generates the execution plan and stores it in c$SQL_PLAN after executing the query. Subsequent executions will use the same plan.
B. The EXPLAIN PLAN generates the execution plan and stores it in PLAN_TABLE without executing the query. Subsequent executions will always use the same plan.
C. The row with the ID 3 is the first step executed in the execution plan.
D. The row with the ID 0 is the first step executed in the execution plan.
E. The rows with the ID 3 and 4 are executed simultaneously.
Explanation: Note the other_tag parallel in the execution plan.
Within the Oracle plan_table, we see that Oracle keeps the parallelism in a column called
other_tag. The other_tag column will tell you the type of parallel operation that is being
performed within your query.
For parallel queries, it is important to display the contents of the other_tag in the execution.
Q2. When would bind peeking be done for queries that vary only in values used in the WHERE clause?
A. When the column used in the WHERE clause has evenly distributed data and histogram exists on that column.
B. When the column used in the WHERE clause has evenly distributed data and index exists on that column.
C. When the column used in the WHERE clause has non uniform distribution of data, uses a bind variable, and no histogram exists for the column.
D. When the column used in the WHERE clause has non uniform distribution of data and histogram exists for the column.
Q3. Examine the query and its execution plan:
Which statement is true regarding the execution plan?
A. This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table.
B. The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset.
C. The query first fetches rows from the ORDERS table that satisfy the conditions, and then the join returns NULL form CUSTOMER_ID column when it does not find any corresponding rows in the CUSTOMERS table.
D. The query first joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not find any corresponding rows in the ORDERS table, and then fetches the rows that satisfy the conditions from the result set.
Q4. You are administering a database that supports a DSS workload, where in an application a set of queries use the query rewrite on materialized views. You notice that these queries are performing poorly.
Which two actions can you make to improve the performance of theses queries?
A. Use DBMS_MVIEW.EXPLAIN_REWRITE to analyze whether the queries are rewritten.
B. USE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the entire workload.
C. Create an STS for all the queries and use SQL performance Analyzer to generate recommendations for determining the regressed SQL statements.
D. Create an STS for all the queries in the application and use the SQL Tuning Advisor to generate recommendations.
E. Create an STS for all the queries in the application and use the SQL Access Advisor to generate a recommendation for optimizing materialized views for maximum query rewrite usage and fast refresh.
Q5. Tracing has been enabled for the HR user. You execute the following command to check the contents of the orcl_25052.trc trace file, which was generated during tracing:
Which two statements are correct about the execution of the command?
A. SCRIPT.SQL stores the statistics for all traced SWL statements.
B. Execution plans for SQL statements are stored in TEMP_PLAN_TABLE and can be queried by the user.
C. SQL statements in the output files are stored in the order of elapsed time.
D. TKPROF use TEMP_PLAN_TABLE in the HR schema as a temporary plan table.
E. Recursive SQL statements are included in the output file.
Explanation: INSERT Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.
Q6. A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database by using different services and tracing is enabled for the services. You want to view the detailed tracing setting for particular service.
What would you use to view the tracing information?
A. DBMS_SERVICE package
B. DBMS_MONITOR package
C. DBA_ENABLED_TRACES view
D. Trcsess and tkprof
Explanation: DBA_ENABLED_TRACES displays information about enabled SQL traces.
A: The DBMS_SERVICE package lets you create, delete, activate, and deactivate services for a single instance.
B: The DBMS_MONITOR package let you use PL/SQL for controlling additional tracing and statistics gathering.
Reference: Oracle Database Reference, DBA_ENABLED_TRACES
Q7. 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.
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.
Q8. An application supplied by a new vendor is being deployed and the SQL statements have plan baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You require the optimizer to use these baselines, but allow better plans to used, should any be created.
Which two tasks would you perform to achieve this?
A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
B. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
E. Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
Explanation: OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management B ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Q9. Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.
Which three statements are true regarding the execution plan?
A. The view operator collects all rows from a query block before they can be processed but higher operations in the plan.
B. The in-line query in the select list is processed as a view and then joined.
C. The optimizer pushes the equality predicate into the view to satisfy the join condition.
D. The optimizer chooses sort-merge join because sorting is required for the join equality predicate.
E. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables.
Not D, not E:
Sort Merge joins are used for UN-Equality and also there is no SORT clause in the SQL.
Note: The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true:
The join condition between two tables is not an equijoin, that is, uses an inequality condition such as <, <=, >, or >=.
Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.
Q10. 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
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