High good quality and great value involving our Oracle 1Z0-117: 100% passing guarantee and money back. In the event you use Testkings Oracle Oracle exam practice resources, we promise you wonderful success on your very first try. Otherwise, you will receive the full refund of ones purchasing costs. And you will appreciate free updated Oracle 1Z0-117 exam questions along with answers within 120 days soon after buying.
2021 Aug 1Z0-117 download
Q81. You enable auto degree of parallelism (DOP) for your database instance.
Examine the following query:
Which two are true about the execution of statement?
A. Dictionary DOP for the objects accessed by the query is used to determine the statement DOP.
B. Auto DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
C. Dictionary DOP is used to determine the statement DOP only if the estimated serial execution time exceeds PARALLEL_MIN_TIME_THRESHOLD.
D. The statement will be queued if insufficient parallel execution slaves are available to satisfy the statements DOP.
E. The statement will be queued if the number of busy parallel execution servers exceeds PARALLEL_SERVERS_TARGET.
F. The statements may execute serially.
Answer: E,F
Explanation: * Parallel (Manual): The optimizer is forced to use the parallel settings of the objects in the statement.
*
MANUAL - This is the default. Disables Auto DOP (not B), statement queuing (not D, Not
E) 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).
*
PARELLEL (MANUAL)
You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the
DOP at which the statement should run.
The following example forces the statement to use Oracle Database 11g Release 1 (11.1)
behavior:
SELECT /*+ parallel(manual) */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;
*
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.
By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.
Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.
Q82. Identify two situations in which full table scans will be faster than index range scans.
A. A query with a highly selective filter fetching less than 5 percent of the rows from a table.
B. A highly selective query on a table having high clustering factor for an index.
C. A query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.
D. A query executing in parallel on a partitioned table with partitioned indexes.
E. A query on a table with sparsely populated table blocks.
Answer: C,D
Explanation:
D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
Note:
* See 6) and 7) below.
The oracle optimizer choose the best plan and execute the query according the plan. It is
common to hear that my table has indexes but why oracle does not use indexes rather it is
using full table scan. There are several reasons behind choosing optimizer full table scans.
1)The table has no indexes within it.
2)Table has indexes but they are not appropriate to queries. For example in the table there is normal B-tree indexes but in the query the column used in the WHERE clause contains function. 3)Query access large amount of data. The table has indexes but query against it select almost all of the rows. In that case optimizer might choose to full access of table. 4)Index creation order may not appropriate. You have composite indexes on a table but in the where clause the leading column inside indexes are not used rather trailing columns are used. 5)The table is skewed. For example column gender contains value 'M' 10,000 times but value 'F' only 10 times.6)The table is small. If a table can read in a single I/O call, then a full table scan might be cheaper than an index range scan. Single I/O call is defined by DB_FILE_MULTIBLOCK_READ_COUNT parameter and value defined by blocks.Check it by,SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNTNAME TYPE VALUE--
db_file_multiblock_read_count integer 16
7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans.
8)In the query if there is no filtering then full table scan is the choice.
*
If an index has poor cardinality (ie. more than 4% rows with the same index key) then it
will perform poorly. It will usually be faster to perform a full table scan. eg. Table SALES
has an index on the column PAYMENT_METHOD which can contain values such as COD,
CREDIT, CHEQUE, CASH. The statement
SELECT *
FROM sales
WHERE payment_method = 'CASH'
will probably perform so badly that you are better off without the index.
*
Oracle uses the full table scan as it assumes that it will have to read a certain part of the
table.
Reference: Oracle Database Reference, DB_FILE_MULTIBLOCK_READ_COUNT
Q83. An application accessing your database got the following error in response to SQL query:
ORA-12827: insufficient parallel query slaves available
View the parallel parameters for your instance:
No hints are used and the session use default parallel settings.
What four changes could you make to help avoid the error and ensure that the query executes in parallel?
A. Set PARELLEL_DEGREE_POLICY to AUTO.
B. Increase the value of PARELLEL_MAX_SERVERS.
C. Increase PARELLEL_SERVERS_TARGET.
D. Decrease PARELLEL_MIN_PERCENT.
E. Increase PARELLEL_MIN_SERVERS.
F. Decrease PARELLEL_MIN_TIME_THRESHOLD.
G. Increase PARELLEL__MIN_TIME_THRESHOLD.
Answer: A,C,D,G
Explanation: C: 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.
By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.
D: Note: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves
A, G: 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.
Updated 1Z0-117 exam question:
Q84. View 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 have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million
rows.
View exhibit 2 and examine one of the queries and its autotrace output.
Which two methods can examine one of the queries and its autotrace output?
A. Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes.
B. Use the INDEX_COMBINE hint in the query.
C. Create a composite index involving the CUST_ID and PROD_ID columns.
D. Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute.
E. Collect histogram statistics for the CUST_ID and PROD_ID columns.
Answer: B,C
Explanation: B: The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint.
C: Combining the CUST_ID and PROD_ID columns into an composite index would improve performance.
Q85. Examine the utilization parameters for an instance:
You notice that despite having an index on the column used in the where clause, queries use full table scans with highly selective filters.
What are two possible reasons for the optimizer to use full table scans instead of index unique scans and index range scans?
A. The OPTIMIZER_MODE parameter is set to ALL_ROWS.
B. The clustering factor for the indexes is high.
C. The number of leaf blocks for the indexes is high.
D. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to 100.
E. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
Answer: A,B
Explanation: http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html
* OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows. all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
Q86. Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query?
A. The optimizer always uses a plan in which the transformation is used.
B. The optimizer uses transformation only if the cost is less than a query executing without transformation.
C. The optimizer always generates subqueries to transform a query.
D. The optimizer always uses bitmap indexes on the primary key column for any dimension table to transform a query.
Answer: B
Explanation:
(http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50
508)
says that "Even if the hint is specified, there is no guarantee that the transformation will
take place".
Q87. You are working on a database that supports an OLTP workload. You see a large number of hard parses occurring and several almost identical SQL statements in the library cache that vary only in the literal values in the WHERE clause conditions.
Which two methods can you use to reduce hard parsing?
A. Replace literals with bind variables and evolve a baseline for the statement.
B. Use the RESULT_CACHE hint in the queries.
C. Create baselines for the almost identical SQL statement by manually loading them from the cursor cache.
D. Set the CURSOR_SHARING parameter to SIMILAR.
Answer: A,D
Explanation: A: We can reduce this Hard parsing by using bindvariables
D: 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.
Note: A hard parse is when your SQL must be re-loaded into the shared pool. A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for a easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.
Reference: Oracle Database Reference, CURSOR_SHARING