1Z0-117 | how many questions of 1Z0-117 exam question?
Q61. You ran a high load SQL statement that used an index through the SQL Tuning Advisor and accepted its recommendation for SQL profile creation. Subsequently you noticed that there has been a 2% growth in number of rows in the tables used by the SQL statement and database statistics have also been refreshed.
How does this impact the created SQL profile?
A. It becomes invalid and no longer used the optimizer.
B. It remains valid and ensures that the optimizer always use the execution plan that was created before the changes happened.
C. It remains and allows the optimizer to pick a different plan required.
D. It becomes invalid and a new SQL profile is created for the statement by the auto tuning task.
Answer: C
Q62. Examine the Exhibit and view the query and its execution plan.
Which statement is correct about the parallel executions plan?
A. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined in parallel.
B. First, the CUSTOMERS table is scanned in parallel, then the SALES table is scanned in parallel, and then they are joined serially.
C. First, the SALES table is scanned in parallel, then the CUSTOMERS table us scanned in parallel, and then they are joined in parallel.
D. The CUSTOMERS and SALES tables are scanned simultaneously in parallel and then joined serially.
E. First, the CUSTOMERS table is scanned in parallel, then the SALES table us scanned in parallel, and then they are joined in parallel.
Answer: A
Explanation: As per exhibit:
Line 7 and line 11 are run in parallel.
Line 8 and line 12 are run in parallel.
Line 9 and line 13 are run in parallel.
Line 10 and line 14 are run in parallel.
Line 6 is a PCWP (parallel combined with parent) and the parent is a P-> P (Parallel to
parallel) operation.
Q63. 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.
Q64. You are administering a database, where an application frequently executes identical SQL
statements with the same syntax.
How will you optimize the query results without retrieving data blocks from the storage?
A. By setting the CURSOR_SHARING parameter to FORCE.
B. By using the bind variables and setting the CURSOR_SHARING parameter to EXACT.
C. By using the CACHE hint to pin the queries in the library cache
D. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries.
E. By creating a SQL plan baseline for the identical statements.
Answer: D
Explanation: As its name suggests, the query result cache is used to store the results of
SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle
can avoid having to repeat the potentially time-consuming and intensive operations that
generated the resultset in the first place (for example, sorting/aggregation, physical I/O,
joins etc). The cache results themselves are available across the instance (i.e. for use by
sessions other than the one that first executed the query) and are maintained by Oracle in
a dedicated area of memory. Unlike our homegrown solutions using associative arrays or
global temporary tables, the query result cache is completely transparent to our
applications. It is also maintained for consistency automatically, unlike our own caching
programs.
Note:
RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's
execution plan.
Values:
MANUAL
The ResultCache operator is added only when the query is annotated (that is, hints).
FORCE
The ResultCache operator is added to the root of all SELECT statements (provided that it
is valid to do so).
For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the
hint takes precedence over the parameter setting.
Incorrect:
A, B: CURSOR_SHARING determines what kind of SQL statements can share the same
cursors.
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.
C: The Oracle library cache is a component of the System Global Area (SGA) shared pool. Similarly to other Oracle cache structures, the point of the library cache is to reduce work – and therefore to improve performance – by caching the result of parsing and optimizing SQL or PL/SQL so that subsequent executions of the same SQL or PL/SQL require fewer preparatory steps to deliver a query result.
Q65. 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
Q66. Examine the initialization parameters for a database an OLTP overload.
What is the effect of changing the value of the parameter?
A. It influences the optimizer to always use the value of the parameter?
B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.
C. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
D. It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced.
Answer: B
Explanation: 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.
Q67. Examine the Following Query and execution plan:
Which query transformation technique is used by the optimizer?
A. Filter push down
B. Subquery factoring
C. Subquery unnesting
D. Predicate pushing
Answer: D
Explanation:
Note:
* In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation.
Q68. Which three statements are true about the usage of optimizer hints?
A. Whenever a query uses table aliases, the hints in the query must use the aliases.
B. The OPTIMIZER_FEATURES_ENABLE parameter must be set to a version supports the hints used.
C. The optimizer uses the execution plan with lower cost even if a hint is specified.
D. A schema name for the table must be used in the hint if the table us qualified in the FROM clause.
E. Hints can be used to override the optimization approach specified with the OPTIMIZER_MODE parameter.
F. A statement block can have only one hint, and that hint must be immediately after SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.
Answer: A,B,E
Explanation: * You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint.
* OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to
10.1.0. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to 11.1.0.6.
* If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.
Q69. Which are the two prerequisites for enabling star transformation on queries?
A. The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or TEMP_DISABLE.
B. A B-tree index should be built on each of the foreign key columns of the fact table(s),
C. A bitmap index should be built on each of the primary key columns of the fact table(s).
D. A bitmap index should be built on each of the foreign key columns of the fact table(s).
E. A bitmap index must exist on all the columns that are used in the filter predicates of the query.
Answer: A,E
Explanation: A: Enabling the transformation
E: Star transformation is essentially about adding subquery predicates corresponding to the constraint dimensions. These subquery predicates are referred to as bitmap semi-join predicates. The transformation is performed when there are indexes on the fact join columns (s.timeid, s.custid...). By driving bitmap AND and OR operations (bitmaps can be from bitmap indexes or generated from regular B-Tree indexes) of the key values supplied by the subqueries, only the relevant rows from the fact table need to be retrieved. If the filters on the dimension tables filter out a lot of data, this can be much more efficient than a full table scan on the fact table. After the relevant rows have been retrieved from the fact table, they may need to be joined back to the dimension tables, using the original predicates. In some cases, the join back can be eliminated.
Star transformation is controlled by the star_transformation_enabled parameter. The parameter takes 3 values.
TRUE - The Oracle optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e. the transformation is performed only if the cost of the transformed plan is lower than the non-transformed plan. Also the optimizer will attempt temporary table transformation automatically whenever materialization improves performance.
FALSE - The transformation is not tried.
TEMP_DISABLE - This value has similar behavior as TRUE except that temporary table transformation is not tried.
The default value of the parameter is FALSE. You have to change the parameter value and create indexes on the joining columns of the fact table to take advantage of this transformation.
Reference: Optimizer Transformations: Star Transformation
Q70. Which two statements are true about the use of the DYNAMIC_SAMPLING hint in a query?
A. It estimates selectivity better for the filters.
B. It is always used for flashback queries that contain the AS OF clause.
C. It cannot be used if there is a single-table predicate in the WHERE clause.
D. It cannot be used for processing SQL statements in parallel.
E. It can compensate for the lack of extended statistics to get accurate cardinality estimates for complex predicate expressions.
Answer: D,E
Explanation:
D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance. the value is honored.
Reference: Oracle Database Administrator's Guide, About Oracle Database Resource Manager