Oracle 1Z0-117 the right answers, 1Z0-117 qualification thoughts, Modern times, Oracle gets to be more and a lot more well-liked. And Oracle 1Z0-117 audit is very adapt to those who are helpful in the information technology domain. If someone wishes to go away this Oracle 1Z0-117 audit, to get this Oracle qualification properly, just need to decide Oracle 1Z0-117 test Q&As for preparation elements carefully.
2021 Jan 1Z0-117 free exam questions
Q1. You created a SQL Tuning Set (STS) containing resource-intensive SQL statements. You plan to run the SQL Tuning Advisor.
Which two types of recommendations can be provided by the SQL Tuning Advisor?
A. Semantic restructuring for each SQL statement
B. Gathering missing or stale statistics at the schema level for the entire workload
C. Creating a materialized view to benefit from query rewrite for the entire workload
D. Gathering missing or stale statistics for objects used by the statements.
E. Creating a partition table to benefit from partition pruning for each statement
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 ( D), creation of new indexes, restructuring of the SQL statement (A), or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user.
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. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.
Reference: Oracle Database Performance Tuning Guide 11g , SQL Tuning Advisor
Q2. Examine the Exhibit. Given two sets of parallel execution processes, SS1 and SS2, which is true?
A. Each process SS1 reads some of the rows from the CUSTOMERS table and sends all the rows it reads to each process in SS2.
B. Each process in SS1 reads all the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.
C. Each process in SS1 reads some of the rows from the SALES table and sends all the rows it reads to each process in SS2.
D. Each process in SS1 reads all the rows from the SALES table and distributes the rows evenly among the processes in SS2.
E. Each process in SS1 reads some of the rows from the SALES table and distributes the rows evenly among the processes in SS2.
F. Each process in the SS1 reads some of the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.
The execution starts with line 16 (accessing the SALES table), followed by line 15.
PX BLOCKITERATOR The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASHtable queue, and then read by and aggregated on the top slave set.
Q3. 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.
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 *
If there were insufficient parallel servers to satisfy the requested DOP, one of three things
/ 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".
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
Q4. Examine the query and its execution plan:
Which two statements are true regarding the execution plan?
A. For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.
B. An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.
C. The data is aggregated from the ORDERS table before joining to CUSTOMERS.
D. The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.
Explanation: B: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
All_rows attempts to optimize the query to get the very last row as fast as possible.
This makes sense in a stored procedure for example where the client does not regain
control until the stored procedure completes. You don't care if you have to wait to get
the first row if the last row gets back to you twice as fast. In a client
server/interactive application you may well care about that.
The optimizer uses nested loop joins to process an outer join in the following
/ It is possible to drive from the outer table to inner table.
/ Data volume is low enough to make the nested loop method efficient.
First_rows attempts to optimize the query to get the very first row back to the client as
fast as possible. This is good for an interactive client server environment where the
client runs a query and shows the user the first 10 rows or so and waits for them to page
down to get more.
Q5. You notice some performance degradation for a high-load SQL statement in your database. After investigations, you run the SQL Tuning Advisor, which recommends a SQL Profile. You accept the profile recommendation resulting in a new, tuned execution plan for the statement.
Your database uses SQL plan management and a SQL plan baseline exists for this SQL statement.
Which statement is true?
A. The database adds the tuned plan to the SQL plan baseline as a nonfixed plan.
B. The database adds the tuned plan to the SQL plan baseline as a fixed plan.
C. The optimizer uses the new tuned plan only when a reproducible fixed plan is present.
D. The created SQL profile will continuously adapt to all changes made to the database, the object, and to the system statistics over an extended length of time.
When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.
When tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline.
If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created.
SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.
SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to YES.
ACCEPT_SQL_PROFILE Procedure and Function
This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability.
Update 1Z0-117 test preparation:
Q6. Which three statements are true the Automatic Tuning Optimizer (ATO)?
A. It identifies the objects with stale or missing statistics and gathers statistics automatically.
B. It investigates the effect of new or modified indexes on the access paths for a workload and recommends running that statistics through the SQL Access Advisor.
C. It recommends a SQL profile to help create a better execution plan.
D. It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.
E. It identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor performance and suggests restricting the statements.
F. It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.
Explanation: Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke Automatic Tuning Optimizer every time a query must be hard-parsed. Automatic Tuning Optimizer is meant for complex and high-load SQL statements that have nontrivial impact on the database.
Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements that are good candidates for SQL tuning. The automatic SQL tuning feature also automatically identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task.
The Automatic Tuning Optimizer performs the following types of tuning analysis:
Statistics Analysis SQL Profiling Access Path Analysis SQL Structure Analysis Alternative Plan Analysis
* Oracle Database uses the optimizer to generate the execution plans for submitted SQL statements. The optimizer operates in the following modes:
The optimizer compiles the SQL and generates an execution plan. The normal mode generates a reasonable plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second.
The optimizer performs additional analysis to check whether it can further improve the plan produced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.
Q7. Auto DOP is enabled for your instance.
You execute the following statements:
Which three are true about the execution of the join?
A. Dictionary DOP is used to calculate statements DOP.
B. Hinted DOP is used to calculate statement DOP.
C. The EMPLOYEES table is accessed in parallel.
D. The DEPARTMENTS table is accessed in parallel.
E. The hint operates at the level of each table accessed by the statement.
Explanation: C: As per ALTER TABLE employees PARALLEL 2;
not D: As per ALTER TABLE departments NOPARALLEL;
Q8. Examine the exhibit to view the query and its execution plan?
What two statements are true?
A. The HASH GROUP BY operation is the consumer of the HASH operation.
B. The HASH operation is the consumer of the HASH GROUP BY operation.
C. The HASH GROUP BY operation is the consumer of the TABLE ACCESS FULL operation for the CUSTOMER table.
D. The HASH GROUP BY operation is consumer of the TABLE ACCESS FULL operation for the SALES table.
E. The SALES table scan is a producer for the HASH JOIN operation.
Explanation: A, not C, not D: Line 3, HASH GROUP BY, consumes line 6 (HASH JOIN BUFFERED).
E: Line 14, TABLE ACCESS FULL (Sales), is one of the two producers for line 6 (HASH JOIN).
Q9. See the table below:
All execution servers are currently available and the sessions use defaults for all parallel settings.
In which two cases will statements execute in parallel?
A. When parallel hints are used but only if estimated serial execution takes more than 10 seconds.
B. When parallelism is defined at the statement level.
C. When the degree of parallelism is explicitly defined in the data dictionary for tables and indexes accessed by a query.
D. Parallel DDL statements but only if estimated serial DDL execution time is greater than 10 seconds.
E. When the degree of parallelism is explicitly defined for tables and indexes but only if estimated serial execution takes more than 10 seconds.
A, D, E: When PARALLEL_MIN_TIME_THRESHOLD is set to AUTO the
PARALLEL_MIN_TIME_THRESHOLD is set to 30, not to 10. See note below.
* parallel_min_time_threshold 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.
Q10. You are administering database that supports an OLTP workloads. Most of the queries use an index range scan or index unique scan as access methods.
Which three scenarios can prevent the index access being used by the queries?
A. When highly selective filters is applied on an indexed column of a table with sparsely populated blocks.
B. When the rows are filtered with an IS NULL operator on the column with a unique key defined
C. When the histogram statistics are not collected for the columns used in where clause.
D. When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.
E. When the statistics for the table are not current.
A: Low clustering factor promotes good performance.
The clustering_factor measures how synchronized an index is with the data in a table. A
table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for optimal access.
Oracle SQL not using an index is a common complaint, and it’s often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to:
(E) Bad/incomplete statistics – Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.
Wrong optimizer_mode – The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.
Bugs – See these important notes on optimizer changes in 10g that cause Oracle not to use an index.
Cost adjustment – In some cases, the optimizer will still not use an index, and you must decrease optimizer_index_cost_adj.