The Oracle specialists potentially have being a specialist in IT industry want to attain the top associated with capability as well as achievement, so that they need to participate in different Oracle accreditation exams. Oracle 1Z0-117 called Oracle Database 11g Release 2: SQL Tuning Exam will be stepping-stone to check the candidates information as well as ability inside a appropriate industry at work. Actualtests Oracle 1Z0-117 on the web exercise checks can guarantee that you can to control individuals highly trained as well as competent operates. The 1Z0-117 exercise exam provides the real answers and questions, guaranteeing an individual pass the actual 1Z0-117 exam together with leading levels.
2021 Oct 1Z0-117 free exam
Q41. 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
Q42. You instance has these parameter settings:
Which three statements are true about these settings if no hints are used in a SQL statement?
A. A statement estimated for more than 10 seconds always has its degree of parallelism computed automatically.
B. A statement with a computed degree of parallelism greater than 8 will be queued for a maximum of 10 seconds.
C. A statement that executes for more than 10 seconds always has its degree of parallelism computed automatically.
D. A statement with a computed degree of parallelism greater than 8 will raise an error.
E. A statement with any computed degree of parallelism will be queued if the number of busy parallel execution processes exceeds 64.
F. A statement with a computed degree of parallelism of 20 will be queued if the number of available parallel execution processes is less 5.
Answer: C,E,F
Explanation: C (not A): 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.
PARALLEL_DEGREE_LIMIT integer
A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
E: 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.
F: PARALELL_MIN_MINPERCENT PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution processes (of the value of PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set. Consider the following settings: PARALLEL_MIN_PERCENT = 50 PARALLEL_MIN_SERVERS = 5 PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.
Note: With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement. However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. This limit is enforced by PARALLEL_DEGREE_LIMIT.
Values:
CPU
IO
integer
A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
Reference: PARALLEL_MIN_TIME_THRESHOLD PARALLEL_DEGREE_LIMIT PARALELL_MIN_MINPERCENT PARALELL_SERVERS_TARGET
Q43. Which three are benefits of In-Memory Parallel Execution?
A. Reduction in the duplication of block images across multiple buffer caches
B. Reduction in CPU utilization
C. Reduction in the number of blocks accessed
D. Reduction in physical I/O for parallel queries
E. Ability to exploit parallel execution servers on remote instance
Answer: A,C,D
Explanation:
Note: In-Memory Parallel Execution
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads.
Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works
Q44. 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.
Answer: B,C,E
Explanation: C: As per ALTER TABLE employees PARALLEL 2;
Incorrect:
not D: As per ALTER TABLE departments NOPARALLEL;
Q45. Which three are tasks performed in the hard parse stage of a SQL statement executions?
A. Semantics of the SQL statement are checked.
B. The library cache is checked to find whether an existing statement has the same hash value.
C. The syntax of the SQL statement is checked.
D. Information about location, size, and data type is defined, which is required to store fetched values in variables.
E. Locks are acquired on the required objects.
Answer: B,D,E
Explanation: Parse operations fall into the following categories, depending on the type of
statement submitted and the result of the hash check:
A) Hard parse
If Oracle Database cannot reuse existing code, then it must build a new executable version
of the application code. This operation is known as a hard parse, or a library cache miss.
The database always perform a hard parse of DDL.
During the hard parse, the database accesses the library cache and data dictionary cache
numerous times to check the data dictionary. When the database accesses these areas, it
uses a serialization device called a latch on required objects so that their definition does
not change (see "Latches"). Latch contention increases statement execution time and
decreases concurrency.
B) Soft parse
A soft parse is any parse that is not a hard parse. If the submitted statement is the same as
a reusable SQL statement in the shared pool, then Oracle Database reuses the existing
code. This reuse of code is also called a library cache hit.
Soft parses can vary in the amount of work they perform. For example, configuring the
session cursor cache can sometimes reduce the amount of latching in the soft parses,
making them "softer."
In general, a soft parse is preferable to a hard parse because the database skips the
optimization and row source generation steps, proceeding straight to execution.
Incorrect:
A, C: During the parse call, the database performs the following checks:
Syntax Check
Semantic Check
Shared Pool Check
The hard parse is within Shared Pool check.
Reference: Oracle Database Concepts 11g, SQL Parsing
Rebirth 1Z0-117 practice test:
Q46. Examine the following command:
Which query transformation technique is used by the optimizer in this case?
A. View merging
B. Filter push-down
C. Predicate pushing
D. Predicate move-around
Answer: C
Explanation: In predicate pushing, the optimizer "pushes" the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters.
For example, suppose you create a view that references two employee tables. The view is defined with a compound query that uses the UNION set operator, as follows:
CREATE VIEW all_employees_vw AS ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees ) UNION ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers ); You then query the view as follows:
SELECT last_name FROM all_employees_vw WHERE department_id = 50; Because the view is a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform the accessing statement by pushing its predicate, the WHERE clause condition department_id=50, into the view's compound query. The equivalent transformed query is as follows:
SELECT last_name FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees WHERE department_id=50 UNION SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers WHERE department_id=50 );
Reference: Oracle Database Performance Tuning Guide, Predicate Pushing
Q47. Examine the Exhibit and view the structure of an indexes for the EMPLOYEES table.
Which two actions might improve the performance of the query?
A. Use the ALL_ROWS hint in the query.
B. Collect the histogram statistics for the EMPLOYEE_ID column.
C. Decrease the value for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
D. Decrease the index on the EMPLOYEE_ID if not being used.
E. Set the OPTIMIZER_MODE parameter to ALL_ROWS.
Answer: A,E
Explanation: A: The ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput, which is minimum total resource consumption.
E: optimizer_mode=all_rows - This optimizer mode favors full-table scans (especially parallel full-table-scans) in cases where the server resources will be minimized. The all_rows mode is generally used during batch-oriented processing and for data warehouses where the goal is to minimize server resource consumption.
Q48. You recently gathered statistics for a table by using the following commands:
You noticed that the performance of queries has degraded after gathering statistics. You want to use the old statistics. The optimizer statistics retention period is default.
What must you do to use the old statistics?
A. Use the flashback to bring back the statistics to the desired time.
B. Restore statistics from statistics history up to the desired time.
C. Delete all the statistics collected after the desired time.
D. Set OPTIMIZER_USE_PENDING_STATISTICS to TRUE.
Answer: B
Explanation: Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoration. Statistics can be restored using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.
Reference: Oracle Database Performance Tuning Guide, Restoring Previous Versions of Statistics
Q49. You are administering a database supporting a DDS workload in which some tables are updated frequently but not queried often. You have SQL plan baseline for these tables and you do not want the automatic maintenance task to gather statistics for these tables regularly.
Which task would you perform to achieve this?
A. Set the INCREMENTAL statistic preference FALSE for these tables.
B. Set the STALE_PERCENT static preference to a higher value for these tables.
C. Set the GRANULARITY statistic preference to AUTO for these tables.
D. Set the PUBLISH statistic preference to TRUE for these tables.
Answer: B
Explanation: With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.
Reference: Oracle Database PL/SQL Packages and Types Reference
Q50. Partial details of an execution plan.
Which statement correctly describes the BITMAP AND operation?
A. It produces a bitmap, representing dimension table rows from all dimension tables that join with qualified fact table rows.
B. It produces a concentration of the bitmaps for all dimension tables.
C. It produces a bitmap, representing fact table rows that do not join with qualified dimension table rows from all dimension tables.
D. It produces a bitmap, representing fact table rows that join with qualified dimension table rows from all dimension tables.
Answer: D
Explanation:
Example: Additional set operations will be done for the customer dimension and the product dimension. At this point in the star query processing, there are three bitmaps. Each bitmap corresponds to a separate dimension table, and each bitmap represents the set of rows of the fact table that satisfy that individual dimension's constraints.
These three bitmaps are combined into a single bitmap using the bitmap AND operation. This final bitmap represents the set of rows in the fact table that satisfy all of the constraints on the dimension table.
Reference: Oracle Database Data Warehousing Guide, Star Transformation with a Bitmap Index