Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 441:

  Which two statements about Oracle Direct Network File System (NFS) are true? (Choose two.)

  A. It bypasses the OS file system cache.

  B. A separate NFS interface is required for use across Linux, UNIX, and Windows platforms.

  C. It uses the operating system kernel NFS layer for user tasks and network communication modules.

  D. File systems need not be mounted by the kernel NFS system when being served through Direct NFS.

  E. Oracle Disk Manager can manage NFS on its own, without using the operating system kernel NFS driver.

  Correct Answer: AE

  Direct NFS provides faster performance that what can be provided by the operating system's NFS driver as Oracle bypasses the operating system and generates exactly the requests it needs (no user configuration or tuning required). Data is cached just once in user space, which saves memory (no second copy in kernel space). Performance is further improved by load balancing across multiple network interfaces (if available).

• Question 442:

  The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE. The optimizer generates a plan for a SQL statement but does not find a matching plan in the SQL plan baseline.

  Which two operations are performed by the optimizer in this scenario? (Choose two.)

  A. The optimizer adds the new plan to the plan history.

  B. The optimizer selects the new plan for the execution of the SQL statement.

  C. The optimizer adds the new plan to the SQL plan baseline as an accepted plan.

  D. The optimizer adds the new plan to the SQL plan baseline but not in the ENABLED state.

  E. The optimizer costs each of the accepted plans in the SQL plan baseline and picks the one with the lowest cost.

  Correct Answer: AE

  15.2.2 Selecting SQL Plan Baselines

  During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements.

  Each time the database compiles a SQL statement, the optimizer does the following:

  1.

  Uses a cost-based search method to build a best-cost plan

  2.

  Tries to find a matching plan in the SQL plan baseline

  3.

  Does either of the following depending on whether a match is found:

  If found, then the optimizer proceeds using the matched plan If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost The best-cost plan found by the optimizer that does not match any plans in the plan history for the SQL statement represents a new plan. The database adds this plan as a nonaccepted plan to the plan history. The database does not use the new plan until it is verified to not cause a performance regression. However, if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, then the optimizer selects the best-cost plan. Thus, the presence of a SQL plan baseline causes the optimizer to use conservative plan selection strategy for the SQL statement.

  To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE (default).

  A SQL plan baseline contains one or more accepted plans. The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time, the plans in the baseline form a subset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.

  

• Question 443:

  What recommendations does the SQL Access Advisor provide for optimizing SQL queries? (Choose all that apply.)

  A. selection of SQL plan baselines

  B. partitioning of tables and indexes

  C. creation of index-organized tables

  D. creation of bitmap, function-based, and B-tree indexes

  E. optimization of materialized views for maximum query usage and fast refresh

  Correct Answer: BDE

  Refer to here.

  SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions, and indexes for a given workload. SQL Access Advisor index recommendations include

  bitmap, function-based, and B-tree indexes. SQL Access Advisor materialized view recommendations include fast refreshable and full refreshable MVs, for either general rewrite or exact text match rewrite.

• Question 444:

  Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version. After changing the OPTIMIZER_FEATURES_ENABLE parameter to 10.2.0.4 and running the SQL Performance Analyzer, you observed performance regression a few SQL statements.

  What would you do with these SQL statements?

  A. Set OPTIMIZER_USE_PLAN_BASELINES to FALSE to prevent the use of regressed plans.

  B. Capture the plans from the previous version using STS and then load them into the stored outline.

  C. Capture the plans from the previous version using STS and then load them into SQL Management Base (SMB).

  D. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE to prevent the plans from being loaded to the SQL plan baseline.

  Correct Answer: C

  The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace.

  It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

  Parameters Relating to Stored Outline Migration:

  OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, Enables or disables the automatic recognition of repeatable SQL statement and the generation of SQL plan baselines for these statements.

  OPTIMIZER_USE_SQL_PLAN_BASELINES, Enables or disables the use of SQL plan baselines stored in SQL Management Base.

• Question 445:

  Examine the following PL/SQL block:

  DECLARE

  my_plans PLS_INTEGER;

  BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');

  END;

  /

  Which statement is true about the plan being loaded into the SQL plan baseline by the above command?

  A. It is loaded with the FIXED status.

  B. It is loaded with the ACCEPTED status.

  C. It is not loaded with the ENABLED status.

  D. It is not loaded with the ACCEPTED status.

  Correct Answer: B

  You can create SQL plan baselines by manually loading existing plans for a set of SQL statements as plan baselines. The database does not verify manually loaded plans for performance, but adds them as accepted plans to existing or new SQL plan baselines. (Refer to here 15.2.1.2 Creating Baselines from Existing Plans) The DBMS_SPM package supports the SQL plan management feature by providing an interface for the DBA or other user to perform controlled manipulation of plan history and SQL plan baselines maintained for various SQL statements.

  LOAD_PLANS_FROM_CURSOR_CACHE Functions, This function loads one or more plans present in the cursor cache for a SQL statement, or a set of SQL statements. It has four overloads: using SQL statement text, using SQL handle, using SQL ID, or using attribute_name and attribute_value pair.

  By default, the parameter of LOAD_PLANS_FROM_CURSOR_CACHE has:

  1.

  fixed = 'NO', means loaded with non-fixed plans.

  2.

  enabled = 'YES', means that it is enabled for use by the optimizer.

  Refer to here for details.

• Question 446:

  Which two prerequisites are needed for performing workload capture and replay? (Choose two.)

  A. Close all sessions performing queries using database links.

  B. running the database in shared server mode

  C. The database on which the workload is replayed has to be a restore of the original database to a specific SCN.

  D. setting up the directory to capture the workload

  Correct Answer: CD

• Question 447:

  You plan to set up the Automatic Workload Repository (AWR) baseline metric thresholds for a moving window baseline.

  Which action would you take before performing this task?

  A. Compute the baseline statistics.

  B. Take an immediate AWR snapshot.

  C. Decrease the window size for the baseline.

  D. Decrease the expiration time for the baseline.

  Correct Answer: A

  A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.

• Question 448:

  Which two changes and their effect on the system can be tested by using the Database Replay feature? (Choose two.)

  A. multiplexing of the control file

  B. adding the redo log member to the database

  C. database and operating system upgrades

  D. changing the database storage to ASM-managed storage

  Correct Answer: CD

• Question 449:

  Which is the source used by Automatic SQL Tuning that runs as part of the AUTOTASK framework?

  A. SQL statements that are part of the AWR baseline only

  B. SQL statements based on the AWR top SQL identification

  C. SQL statements that are part of the available SQL Tuning Set (STS) only

  D. SQL statements that are available in the cursor cache and executed by a user other than SYS

  Correct Answer: B

• Question 450:

  What two statements are true regarding the recommendations received from the SQL Access Advisor? (Choose two.)

  A. It cannot generate recommendations that support multiple workload queries.

  B. It can recommend partitioning on tables provided that the workloads have some predicates and joins on the columns of the NUMBER or DATE type.

  C. It can recommend partitioning only on tables that have at least 10,000 rows.

  D. It can recommend only B-tree indexes and not bitmap or function-based indexes.

  Correct Answer: BC