1Z0-054Preview

After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)

You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. The following are the related parameter settings:

SQL> show parameter sga -

NAME TYPE VALUE -
------------------------------ --------------- ----------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 300M sga_target big integer 0

SQL> show parameter target -

NAME TYPE VALUE -

---

.....
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 100M
sga_target big integer 0
You want to balance the memory between the System Global Area (SGA) components within SGA without affecting the size of the Program Global Area (PGA).
Which action would solve this problem?

You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database.
On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the
SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You decided to use the ALTER TABLE...SHRINK SPACE COMPACT command to shrink the table. Why would you choose this method? (Choose all that apply.)

View the Exhibit and examine a portion of the output obtained from the following query:

SQL> SELECT * FROM v$sys_time_model;
Select three correct interpretations of the time model statistics. (Choose three.)

A batch workload that historically completed in the maintenance window between 10:00 PM and midnight is currently showing poor performance and completing at
2 AM. To help in the diagnosis of the performance degradation, the senior DBA in your organization asks you to execute the awrddrpt.sql script to produce a
Compare Periods report. Which two statements are true about the report produced by this script? (Choose two.)

You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager:
Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at
"SYS.DBMS_WORKLOAD_REPOSITORY", line 601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?

Identify two correct statements about the Active Session History (ASH) data. (Choose two.)

A user in a session executed the following SQL statement to set the optimizer mode:
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS
What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)

You are working on an online transaction processing (OLTP) system. By day most of the application users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)

Which three factors influence the optimizer's behavior while choosing an optimization approach and goal for a SQL statement? (Choose three.)

Examine the initialization parameter values for the instance given below:

NAME TYPE VALUE -
------------------------------------ ----------- ------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.1.0.6 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS db_file_multiblock_read_count integer 64
The index created on the column used in the WHERE clause of the query. You notice that the query is not using the index. Instead of an index scan, a full table scan is used.
View the Exhibit and examine the autotrace output for a query.

What could be the reason for it? (Choose all that apply.)

Examine the initialization parameter values for the instance given below:

NAME TYPE VALUE -
------------------------------------ ----------- ------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.1.0.6 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS db_file_multiblock_read_count integer 64
You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index unique scan (view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. The cost for a full table scan is more than that for an index unique scan.

Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)

View Exhibit1 to examine the description of the CUSTOMERS table.

You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.

The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table. What can you do to make the optimizer detect the actual number of rows?

View Exhibit1 to examine the description of the CUSTOMERS table.

The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice that estimated rows and the actual number of rows fetched differ greatly. The COUNTRY_ID column has an index.
View Exhibit2 and examine the query execution plan.

What would you recommend to improve the optimizer's estimation?

You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.

Why is the query using a full table scan instead of an index scan?

View Exhibit1 and examine the indexes on the CUSTOMERS table.

The statistics for the CUSTOMERS table have been updated recently by using the following command:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL
INDEXED COLUMNS SIZE AUTO');
View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan.
What could be the reason?

You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given below:
SQL> SELECT table_name ,blocks, pct_used, pct_free, num_rows
2 FROM all_tables
3* WHERE table_name = 'CUST';
TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS

---

CUST 13768 80 111060 -
What would you recommend to reduce the I/O?

Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements. You are not sure about the elapsed time that the SQL statements will have in the new system due to the version change of the optimizer. Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?

Which three statements are true about performance analysis by SQL Performance Analyzer? (Choose three.)

The database application developers are planning to make some major schema changes such as creating new indexes and materialized views. They want to check the net impact of these changes on the workload performance. This activity has to be performed in the production database, so they want only the query part of the data manipulation language (DML) statements to be executed so that the side effects to the database or user data can be prevented. What should they use to achieve this?

You upgraded database from the Oracle Database 10g to Oracle Database 11g. To test the performance SQL on the upgraded database. You want to build the before-change performance data by using SQL Performance Analyzer .Which method would allow the task to execute quickly?

You plan to collect statistics for an important table in your database to help improved query performance. You are uncertain about the optimizer behavior after the statistics have been collected because currently there are no statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collected statistics. How would you prevent the optimizer from using the newly collected statistics for other sessions immediately after statistics collection?

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regularly, but not being queried often. However statistics are being gathered for these tables automatically and regularly, thereby affecting the database performance.
What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs spend less time during the automatic maintenance task?

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the overhead in statistics collection. Identify a solution to this problem.

You observe that suboptimal execution plans for the queries are being generated on a table that previously used less resources. You have collected statistics on these tables two days ago. The optimizer statistics retention period is set to 31 days. You are able to find the timestamp information about statistics update from the DBA_TAB_STATS_HISTORY view. Because it is a frequently queried table, you would like the optimizer to generate better plans.
Which action would enable you to use the previous set of statistics on the objects that may lead to better execution plans?