Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 481:

  To generate recommendations to improve the performance of a set of SQL queries in an application, you execute the following blocks of code:

  

  The blocks of code execute successfully; however, you do not get the required outcome. What could be the reason?

  A. A template needs to be associated with the task.

  B. A workload needs to be associated with the task.

  C. The partial or complete workload scope needs to be associated with the task.

  D. The type of structures (indexes, materialized views, or partitions) to be recommended need to be specified for the task.

  Correct Answer: B

• Question 482:

  To view the results of the most recent Automatic SQL Tuning Advisor task, which sequence should you follow?

  A. EM Database home page, Software and Support, SQL Advisors, Automatic SQL Tuning Advisor

  B. EM Database home page, Software and Support, Advisor Central, SQL Advisors, Automatic SQL Tuning Advisor

  C. EM Database home page, Software and Support, Support Workbench, Advisor Central, SQL Advisors, Automatic SQL Tuning Advisor

  D. Either B or C

  E. All of the above

  Correct Answer: D

• Question 483:

  View the Exhibit-1 to observe the maintenance window property. View the Exhibit-2 to examine the output of the query.

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

  EM-window (exhibit):

  

  sql-dba_autotask_client (exhibit):

  

  A. RESOURCE_PERCENTAGE should be increased.

  B. The repeat time for the window should be decreased.

  C. RESOURCE_PERCENTAGE should be decreased.

  D. The window duration should be increased.

  Correct Answer: AD

• Question 484:

  Examine the following PL/SQL block:

  

  Which statement describes the effect of the execution of the above PL/SQL block?

  A. The plan baselines are verified with the SQL profiles.

  B. All fixed plan baselines are converted into nonfixed plan baselines.

  C. All the nonaccepted SQL profiles are accepted into the plan baseline.

  D. The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines.

  Correct Answer: D

• Question 485:

  Which package provides API's for the SQL Tuning Advisor?

  A. DBMS_MONITOR

  B. DBMS_STATS

  C. DBMS_SQLTUNE

  D. DBMS_ADVISOR

  Correct Answer: C

  View the Exhibit 1.

  In the CUSTOMERS_OBE table, when the value of CUST_STATE_PROVINCE is "CA", the value of COUNTRY_ID is "US."

  View the Exhibit exhibit2 to examine the commands and query plans. The optimizer can sense 8 rows instead of 29 rows, which is the actual number of rows in the table.

  What can you do to make the optimizer detect the actual selectivity?

  desc-customers_obe (exhibit):

  

  sql-select-customers_obe (exhibit):

  

  A. Change the STALE_PERCENT value for the CUSTOMERS_OBE table.

  B. Set the STATISTICS_LEVEL parameter to TYPICAL.

  C. Create extended statistics for the CUST_STATE_PROVINCE and CUSTOMERS_OBE columns.

  D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE. Answer: C

  Real example refer to here. Determining Single Column Statistics A good example of correlated or related columns are the country_id and cust_state_province columns in the CUSTOMERS_OBE table. When the value of cust_state_province is 'CA' , the value of country_id is 'US'. There is a skew in the data in these two columns, which means the majority of rows in the table have the values 'CA' and 'US'. Both the relationship between the columns and the skew in the data make it difficult for the optimizer to calculate the selectivity or cardinality of these columns correctly when they are used together in a query. Extended statistics should help in this situation.

• Question 486:

  Which of the following advisors within the Oracle advisory framework will analyze a single SQL statement and make recommendations for performance improvement?

  A. SQL Repair Advisor

  B. SQL Optimizer

  C. SQL Access Advisor

  D. SQL Tuning Advisor

  Correct Answer: D

• Question 487:

  The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database.

  Your database is facing a series of interrelated problems over a period of two hours. You need to ensure that the ADDM analysis is run over a time span of two hours in future.

  What would you do?

  A. Create two custom ADDM tasks.

  B. Modify the AWR snapshot time interval to two hours.

  C. Create a new scheduler window for a time period of two hours.

  D. Modify the Automatic Workload Repository (AWR) snapshot retention period to two hours.

  Correct Answer: B

• Question 488:

  Which of the following identifies and creates an index to minimize the DB time for a particular SQL statement?

  A. The SGA Tuning Advisor

  B. The SQL Access Advisor

  C. The SQL Tuning Advisor

  D. The Memory Advisor

  Correct Answer: C

  The SQL Access Advisor allows the DBA to gather global recommendations for a workload. The SQL Tuning advisor is more granular, tuning a single statement. The main functions of the SQL Access advisor is to recommend missing indexes and materialized views, but a comprehensive task analysis will also create SQL Profiles that can be used within the SQL Tuning advisor. The DBA defines the SQL used in the SQL Access Advisor task, and can choose current SQL, a user-defined set of SQL, a historical workload, or a hypothetical workload.

  A hypothetical workload is very useful because the DBA need-only specify the tables that participate in the queries, and the SQL Access Advisor gathers the appropriate SQL statements to create the workload. The SQL Tuning Advisor (STA) is primarily designed to replace the manual tuning of SQL statements and speed up the overall SQL tuning process. The SQL Tuning Advisor studies poorly executing SQL statements and evaluates resource consumption in terms of CPU, I/ O, and temporary space.

  The SQL Tuning Advisor (STA) works with the Automatic Tuning Optimizer (ATO) to analyze historical SQL workload using data from the AWR, and it generates recommendations for new indexes and materialized views that will reduce the disk I/O associated with troublesome SQL statements.

  http://www.dba-oracle.com/oracle10g_tuning/t_sql_tuning_advisor.htm

• Question 489:

  What happens when you run the SQL Tuning Advisor with limited scope?

  A. Access path analysis is not performed for SQL statements.

  B. SQL structure analysis is not performed for SQL statements.

  C. SQL Profile recommendations are not generated for SQL statements.

  D. Staleness and absence of statistics are not checked for the objects in the SQL Tuning Advisor.

  Correct Answer: B

• Question 490:

  You have a very large table that your users access frequently.

  Which of the following advisors will recommend any indexes to improve the performance of queries against this table?

  A. The Automatic Memory Manager (AMM)

  B. The SQL Tuning Advisor

  C. The Segment Advisor

  D. The SQL Access Advisor

  Correct Answer: D

  The SQL Access Advisor analyzes all SQL running during a given time period and recommends indexes and materialized views to improve the overall performance of the database. A is incorrect because there is no such advisor as the Automatic Memory Manager. B is incorrect because the SQL Tuning Advisor looks only at a single SQL statement and provides recommendations.

  C is incorrect because the Segment Advisor recommends segment shrink when table and index segments are heavily fragmented.