Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 491:

  You plan to use SQL Performance Analyzer to analyze the SQL workload. You created a SQL Tuning Set as a part of the workload capturing. What information is captured as part of this process? (Choose all that apply.)

  A. the SQL text

  B. the execution plan

  C. the execution context

  D. the execution frequency

  E. the system change number (SCN)

  Correct Answer: ACD

  http://www.oracle.com/technetwork/cn/articles/o69ocp-099954.html

  You plan to use SQL Performance Analyzer to analyze a SQL workload, and you've created a SQL tuning set as a part of the workload capture. What information is captured as part of this process?

  A. The SQL text

  B. The execution context

  C. The execution frequency

  D. The performance statistics

  The correct answers are A, B, and C. While creating the SQL tuning set, SQL Performance Analyzer captures the SQL text, the execution context, and the number of times the statement is executed. SQL Performance Analyzer then uses that information to analyze the performance of the SQL statement.

• Question 492:

  View the Exhibit to examine the Automatic SQL Tuning result details. Which action would you suggest for the selected SQL statement in the Exhibit?

  

  A. Accept the recommended SQL profile.

  B. Collect statistics for the related objects.

  C. Run the Access Advisor for the SQL statement.

  D. Run the Segment Advisor for recommendations.

  Correct Answer: C

• Question 493:

  Evaluate the following code:

  

  What is the outcome of this block of code?

  A. It creates a task and workload, and executes the task.

  B. It creates a task and workload but does not execute the task.

  C. It produces an error because a template has not been created.

  D. It produces an error because the SQL Tuning Set has not been created.

  Correct Answer: A

• Question 494:

  The SQL Tuning Advisor configuration has default settings in your database instance.

  Which recommendation is automatically implemented after the SQL Tuning Advisor is run as part of the automatic maintenance task?

  A. statistics recommendations

  B. SQL Profile recommendations

  C. Index-related recommendations

  D. restructuring of SQL recommendations

  Correct Answer: B

• Question 495:

  You want the Automatic SQL Tuning process to stop accepting and implementing the recommended SQL profiles automatically.

  Which action would you perform to achieve this?

  A. Edit the automatic maintenance window group configuration.

  B. Set the CURSOR_SHARING parameter to EXACT for the database instance.

  C. Use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set ACCEPT_SQL_PROFILES to FALSE.

  D. Set the SQLTUNE_CATEGORY parameter to DEFAULT for the database instance.

  Correct Answer: C

• Question 496:

  An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:

  SQL>CREATE INDEX ord_custname_ix ON orders (custname);

  The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause. You want to check the impact on the performance of the queries if the index is not available. You do not want the index to be dropped or rebuilt to perform this test.

  Which is the most efficient method of performing this task?

  A. disabling the index

  B. making the index invisible

  C. making the index unusable

  D. using the MONITORING USAGE clause for the index

  Correct Answer: B

  Invisible Indexes An invisible index is maintained by Oracle Database for every data manipulation language (DML) statement, but is ignored by the optimizer unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE on a session or system level. Making an index invisible is an alternative to making it unusable or dropping it. Using invisible indexes, you can:

  Test the removal of an index before dropping it Create invisible indexes temporarily for specialized, nonstandard operations, such as online application affecting the behavior of existing applications For more information, see Oracle Database Administrator's Guide.

• Question 497:

  

  Which two statements are true regarding hot patching? (Choose two.)

  A. It requires relinking of the Oracle binary.

  B. It does not require database instance shutdown.

  C. It can detect conflicts between two online patches.

  D. It is available for installing all patches on all platforms.

  E. It works only in a single database instance environment.

  Correct Answer: BC

  Online Patching Regular patches typically contain .o (object) files and/or .a (archive) libraries, and therefore require a relink of the RDBMS binary. Online patches, however, contain .so files, which are dynamic/shared libraries, and do not require a relink of the RDBMS binary. Consequently, since a relink is not needed, you can apply or roll back online patches while the RDBMS instance is running. This simplifies administration, because no downtime is needed, and also results in a much quicker turnaround time for installing or de-installing Online Patches. A regular RDBMS patch can require many minutes to install, since it requires instance shutdown, a relink, and instance startup. On the other hand, you can install an online patch in just a few seconds.

  Online patches are only applicable for Oracle RDBMS and not any other products. Online patches are currently supported on the following Windows and UNIX platforms for version 11.2.0.1.0 and later:

  Linux x86 Linux x86_64 HP-UX Itanium Solaris SPARC 64-bit Solaris AMD 64-bit AIX (AIX 6.1 and later) About Patch Conflicts:

  All patches may not be compatible with one another. For example, if you apply a patch, all the bugs the patch fixes could reappear after you apply another patch. This is called a conflict situation. OPatch detects such situations and raises an error when it detects a conflict.

• Question 498:

  View the Exhibit that sets the threshold for the Current Open Cursors Count metric.

  Why is the Significance Level threshold type not available in the threshold setting?

  Exhibit:

  

  A. because AWR baseline is not enabled

  B. because Current Open Cursors Count is not a basic metric

  C. because the STATISTICS_LEVEL parameter is set to BASIC

  D. because the AWR baseline is a system-defined moving window baseline

  Correct Answer: B

• Question 499:

  You plan to have a larger moving window size for the default system-defined moving window baseline because you want to use the adaptive threshold.

  Which statement factors in this consideration while increasing the size of the moving window?

  A. The collection level for the AWR should be set to BASIC.

  B. The moving window size must be less than Undo Retention.

  C. The moving window size should be greater than the Automatic Workload Repository (AWR) retention period.

  D. The moving window size should be equal to or less than the Automatic Workload Repository (AWR) retention period.

  Correct Answer: D

  Moving Window Baseline (link)

  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. Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8

  days.

  If you are planning to use adaptive thresholds, consider using a larger moving window--such as 30 days--to accurately compute threshold values. You can resize the moving window baseline by changing the number of days in the moving

  window to a value that is equal to or less than the number of days in the AWR retention period. Therefore, to increase the size of a moving window, you must first increase the AWR retention period accordingly.

• Question 500:

  While deploying a new application module, the software vendor ships the application software along with appropriate SQL plan baselines for the new SQLs being introduced.

  Which two statements describe the consequences? (Choose two.)

  A. The plan baselines can be evolved over time to produce better performance.

  B. The newly generated plans are directly placed into the SQL plan baseline without being verified.

  C. The new SQL statements initially run with the plans that are known to produce good performance under standard test configuration.

  D. The optimizer does not generate new plans for the SQL statements for which the SQL plan baseline has been imported.

  Correct Answer: AC

  Purpose of SQL Plan Baselines

  Common scenarios where SQL plan management can improve or preserve SQL performance include:

  A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements. Most of these plan changes result in either no performance change or improvement. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade. Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance regressions. SQL plan baselines help minimize performance regressions and stabilize SQL performance.

  Deployment of new application modules means introducing new SQL statements into the database. The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.