Top Most recent 1Z0-117 sample question Reviews!

Proper study guides for Regenerate Oracle Oracle Database 11g Release 2: SQL Tuning Exam certified begins with Oracle 1Z0-117 preparation products which designed to deliver the High quality 1Z0-117 questions by making you pass the 1Z0-117 test at your first time. Try the free 1Z0-117 demo right now.

2016 Sep 1Z0-117 Study Guide Questions:

Q21. While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database. 

What does the optimizer do in this situation? 

A. Updates the existing SQL profiles for which the statistics are stale. 

B. Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC 

C. Starts the statistics collection process by running GATHER_STATS_JOB 

D. Writes a warning message in the alert log file 

Answer: B 

Explanation: Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROCprioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes. 

Note: 

* The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output: 

/ Recommendations to gather relevant statistics for objects with stale or no statistics 

Because optimizer statistics are automatically collected and refreshed, this problem occurs only when automatic optimizer statistics collection is disabled. See "Managing Automatic Optimizer Statistics Collection". 

/ Auxiliary statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics 

The database stores this auxiliary information in an object called a SQL profile. 

* Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects them both for the table and associated indexes. 

Automatic collection eliminates many manual tasks associated with managing the optimizer. It also significantly reduces the risks of generating poor execution plans because of missing or stale statistics. 

Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROC prioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes. 

Reference: Oracle Database Performance Tuning Guide, Managing Automatic Optimizer Statistics Collection 

Q22. Refer to the Exhibit. Execution plan: 

What must be the correct order of steps that the optimizer executes based on the ID column the execution plan? 

A. 3, 5, 4, 6, 7 

B. 3, 5, 4, 7, 6 

C. 3, 4, 5, 7, 6 

D. 4, 5, 3, 7, 6 

Answer: B 

Q23. Examine the initialization parameters for a database an OLTP overload. 

What is the effect of changing the value of the parameter? 

A. It influences the optimizer to always use the value of the parameter? 

B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced. 

C. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced. 

D. It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced. 

Answer: B 

Explanation: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal. 

1Z0-117  study guide

Up to the minute 1Z0-117 exam question:

Q24. Your database has the OLTP_SRV service configured for an OLTP application running on a middle tier. This service is used to connect to the database by using connection pools. The application has three modules. You enabled tracing at the service by executing the following command: 

SQL exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (‘OLTP_SRV’); 

What is the correct method of consolidating the trace files generated by the procedure? 

A. Use all trace files as input for the tkprof utility to consolidate the trace files for a module. 

B. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module. 

C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility. 

D. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes. 

Answer: C 

Explanation: 

Note: 

* Oracle provides the trcsess command-line utility that consolidates tracing information 

based on specific criteria. 

The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can 

help you monitor applications running against the Oracle Server. 

Note: SERV_MOD_ACT_TRACE_ENABLE Procedure Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified 

Reference: Oracle Database Performance Tuning Guide 

Q25. 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. 

Answer: C 

Explanation: SERV_MOD_ACT_TRACE_ENABLE 

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', 

module_name=>'SQL*Plus') 

PL/SQL procedure successfully completed. 

SQL> select * from sys.wri$_tracing_enabled; 

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME 

FLAGS 

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 

Q26. Which two statements are true about the trcsess utility? 

A. It merges multiple trace files and produces a formatted output file. 

B. It merges multiple trace files from a particular session into one single trace file. 

C. It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility. 

D. It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility. 

E. It merges files pertaining to a user session scattered across different processes in a shared server configuration. 

Answer: A,B 

Explanation: The trcsess utility consolidates trace output from selected trace files based on several criteria: 

Session ID Client ID Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be processed by TKPROF. 

Note: 

*

 trcsess is useful for consolidating the tracing of a particular session for performance or debugging purposes. Tracing a specific session is usually not a problem in the dedicated server model as a single dedicated process serves a session during its lifetime. You can see the trace information for the session from the trace file belonging to the dedicated server serving it. However, in a shared server configuration a user session is serviced by different processes from time to time. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This makes it difficult to get a complete picture of the life cycle of a session. 

*

 Now there is a new tool, a command line utility called trcsess to help read the trace files. 

Reference: Oracle Database Performance Tuning Guide 11g , Using the trcsess Utility The trcsess command-line utility consolidates trace information from selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name. 

1Z0-117  study guide

Accurate 1Z0-117 free demo:

Q27. 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". 

Q28. 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 

Answer: A,B,G 

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. 

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. 

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. 

Q29. What are three common reasons for SQL statements to perform poorly? 

A. Full table scans for queries with highly selective filters 

B. Stale or missing optimizer statistics C. Histograms not existing on columns with evenly distributed data 

D. High index clustering factor 

E. OPTIMIZER_MODE parameter set to ALL_ROWS for DSS workload 

Answer: A,B,D 

Explanation: 

D: The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for optimal access. 

Note: 

*

 (Not C) Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order. 

*

 OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance. 

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). 

Q30. 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). 

About 1Z0-117 Information: 1Z0-117 Dumps

Certleader Dumps
Certleader is a company specialized on providing high quality IT exam materials and fully committed to assist our respected clients crack any IT certification tests on their 1st efforts.