Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 1:

  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.
  C. A single trace file is created for each session that uses the ACC_SRV service.

  ---

  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

• Question 2:

  Examine the Following Query and execution plan: Which query transformation technique is used by the optimizer?

  

  A. Filter push down
  B. Subquery factoring
  C. Subquery unnesting
  D. Predicate pushing
  D. Predicate pushing

  ---

  Note:

  * In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation.

• Question 3:

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

  ---

  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

• Question 4:

  Which three tasks are performed by the parallel execution coordinator process?

  A. Allocating parallel execution processes from the parallel execution server pool.
  B. Determining the parallel execution method for each operation in the execution plan.
  C. Managing the data flow between the producers and consumers during inter-operation parallelism.
  D. Any serial processing that is part of the execution plan.
  E. Determining the desired number of parallel execution processes
  F. Managing the data flow between the producers and consumers during intra-operation parallelism.
  A. Allocating parallel execution processes from the parallel execution server pool.
  B. Determining the parallel execution method for each operation in the execution plan.
  C. Managing the data flow between the producers and consumers during inter-operation parallelism.

  ---

  A: When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle Database can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout execution. After the statement has been processed completely, the parallel execution servers return to the pool.

  B:

  *

  The parallel execution coordinator examines each operation in a SQL statement's execution plan then determines the way in which the rows operated on by the operation must be divided or redistributed among the parallel execution servers.

  *

  After the optimizer determines the execution plan of a statement, the parallel execution coordinator determines the parallel execution method for each operation in the plan.

  Note:

  *

  Oracle Database can process a parallel operation with fewer than the requested number of processes. If all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started, the parallel execution coordinator switches to serial processing.

  Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works

• Question 5:

  When would bind peeking be done for queries that vary only in values used in the WHERE clause?

  A. When the column used in the WHERE clause has evenly distributed data and histogram exists on that column.
  B. When the column used in the WHERE clause has evenly distributed data and index exists on that column.
  C. When the column used in the WHERE clause has non uniform distribution of data, uses a bind variable, and no histogram exists for the column.
  D. When the column used in the WHERE clause has non uniform distribution of data and histogram exists for the column.
  B. When the column used in the WHERE clause has evenly distributed data and index exists on that column.

• Question 6:

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

  ---

  http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12509

• Question 7:

  Which type of SQL statement would be selected for tuning by the automatic SQL framework?

  A. Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement
  B. Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.
  C. Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.
  D. Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.
  E. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.
  E. Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.

  ---

  The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.

  Note:

  * When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query optimizer operates in two modes: a normal mode and a tuning mode.

  In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

  In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.

  Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

• Question 8:

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

  ---

  Note:

  Oracle SQL is parsed before execution, and a hard parse includes these steps: *

  1.

  Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)

  2.

  Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.

  3.

  Semantic parse - Oracle verifies all table and column names from the dictionary and checks to see if you are authorized to see the data.

  4.

  Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.

  5.

  Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).

  6.

  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

• Question 9:

  Examine the exhibit.

  

  Which is true based on the information obtainable from the execution plan?

  A. A full partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.
  B. A full table scan on the DEPARTMENTS table performed serially by the query coordinator.
  C. A full table scan on the DEPARTMENTS table is performed serially by a single parallel execution server process.
  D. A partial partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.
  E. A full table scan on the EMPLOYEES table is done in parallel.
  E. A full table scan on the EMPLOYEES table is done in parallel.

  ---

  PX BLOCK ITERATOR This operation is typically the first step in a parallel pipeline. The BLOCK ITERATOR breaks up the table into chunks that are processed by each of the parallel servers involved.

  Incorrect:

  B, C: The scan on the Departsments table is done in parallel.

  Note:

  * As per exhibit: Line 7 is run first, followed by line 6.

  *

  Example with same structure of execution plan:

  

  Here's how to read the plan:

  1.

  The first thing done is at line 9 an index fast full scan on SYS.OBJ$.I_OBJ1 index. This is done in parallel, as indicated from the "PX SEND" line above.

  2.

  In line 8, we're doing a "PX SEND BROADCAST" operation. When joining tables in parallel, Oracle can choose to either broadcast results (rows) from one operation to apply to the other table scan, or it can choose PX SEND HASH. In this case, our CBO determined that a BROADCOAST was appropriate because the results from the OBJ$ table were much lower than the MYOBJ table

  3.

  Line 7, the PX RECEIVE step, is basically the consumer of the broadcasted rows in step 8

  4.

  Line 6 is an in-memory BUFFER SORT of the rows returned from the index scan on OBJ$

  5.

  Lines 11 and 10, respectively, indicate the full scan and PX BOCK ITERATOR operation for the granules involved in the 8 PQ servers

  6.

  In line 5, Oracle is doing a hash join on the resulting rows from the parallel scans on MYOBJ and OBJ$

  7.

  Line 4 is a per-PQ server sort of data from the joind PQ servers

  8.

  Line 3 is the consumer QC that holds the result of the each of the PQ servers

  9.

  Line 2 is the PX Coordinator (QC) collecting, or consuming the rows of the joined data

  10.

  Line 1 is the final SORT AGGREGATE line that performs the grouping function

• Question 10:

  Which four types of column filtering may benefit from partition pruning when accessing tables via partitioned indexes?

  A. Equality operates on List-Partitioned Indexes
  B. Not Equal operates on a Global Hash-Partitioned Indexes
  C. Equality operates on System-Partitioned Tables
  D. In-List operates on Range-Partitioned Indexes
  E. Not Equal operates on a local Hash-Partitioned Indexes
  F. Equality operates on Range-Partitioned Indexes
  G. Equality operates on Hash-Partitioned Indexes
  A. Equality operates on List-Partitioned Indexes
  D. In-List operates on Range-Partitioned Indexes
  F. Equality operates on Range-Partitioned Indexes
  G. Equality operates on Hash-Partitioned Indexes

  ---

  Oracle Database prunes partitions when you use range, LIKE, equality (A, F), and IN-list (D) predicates on the range or list partitioning columns, and when you use equality (G) and IN-list predicates on the hash partitioning columns. Reference: Oracle Database VLDB and Partitioning Guide 11g, Information that can be Used for Partition Pruning