Want to know Actualtests 1Z0-117 Exam practice test features? Want to lear more about Oracle Oracle Database 11g Release 2: SQL Tuning Exam certification experience? Study Tested Oracle 1Z0-117 answers to Down to date 1Z0-117 questions at Actualtests. Gat a success with an absolute guarantee to pass Oracle 1Z0-117 (Oracle Database 11g Release 2: SQL Tuning Exam) test on your first attempt.
2021 Aug 1Z0-117 free exam questions
Q21. 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.
Q22. You executed the following statements:
Which two statements are true about the query execution?
A. The execution plan is generated and fetched from the library cache.
B. The query executes and displays the execution plan and statistics.
C. The query executes and inserts the execution plan in PLAN_TABLE.
D. The query executes and execution plan is stored in the library cache and can be viewed using v$SQL_PLAN.
E. The query will always use the plan displayed by the AUTOTRACE output.
Answer: B,D
Explanation: B: set autotrace traceonly:Displays the execution plan and the statistics (as
set autotrace on does), but doesn't print a query's result.
Note:
/ Autotrace
Autotrace can be configured to run the SQL & gives a plan and statistics afterwards or just
give you an explain plan without executing the query. To achieve this use the following:
*
Explain only set autotrace traceonly explain
*
Execute with stats and explain plan
set autotrace on explain stat (with data returned by query)
or
autotrace traceo expl stat (without data returned by query)
*
To make the output from an autotrace more readable col plan_plus_exp format a100
*
Turn off autotrace set autotrace off
/ V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3054.htm#REFRN302
Q23. In which three situations must you collect optimizer statistics manually for database objects in addition to automatic statistics collection?
A. When substantial DML activity occurs between the nightly automatic stats gathering maintenance job
B. When substantial activity occurs on a partition of the partitioned table.
C. When a table is used for bulk loads that add 10% or more to the total size of the table
D. When an index is created or dropped for a column
E. When the degree of parallelism is explicitly defined for a table
Answer: A,B,C
Explanation:
http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12509
Q24. 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
Answer: C
Explanation: DBA_ENABLED_TRACES displays information about enabled SQL traces.
Incorrect:
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
Q25. While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database.
What does the optimizer do in this situation?
A. Updates the existing SQL profiles for which the statistics are stale.
B. Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC
C. Starts the statistics collection process by running GATHER_STATS_JOB
D. Writes a warning message in the alert log file
Answer: B
Explanation: Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROCprioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.
Note:
* The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output:
/ Recommendations to gather relevant statistics for objects with stale or no statistics
Because optimizer statistics are automatically collected and refreshed, this problem occurs only when automatic optimizer statistics collection is disabled. See "Managing Automatic Optimizer Statistics Collection".
/ Auxiliary statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics
The database stores this auxiliary information in an object called a SQL profile.
* Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects them both for the table and associated indexes.
Automatic collection eliminates many manual tasks associated with managing the optimizer. It also significantly reduces the risks of generating poor execution plans because of missing or stale statistics.
Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROC prioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.
Reference: Oracle Database Performance Tuning Guide, Managing Automatic Optimizer Statistics Collection
Up to date 1Z0-117 exam guide:
Q26. You are logged in as the HR user and you execute the following procedure:
SQL > exec DBMS_STATS.SET_TABLE_PREFS (‘HR’, ‘EMPLOYEES’, ‘PUBLISH’, ‘FALSE’);
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (‘HR’, ‘EMPLOYEES’);
Which statement is true about the newly gathered statistics?
A. They are temporary and purged when the session exits.
B. They are used by the optimizer for all sessions.
C. They are locked and cannot be overwritten.
D. They are marked as pending and stored in the pending statistics table.
Answer: D
Explanation: In previous database versions, new optimizer statistics were automatically published when they were gathered. In 11g this is still the default action, but you now have the option of keeping the newly gathered statistics in a pending state until you choose to publish them.
The DBMS_STATS.GET_PREFS function allows you to check the 'PUBLISH' attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state.
Reference: Statistics Collection Enhancements in Oracle Database 11g Release 1, Pending Statistics
Q27. Your database has the OLTP_SRV service configured for an OLTP application running on a middle tier. This service is used to connect to the database by using connection pools. The application has three modules. You enabled tracing at the service by executing the following command:
SQL exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (‘OLTP_SRV’);
What is the correct method of consolidating the trace files generated by the procedure?
A. Use all trace files as input for the tkprof utility to consolidate the trace files for a module.
B. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module.
C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility.
D. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes.
Answer: C
Explanation:
Note:
* Oracle provides the trcsess command-line utility that consolidates tracing information
based on specific criteria.
The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can
help you monitor applications running against the Oracle Server.
Note: SERV_MOD_ACT_TRACE_ENABLE Procedure Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified
Reference: Oracle Database Performance Tuning Guide
Q28. See the table below:
All parallel execution servers are available and sessions use default settings for parallelism.
Which three are true about parallel execution in your instance?
A. Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.
B. Parallel execution occurs for all DML statements.
C. Parallel execution occurs for those statements that access tables with dictionary DOP defined.
D. Parallel execution occurs for those statements that access tables with no dictionary DOP defined.
E. Parallel execution occurs for all DDL statements.
Answer: A,C,D
Explanation: A (not B, Not E): PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will
ascertain if the query’s estimated execution time is likely to run longer than the acceptable
value (in seconds) for pARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for
parallel execution exist right now, it will allow the query to execute; otherwise, it will delay
its execution until sufficient resources exist. This helps prevent a single parallel query from
consuming excessive resources at the cost of other non-parallelizable operations. The
default of this parameter is 10 seconds.
C, D: In earlier versions of the Oracle Database, we had to determine the DOP more or
less manually, either with a parallel hint or by setting a parallel degree with alter table.
There was an automatic computation of the DOP available for the objects with dictionary
DOP of default, derived from the simple formula CPU_COUNT *
PARALLEL_THREADS_PER_CPU.
If there were insufficient parallel servers to satisfy the requested DOP, one of three things
could occur:
/ The SQL would be run at a reduced DOP (be downgraded)
/ The SQL would run in serial mode (be serialized)
/ If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of
the DOP was achievable, then the the SQL statement might terminate with "ORA-12827:
insufficient parallel query slaves available".
Note:
*
PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO
MANUAL - This is the default. Disables Auto DOP, statement queuing and in-memory parallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2).
*
Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:
.
Queries that involve table or index range scans.
.
Bulk insert, update or delete operations.
.
Table and index creation.
*
Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it.
*
Three modes are available to request a DOP :
default fixed adaptive
*
The DOP is determined in the following priority order:
hint session table and limited by the Oracle Database Resource Manager (DBRM) settings.
Reference: AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2
Q29. The following parameter values are set for the instance:
OPTIMIZER_CAPTURE_SQL_BASELINE = FALSE OPTIMIZER_USESQL_PLAN_BASELINE = TRUE The SQL plan baseline for a SQL statement contains an accepted plan.
You want to add a new plan automatically as an accepted plan to the existing SQL plan baseline.
Examine the following tasks.
1.
Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE parameter to TRUE.
2.
Evolve the new plan.
3.
Fix the existing accepted plan.
4.
Manually load the new plan.
Identify the task(s) that must be performed to accomplish this.
A. 1, 2, and 3
B. 4 and 3
C. 1, 4, and 3
D. Only 4
E. 1, 2, 4, and 3
F. 1 and 2
Answer: D
Explanation: Manual Plan Loading 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.
Note:
*
The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.
*
Managing SQL plan baselines involves three phases:
Capturing SQL Plan Baselines Selecting SQL Plan Baselines Evolving SQL Plan Baselines
*
Evolving SQL Plan Baselines
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.
Reference: SQL Plan Management in Oracle Database 11g Release 1
Q30. Examine the exhibit to view the query and its execution plan.
Identify the two correct interpretations that can be made from the execution plan.
A. The DEPT table is driving table and the EMP table join is the driven table.
B. Rows from the DEPT table are first hashed by the join key into memory and then joined to the EMP table on the join key.
C. The EMP table is the driving table and the DEPT table us the driven table.
D. The rows from the DEPT table are sorted first by the join key and then hashed into memory.
E. Rows from both the tables are sorted by the join key, but only rows from the DEPT table are hashed into memory.
Answer: B,C
Explanation: If two lines are indented equally, then the top line is normally executed first. Here the line 3 and line 4 are indented equally, so line 3 ( TABLE ACCESS FULL EMP) with EMP being the driving table as the ordered hint requests that the tables listed in the FROM clause of a SQL statement be joined in the order specified, with the first table in the FROM clause specifying the driving table.