Oracle 1Z0-117 Online Practice Questions and Exam Preparation

Oracle 1Z0-117 Online Questions & Answers

• Question 61:

  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.
  D. Gather index statistics for the MYSALES_PRODID_IDX and MYSALES_CUSTID_IDX indexes.

  ---

  Note:

  *

  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

• Question 62:

  Examine the parallelism parameters for your instance: What are true about the execution of the query?

  

  A. It will execute in parallel only if the LINEITEM table has a dictionary DOP defined.
  B. DOP for the statement is determined by the dictionary DOP of the accessed objects.
  C. It is generated to execute in parallel.
  D. It will execute in parallel only if the estimated execution time is 10 or more seconds.
  E. DOP for the statement is calculated automatically.
  F. It may execute serially.
  E. DOP for the statement is calculated automatically.
  F. It may execute serially.

  ---

  E:

  F (not C): It may execute serially. See note below.

  Incorrect:

  A, B: Dictionary DOP not used with PARALLEL (AUTO) hint.

  D: The default value of parallel_min_time_threshold is 30 (not 10) seconds.

  Note:

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

• Question 63:

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

  ---

  CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

  EXACT

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

  Note:

  EXACT-This is the default setting. With this value in place, the query is not rewritten to use bind variables. 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 .

• Question 64:

  Which three options are true about MVIEWs?

  A. The defining query of an MVIEWs may be based on a populated table.
  B. Queries that are rewritten to an MVIEW will never obtain results from the result cache.
  C. All MVIEWS may be configured to support "refresh on demand".
  D. The defining query of an MVIEW may be based on non_partitioned table.
  E. All MVIEWs may be configured to support "refresh on commit"
  A. The defining query of an MVIEWs may be based on a populated table.
  B. Queries that are rewritten to an MVIEW will never obtain results from the result cache.
  C. All MVIEWS may be configured to support "refresh on demand".

  ---

  The defining query of a materialized view can select from tables, views, or

  A:

  materialized views owned by the user SYS, but you cannot enable QUERY REWRITE on such a materialized view.

  B: You cannot specify the following CREATE MATERIALIZED VIEW clauses: CACHE or NOCACHE, CLUSTER, or ON PREBUILT TABLE.

  Specify ON DEMAND to indicate that the materialized view will be refreshed on

  C:

  demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both and ON DEMAND, ON DEMAND is the default.

  ON COMMIT

  Incorrect:

  E: Materialized views can only refresh ON COMMIT in certain situations. The materialized view cannot contain object types or Oracle-supplied types. The base tables will never have any distributed transactions applied to them.

  Note:

  Oracle uses materialized views (also known as snapshots in prior releases) to *

  replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.

  * A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

• Question 65:

  The following parameter values are set for the instance:

  OPTIMIZER_CAPTURE_SQL_BASELINE = FALSE OPTIMIZER_USESQL_PLAN_BASELINE = TRUE

  The SQL plan baseline for a SQL statement contains an accepted plan.

  You want to add a new plan automatically as an accepted plan to the existing SQL plan baseline.

  Examine the following tasks.

  1.

  Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE parameter to TRUE.

  2.

  Evolve the new plan.

  3.

  Fix the existing accepted plan.

  4.

  Manually load the new plan.

  Identify the task(s) that must be performed to accomplish this.

  A. 1, 2, and 3
  B. 4 and 3
  C. 1, 4, and 3
  D. Only 4
  E. 1, 2, 4, and 3
  F. 1 and 2
  D. Only 4

  ---

  Manual Plan Loading Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.

  Note:

  *

  The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

  *

  Managing SQL plan baselines involves three phases:

  Capturing SQL Plan Baselines Selecting SQL Plan Baselines Evolving SQL Plan Baselines

  *

  Evolving SQL Plan Baselines

  Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted plans in the baseline should be accepted. As mentioned previously, manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process. When plans are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE function, which returns a CLOB reporting its results.

  Reference: SQL Plan Management in Oracle Database 11g Release 1

• Question 66:

  Identify two situations in which full table scans will be faster than index range scans.

  A. A query with a highly selective filter fetching less than 5 percent of the rows from a table.
  B. A highly selective query on a table having high clustering factor for an index.
  C. A query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.
  D. A query executing in parallel on a partitioned table with partitioned indexes.
  E. A query on a table with sparsely populated table blocks.
  C. A query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.
  D. A query executing in parallel on a partitioned table with partitioned indexes.

  ---

  D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

  Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

  Note:

  * See 6) and 7) below.

  The oracle optimizer choose the best plan and execute the query according the plan. It is common to hear that my table has indexes but why oracle does not use indexes rather it is using full table scan.

  There are several reasons behind choosing optimizer full table scans.

  1)The table has no indexes within it.

  2)Table has indexes but they are not appropriate to queries. For example in the table there is normal B- tree indexes but in the query the column used in the WHERE clause contains function.

  3)Query access large amount of data. The table has indexes but query against it select almost all of the rows. In that case optimizer might choose to full access of table.

  4)Index creation order may not appropriate. You have composite indexes on a table but in the where clause the leading column inside indexes are not used rather trailing columns are used.

  5)The table is skewed. For example column gender contains value 'M' 10,000 times but value 'F' only 10 times.

  6)The table is small. If a table can read in a single I/O call, then a full table scan might be cheaper than an index range scan. Single I/O call is defined by DB_FILE_MULTIBLOCK_READ_COUNT parameter and value defined by blocks.

  Check it by,

  SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT

  NAME TYPE VALUE

  ------------------------------------ --------------------------------- ------------------------------ db_file_multiblock_read_count integer 16

  7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans.

  8)In the query if there is no filtering then full table scan is the choice.

  If an index has poor cardinality (ie. more than 4% rows with the same index key) *

  then it will perform poorly. It will usually be faster to perform a full table scan. eg. Table SALES has an index on the column PAYMENT_METHOD which can contain values such as COD, CREDIT, CHEQUE, CASH. The statement

  SELECT *

  FROM sales

  WHERE payment_method = 'CASH'

  will probably perform so badly that you are better off without the index.

  * Oracle uses the full table scan as it assumes that it will have to read a certain part of the table. Reference: Oracle Database Reference, DB_FILE_MULTIBLOCK_READ_COUNT

• Question 67:

  Which are the two prerequisites for enabling star transformation on queries?

  A. The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or TEMP_DISABLE.
  B. A B-tree index should be built on each of the foreign key columns of the fact table(s),
  C. A bitmap index should be built on each of the primary key columns of the fact table(s).
  D. A bitmap index should be built on each of the foreign key columns of the fact table(s).
  E. A bitmap index must exist on all the columns that are used in the filter predicates of the query.
  A. The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or TEMP_DISABLE.
  E. A bitmap index must exist on all the columns that are used in the filter predicates of the query.

  ---

  A: Enabling the transformation

  E: Star transformation is essentially about adding subquery predicates corresponding to the constraint dimensions. These subquery predicates are referred to as bitmap semi-join predicates. The transformation is performed when there are indexes on the fact join columns (s.timeid, s.custid...). By driving bitmap AND and OR operations (bitmaps can be from bitmap indexes or generated from regular B-Tree indexes) of the key values supplied by the subqueries, only the relevant rows from the fact table need to be retrieved. If the filters on the dimension tables filter out a lot of data, this can be much more efficient than a full table scan on the fact table. After the relevant rows have been retrieved from the fact table, they may need to be joined back to the dimension tables, using the original predicates. In some cases, the join back can be eliminated.

  Star transformation is controlled by the star_transformation_enabled parameter. The parameter takes 3 values.

  TRUE - The Oracle optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e. the transformation is performed only if the cost of the transformed plan is lower

  than the non-transformed plan. Also the optimizer will attempt temporary table transformation automatically whenever materialization improves performance.

  FALSE - The transformation is not tried.

  TEMP_DISABLE - This value has similar behavior as TRUE except that temporary table transformation is not tried.

  The default value of the parameter is FALSE. You have to change the parameter value and create indexes on the joining columns of the fact table to take advantage of this transformation.

  Reference: Optimizer Transformations: Star Transformation

• Question 68:

  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.
  A. SCRIPT.SQL stores the statistics for all traced SWL statements.
  D. TKPROF use TEMP_PLAN_TABLE in the HR schema as a temporary plan table.

  ---

  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.

• Question 69:

  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.
  E. The rows with the ID 3 and 4 are executed simultaneously.

  ---

  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.

• Question 70:

  You are administering database that supports an OLTP workloads. Most of the queries use an in- dex range scan or index unique scan as access methods.

  Which three scenarios can prevent the index access being used by the queries?

  A. When highly selective filters is applied on an indexed column of a table with sparsely popu- lated blocks.
  B. When the rows are filtered with an IS NULL operator on the column with a unique key de- fined
  C. When the histogram statistics are not collected for the columns used in where clause.
  D. When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.
  E. When the statistics for the table are not current.
  A. When highly selective filters is applied on an indexed column of a table with sparsely popu- lated blocks.
  B. When the rows are filtered with an IS NULL operator on the column with a unique key de- fined
  E. When the statistics for the table are not current.

  ---

  A: Low clustering factor promotes good performance.

  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:

  *

  Oracle SQL not using an index is a common complaint, and it's often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to:

  *

  (E) Bad/incomplete statistics Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.

  *

  Wrong optimizer_mode The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.

  *

  Bugs See these important notes on optimizer changes in 10g that cause Oracle not to use an index.

  *

  Cost adjustment In some cases, the optimizer will still not use an index, and you must de- crease optimizer_index_cost_adj.