What can you get after purchase: One particular. A Oracle 1Z0-117 review guide Only two. Printable Pdf files and Examination Engine software Several. Truly on the web Oracle 1Z0-117 test Some. Interactive space to candidates 5. Produce and take notes about the 1Z0-117 practice questions
2021 Aug 1Z0-117 test preparation
Q31. Which two types of SQL statements will benefit from dynamic sampling?
A. SQL statements that are executed parallel
B. SQL statement that use a complex predicate expression when extended statistics are not available.
C. SQL statements that are resource-intensive and have the current statistics
D. SQL statements with highly selective filters on column that has missing index statistics
E. Short-running SQL statements
Explanation: A: he optimizer decides whether to use dynamic statistics based on several factors. For example, the database uses automatic dynamic statistics when the SQL statement uses parallel execution.
B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions.
D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.
Reference: When the Optimizer Uses Dynamic Statistics
Q32. Examine the Exhibit1 to view the structure of an indexes for the EMPLOYEES table.
Examine the query:
SQL> SELECT * FROM employees WHERE employees_id IN (7876, 7900, 7902);
EMPLOYEE_ID is a primary key in the EMPLOYEES table that has 50000 rows.
Which statement is true regarding the execution of the query?
A. The query uses an index skip scan on the EMP_EMP_ID_PK index to fetch the rows.
B. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index range scan on the EMP_EMP_ID_PK index.
C. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using a fast full index scan on the EMP_EMP_ID_PK index.
D. The query uses the INLIST ITERATOR operator to iterate over the enumerated value list, and values are evaluated using an index unique scan on the EMP_EMP_ID_PK index.
E. The query uses a fast full index scan on the EMP_EMP_ID_PK index fetch the rows.
Reference: Database Performance Tuning Guide and Reference
Q33. You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently during day time and you notice a performance degradation for queries using those tables due to stale statistics.
Which two options might help to avoid the performance degradation of the queries?
A. Set the global statistics preference STALE_PERCENT to 0.
B. Use dynamically sampling hint for the queries on frequently updated tables.
C. Create histogram for the columns used frequently in the WHERE clause.
D. Gather statistics with global statistics preference NO_VALIDATE to TRUE.
E. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRUE.
Explanation: B: Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.
There are two ways to use dynamic sampling:
The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command. The DYNAMIC_SAMPLING query hint can be added to specific queries.
C: A histogram is a collection of information about the distribution of values within a column.In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access. Histograms are also important for determinine the optimal table join order.
A: Too much statistics would be gathered.
Note: 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%.
D: In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column
in a table.
E: OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses
pending statistics when compiling SQL statements.
Q34. Which two types of column filtering may benefit from partition pruning?
A. Equally operates on range-partitioned tables.
B. In-list operators on system-partitioned tables
C. Equality operators on system-partitioned tables
D. Operators on range-partitioned tables E. Greater than operators on hash-partitioned tables
Explanation: The query optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases:
partition_column = constant
partition_column IN (constant1, constant2, ..., constantN)
In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning.
The core concept behind partition pruning is relatively simple, and can be described as “Do not scan partitions where there can be no matching values”.
When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data.
Suppose that you have a partitioned table t1 defined by this statement:
CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
PARTITION BY RANGE( region_code ) (
PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN MAXVALUE
Consider the case where you wish to obtain results from a query such as this one:
SELECT fname, lname, region_code, dob
FROM t1 WHERE region_code > 125 AND region_code < 130; It is easy to see that none of the rows which ought to be returned will be in either of the partitions p0 or p3; that is, we need to search only in partitions p1 and p2 to find matching rows. By doing so, it is possible to expend much less time and effort in finding matching rows than would be required to scan all partitions in the table. This“cutting away” of unneeded partitions is known as pruning.
Q35. View the Exhibit1 and examine the structure and indexes for the MYSALES table.
The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns are frequently used in WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table.
View exhibit 2 and examine one of the queries and its auto trace output.
What should you do to improve the performance of the query?
A. Use the INDEX_COMBINE hint in the query.
B. Create composite index involving the CUST_ID and PROD_ID columns.
C. Gather histograms statistics for the CUST_ID and PROD_ID columns.
D. Gather index statistics for the MYSALES_PRODID_IDX and MYSALES_CUSTID_IDX indexes.
Statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
INDEX_COMBINE Forces a bitmap index access path on tab. Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of indexes it can think of based on the statistics. If it is ignoring a bitmap index that you think would be helpful, you may specify that index plus all of the others taht you want to be used. Note that this does not force the use of those indexes, Oracle will still make cost based choices.
Histograms Opportunities Any column used in a where clause with skewed data Histograms are NOT just for indexed columns.
– Adding a histogram to an un-indexed column that is used in
Leading 1Z0-117 exam guide:
Q36. View the code sequence:
Which three statements are true about the execution of this query?
A. The optimizer uses an index join as an access path.
B. The optimizer uses a B* tree access path for the EMPLOYEES table.
C. The optimizer uses a bitmap access path for the EMPLOYEES table.
D. The optimizer performs bitmap conversion from row IDs.
E. The optimizer performs bitmap conversion to row IDs of the combined maps.
F. The optimizer performs bitmap conversion from the row IDs of the combined maps.
Q37. Which three statements are true about histograms?
A. They capture the distribution of different values in an index for better selectivity estimates.
B. They can be used only with indexed columns.
C. They provide metadata about distribution of and occurrences of values in a table column.
D. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.
E. They help the optimizer in deciding whether to use an index or a full table scan.
F. They help the optimizer to determine the fastest table join order.
Explanation: C: A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.
E: It's well established that histograms are very useful for helping the optimizer choose between a full-scan and and index-scan.
F: Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order. Note:
* The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.
B: Histograms are NOT just for indexed columns.
– Adding a histogram to an un-indexed column that is used in a where clause can improve performance.
D: Histograms Opportunities Any column used in a where clause with skewed data Columns that are not queried all the time Reduced overhead for insert, update, delete
Q38. Which four statements are correct about communication between parallel execution process?
A. The number of logical pathways between parallel execution producers and consumers depends on the degree parallelism.
B. The shared pool can be used for parallel execution messages buffers.
C. The large pool can be used for parallel execution messages buffers.
D. The buffer cache can be used for parallel execution message buffers.
E. Communication between parallel execution processes is never required if a query uses full partition-wise joins.
F. Each parallel execution process has an additional connection to the parallel execution coordinator.
Explanation: A: Note that the degree of parallelism applies directly only to intra-operation parallelism. If inter-operation parallelism is possible, the total number of parallel execution servers for a statement can be twice the specified degree of parallelism. No more than two sets of parallel execution servers can run simultaneously. Each set of parallel execution servers may process multiple operations. Only two sets of parallel execution servers need to be active to guarantee optimal inter-operation parallelism.
B: By default, Oracle allocates parallel execution buffers from the shared pool.
F: When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout job execution, then become available for other operations. After the statement has been processed completely, the parallel execution servers return to the pool.
Reference: Oracle Database Data Warehousing Guide, Using Parallel Execution
Q39. In your database, the CURSOR_SHARING parameter is set to FORCE.
A user issues the following SQL statement:
Select * from SH.CUSTOMERS where REIGN=’NORTH’
Which two statements are correct?
A. The literal value ‘NORTH’ is replaced by a system-generated bind variable.
B. Bind peeking will not happen and subsequent executions of the statement with different literal values will use the same plan.
C. Adaptive cursor sharing happens only if there is a histogram in the REIGN column of the CUSTOMERS table.
D. Adaptive cursor sharing happens irrespective of whether there is a histogram in the REIGN column of the CUSTOMERS table.
Q40. Which two statements about In-Memory Parallel Execution are true?
A. It can be configured using the Database Resource Manager.
B. It increases the number of duplicate block images in the global buffer cache.
C. It requires setting PARALLEL_DEGREE_POLICY to LIMITED.
D. Objects selected for In-Memory Parallel Execution have blocks mapped to specific RAC instances.
E. It requires setting PARALLEL_DEGREE_POLICY to AUTO
F. Objects selected for In-Memory Parallel Execution must be partitioned tables or indexes.
D, E: 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.
E: 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.
C: LIMITED Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism.
Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works