All the crucial guides are from here that may help you prepare for your Oracle 1Z0-117 exam. We have created our Study guides, Q&As using Detailed Explanations, Comprehensive Preparation labs to ensure a person pass your test on the very first try. The actual Pdf files are printable and also portable. You can carry these people with a person and evaluation the Oracle exam questions and also answers anytime and anyplace. If you dont pass the Oracle Oracle exam, Actualtests.com will offer a person full refund as well as another totally free product in accordance with your need. You should get in touch with our consumer support and also claim the exam dump youd just like. If you claim the full money , you should email us your own failed transcript. Our workers will remit you right after making positive your details.
2021 Sep 1Z0-117 practice test
Q71. You need to migrate database from oracle Database 10g to 11g. You want the SQL workload to start the 10g plans in the 11g database instance and evolve better plans.
Examine the following steps:
1.
Capture the pre-Oracle Database 11g plans in a SQL Tuning Set (STS)
2.
Export the STS from the 10g system.
3.
Import the STS into Oracle Database 11g.
4.
Set the OPTIMIZER_FEATURES_ENABLE parameter to 10.2.0.
5.
Run SQL Performance Analyzer for the STS.
6.
Set the OPTIMIZER_FEATURES_ENABLE parameter to 11.2.0.
7.
Rerun the SQL Performance Analyzer for the STS.
8.
Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.
9.
Use DBMS_SPM.EVOLVE_SQL_BASELINE function to evolve the plans.
10.
Set the OPTIMIZER_USE_SQL_PLAN_BASELINE to TRUE.
Identify the required steps in the correct order.
A. 1, 2, 3, 4, 5, 6, 7,
B. 4, 8, 10
C. 1, 2, 3, 4, 8, 10
D. 1, 2, 3, 6, 9, 5
E. 1, 2, 3, 5, 9, 10
Answer: A
Explanation: Step 1: (1)
Step 2: (2)
Step 3: (3)
Step 4: (4)
By setting the parameter OPTIMIZER_FEATURES_ENABLE to the 10g version used
before the upgrade, you should be able to revert back to the same execution plans you had
prior to the
upgrade.
Step 5: (8)
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been
introduced to guarantees any plan changes that do occur lead to better performance. When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will automatically capture a SQL plan baseline for every repeatable SQL statement on the system. The execution plan found at parse time will be added to the SQL plan baseline as an accepted plan.
Step 6: (10) 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 Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Q72. You need to upgrade you Oracle Database 10g to 11g. You want to ensure that the same SQL plans that are currently in use in the 10g database are used in the upgraded database initially, but new, better plans are allowed subsequently.
Steps to accomplish the task:
1.
Set the OPTIMIZER_USE_SQL_BASELINE and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.
2.
Bulk load the SQL Management Base as part of an upgrade using an STS containing the plans captured in Oracle Database 10g.
3.
Evolve the plan baseline using the DBMS_SPM.EVOLVE_PLAN_BASELINE procedure.
4.
Fix the plan baseline – using the DBMS_SPM.ALTER_SQL_PLANBASELINE procedure.
5.
Accept new, better plans using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure and manually load them to the existing baseline.
6.
Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.
Identify the required steps.
A. 1, 3, 4, 5
B. 1, 6, 3, 4, 5
C. 1, 2, 3, 5
D. 1, 2, 3, 4
E. 1, 6, 3
F. 1 and 2
Answer: F
Explanation: * (1) OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been introduced to guarantees any plan changes that do occur lead to better performance. When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will automatically capture a SQL plan baseline for every repeatable SQL statement on the system. The execution plan found at parse time will be added to the SQL plan baseline as an accepted plan.
* (2) Once you have completed the software upgrade, but before you restart the applications and allow users back on the system, you should populate SQL Plan Management (SPM) with the 10g execution plans you captured before the upgrade. Seeding SPM with the 10g execution plans ensures that the application will continue to use the same execution plans you had before the upgrade. Any new execution plans found in Oracle Database 11g will be recorded in the plan history for that statement but they will not be used. When you are ready you can evolve or verify the new plans and only implement those that perform better than the 10g plan.
Incorrect:
Not (3): DBMS_SPM.EVOLVE_PLAN_BASELINE is not used to evolve new plans.
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE should be used:
It is possible to evolve a SQL statement’s execution plan using Oracle Enterprise Manager
or by
running the command-line function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. U
Note:
* SQL plan management (SPM) ensures that runtime performance will never degrade due
to the
change of an execution plan. To guarantee this, only accepted (trusted) execution plans
will be
used; any plan will be tracked and evaluated at a later point in time and only accepted as
verified
if the new plan performs better than an accepted plan. SQL Plan Management has three
main
components:
1.
SQL plan baseline capture:
Create SQL plan baselines that represents accepted execution plans for all relevant SQL
statements. The SQL plan baselines are stored in a plan history inside the SQL
Management Base in the SYSAUX tablespace.
2.
SQL plan baseline selection
Ensure that only accepted execution plans are used for statements with a SQL plan
baseline and track all new execution plans in the history for a statement as unaccepted
plan. The plan history consists of accepted and unaccepted plans. An unaccepted plan can be unverified (newly found but not verified) or rejected (verified but not found to performant).
3.
SQL plan baseline evolution Evaluate all unverified execution plans for a given statement in the plan history to become either accepted or rejected
Q73. You plan to bulk load data INSERT INTO . . . SELECT FROM statements.
Which two situations benefit from parallel INSERT operations on tables that have no materialized views defined on them?
A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows and a conventional B*tree secondary index.
B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index.
C. Direct path insert of 10 rows into a partitioned, index-organized table containing one million rows and conventional B* tree secondary index.
D. Direct path insert of 10 rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index
E. Conventional path insert of a million rows into a nonpartitioned, heap-organized containing 10 rows and having a conventional B* tree index.
F. Conventional path insert of 10 rows into a nonpartitioned, heap-organized table one million rows and a bitmapped index.
Answer: A,B
Explanation:
Note:
*
A materialized view is a database object that contains the results of a query.
*
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERTand direct-path INSERT.
*
With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default.
*
Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
*
When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints
*
Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force
logging settings of the database
Q74. You want to run SQL Tuning Advisor statements that are not captured by ADDM, AWR, and are not in the library cache.
What is the prerequisite?
A. Enable SQL plan management
B. Create a SQL plan baseline for each query
C. Create a SQL Tuning Set (STS) containing the SQL statements
D. Gather statistics for objects used in the application
Answer: C
Explanation: You can use an STS as input to SQL Tuning Advisor, which performs
automatic tuning of the SQL statements based on other user-specified input parameters.
Note:
A SQL tuning set (STS) is a database object that includes one or more SQL statements
along with their execution statistics and execution context, and could include a user priority
ranking. You can load SQL statements into a SQL tuning set from different SQL sources,
such as AWR, the shared SQL area, or customized SQL provided by the user. An STS
includes:
A set of SQL statements
Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk
reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type Associated execution plans and row source statistics for each SQL statement (optional). Reference: Oracle Database Performance Tuning Guide, Managing SQL Tuning Sets
Q75. Which three options are true about MVIEWs?
A. The defining query of an MVIEWs may be based on a populated table.
B. Queries that are rewritten to an MVIEW will never obtain results from the result cache.
C. All MVIEWS may be configured to support “refresh on demand”.
D. The defining query of an MVIEW may be based on non_partitioned table.
E. All MVIEWs may be configured to support “refresh on commit”
Answer: A,B,C
Explanation: A: The defining query of a materialized view can select from tables, views, or materialized views owned by the user SYS, but you cannot enable QUERY REWRITE on such a materialized view.
B: You cannot specify the following CREATE MATERIALIZED VIEW clauses: CACHE or NOCACHE, CLUSTER, or ON PREBUILT TABLE.
C: Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.
Incorrect:
E: Materialized views can only refresh ON COMMIT in certain situations. The materialized view cannot contain object types or Oracle-supplied types. The base tables will never have any distributed transactions applied to them.
Note:
*
Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.
*
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.
Renewal 1Z0-117 practice test:
Q76. You are administering a database supporting an OLTP workload. A new module was added to one of the applications recently in which you notice that the SQL statements are highly resource intensive in terms of CPU, I/O and temporary space. You created a SQL Tuning Set (STS) containing all resource-intensive SQL statements. You want to analyze the entire workload captured in the STS. You plan to run the STS through the SQL Advisor.
Which two recommendations can you get?
A. Combing similar indexes into a single index
B. Implementing SQL profiles for the statements
C. Syntactic and semantic restructuring of SQL statements
D. Dropping unused or invalid index.
E. Creating invisible indexes for the workload
F. Creating composite indexes for the workload
Answer: C,F
Explanation: The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects , creation of new indexes (F), restructuring of the SQL statement (C), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
Reference: Oracle Database Performance Tuning Guide 11g , SQL Tuning Advisor
Q77. Which two statements are true about the trcsess utility?
A. It merges multiple trace files and produces a formatted output file.
B. It merges multiple trace files from a particular session into one single trace file.
C. It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility.
D. It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility.
E. It merges files pertaining to a user session scattered across different processes in a shared server configuration.
Answer: A,B
Explanation: The trcsess utility consolidates trace output from selected trace files based on several criteria:
Session ID Client ID Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be processed by TKPROF.
Note:
*
trcsess is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.
*
Now there is a new tool, a command line utility called trcsess to help read the trace files.
Reference: Oracle Database Performance Tuning Guide 11g , Using the trcsess Utility The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.
Q78. A new application module is deployed on middle tier and is connecting to your database. You want to monitor the performance of the SQL statements generated from the application.
To accomplish this, identify the required steps in the correct order from the steps given below:
1.
Use DBNMS_APPLICATION_INFO to set the name of the module
2.
Use DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE to enable statistics gathering for the module.
3.
Use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE to enable tracing for the service
4.
Use the trcsess utility to consolidate the trace files generated.
5.
Use the tkprof utility to convert the trace files into formatted output.
A. 1, 2, 3, 4, 5
B. 2, 3, 1, 4, 5
C. 3, 1, 2, 4, 5
D. 1, 2, 4, 5
E. 1, 3, 4, 5
F. 2, 1, 4, 5
Answer: A
Explanation:
Note:
*
Before tracing can be enabled, the environment must first be configured to enable gathering of statistics.
*
(gather statistics): DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE Enables statistic gathering for a given combination of Service Name, MODULE and ACTION
*
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified.
dbms_monitor.serv_mod_act_trace_enable(
service_name IN VARCHAR2,
module_name IN VARCHAR2 DEFAULT ANY_MODULE,
action_name IN VARCHAR2 DEFAULT ANY_ACTION,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL,
plan_stat IN VARCHAR2 DEFAULT NULL);
SELECT instance_name
FROM gv$instance;
exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');
exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');
*
When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions, but for shared server sessions, many processes are involved. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session.
Now there is a new tool, a command line utility called trcsess to help read the trace files. The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.
*
Once the trace files have been consolidated (with trcsess), tkprof can be run against the consolidated trace file for reporting purposes.
Q79. Exhibit A table has three distinct values in its ID column. In the ID column, values 10 and 20 have more than 20000 rows each and value 30 has only five rows. The statistics for the schema have been updated recently.
The CURSOR_SHARING parameter is set to EXACT.
The query was executed recently and the cursor for the query is bind aware. Examine the exhibits to view the commands executed.
You plan to execute the same query with a value of 30 for the bind variable V_ID.
Which statement is true in this scenario?
A. The same execution plan will always be used irrespective of the value in the bind variable.
B. A new execution plan will be generated depending on the access pattern and the bind value.
C. Adaptive cursor sharing will ensure that a new cursor is generated for each distinct value in the bind variable.
D. Adaptive cursor sharing will happen only if you use the literal values instead of bind variables in the query.
Answer: C
Explanation:
Note:
*
CURSOR_SHARING determines what kind of SQL statements can share the same
cursors.
*
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area
only when their texts match exactly. This is the default behavior. Using this setting, similar
statements cannot shared; only textually exact statements can be shared.
*
Values:
*
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a
cursor, unless the literals affect the meaning of the statement.
*
SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a
cursor, unless the literals affect either the meaning of the statement or the degree to which
the plan is optimized.
*
EXACT
Only allows statements with identical text to share the same cursor.
Q80. You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;
The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.
Which two options true about DML statements for which parallel execution is requested?
A. Statements for which PDML is requested will execute serially estimated time is less than the time specified by the PARALLEL_MIN_THRESHOLD parameter.
B. Statements for which PDML is requested will be queued if the number of busy parallel execution servers greater than PARALLEL_MIN_SERVERS parameter.
C. Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.
D. Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.
E. Statement for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_DEGREE_LIMIT parameter.
Answer: C,D
Explanation: C: 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.
D: 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.
Note:
*
PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
AUTO Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
*
PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.
Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET