However, Exambible will be the leading resource provider of the Oracle 1Z0-117 exam practice components. If you take aid from Exambible, you can find that simply the most up-to-date contents for that Oracle certification exam can develop obvious result. If you buy the Oracle Oracle exam demos from Exambible, you will help to make yourself properly prepared for the exam. Getting certified is no dilemma with the aid of Exambible.
2016 Sep 1Z0-117 real exam
Q81. Examine Exhibit 1 to view the query and its execution plan.
Examine Exhibit 2 to view the structure and indexes for the EMPLOYEES and DEPARTMENTS tables.
Examine Exhibit 3 to view the initialization parameters for the instance.
Why is sort-merge join chosen as the access method?
A. Because the OPTIMIZER_MODE parameter is set to ALL_ROWS.
B. Because of an inequality condition.
C. Because the data is not sorted in the LAST_NAME column of the EMPLOYEES table
D. Because of the LIKE operator used in the query to filter out records
B: There is not an inequality condition in the statement.
C: Merge joins are beneficial if the columns are sorted.
D: All regular joins should be able to use Hash or Sort Merge, except LIKE, !=, and NOT ... joins.
A sort merge join is a join optimization method where two tables are sorted and then joined.
A "sort merge" join is performed by sorting the two data sets to be joined according to the join keys and then merging them together. The merge is very cheap, but the sort can be prohibitively expensive especially if the sort spills to disk. The cost of the sort can be lowered if one of the data sets can be accessed in sorted order via an index, although accessing a high proportion of blocks of a table via an index scan can also be very expensive in comparison to a full table scan.
Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality
When the Optimizer Uses Sort Merge Joins
The optimizer can choose a sort merge join over a hash join for joining large amounts of
data if any of the following conditions are true:
/ The join condition between two tables is not an equi-join.
/ Because of sorts already required by other operations, the optimizer finds it is cheaper to
use a sort merge than a hash join.
Reference: Oracle Database Performance Tuning Guide , Sort Merge Joins
Q82. An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values.
The CURSOR_SHARING parameter to set to EXACT.
Which two statements are true about the usage of bind variables?
A. The number of latch gets in the library cache will be reduced.
B. Bind peeking will take place and subsequent execution of queries can have different plans based on the cardinality of the bind value in the column.
C. Bind peeking will take place and subsequent execution of queries can have different plans only when the histograms exists on a column used in WHERE clause.
D. Bind peeking will not happen and the optimizer will use the same plan for all bind values if no histograms exist on a column use in the WHERE clause.
E. Bind peeking will happen and subsequent execution of queries will have the same parent cursor but different child cursors.
Explanation: CURSOR_SHARING determines what kind of SQL statements can share the
Only allows statements with identical text to share the same cursor.
EXACT-This is the default setting. With this value in place, the query is not rewritten to use
With CURSOR_SHARING=EXACT (the default), every unique SQL statement executed
will create a new entry in V$SQL, it will be hard-parsed, and an execution plan will be
created just for it. There can be hundreds or thousands of very similar queries in the shared
pool that differ only in the literals used in the SQL statement itself. This implies that the
database is forced to hard-parse virtually every query, which, in turn, not only consumes a
lot of CPU cycles but also leads to decreased scalability.
The database just cannot hard-parse hundreds or thousands of SQL statements
concurrently—the application ends up waiting for the shared pool to become available. One
of the major scalability inhibitors in the database is not using bind variables. That was the
motivation behind adding CURSOR_SHARING=FORCE .
Q83. A database supports three applications: CRM, ERP, and ACC. These applications connect to the database by using three different services: CRM_SRV for the CRM application, ERP_SRV for the ERP application, and ACC_SRV for the ACC application.
You enable tracing for the ACC_SRV service by issuing the following command:
SQL> EXECUTE DBMS for the ACC_SRV service by issuing the following command:
SQL> EXECUITIVE DBMS_MONITOR. SERV_MOD_ACT_TRACE_ENABLE (service_name => ‘ACC_SRV’, waits => TRUE, binds = > FALSE, instance_name = > ‘inst1’);
Which statement is true?
A. All trace information for the service connection to inst1 will be stored in a single trace file.
B. A trace file is not created because the module name is not specified.
C. A single trace file is created for each session that uses the ACC_SRV service.
D. Only those SQL statements that are identified with the ACC_SRV service executed on the inst1 instance are recorded in trace files.
E. All trace information for the ACC_SRV service connected to inst1 is stored in multiple trace files, which can be consolidated by using the tkprof utility.
serv_mod_act_trace_enable and serv_mod_act_trace_disable, which enables and disables
trace for given service_name, module and action.
For example for a given service name you can trace all session started from SQL*Plus.
Module and action in your own created application can be set using dbms_application_info
set_module and set_action procedures.
serv_mod_act_trace_enable fills sys table wri$_tracing_enabled and view
dba_enabled_traces on top of this table as follows:
SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl',
PL/SQL procedure successfully completed.
SQL> select * from sys.wri$_tracing_enabled;
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME
4 orcl SQL*Plus 8 SQL> select * from dba_enabled_traces;
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME SERVICE_MODULE orcl SQL*Plus TRUE FALSE
Up to the immediate present 1Z0-117 exam prep:
Q84. Examine the query:
The RESULT_CACHE_MODE parameter is set to MANUAL for the database.
Which two statements are true about the usage of the result cache?
A. The SQL runtime environment checks whether the query result is cached in the result cache; if the result exists, the optimizer fetches the result from it.
B. The SQL runtime environment does check for the query result in the result cache because the RESULT_CACHE_MODE parameter is set to MANUAL.
C. The SQL runtime environment checks for the query result in the result cache only when the query is executed for the second time.
D. If the query result does not exist in the cache and the query is executed, the result is generated as output, and also sorted in the result cache.
result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;
As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.
RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.
The ResultCache operator is added only when the query is annotated (that is, hints).
The ResultCache operator is added to the root of all SELECT statements (provided that it
is valid to do so).
For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the
hint takes precedence over the parameter setting.
Q85. 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.
Q86. You are administering a database supporting an OLTP application. The application runs a series of extremely similar queries the MYSALES table where the value of CUST_ID changes.
Examine Exhibit1 to view the query and its execution plan.
Examine Exhibit 2 to view the structure and indexes for the MYSALES table. The MYSALES table has 4 million records.
Data in the CUST_ID column is highly skewed. Examine the parameters set for the instance:
Which action would you like to make the query use the best plan for the selectivity?
A. Decrease the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
B. Us the /*+ INDEX(CUST_ID_IDX) */ hint in the query.
C. Drop the existing B* -tree index and re-create it as a bitmapped index on the CUST_ID column.
D. Collect histogram statistics for the CUST_ID column and use a bind variable instead of literal values.
Explanation: Using Histograms
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.
A column histogram should only be created when we have data skew exists or is suspected.
Q87. 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.