Avant-garde 1Z0-117: Examcollection real preparation exams from 71 to 80

Cause all that matters here is passing the Oracle 1Z0-117 exam. Cause all that you need is a high score of 1Z0-117 Oracle Database 11g Release 2: SQL Tuning Exam exam. The only one thing you need to do is downloading {brand} 1Z0-117 exam study guides now. We will not let you down with our money-back guarantee.

2016 Sep 1Z0-117 Study Guide Questions:

Q71. A new application module is deployed on middle tier and is connecting to your database. You want to monitor the performance of the SQL statements generated from the application. 

To accomplish this, identify the required steps in the correct order from the steps given below: 

1.

 Use DBNMS_APPLICATION_INFO to set the name of the module 

2.

 Use DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE to enable statistics gathering for the module. 

3.

 Use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE to enable tracing for the service 

4.

 Use the trcsess utility to consolidate the trace files generated. 

5.

 Use the tkprof utility to convert the trace files into formatted output. 

A. 1, 2, 3, 4, 5 

B. 2, 3, 1, 4, 5 

C. 3, 1, 2, 4, 5 

D. 1, 2, 4, 5 

E. 1, 3, 4, 5 

F. 2, 1, 4, 5 

Answer: A 

Explanation: 

Note: 

*

 Before tracing can be enabled, the environment must first be configured to enable gathering of statistics. 

*

 (gather statistics): DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE Enables statistic gathering for a given combination of Service Name, MODULE and ACTION 

*

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

dbms_monitor.serv_mod_act_trace_enable( 

service_name IN VARCHAR2, 

module_name IN VARCHAR2 DEFAULT ANY_MODULE, 

action_name IN VARCHAR2 DEFAULT ANY_ACTION, 

waits IN BOOLEAN DEFAULT TRUE, 

binds IN BOOLEAN DEFAULT FALSE, 

instance_name IN VARCHAR2 DEFAULT NULL, 

plan_stat IN VARCHAR2 DEFAULT NULL); 

SELECT instance_name 

FROM gv$instance; 

exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase'); 

exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase'); 

*

 When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions, but for shared server sessions, many processes are involved. 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. 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. 

*

 Once the trace files have been consolidated (with trcsess), tkprof can be run against the consolidated trace file for reporting purposes. 

Q72. Exhibit 

Examine the following SQL statement: 

Examine the exhibit to view the execution plan. 

Which statement is true about the execution plan? 

A. The EXPLAIN PLAN generates the execution plan and stores it in c$SQL_PLAN after executing the query. Subsequent executions will use the same plan. 

B. The EXPLAIN PLAN generates the execution plan and stores it in PLAN_TABLE without executing the query. Subsequent executions will always use the same plan. 

C. The row with the ID 3 is the first step executed in the execution plan. 

D. The row with the ID 0 is the first step executed in the execution plan. 

E. The rows with the ID 3 and 4 are executed simultaneously. 

Answer: E 

Explanation: Note the other_tag parallel in the execution plan. 

Note: 

Within the Oracle plan_table, we see that Oracle keeps the parallelism in a column called 

other_tag. The other_tag column will tell you the type of parallel operation that is being 

performed within your query. 

For parallel queries, it is important to display the contents of the other_tag in the execution. 

Q73. Examine the Exhibit to view the structure of an indexes for the SALES table. 

The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values. 

What would you do to influence the optimizer for better selectivity? 

A. Drop bitmap index and create balanced B*Tree index on the CUST_ID column. 

B. Create a height-balanced histogram for the CUST_ID column. 

C. Gather statistics for the indexes on the SALES table. 

D. Use the ALL_ROWS hint in the query. 

Answer: D 

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

1Z0-117  test preparation

Renovate 1Z0-117 training:

Q74. Examine the following query and execution plan: 

Which query transformation technique is used in this scenario? 

A. Join predicate push-down 

B. Subquery factoring 

C. Subquery unnesting 

D. Join conversion 

Answer: A 

Explanation: * Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins. 

* The following shows the types of views on which join predicate pushdown is currently supported. 

UNION ALL/UNION view Outer-joined view Anti-joined view Semi-joined view DISTINCT view GROUP-BY view 

Q75. Which two tasks are performed during the optimization stage of a SQL statement? 

A. Evaluating the expressions and conditions in the query 

B. Checking the syntax and analyzing the semantics of the statement 

C. Separating the clauses of the SQL statement into structures that can be processed 

D. Inspecting the integrity constraints and optimizing the query based on this metadata 

E. Gathering the statistics before creating the execution plan for the statement 

Answer: D,E 

Explanation: 

Note: 

* Oracle SQL is parsed before execution, and a hard parse includes these steps: . Loading into shared pool – The SQL source code is loaded into RAM for parsing. (the "hard" parse step) . Syntax parse – Oracle parses the syntax to check for misspelled SQL keywords. . Semantic parse – Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data. . Query Transformation – If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate. 

. Optimization – Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g). . Create executable – Oracle builds an executable file with native file calls to service the SQL query. 

The parsing process performs two main functions: 

o Syntax Check: is the statement a valid one. Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL. 

o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on. 

So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute properly. 

Reference: Oracle hard-parse vs. soft parse 

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

Answer: B,C 

Explanation: B: As per line 14 and 15. 

C: As per exhibit line 13 is execute before line 15. 

Incorrect: 

Not E: As per line 13 and 15 – they are not executed simultaneously. 

certleader.com

Certified 1Z0-117 torrent:

Q77. Examine the Exhibit. 

Which two options are true about the execution plan and the set of statements? 

A. The query uses a partial partition-wise join. 

B. The degree of parallelism is limited to the number of partitions in the EMP_RANGE_DID table. 

C. The DEPT table id dynamically distributed based on the partition keys of the EMP_RANGE_DID table. 

D. The server process serially scans the entire DEPT table for each range partition on the EMP_RANGE_DID table. 

E. The query uses a full partition-wise join. 

Answer: A,D 

Q78. You are administering a database, where an application frequently executes identical SQL 

statements with the same syntax. 

How will you optimize the query results without retrieving data blocks from the storage? 

A. By setting the CURSOR_SHARING parameter to FORCE. 

B. By using the bind variables and setting the CURSOR_SHARING parameter to EXACT. 

C. By using the CACHE hint to pin the queries in the library cache 

D. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries. 

E. By creating a SQL plan baseline for the identical statements. 

Answer: D 

Explanation: 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. 

Note: 

RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's 

execution plan. 

Values: 

MANUAL 

The ResultCache operator is added only when the query is annotated (that is, hints). 

FORCE 

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. 

Incorrect: 

A, B: CURSOR_SHARING determines what kind of SQL statements can share the same 

cursors. 

Values: 

FORCE 

Forces statements that may differ in some literals, but are otherwise identical, to share a 

cursor, unless the literals affect the meaning of the statement. 

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. 

EXACT 

Only allows statements with identical text to share the same cursor. 

C: The Oracle library cache is a component of the System Global Area (SGA) shared pool. Similarly to other Oracle cache structures, the point of the library cache is to reduce work – and therefore to improve performance – by caching the result of parsing and optimizing SQL or PL/SQL so that subsequent executions of the same SQL or PL/SQL require fewer preparatory steps to deliver a query result. 

Q79. Examine the query and its execution plan: 

Which statement is true regarding the execution plan? 

A. This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table. 

B. The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset. 

C. The query first fetches rows from the ORDERS table that satisfy the conditions, and then the join returns NULL form CUSTOMER_ID column when it does not find any corresponding rows in the CUSTOMERS table. 

D. The query first joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not find any corresponding rows in the ORDERS table, and then fetches the rows that satisfy the conditions from the result set. 

Answer: A 

Q80. You plan to bulk load data INSERT INTO . . . SELECT FROM statements. 

Which two situations benefit from parallel INSERT operations on tables that have no materialized views defined on them? 

A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows and a conventional B*tree secondary index. 

B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index. 

C. Direct path insert of 10 rows into a partitioned, index-organized table containing one million rows and conventional B* tree secondary index. 

D. Direct path insert of 10 rows into a partitioned, index-organized table containing 10 rows and a bitmapped secondary index 

E. Conventional path insert of a million rows into a nonpartitioned, heap-organized containing 10 rows and having a conventional B* tree index. 

F. Conventional path insert of 10 rows into a nonpartitioned, heap-organized table one million rows and a bitmapped index. 

Answer: A,B 

Explanation: 

Note: 

*

 A materialized view is a database object that contains the results of a query. 

*

 You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERTand direct-path INSERT. 

*

 With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-path INSERT is the default. 

*

 Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view. 

*

 When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints 

*

 Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force 

logging settings of the database 

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.