We update the actual Oracle 1Z0-117 training materials as before long as you can find some modifications in the Oracle Oracle true exam. Our customers wont feel any inconvenience or even difficulty in preparing for the Oracle 1Z0-117 exam. You will get the most recent Oracle Oracle 1Z0-117 exam practice materials coming from Testking. We may inform of a person when we upgrade the actual Oracle 1Z0-117 exam dumps through email.
2021 Jan 1Z0-117 book
Q51. Which two are the fastest methods for fetching a single row from a table based on an equality predicate?
A. Fast full index scan on an index created for a column with unique key
B. Index unique scan on an created for a column with unique key
C. Row fetch from a single table hash cluster
D. Index range scan on an index created from a column with primary key
E. Row fetch from a table using rowid
Explanation: A scan is slower than a row fetch (from hash value or rowid).
Q52. Examine the execution plan:
Which two are true regarding the execution plan?
A. The CUSTOMERS table is hash partitioned.
B. The SALES table is hash partitioned.
C. The CUSTOMERS table is scanned first and selected partitions from the SALES table are scanned based on the BLOOM Filter created during the scan of the CUSTOMERS table.
D. The SALES table is scanned first and selected partitions from the CUSTOMERS table are scanned based on the Bloom Filter created during the scan of the SALES table.
E. Both the CUSTOMERS and SALES tables are scanned simultaneously and rows from the CUSTOMERS table are joined to row of the SALES table.
F. The CUSTOMERS table is range partitioned.
Explanation: B: As per line 14 and 15.
C: As per exhibit line 13 is execute before line 15.
Not E: As per line 13 and 15 – they are not executed simultaneously.
Q53. See the code fragment:
You receive the following error message:
ORA-12827: insufficient parallel query slaves available
Which three parameter settings could you change to avoid this error?
A. Decrease the value of PARALLEL_MIN_PERCENT
B. Increase the value of PARALLEL_MAX_SERVERS
C. Increase the value of PARALLEL_MIN_SERVERS
D. Reduce the value of PARALLEL_MIN_TIME_THRESHOLF
E. Increase the value of PARALLEL_DEGREE_LIMIT
F. Set the PARALLEL_DEGREE_POLICY = AUTO
G. Set the PARALLEL_DEGREE_POLICY = LIMITED
Explanation: A: 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
B: Your query doesn't run because you've told Oracle not to run it unless at least 5% of the parallel execution processes are available for your query.Set PARALLEL_MIN_PERCENT=0 or increase the number of parallel execution processes by increasing the PARALLEL_MAX_SERVERS parameter.
G: PARALLEL_DEGREE_POLICY PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
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.
Note: 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.
You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multi-user environment, an individual user or application can set PARALLEL_MIN_PERCENT to a minimum value until sufficient resources are available on the system and an acceptable degree of parallelism is returned.
Q54. Examine the exhibit.
Which two are true concerning the execution plan?
A. No partition-wise join is used
B. A full partition-wise join is used
C. A partial partition-wise join is used
D. The SALES table is composite partitioned
Explanation: * The following example shows the execution plan for the full partition-wise
join with the sales table range partitioned by time_id, and subpartitioned by hash on cust_id.
| Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | P->S | QC (RAND) |
|* 3 | FILTER | | | | PCWC | |
| 4 | HASH GROUP BY | | | | PCWP | |
| 5 | PX RECEIVE | | | | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | | | P->P | HASH |
| 7 | HASH GROUP BY | | | | PCWP | |
| 8 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | |
|* 9 | HASH JOIN | | | | PCWP | |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 16 | PCWP | |
| 11 | PX PARTITION RANGE ITERATOR| | 8 | 9 | PCWC | |
|* 12 | TABLE ACCESS FULL | SALES | 113 | 144 | PCWP | |
Predicate Information (identified by operation id):
3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100)
9 - access("S"."CUST_ID"="C"."CUST_ID")
12 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* Full partition-wise joins can occur if two tables that are co-partitioned on the same key are joined in a query. The tables can be co-partitioned at the partition level, or at the subpartition level, or at a combination of partition and subpartition levels. Reference partitioning is an easy way to guarantee co-partitioning. Full partition-wise joins can be executed in serial and in parallel.
Reference: Oracle Database VLDB and Partitioning Guide, Full Partition-Wise Joins: Composite - Single-Level
Q55. 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
Renovate 1Z0-117 free download:
Q56. An application supplied by a new vendor is being deployed and the SQL statements have plan baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You require the optimizer to use these baselines, but allow better plans to used, should any be created.
Which two tasks would you perform to achieve this?
A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
B. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
E. Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
Explanation: OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management B ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Q57. Tracing has been enabled for the HR user. You execute the following command to check the contents of the orcl_25052.trc trace file, which was generated during tracing:
Which two statements are correct about the execution of the command?
A. SCRIPT.SQL stores the statistics for all traced SWL statements.
B. Execution plans for SQL statements are stored in TEMP_PLAN_TABLE and can be queried by the user.
C. SQL statements in the output files are stored in the order of elapsed time.
D. TKPROF use TEMP_PLAN_TABLE in the HR schema as a temporary plan table.
E. Recursive SQL statements are included in the output file.
Explanation: INSERT Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.
Q58. In which three situations must you collect optimizer statistics manually for database objects in addition to automatic statistics collection?
A. When substantial DML activity occurs between the nightly automatic stats gathering maintenance job
B. When substantial activity occurs on a partition of the partitioned table.
C. When a table is used for bulk loads that add 10% or more to the total size of the table
D. When an index is created or dropped for a column
E. When the degree of parallelism is explicitly defined for a table
Q59. You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;
The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.
Which two options true about DML statements for which parallel execution is requested?
A. Statements for which PDML is requested will execute serially estimated time is less than the time specified by the PARALLEL_MIN_THRESHOLD parameter.
B. Statements for which PDML is requested will be queued if the number of busy parallel execution servers greater than PARALLEL_MIN_SERVERS parameter.
C. Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.
D. Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.
E. Statement for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_DEGREE_LIMIT parameter.
Explanation: C: 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.
D: 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.
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.
PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.
Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET
Q60. A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database using different services. You enabled the statistics gathering for the service by using the following command:
SQL > EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (‘APPS1’, NULL, NULL);
Which two statements are true regarding statistics gathered for APPS1 service?
A. The statistics are gathered for all the modules and actions within the service.
B. The statistics are collected at the session level for all sessions connected using the service.
C. The statistics are aggregated and stored in the V$SERV_MOD_ACT_STATS view.
D. The statistics are gathered for all the modules using the service only when DBMS_APPLICATION_INFO.SET_MODULE is executed to register with the service.
E. Statistics gathering is enabled only for the subsequent sessions using the service.
F. The statistics are gathered for all the applications using the service only when DBMS_APPLICATION_INFO.SET_ACTION is executed to register with the service.
Explanation: SERV_MOD_ACT_STAT_ENABLE Procedure This procedure enables statistic gathering for a given combination of Service Name, MODULE and ACTION. Calling this procedure enables statistic gathering for a hierarchical combination of Service name, MODULE name, and ACTION name on all instances for the same database. Statistics are accessible by means of the V$SERV_MOD_ACT_STATS view.
service_name IN VARCHAR2,
module_name IN VARCHAR2,
action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);
Name of the service for which statistic aggregation is enabled
Name of the MODULE. An additional qualifier for the service. It is a required parameter.
Name of the ACTION. An additional qualifier for the Service and MODULE name. Omitting
the parameter (or supplying ALL_ACTIONS constant) means enabling aggregation for all
Actions for a given Server/Module combination. In this case, statistics are aggregated on
the module level.
Reference: Oracle Database PL/SQL Packages and Types Reference 11g,