Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 431:

  The following command is executed to create the baseline template:

  SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE

  ( START_TIME=> TO_TIESTAMP(SYSDATE+2),

  END_TIME=>TO_TIMESTAMP(SYSDATE+10),

  BASELINE_NAME=>'Mybase4',

  TEMPLATE_NAME=>'Mytemp4',

  EXPIRATION=>NULL);

  Which statement is true about the effect of the above command?

  A. It creates a baseline template that never expires.

  B. It produces an error because no snapshot information is provided to create the baseline.

  C. It creates a baseline template that expires after the Automatic Workload Repository (AWR) retention period.

  D. It creates a repeating baseline template that repeats after the Automatic Workload Repository (AWR) retention period.

  Correct Answer: A

• Question 432:

  Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance window? (Choose all that apply.)

  A. generating the SQL profile

  B. testing and accepting the SQL profile

  C. generating a list of candidate SQLs for tuning

  D. adding tuned SQL plans into the SQL plan baseline

  E. tuning each SQL statement in the order of importance

  F. generating baselines that include candidate SQLs for tuning

  Correct Answer: ABCE

  17.2.1 How Automatic SQL Tuning Works (link)

  Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week. After automatic SQL tuning begins, the database performs the following steps:

  1.

  Identifies SQL candidates in the AWR for tuning

  Oracle Database analyzes statistics in AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the database.

  The database tunes only SQL statements that have an execution plan with a high potential for improvement. The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL,

  and SQL statements with performance problems caused by concurrency issues.The database orders the SQL statements that are selected as candidates based on their performance impact. The database calculates the impact by summing

  the CPU time and the I/O times in AWR for the selected statement in the past week.

  2.

  Tunes each SQL statement individually by calling SQL Tuning Advisor During the tuning process, the database considers and reports all recommendation types, but it can implement only SQL profiles automatically.

  3.

  Tests SQL profiles by executing the SQL statement

  If a SQL profile is recommended, the database tests the new profile by executing the SQL statement both with and without the profile. If the performance improvement improves at least threefold, then the database accepts the SQL profile, but

  only if the ACCEPT_SQL_PROFILES task parameter is set to TRUE. Otherwise, the automatic SQL tuning reports merely report the recommendation to create a SQL profile.

  4.

  Optionally, implements the SQL profiles provided they meet the criteria of threefold performance improvement The database considers other factors when deciding whether to implement the SQL profile.

  For example, the database does not implement a profile when the objects referenced in the statement have stale optimizer statistics. SQL profiles that have been implemented automatically show type is AUTO in the DBA_SQL_PROFILES view. If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan baseline when creating the SQL profile. As a result, the optimizer uses the new plan immediately after profile creation. See Chapter 15, "Using SQL Plan Management". At any time during or after the automatic SQL tuning process, you can view the results using the automatic SQL tuning report. This report describes in detail all the SQL statements that were analyzed, the recommendations generated, and the SQL profiles that were automatically implemented.

  

• Question 433:

  The SQL Tuning Advisor has been configured with default configurations in your database instance.

  Which recommendation is automatically implemented without the DBA's intervention after the SQL Tuning Advisor is run as part of the AUTOTASK framework?

  A. statistics recommendations

  B. SQL profile recommendations

  C. index-related recommendations

  D. restructuring of SQL recommendations

  Correct Answer: B

• Question 434:

  Which statements are true regarding the system-defined moving window baseline in Oracle Database 11g? (Choose all that apply.)

  A. It does not allow you to change the moving window size.

  B. Adaptive threshold functionalities use it by default to compute statistics.

  C. It is created by default with the window size being equal to the AWR retention time.

  D. It is created when the first snapshot is collected by the Automatic Workload Repository (AWR).

  Correct Answer: BC

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

  Which tasks are run automatically as part of the Automated Maintenance Task by default? (Choose all that apply.)

  A. Segment Advisor

  B. SQL Access Advisor

  C. Optimizer statistics gathering

  D. Automatic SQL Tuning Advisor

  E. Automatic Database Diagnostics Monitor

  Correct Answer: ACD

  About Automated Maintenance Tasks (link)

  Oracle Database has three predefined automated maintenance tasks:

  Automatic Optimizer Statistics Collection--Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to

  improve the performance of SQL execution.

  See Also:

  Oracle Database Performance Tuning Guide for more information on automatic statistics collection

  Automatic Segment Advisor--Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments. You can also run the Segment Advisor manually to obtain more up-to-theminute recommendations or to obtain recommendations on segments that the Automatic Segment Advisor did not examine for possible space reclamation.

  See Also:

  "Using the Segment Advisor" for more information.

  Automatic SQL Tuning Advisor--Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements. You can configure this advisor to automatically implement SQL profile

  recommendations.

  See Also:

  Oracle Database Performance Tuning Guide for more information on SQL Tuning Advisor By default, all three automated maintenance tasks are configured to run in all maintenance windows.

• Question 436:

  Which two statements about the SQL Management Base (SMB) are true? (Choose two.)

  A. It contains only SQL profiles generated by SQL Tuning Advisor.

  B. It stores plans generated by the optimizer using a stored outline.

  C. It is part of the data dictionary and stored in the SYSAUX tablespace.

  D. It is part of the data dictionary and stored in the SYSTEM tablespace.

  E. It contains the statement log, the plan history, plan baselines, and SQL profiles.

  Correct Answer: CE

• Question 437:

  Which client requests to the database can be captured as a part of the workload capture? (Choose all that apply.)

  A. flashback query

  B. distributed transactions

  C. logging in and logging out of sessions

  D. all DDL statements having bind variables

  E. direct path load of data from external files

  Correct Answer: CD

• Question 438:

  In Oracle 11g, which recommendations does the SQL Access Advisor generate? (Choose all that apply.)

  A. partitioning recommendations

  B. statistics collection recommendations

  C. index creation recommendations

  D. materialized view recommendations

  E. materialized view log recommendations

  Correct Answer: ACDE

  18.1 Overview of SQL Access Advisor

  Materialized views, partitions, and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. 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. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant. In particular, partitioning of an unpartitioned base table is a complex operation that must be planned carefully.

• Question 439:

  You run the SQL Tuning Advisor (STA) to tune a SQL statement that is part of a fixed SQL plan baseline. The STA generates a SQL profile for the SQL statement, which recommends that you accept the profile. Which statement is true when you accept the suggested SQL profile?

  A. The tuned plan is not added to the SQL plan baseline.

  B. The tuned plan is added to the fixed SQL plan baseline as a fixed plan.

  C. The tuned plan is added to the fixed SQL plan baseline as a nonfixed plan.

  D. The tuned plan is added to a new nonfixed SQL plan baseline as a nonfixed plan.

  Correct Answer: C

  15.4 Using Fixed SQL Plan Baselines (Refer to here)

  When you tune a SQL statement with a fixed SQL plan baseline using SQL Tuning Advisor, a SQL profile recommendation has special meaning. When the SQL profile is accepted, the database adds the tuned plan to the fixed SQL plan baseline as a non-fixed plan. However, as described above, the optimizer does not use the tuned plan when a reproducible fixed plan is present. Therefore, the benefit of SQL tuning may not be realized. To enable the use of the tuned plan, manually alter the tuned plan to a fixed plan by setting its FIXED attribute to YES.

• Question 440:

  Which three are the valid statements in relation to SQL plan baselines? (Choose three.)

  A. The plans can be manually loaded to the SQL plan baseline.

  B. The plans in the SQL plan baseline are verified and accepted plans.

  C. The plans generated for every SQL statement are stored in the SQL plan baseline by default.

  D. The plan baselines are stored temporarily in the memory as long as the database instance is running.

  E. For the SQL plan baselines to be accessible to the optimizer, the SYSAUX tablespace must be online.

  Correct Answer: ABE

  The SQL management base (SMB), which is part of the data dictionary, stores the SQL plan baselines and plan history in the SYSAUX tablespace. The SMB also contains SQL profiles. The SMB uses automatic space management.

  Capturing SQL Plan Baselines (refer to here).

  You can configure the SQL Plan Baseline Capture phase for automatic capture of plan history and SQL plan baselines for repeatable SQL statements. Alternatively, you can manually load plans as SQL plan baselines.

  Not all the new generated SQL plan can be accepted, so that all SQL plan stored in the plan history, only after evolving the plans in the plan history, it becomes in the plan baseline. You can evolve an existing SQL plan baseline by manually

  loading plans from the shared SQL area or from a SQL tuning set. When you manually load plans into a SQL plan baseline, the database adds these loaded plans as accepted plans.